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
