def insert_td_data_direct(table, ts_data):
if len(ts_data) > 0:
for i in range(0, len(ts_data)):
insert_sql = 'insert into ' + table + ' values(\'' + ts_data.symbol[
i] + '\',\'' + dt.strftime(
ts_data.bob[i], '%Y-%m-%d %H:%M:%S') + '\',' + str(
round(ts_data.open[i], 3)
) + ',' + str(round(ts_data.high[i], 3)) + ',' + str(
round(ts_data.low[i], 3)) + ',' + str(
round(ts_data.close[i], 3)) + ',' + str(
round(ts_data.volume[i], 0)) + ',' + str(
round(ts_data.amount[i], 0)) + ');'
try:
fill_data(insert_sql)
except Exception as e:
pass
def update_info():
orders = get_order()
trades = get_trade()
balance = get_balance()
pos = get_pos()
current = dt.now().strftime('%Y-%m-%d %H:%M:%S')
# insert balance info
update_act_info = 'insert into act_info values(\'' + current + '\',' + str(balance['总资产']) + ',' + str(balance['股票市值']) + ',' + str(balance['可用金额'])+ ',' + str(balance['冻结资金'])+ ',' + str(balance['可取金额']) + ');'
try:
fill_data(update_act_info)
except Exception as e:
print(e)
print(update_act_info)
# insert orders info
if len(orders) > 0:
for i in range(0, len(orders)):
update_act_order = 'insert into act_order values(' + orders['合同编号'].iloc[i] + ',\'' + dt.isoformat(dt.strptime(str(orders['委托日期'].iloc[i]),'%Y%m%d'))[:10] + ' ' + str(orders['委托时间'].iloc[i]) + '\',\'' + orders['操作'].iloc[i] + '\',\'' + orders['备注'].iloc[i] + '\',\'' + orders['证券代码'].iloc[i] + '\',\'' + orders['证券名称'].iloc[i] + '\','+ str(orders['委托价格'].iloc[i]) + ','+ str(orders['委托数量'].iloc[i]) + ','+ str(orders['成交均价'].iloc[i]) + ','+ str(orders['成交数量'].iloc[i]) + ');'
try:
fill_data(update_act_order)
except Exception as e:
print(e)
print(update_act_order)
# insert trades info
if len(trades) > 0 :
for j in range(0, len(trades)):
update_act_td = 'insert into act_td values(' + str(trades['成交编号'].iloc[j]) + ',' + trades['合同编号'].iloc[j] + ',\'' + dt.isoformat(dt.strptime(str(trades['成交日期'].iloc[j]),'%Y%m%d'))[:10] + '\',\'' + trades['操作'].iloc[j] + '\',\'' + trades['证券代码'].iloc[j] + '\',\'' + trades['证券名称'].iloc[j] + '\','+ str(trades['成交均价'].iloc[j]) + ','+ str(trades['成交数量'].iloc[j]) + ','+ str(trades['成交金额'].iloc[j]) + ');'
try:
fill_data(update_act_td)
except Exception as e:
print(e)
print(update_act_td)
# update position info
del_all = 'delete from act_pos'
fill_data(del_all)
if len(pos) > 0:
for k in range(0, len(pos)):
update_act_pos = 'insert into act_pos values(\'' + pos['证券代码'].iloc[k] + '\',\'' + pos['证券名称'].iloc[k] + '\',' + str(pos['股票余额'].iloc[k]) + ',' + str(pos['可用余额'].iloc[k]) + ',' + str(pos['市价'].iloc[k]) + ','+ str(pos['市值'].iloc[k]) + ','+ str(round(pos['成本价'].iloc[k],3)) + ','+ str(round(pos['保本价'].iloc[k],3))+ ','+ str(pos['盈亏比(%)'].iloc[k])+ ','+ str(round(pos['盈亏'].iloc[k],3)) + ');'
try:
fill_data(update_act_pos)
except Exception as e:
print(e)
print(update_act_pos)
def update_calc_data(symbol_list):
for symbol in symbol_list:
df_1h = get_1h_data(symbol) # 获取1h数据
if len(df_1h) == 0:
continue
ts_data = calc_cci(df_1h) # 计算指标
if len(ts_data) > 0:
# last_dict = get_last('td_cci', ts_data)
for i in range(len(ts_data) - 4, len(ts_data)):
# if ts_data['datetime'].iloc[i] <= last_dict[ts_data.symbol.iloc[i]] or ts_data.symbol.iloc[i] not in last_dict.keys() or ts_data['datetime'].iloc[i][11:16] not in ['09:30','10:30','13:00','14:00']:
# continue
insert_sql = 'insert into td_cci values(' + str(
list(ts_data.iloc[i])).replace('[', '').replace(']',
'') + ');'
try:
fill_data(insert_sql)
except Exception as e:
print(e)
print(insert_sql)
def insert_td_data(table, ts_data):
if len(ts_data) > 0:
# bob_dict = dict(list(zip(list(ts_data.symbol), list(ts_data.bob))))
# last_dict = get_last(table, ts_data)
for i in range(0, len(ts_data)):
# if dt.strftime(bob_dict[ts_data.symbol[i]],'%Y-%m-%d %H:%M:%S') <= last_dict[ts_data.symbol[i]] or ts_data.symbol[i] not in last_dict.keys():
# continue
insert_sql = 'insert into ' + table + ' values(\'' + ts_data.symbol[
i] + '\',\'' + dt.strftime(
ts_data.bob[i], '%Y-%m-%d %H:%M:%S') + '\',' + str(
round(ts_data.open[i], 3)
) + ',' + str(round(ts_data.high[i], 3)) + ',' + str(
round(ts_data.low[i], 3)) + ',' + str(
round(ts_data.close[i], 3)) + ',' + str(
round(ts_data.volume[i], 0)) + ',' + str(
round(ts_data.amount[i], 0)) + ');'
try:
fill_data(insert_sql)
except Exception as e:
print(e)
print(insert_sql)