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 里面