def get_table_list(mysql_handler: MySqlHandler, content: Content) -> List:
"""
通过前缀名，从数据库获取对应的数据表名称列表
"""
mysql_handler.set_cursor_type(Cursor)
tables = mysql_handler.get_tables(content.value, False)
tables = [item[0] for item in tables]
mysql_handler.set_cursor_type(DictCursor)
return tables
def create_table(mysql_handler: MySqlHandler, content: Content) -> None:
"""
通过前缀名创建数据库表格
content: 'setting' or 'data' or 'follow_data'
"""
create_func = content_map[content]['create_func']
fields_list = create_func()
for (table_name, field_dict) in fields_list:
mysql_handler.create_table(table_name, field_dict)
logger.info(f"{content.value}：数据表创建成功")
def clear_server_data(mysql_handler: MySqlHandler, content: Content):
"""
清除数据库数据
"""
tables = get_table_list(mysql_handler, content)
if tables:
for table in tables:
mysql_handler.drop_table(table)
logger.info(f"{content.value}所有数据表删除成功")
else:
logger.info(f"{content.value}没有数据表")
def cta_data_to_server(mysql_handler: MySqlHandler) -> None:
"""
推送本地cta数据到数据库
"""
modified_time = get_file_modified_time(CTA_DATA_FILENAME)
for strategy_name, data in cta_data.items():
row = dict()
row['strategy_name'] = strategy_name
row['last_modified_time'] = modified_time
row.update(data)
table_name = strategy_to_table_name(strategy_name, Content.CTA_DATA)
# delete first.
mysql_handler.delete(table_name, get_cond_dict(strategy_name))
# insert
mysql_handler.insert(table_name, row)
logger.info(f"策略{strategy_name}：数据同步到远程成功")
def get_server_modified_time(mysql_handler: MySqlHandler,
content: Content) -> datetime:
"""获取数据库数据最新修改时间"""
tables = get_table_list(mysql_handler, content)
table_name = tables[0]
server_record = mysql_handler.query_all(table_name,
order_field='last_modified_time',
order_by='DESC')
server_time = server_record[0]['last_modified_time']
return server_time
def cta_setting_to_server(mysql_handler: MySqlHandler) -> None:
"""
推送本地cta配置到数据库
"""
modified_time = get_file_modified_time(CTA_SETTING_FILENAME)
for strategy_name, settings in cta_settings.items():
row = dict()
row['strategy_name'] = strategy_name
row['class_name'] = settings['class_name']
row['vt_symbol'] = settings['vt_symbol']
row['last_modified_time'] = modified_time
row.update(settings['setting'])
table_name = strategy_to_table_name(strategy_name, Content.CTA_SETTING)
# print(table_name)
# delete first.
mysql_handler.delete(table_name, get_cond_dict(strategy_name))
# insert
mysql_handler.insert(table_name, row)
logger.info(f"策略{strategy_name}：配置同步到远程成功")
def follow_data_from_server(mysql_handler: MySqlHandler) -> dict:
"""
从数据库获取跟随数据
"""
server_data = dict()
# sync ids
ids_list = mysql_handler.query_all('follow_data_trade_ids')
# print(ids_list)
if ids_list[0]['follow_id'] == 'empty':
ids_dict = dict()
else:
ids_dict = defaultdict(list)
for row_dict in ids_list:
follow_id = row_dict['follow_id']
order_id = row_dict['order_id']
ids_dict[follow_id].append(order_id)
# print(ids_dict)
# sync pos
pos_list = mysql_handler.query_all('follow_data_positions')
# print(pos_list)
if pos_list[0]['vt_symbol'] == 'empty':
pos_dict = dict()
else:
pos_dict = dict()
for row_dict in pos_list:
vt_symbol = row_dict['vt_symbol']
pos_dict[vt_symbol] = dict()
for key in ['vt_symbol', 'last_modified_time']:
row_dict.pop(key)
pos_dict[vt_symbol].update(row_dict)
# print(pos_dict)
server_data['tradeid_orderids_dict'] = ids_dict
server_data['positions'] = pos_dict
# print(server_data)
return server_data
def cta_data_from_server(mysql_handler: MySqlHandler) -> dict:
"""
从数据库获取cta数据
"""
tables = get_table_list(mysql_handler, Content.CTA_DATA)
server_data = dict()
for table in tables:
table_res = mysql_handler.query_all(table)
# print(table_res)
for row_dict in table_res:
strategy_name = row_dict['strategy_name']
server_data[strategy_name] = dict()
for key in ['strategy_name', 'last_modified_time']:
row_dict.pop(key)
server_data[strategy_name].update(row_dict)
# print(server_data)
return server_data
def cta_setting_from_server(mysql_handler: MySqlHandler) -> dict:
"""
从数据库获取cta配置
"""
tables = get_table_list(mysql_handler, Content.CTA_SETTING)
server_settings = dict()
for table in tables:
table_res = mysql_handler.query_all(table)
# print(table_res)
for row_dict in table_res:
strategy_name = row_dict['strategy_name']
server_settings[strategy_name] = dict()
temp = server_settings[strategy_name]
temp['class_name'] = row_dict['class_name']
temp['vt_symbol'] = row_dict['vt_symbol']
for key in ['vt_symbol', 'strategy_name', 'last_modified_time']:
row_dict.pop(key)
temp['setting'] = row_dict
# print(server_settings)
return server_settings
def main():
logger.info(f"连接数据库 {mysql_setting['host']}:{mysql_setting['port']}")
mysql = MySqlHandler()
mysql.connect(**mysql_setting)
logger.info("数据库连接等待...")
for i in reversed(range(5)):
logger.info(f"等待倒计时:{i}")
sleep(1)
try:
init()
# for content in [Content.CTA_SETTING, Content.CTA_DATA, Content.FOLLOW_DATA]:
# sync(mysql, content)
sync(mysql, Content.FOLLOW_DATA)
except:
traceback.print_exc()
mysql.close_db()
from sync.sync_script import (init, sync,
get_strategy_to_class_name, strategy_to_table_name,
strategy_to_class_name_map, content_map,
get_cta_data_fields, get_cta_setting_fields, get_follow_data_fields,
create_table, get_table_list, clear_server_data,
is_local_exist, is_remote_exist, get_local_modified_time, get_server_modified_time,
cta_setting_to_server, cta_data_to_server, follow_data_to_server,
cta_setting_from_server, cta_data_from_server, follow_data_from_server)
mysql_setting.update(load_json(MYSQL_SETTING_FILENAME))
mysql_setting['host'] = "192.168.0.107"
print(mysql_setting)
init()
mysql = MySqlHandler()
mysql.connect(**mysql_setting)
print("数据库连接等待时间...")
sleep(3)
print("数据库连接等待结束")
class TestUtility(unittest.TestCase):
def setUp(self) -> None:
pass
def test_to_dash_format(self):
print(to_dash_format('_dfHeoo'))
print(to_dash_format('NormalWorld'))
print(to_dash_format('HHello'))
def follow_data_to_server(mysql_handler: MySqlHandler) -> None:
"""
推送跟随数据到数据库
"""
modified_time = get_file_modified_time(FOLLOW_DATA_FILENAME)
# sync trade ids
id_table_name = 'follow_data_trade_ids'
mysql_handler.delete_all(id_table_name)
trade_ids = follow_data['tradeid_orderids_dict']
if not trade_ids:
row = dict()
row['follow_id'] = 'empty'
row['order_id'] = 'empty'
row['last_modified_time'] = modified_time
mysql_handler.insert(id_table_name, row)
else:
for trade_id, order_list in trade_ids.items():
row = dict()
row['follow_id'] = trade_id
row['last_modified_time'] = modified_time
for order in order_list:
row['order_id'] = order
mysql_handler.insert(id_table_name, row)
logger.info(f"跟随单ID：数据同步到远程成功")
# sync positions
pos_table_name = 'follow_data_positions'
mysql_handler.delete_all(pos_table_name)
positions = follow_data['positions']
if not positions:
row = dict()
row['source_long'] = 0
row['source_short'] = 0
row['target_long'] = 0
row['target_short'] = 0
row['vt_symbol'] = 'empty'
row['last_modified_time'] = modified_time
mysql_handler.insert(pos_table_name, row)
else:
for vt_symbol, pos_dict in positions.items():
row = dict()
row['vt_symbol'] = vt_symbol
row['last_modified_time'] = modified_time
row.update(pos_dict)
# insert
mysql_handler.insert(pos_table_name, row)
logger.info(f"跟随单仓位：数据同步到远程成功")
def is_remote_exist(mysql_handler: MySqlHandler, content: Content) -> bool:
"""
远程数据是否存在
"""
return mysql_handler.is_table_exists(content.value, precise=False)