def get_account_balance(stg_run_id):
"""
获取 account_info 账户走势数据
:param stg_run_id:
:return:
"""
with with_db_session(engine_ibats) as session:
sql_str = str(
session.query(
func.concat(
AccountStatusInfo.trade_date, ' ',
AccountStatusInfo.trade_time).label('trade_datetime'),
AccountStatusInfo.available_cash.label('available_cash'),
AccountStatusInfo.curr_margin.label('curr_margin'),
AccountStatusInfo.balance_tot.label('balance_tot')).filter(
AccountStatusInfo.stg_run_id == stg_run_id).order_by(
AccountStatusInfo.trade_date,
AccountStatusInfo.trade_time))
# sql_str = """SELECT concat(trade_date, " ", trade_time) trade_datetime, available_cash, curr_margin, balance_tot
# FROM account_status_info where stg_run_id=%s order by trade_date, trade_time"""
data_df = pd.read_sql(sql_str, engine_ibats, params=[' ', stg_run_id])
data_df["return_rate"] = (data_df["balance_tot"].pct_change().fillna(0) +
1).cumprod()
data_df = data_df.set_index("trade_datetime")
return data_df
def _save_md(self, data_dic_list, symbol, model_tot: MDMin1,
model_tmp: MDMin1Temp):
"""
保存md数据到数据库及文件
:param data_dic_list:
:param symbol:
:param model_tot:
:param model_tmp:
:return:
"""
if data_dic_list is None:
self.logger.warning("data_dic_list 为空")
return
md_count = len(data_dic_list)
if md_count == 0:
self.logger.warning("data_dic_list len is 0")
return
table_name = model_tot.__tablename__
# 保存到数据库
with with_db_session(engine_md) as session:
try:
# 插入数据到临时表
session.execute(
model_tmp.__table__.insert(on_duplicate_key_update=True),
data_dic_list)
self.logger.info('%d 条 %s 历史数据 -> %s 完成', md_count, symbol,
table_name)
# 将临时表数据导入总表
sql_str = f"""insert into `{table_name}`
select * from `{model_tmp.__tablename__}` where symbol=:symbol ON DUPLICATE KEY UPDATE
open=VALUES(open), high=VALUES(high), low=VALUES(low), close=VALUES(close),
trades=VALUES(trades), volume=VALUES(volume), vwap=VALUES(vwap), lastSize=VALUES(lastSize),
turnover=VALUES(turnover), homeNotional=VALUES(homeNotional),
foreignNotional=VALUES(foreignNotional)"""
session.execute(sql_str, params={"symbol": symbol})
# 删除临时表数据，仅保留最新的一条记录
datetime_latest = session.query(
func.max(model_tmp.timestamp).label('timestamp')).filter(
model_tmp.symbol == symbol).scalar()
# issue:
# https://stackoverflow.com/questions/9882358/how-to-delete-rows-from-a-table-using-an-sqlalchemy-query-without-orm
delete_count = session.query(model_tmp).filter(
model_tmp.symbol == symbol,
model_tmp.timestamp < datetime_latest).delete()
self.logger.debug('%d 条 %s 历史数据被清理，最新数据日期 %s', delete_count,
symbol, datetime_latest)
session.commit()
except:
self.logger.exception('%d 条 %s 数据-> %s 失败', md_count, symbol,
table_name)
def connect(self):
if instrument_info_table is None:
raise EnvironmentError("instrument_info_table 为 None，请先加载 dynamic_load_table_model 再重新 import")
with with_db_session(engine_md) as session:
data = session.query(
instrument_info_table.columns['symbol'],
instrument_info_table.columns['underlying'],
instrument_info_table.columns['tickSize'],
).filter(instrument_info_table.c.state == 'open').all()
self.symbol_currency_dic = {
row[0]: row[1]
for row in data}
self.symbol_precision_dic = {
# row[0]: (int(sym.price_precision), int(sym.amount_precision))
row[0]: row[2]
for row in data}
def _save_md(self, data_dic_list, symbol, model_tot: MDMin1, model_tmp: MDMin1Temp, log_header=''):
"""
保存md数据到数据库及文件
:param data_dic_list:
:param symbol:
:param model_tot:
:param model_tmp:
:return:
"""
if data_dic_list is None or len(data_dic_list) == 0:
self.logger.warning("data_dic_list 为空")
return
md_count = len(data_dic_list)
# 保存到数据库
with with_db_session(engine_md) as session:
try:
# session.execute(self.md_orm_table_insert, data_dic_list)
session.execute(model_tmp.__table__.insert(on_duplicate_key_update=True), data_dic_list)
self.logger.info('%d 条 %s 历史数据 -> %s 完成', md_count, symbol, model_tmp.__tablename__)
sql_str = f"""insert into {model_tot.__tablename__} select * from {model_tmp.__tablename__}
where market=:market and symbol=:symbol
ON DUPLICATE KEY UPDATE open=VALUES(open), high=VALUES(high), low=VALUES(low), close=VALUES(close)
, amount=VALUES(amount), vol=VALUES(vol), count=VALUES(count)"""
session.execute(sql_str, params={"symbol": symbol, "market": config.MARKET_NAME})
datetime_latest = session.query(
func.max(model_tmp.ts_start).label('ts_start_latest')
).filter(
model_tmp.symbol == symbol,
model_tmp.market == config.MARKET_NAME
).scalar()
# issue:
# https://stackoverflow.com/questions/9882358/how-to-delete-rows-from-a-table-using-an-sqlalchemy-query-without-orm
delete_count = session.query(model_tmp).filter(
model_tmp.market == config.MARKET_NAME,
model_tmp.symbol == symbol,
model_tmp.ts_start < datetime_latest
).delete()
self.logger.debug('%s %d 条 %s 历史数据被清理，最新数据日期 %s',
log_header, delete_count, symbol, datetime_latest)
session.commit()
except:
self.logger.exception('%s %d 条 %s 数据-> %s 失败',
log_header, md_count, symbol, model_tot.__tablename__)
def init(alter_table=False):
BaseModel.metadata.create_all(engine_md)
if alter_table:
with with_db_session(engine=engine_md) as session:
for table_name, _ in BaseModel.metadata.tables.items():
sql_str = f"show table status from {config.DB_SCHEMA_MD} where name=:table_name"
row_data = session.execute(sql_str,
params={
'table_name': table_name
}).first()
if row_data is None:
continue
if row_data[1].lower() == 'myisam':
continue
logger.info('修改 %s 表引擎为 MyISAM', table_name)
sql_str = "ALTER TABLE %s ENGINE = MyISAM" % table_name
session.execute(sql_str)
logger.info("所有表结构建立完成")
def dynamic_load_table_model():
"""
动态加载数据库中所有表model，保存到 table_model_dic
:return:
"""
logger.info('动态加载数据库 %s 的表', config.DB_SCHEMA_MD)
metadata = MetaData(engine_md)
sql_str = "select TABLE_NAME from information_schema.TABLES where table_schema=:table_schema"
with with_db_session(engine_md) as session:
table_name_list = [
row[0]
for row in session.execute(sql_str,
params={
'table_schema': config.DB_SCHEMA_MD
}).fetchall()
]
table_name_list_len = len(table_name_list)
for num, table_name in enumerate(table_name_list, start=1):
model = Table(table_name, metadata, autoload=True)
table_model_dic[table_name] = model
logger.debug('%d/%d) load table %s', num, table_name_list_len,
table_name)
def fill_history_period(self, period, model_tot: BaseModel,
model_tmp: BaseModel):
"""
根据数据库中的支持 symbol 补充历史数据
api.Trade.Trade_get(symbol='XBTUSD').result()
:param period:
:param model_tot:
:param model_tmp:
:return:
"""
if period not in {'1m', '5m', '1h', '1d'}:
logger.error(f'{period} 不是有效的周期')
raise ValueError(f'{period} 不是有效的周期')
# 查找有效的 symbol
table_name = 'bitmex_instrument'
sql_str = f"select symbol from {table_name} where state='Open'"
with with_db_session(engine_md) as session:
symbol_set = set(
[row[0] for row in session.execute(sql_str).fetchall()])
pair_datetime_latest_dic = dict(
session.query(model_tmp.symbol, func.max(
model_tmp.timestamp)).group_by(model_tmp.symbol).all())
# 循环获取每一个交易对的历史数据
symbol_set_len = len(symbol_set)
for num, symbol in enumerate(symbol_set):
if not self.is_working:
break
if symbol in pair_datetime_latest_dic:
datetime_latest = pair_datetime_latest_dic[symbol]
datetime_start = datetime_latest + timedelta(minutes=1)
# 设置 startTime
if period == '1m':
size = int(
(datetime.now() - datetime_start).total_seconds() / 60)
elif period == '5m':
size = int(
(datetime.now() - datetime_start).total_seconds() /
300)
elif period == '1h':
size = int(
(datetime.now() - datetime_start).total_seconds() /
3600)
else: # if period == '1d':
size = (datetime.now() - datetime_start).days
# start_time = datetime_2_str(datetime_start, '%Y-%m-%d %H:%M:%S')
# start_time = datetime_start
else:
size = 500
datetime_start = None
if size <= 0:
continue
ret_list = self.get_kline(symbol,
period,
start_time=datetime_start)
if ret_list is None or len(ret_list) == 0:
continue
ret_list_len = len(ret_list)
for data_dic in ret_list:
data_dic['timestamp'] = datetime_2_str(data_dic['timestamp'])
# data_count = bunch_insert_on_duplicate_update(ret_df, model_tmp.__tablename__, engine_md)
self._save_md(ret_list, symbol, model_tot, model_tmp)
logger.info('%d/%d) %s %s 更新 %d 数据成功', num, symbol_set_len, symbol,
period, ret_list_len)
def init(alter_table=False):
logger.info(engine_md)
BaseModel.metadata.create_all(engine_md)
if alter_table:
with with_db_session(engine=engine_md) as session:
show_status_sql_str = f"show table status from {config.DB_SCHEMA_MD} where name=:table_name"
for table_name, _ in BaseModel.metadata.tables.items():
row_data = session.execute(show_status_sql_str,
params={
'table_name': table_name
}).first()
if row_data is None:
continue
if row_data[1].lower() == 'myisam':
continue
logger.info('修改 %s 表引擎为 MyISAM', table_name)
sql_str = "ALTER TABLE %s ENGINE = MyISAM" % table_name
session.execute(sql_str)
sql_str = f"""select table_name from information_schema.columns
where table_schema = :table_schema and column_name = 'ts_start' and extra <> ''"""
table_name_list = [
row_data[0] for row_data in session.execute(
sql_str, params={'table_schema': config.DB_SCHEMA_MD})
]
for table_name in table_name_list:
logger.info('修改 %s 表 ts_start 默认值，剔除 on update 默认项',
table_name)
# TimeStamp 类型的数据会被自动设置 default: 'CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP'
# 需要将 “on update CURRENT_TIMESTAMP”剔除，否则在执行更新时可能会引起错误
session.execute(
f"ALTER TABLE {table_name} CHANGE COLUMN `ts_start` `ts_start` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP"
)
# This is an issue https://www.mail-archive.com/[email protected]/msg19744.html
session.execute(
f"ALTER TABLE {SymbolPair.__tablename__} CHANGE COLUMN `id` `id` INT(11) NULL AUTO_INCREMENT"
)
session.commit()
# This is an issue https://www.mail-archive.com/[email protected]/msg19744.html
session.execute(
f"ALTER TABLE {MDTick.__tablename__} CHANGE COLUMN `id` `id` INT(11) NULL AUTO_INCREMENT"
)
session.commit()
# 关于 MDTick 表，由于 partition的原因，改变需要在索引方面做一些调整，这里暂未对其进行程序化处理
# 需要对原来的旧表进行相应的改变处理，大体的语句如下：
# """
# ALTER TABLE `md_min1_tick_bc`
# ADD COLUMN `ts_date` DATE NOT NULL AFTER `symbol`,
# DROP PRIMARY KEY,
# ADD PRIMARY KEY (`market`, `symbol`, `ts_date`, `ts_curr`),
# DROP INDEX `id` ,
# ADD UNIQUE INDEX `id` (`id` ASC, `ts_date` ASC);
# """
# 另外，表分区语句如下
# """
# ALTER TABLE `bc_md`.`md_min1_tick_bc`
# PARTITION BY RANGE(TO_DAYS(ts_date)) (
# PARTITION part1 VALUES LESS THAN (TO_DAYS('2018-08-01')),
# PARTITION part2 VALUES LESS THAN (TO_DAYS('2018-09-01')),
# PARTITION part3 VALUES LESS THAN (TO_DAYS('2018-10-01')),
# PARTITION part4 VALUES LESS THAN (TO_DAYS('2018-11-01')),
# PARTITION part5 VALUES LESS THAN (TO_DAYS('2018-12-01')),
# PARTITION part6 VALUES LESS THAN (TO_DAYS('2019-01-01')),
# PARTITION part7 VALUES LESS THAN (TO_DAYS('2019-02-01')),
# PARTITION part8 VALUES LESS THAN (TO_DAYS('2019-03-01')),
# PARTITION part9 VALUES LESS THAN (TO_DAYS('2019-04-01')),
# PARTITION part10 VALUES LESS THAN (TO_DAYS('2019-05-01')),
# PARTITION part11 VALUES LESS THAN (TO_DAYS('2019-06-01')),
# PARTITION part12 VALUES LESS THAN (TO_DAYS('2019-07-01')),
# PARTITION part13 VALUES LESS THAN (TO_DAYS('2019-08-01')),
# PARTITION part14 VALUES LESS THAN (TO_DAYS('2019-09-01')),
# PARTITION part15 VALUES LESS THAN (TO_DAYS('2019-10-01')),
# PARTITION part16 VALUES LESS THAN (TO_DAYS('2019-11-01')),
# PARTITION part17 VALUES LESS THAN (TO_DAYS('2019-12-01')),
# PARTITION part18 VALUES LESS THAN (MAXVALUE)
# ) ;
# """
logger.info("所有表结构建立完成")
AccountStatusInfo.curr_margin.label('curr_margin'),
AccountStatusInfo.balance_tot.label('balance_tot')).filter(
AccountStatusInfo.stg_run_id == stg_run_id).order_by(
AccountStatusInfo.trade_date,
AccountStatusInfo.trade_time))
# sql_str = """SELECT concat(trade_date, " ", trade_time) trade_datetime, available_cash, curr_margin, balance_tot
# FROM account_status_info where stg_run_id=%s order by trade_date, trade_time"""
data_df = pd.read_sql(sql_str, engine_ibats, params=[' ', stg_run_id])
data_df["return_rate"] = (data_df["balance_tot"].pct_change().fillna(0) +
1).cumprod()
data_df = data_df.set_index("trade_datetime")
return data_df
if __name__ == "__main__":
with with_db_session(engine_ibats) as session:
# stg_run_id = session.execute("select max(stg_run_id) from stg_run_info").fetchone()[0]
stg_run_id = session.query(func.max(StgRunInfo.stg_run_id)).scalar()
# stg_run_id = 2
data_df = get_account_balance(stg_run_id)
logger.info("\n%s", data_df)
data_df.plot(
ylim=[min(data_df["available_cash"]),
max(data_df["balance_tot"])])
data_df.plot(
ylim=[min(data_df["curr_margin"]),
max(data_df["curr_margin"])])
stat_df = return_risk_analysis(data_df[['return_rate']], freq=None)
logger.info("\n%s", stat_df)
plt.show()
def load_history(self, date_from=None, date_to=None, load_md_count=None) -> (pd.DataFrame, dict):
"""
从mysql中加载历史数据
实时行情推送时进行合并后供数据分析使用
:param date_from: None代表沿用类的 init_md_date_from 属性
:param date_to: None代表沿用类的 init_md_date_from 属性
:param load_md_count: 0 代表不限制，None代表沿用类的 init_load_md_count 属性，其他数字代表相应的最大加载条数
:return: md_df 或者
ret_data {
'md_df': md_df, 'datetime_key': 'ts_start',
'date_key': **, 'time_key': **, 'microseconds_key': **
}
"""
# 如果 init_md_date_from 以及 init_md_date_to 为空，则不加载历史数据
if self.init_md_date_from is None and self.init_md_date_to is None:
ret_data = {'md_df': None, 'datetime_key': 'timestamp'}
return ret_data
if self.md_period not in period_model_dic:
raise ValueError('%s error' % self.md_period)
# 将sql 语句形势改成由 sqlalchemy 进行sql 拼装方式
# sql_str = """select * from md_min_1
# where InstrumentID in ('j1801') and tradingday>='2017-08-14'
# order by ActionDay, ActionTime, ActionMillisec limit 200"""
# sql_str = """SELECT * FROM md_min_1
# WHERE InstrumentID IN (%s) %s
# ORDER BY ActionDay DESC, ActionTime DESC %s"""
model = period_model_dic[self.md_period]
with with_db_session(engine_md) as session:
sub_query = session.query(
model.symbol.label('symbol'), model.timestamp.label('timestamp'),
model.open.label('open'), model.high.label('high'),
model.low.label('low'), model.close.label('close'),
model.volume.label('volume'), model.turnover.label('turnover'), model.trades.label('trades')
).filter(
model.symbol.in_(self.instrument_id_list)
).order_by(model.timestamp.desc())
# 设置参数
params = list(self.instrument_id_list)
# date_from 起始日期
if date_from is None:
date_from = self.init_md_date_from
if date_from is not None:
# qry_str_date_from = " and tradingday>='%s'" % date_from
sub_query = sub_query.filter(model.timestamp >= date_from)
params.append(date_from)
# date_to 截止日期
if date_to is None:
date_to = self.init_md_date_to
if date_to is not None:
# qry_str_date_to = " and tradingday<='%s'" % date_to
sub_query = sub_query.filter(model.timestamp <= date_to)
params.append(date_to)
# load_limit 最大记录数
if load_md_count is None:
load_md_count = self.init_load_md_count
if load_md_count is not None and load_md_count > 0:
# qry_str_limit = " limit %d" % load_md_count
sub_query = sub_query.limite(load_md_count)
params.append(load_md_count)
sub_query = sub_query.subquery('t')
query = session.query(
sub_query.c.symbol.label('symbol'), sub_query.c.timestamp.label('timestamp'),
sub_query.c.open.label('open'), sub_query.c.high.label('high'),
sub_query.c.low.label('low'), sub_query.c.close.label('close'),
sub_query.c.volume.label('volume'), sub_query.c.turnover.label('turnover'),
sub_query.c.trades.label('trades')
).order_by(sub_query.c.timestamp)
sql_str = str(query)
# 合约列表
# qry_str_inst_list = "'" + "', '".join(self.instrument_id_list) + "'"
# 拼接sql
# qry_sql_str = sql_str % (qry_str_inst_list, qry_str_date_from + qry_str_date_to, qry_str_limit)
# 加载历史数据
self.logger.debug("%s on:\n%s", params, sql_str)
md_df = pd.read_sql(sql_str, engine_md, params=params)
# self.md_df = md_df
ret_data = {'md_df': md_df, 'datetime_key': 'timestamp', 'symbol_key': 'symbol', 'close_key': 'close'}
return ret_data
def init(self, periods=['1min', '60min', '1day'], symbol_partition_set={'main', 'innovation', 'bifurcation'}):
"""
初始化，订阅行情
默认1分钟、1小时、1日
包含 {'main', 'innovation', 'bifurcation'} 全部币种
:param periods:
:param symbol_partition_set:
:return:
"""
if self.init_symbols:
# 获取有效的交易对信息保存（更新）数据库
ret = self.api.get_symbols()
key_mapping = {
'base-currency': 'base_currency',
'quote-currency': 'quote_currency',
'price-precision': 'price_precision',
'amount-precision': 'amount_precision',
'symbol-partition': 'symbol_partition',
}
# 获取支持的交易对
data_dic_list = []
for d in ret['data']:
d['market'] = config.MARKET_NAME # 'huobi'
data_dic_list.append({key_mapping.setdefault(k, k): v for k, v in d.items()})
with with_db_session(engine_md) as session:
session.execute(SymbolPair.__table__.insert(on_duplicate_key_update=True), data_dic_list)
available_pairs = [d['base_currency'] + d['quote_currency']
for d in data_dic_list if d['symbol_partition'] in symbol_partition_set]
# 通过 on_open 方式进行订阅总是无法成功
for pair, period in itertools.product(available_pairs, periods):
self.hb.sub_dict[pair+period] = {'id': '', 'topic': f'market.{pair}.kline.{period}'}
else:
self.hb.sub_dict['ethbtc60'] = {'id': '', 'topic': 'market.ethbtc.kline.60min'}
# self.hb.sub_dict['ethusdt'] = {'id': '', 'topic': 'market.ethusdt.kline.1min'}
self.hb.sub_dict['ethusdt60'] = {'id': '', 'topic': 'market.ethusdt.kline.60min'}
# handler = SimpleHandler('simple handler')
if config.DB_HANDLER_ENABLE:
# Tick 数据插入
period = '1min'
handler = DBHandler4Tick(period=period, db_model=MDTick, save_tick=True)
self.hb.register_handler(handler)
logger.info("注册 %s 处理句柄 period='%s'", handler.name, period)
time.sleep(1)
# 其他周期数据插入
for period in periods:
save_tick = False
if period == '1min':
db_model = MDMin1
elif period == '60min':
db_model = MDMin60
# save_tick = True
elif period == '1day':
db_model = MDMinDaily
else:
self.logger.warning(f'{period} 不是有效的周期')
continue
handler = DBHandler(period=period, db_model=db_model, save_tick=save_tick)
self.hb.register_handler(handler)
logger.info("注册 %s 处理句柄 period='%s'", handler.name, period)
time.sleep(1)
# 数据redis广播
if config.REDIS_PUBLISHER_HANDLER_ENABLE and check_redis():
handler = PublishHandler(market=config.MARKET_NAME)
self.hb.register_handler(handler)
logger.info('注册 %s 处理句柄', handler.name)
# Heart Beat
self.hb.register_handler(self.heart_beat)
logger.info('注册 %s 处理句柄', self.heart_beat.name)
server_datetime = self.get_server_datetime()
logger.info("api.服务期时间 %s 与本地时间差： %f 秒",
server_datetime, (datetime.now() - server_datetime).total_seconds())
self.check_state()
def fill_history_period(self, period, model_tot, model_tmp: MDMin1Temp):
"""
根据数据库中的支持 symbol 补充历史数据
:param period:
:param model_tot:
:param model_tmp:
:return:
"""
with with_db_session(engine_md) as session:
data = session.query(SymbolPair).filter(
SymbolPair.market == config.MARKET_NAME).all() # , SymbolPair.symbol_partition == 'main'
pair_datetime_latest_dic = dict(
session.query(
model_tmp.symbol, func.max(model_tmp.ts_start)
).filter(model_tmp.market == config.MARKET_NAME).group_by(model_tmp.symbol).all()
)
# 循环获取每一个交易对的历史数据
data_count = len(data)
for num, symbol_info in enumerate(data, start=1):
symbol = f'{symbol_info.base_currency}{symbol_info.quote_currency}'
if symbol in pair_datetime_latest_dic:
datetime_latest = pair_datetime_latest_dic[symbol]
if period == '1min':
second_of_period = 60
elif period == '60min':
second_of_period = 60 * 60
elif period == '1day':
second_of_period = 60 * 60 * 24
else:
self.logger.warning(f'{period} 不是有效的周期')
continue
size = min([2000, int((datetime.now() - datetime_latest).seconds / second_of_period * 1.2)])
else:
size = 2000
if size <= 0:
continue
ret = self.get_kline(symbol, period, size=size)
# for n in range(1, 3):
# try:
# ret = self.api.get_kline(symbol, period, size=size)
# except ProxyError:
# self.logger.exception('symbol:%s, period:%s, size=%d', symbol, period, size)
# ret = None
# time.sleep(5)
# continue
# break
if ret is None:
continue
if ret['status'] == 'ok':
data_list = ret['data']
data_dic_list = []
for data in data_list:
ts_start = datetime.fromtimestamp(data.pop('id'))
data['ts_start'] = ts_start
data['market'] = config.MARKET_NAME
data['ts_curr'] = ts_start + timedelta(seconds=59) # , microseconds=999999
data['symbol'] = symbol
data_dic_list.append(data)
self._save_md(data_dic_list, symbol, model_tot, model_tmp, log_header=f"{num}/{data_count}")
else:
self.logger.error("get_kline(symbol='%s', period='%s', size='%d') got error:%s",
symbol, period, size, ret)
# 过于频繁方位可能导致链接失败
time.sleep(5) # 已经包含在 try_n_times 里面