def store_statement(db_table, dir_path, hsfa_constr, trade_constr): data_list = [] parentAccPath = dir_path + '/对账单/' kingNewPath = dir_path + '/金牛对账单/' # 读取中信期货对账单 pbills = ParentBillList(parentAccPath) bills = list(pbills.getAllPBills()) for a_bill in bills: data_list.append(a_bill.get_accsum()) # 读取金牛对账单 cbills = ChildBillList(kingNewPath) kn_bills = list(cbills.getAllBills()) for a_bill in kn_bills: data_list.append(a_bill.get_accsum()) db_man = dbmanager.DBManager(source_constr=trade_constr, des_constr=hsfa_constr) ans = db_man.insert_direct(tablename=db_table, data_list=data_list) print(ans) return ans
def data_sync_v3(db_table, excel_path, hsfa_constr, trade_constr, vertical=True, delete_old=True): df_input = None # vertical means the headers is located vertically if vertical: df_input = reader.SecFileReader.read_vertical(path=excel_path, header=db_table) else: df_input = reader.SecFileReader.read(path=excel_path, header=db_table) db_man = dbmanager.DBManager(source_constr=trade_constr, des_constr=hsfa_constr) reportdate = str(df_input['REPORT_DATE'].iloc[0]) if delete_old: ans = db_man.delete_report_date(constr=trade_constr, tablename=db_table, t_date=reportdate) ans = None ans = db_man.insert_sou_db(tablename=db_table, data=df_input) return ans
def data_sync_v2(db_table, excel_path, hsfa_constr, task_db_table, trade_constr, vertical=True, delete_old=True): # df_input = reader.file_reader.read(path=excel_path, header=mconfig[db_table.lower()]) df_input = None # vertical means the headers is located vertically if vertical: df_input = reader.SecFileReader.read_vertical(path=excel_path, header=db_table) else: df_input = reader.SecFileReader.read(path=excel_path, header=db_table) db_man = dbmanager.DBManager(source_constr=trade_constr, des_constr=hsfa_constr) reportdate = str(df_input['REPORT_DATE'].iloc[0]) if delete_old: # 删除同步表所有数据,但是本地数据库表只删除同一个持仓日期的数据 # ans = db_man.delete_sou_table(tablename=task_db_table) ans = db_man.delete_report_date(constr=trade_constr, tablename=db_table, t_date=reportdate) ans = db_man.insert_sou_db(tablename=db_table, data=df_input) print(ans) # ans = db_man.dbsync(tablename=task_db_table) return ans
def store_position_v2(excel_path, hsfa_constr, trade_constr, vertical=True, delete_old=False, position_table=None, del_date_list=None): df_input = None # vertical means the headers is located vertically if vertical: df_input = reader.SecFileReader.read_vertical(path=excel_path, header=position_table) else: df_input = reader.SecFileReader.read(path=excel_path, header=position_table) # print(df_input) # print(excel_path) temp_df = df_input['TRADING_DAY'] # print(temp_df) df_input['REPORT_DATE'] = temp_df.apply(lambda x: x.strftime('%Y%m%d')) df_input['REPORT_DATE'] = df_input['REPORT_DATE'].astype('int') tmp = df_input['REPORT_DATE'] reportdate = int(df_input['REPORT_DATE'].iloc[0]) # print(df_input) db_man = dbmanager.DBManager(source_constr=trade_constr, des_constr=hsfa_constr) if del_date_list: if reportdate not in del_date_list: ans = db_man.delete_report_date(constr=trade_constr, tablename=position_table, t_date=reportdate) else: ans = db_man.delete_report_date(constr=trade_constr, tablename=position_table, t_date=reportdate) # print(reportdate) # if delete_old: # # # 删除同步表所有数据,但是本地数据库表只删除同一个持仓日期的数据 # # report_date TRADING_DAY 不一样 # # 删除持仓表和其他的表格不一样不能直接删除,因为持仓分几个文件导入,如果删除也把之前导入的持仓也删除了。必须 # # 在导入持仓之前统一删除。 # ans = db_man.delete_report_date(constr=trade_constr, tablename=position_table, t_date=reportdate) ans = db_man.insert_sou_db(tablename=position_table, data=df_input) if ans: print(excel_path + ' insert success! ') else: print(excel_path + ' insert fail! ') return reportdate
def sync_qiquanzuoshi(date=None): trade_constr = 'oracle://*****:*****@10.21.68.206:1521/trade' hsfa_constr = 'oracle://*****:*****@10.21.68.211:1521/hsfa' db_man = dbmanager.DBManager(source_constr=trade_constr, des_constr=hsfa_constr) # ans = db_man.sync_qiquanzuoshi(t_date=date) # def sync_qiquanzuoshi_general(self, t_date=None, table_position = 'POSITION', table_qiquanzuoshi = 'G_OPTION_DEALER', table_task = 'T_OWN_INVEST_DERIV'): ans = db_man.sync_qiquanzuoshi_general(t_date=date) return ans
def delete_citicsf_tables(hsfa_constr=None, task_db_table=None, trade_constr=None): db_man = dbmanager.DBManager(source_constr=trade_constr, des_constr=hsfa_constr) if hsfa_constr: ans = db_man.delete_sou_table(tablename=task_db_table) if trade_constr != hsfa_constr: ans = db_man.delete_des_table(tablename=task_db_table) return
def store_position_v2(excel_path, hsfa_constr, trade_constr, vertical=True, delete_old=False, position_table=None, del_date_list=None): df_input = None # vertical means the headers is located vertically if vertical: df_input = reader.SecFileReader.read_vertical(path=excel_path, header=position_table) else: df_input = reader.SecFileReader.read(path=excel_path, header=position_table) # print(df_input) # print(excel_path) temp_df = df_input['TRADING_DAY'] # print(temp_df) try: df_input['REPORT_DATE'] = temp_df.apply(lambda x: x.strftime('%Y%m%d')) except Exception as e: print(e) df_input['REPORT_DATE'] = df_input['REPORT_DATE'].astype('int') tmp = df_input['REPORT_DATE'] reportdate = int(df_input['REPORT_DATE'].iloc[0]) # print(df_input) db_man = dbmanager.DBManager(source_constr=trade_constr, des_constr=hsfa_constr) if del_date_list: if reportdate not in del_date_list: ans = db_man.delete_report_date(constr=trade_constr, tablename=position_table, t_date=reportdate) else: ans = db_man.delete_report_date(constr=trade_constr, tablename=position_table, t_date=reportdate) ans = db_man.insert_sou_db(tablename=position_table, data=df_input) if ans: print(excel_path + ' insert success! ') else: print(excel_path + ' insert fail! ') return reportdate
def store_position(excel_path, hsfa_constr, trade_constr, vertical=True, delete_old=False): # df_input = reader.file_reader.read(path=excel_path, header=mconfig[db_table.lower()]) # 通过将当日数据导入临时持仓表TEMP_POSITION, 然后通过TEMP_POSITION与期权做市组合 position_table = 'POSITION' temp_position_table = 'TEMP_POSITION' df_input = None # vertical means the headers is located vertically if vertical: df_input = reader.SecFileReader.read_vertical(path=excel_path, header=position_table) else: df_input = reader.SecFileReader.read(path=excel_path, header=position_table) # print(df_input) df_input['REPORT_DATE'] = df_input['TRADING_DAY'] df_input['REPORT_DATE'] = df_input['REPORT_DATE'].apply( lambda x: int(x.strftime('%Y%m%d'))) # print(df_input) db_man = dbmanager.DBManager(source_constr=trade_constr, des_constr=hsfa_constr) # tmp = df_input['TRADING_DAY'].iloc[0].strftime('%Y%m%d') # print(tmp) reportdate = df_input['TRADING_DAY'].iloc[0] # print(reportdate) if delete_old: # 删除同步表所有数据,但是本地数据库表只删除同一个持仓日期的数据 # report_date TRADING_DAY 不一样 # 删除持仓表和其他的表格不一样不能直接删除,因为持仓分几个文件导入,如果删除也把之前导入的持仓也删除了。必须 # 在导入持仓之前统一删除。 ans = db_man.delete_report_date(constr=trade_constr, tablename=position_table, t_date=reportdate) # ans = db_man.delete_sou_table(tablename=temp_position_table) ans = db_man.insert_sou_db(tablename=position_table, data=df_input) if ans: print(ans) else: print(excel_path + ' insert fail! ') reportdate = reportdate.strftime('%Y%m%d') return reportdate
def sync_citicsf_tables(hsfa_constr, task_db_table, trade_constr): db_man = dbmanager.DBManager(source_constr=trade_constr, des_constr=hsfa_constr) ans = db_man.dbsync(tablename=task_db_table) return ans