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 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('%d 条 %s 历史数据被清理，最新数据日期 %s', delete_count,
symbol, datetime_latest)
session.commit()
except:
self.logger.exception('%d 条 %s 数据-> %s 失败', 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:
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()
logger.info("所有表结构建立完成")
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 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('%d 条 %s 历史数据被清理，最新数据日期 %s', delete_count, symbol, datetime_latest)
session.commit()
except:
self.logger.exception('%d 条 %s 数据保存到 %s 失败', md_count, symbol, model_tot.__tablename__)
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.ENABLE_DB_HANDLER:
# Tick 数据插入
handler = DBHandler(period='1min', db_model=MDTick, save_tick=True)
self.hb.register_handler(handler)
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 处理句柄', handler.name)
time.sleep(1)
# 数据redis广播
if Config.ENABLE_REDIS_HANDLER 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())
# 循环获取每一个交易对的历史数据
for symbol_info in data:
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 <= 1:
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)
else:
self.logger.error(ret)
# 过于频繁方位可能导致链接失败
time.sleep(5) # 已经包含在 try_n_times 里面
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'] = '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')
# Tick 数据插入
handler = DBHandler(period='1min', db_model=MDTick, save_tick=True)
self.hb.register_handler(handler)
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)
time.sleep(1)
# 数据redis广播
handler = PublishHandler(market=Config.MARKET_NAME)
self.hb.register_handler(handler)
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()
)
# 循环获取每一个交易对的历史数据
for symbol_info in data:
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 <= 1:
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)
else:
self.logger.error(ret)
# 过于频繁方位可能导致链接失败
time.sleep(5) # 已经包含在 try_n_times 里面