def gen(max):
global t, df
t = 0
while t <= max:
try:
d = json.loads(getPlainText(lastdate, t, 'S'))
# for k, v in d.items():
# print(k, v)
data = d['data']
fields = d['fields']
date = d['date'][0:4] + '-' + d['date'][4:6] + '-' + d['date'][6:]
df = DataFrame(data, columns=fields).replace(',', '', regex=True).replace('--', 'NaN').replace('', 'NaN')
df.insert(0, '年月日', date)
df.insert(len(list(df)), '第幾筆', 1)
df['年月日'] = to_datetime(df['年月日']).astype(str)
intColumns = ['第幾筆']
floatColumns = []
for col in ['成交價', '成交股數', '成交金額', '成交量']:
if col in list(df):
floatColumns.append(col)
df[floatColumns] = df[floatColumns].astype(float)
df[intColumns] = df[intColumns].astype(int)
df = df.groupby(['年月日', '證券代號']).apply(f1)
yield t, sqlCommand.insertData(tablename, df, connLite)
t = t + 1
except Exception as e:
print('Exception: ', e)
pass
sql = "SELECT * FROM '{}'"
df = read_sql_query(sql.format('每日收盤行情(全部(不含權證、牛熊證))'), connLite).replace('--', 'NaN')
df['漲跌(+/-)'] = df['漲跌(+/-)'].replace('－', -1).replace('＋', 1).replace('X', 'NaN').replace(' ', '0').fillna('NaN')
df['年月日'] = to_datetime(df['年月日']).astype(str)
floatColumns = ['成交股數', '成交筆數', '成交金額', '開盤價', '最高價', '最低價', '收盤價', '漲跌(+/-)', '漲跌價差', '最後揭示買價', '最後揭示買量', '最後揭示賣價', '最後揭示賣量', '本益比']
intColumns = []
df[intColumns] = df[intColumns].astype(int)
df[floatColumns] = df[floatColumns].astype(float)
df.dtypes
tablename_new = tablename + '0'
sqlCommand.renameTable(tablename, tablename_new, connLite)
columns = list(df)
primaryKeys = ['年月日', '證券代號']
sqlCommand.createTable(tablename, columns, primaryKeys, connLite)
sqlCommand.insertData(tablename, df, connLite)
sqlCommand.dropTable(tablename_new, connLite)
tablename = '個股日本益比、殖利率及股價淨值比'
sql = "SELECT * FROM '{}'"
df = read_sql_query(sql.format(tablename), connLite).replace('--', 'NaN').fillna('NaN')
print(df)
df['本益比'] = df['本益比'].replace('-', 'NaN')
df['股價淨值比'] = df['股價淨值比'].replace('-', 'NaN')
df['年月日'] = to_datetime(df['年月日']).astype(str)
floatColumns = ['本益比', '殖利率(%)', '股價淨值比']
# for col in floatColumns:
# print(col)
# df[col].astype(float)