目录
写在前面
本文默认已经创建了项目,如果不知道如何创建一个空项目的,请参看以下两篇博文
PyQt5将项目搬到一个新的虚拟环境中
https://blog.csdn.net/m0_37967652/article/details/122625280
python_PyQt5开发工具结构基础
https://blog.csdn.net/m0_37967652/article/details/131969032
前序:
【期货日数据维护与使用_日数据维护_界面代码】博文
【期货日数据维护与使用_日数据维护_sqlite3数据库创建】 博文
下载最新合约列表
df = DataAPI.FutuGet(secID=u"",ticker=u"",exchangeCD=u"XSGE",contractStatus="",contractObject=u"",prodID="",field=u"",pandas="1")
df.to_csv('futures_XSGE.csv',encoding='utf-8')
df = DataAPI.FutuGet(secID=u"",ticker=u"",exchangeCD=u"XDCE",contractStatus="",contractObject=u"",prodID="",field=u"",pandas="1")
df.to_csv('futures_XDCE.csv',encoding='utf-8')
df = DataAPI.FutuGet(secID=u"",ticker=u"",exchangeCD=u"XZCE",contractStatus="",contractObject=u"",prodID="",field=u"",pandas="1")
df.to_csv('futures_XZCE.csv',encoding='utf-8')
df = DataAPI.FutuGet(secID=u"",ticker=u"",exchangeCD=u"CCFX",contractStatus="",contractObject=u"",prodID="",field=u"",pandas="1")
df.to_csv('futures_CCFX.csv',encoding='utf-8')
df = DataAPI.FutuGet(secID=u"",ticker=u"",exchangeCD=u"XSIE",contractStatus="",contractObject=u"",prodID="",field=u"",pandas="1")
df.to_csv('futures_XSIE.csv',encoding='utf-8')
从优矿中下载的文件字段列表为:
['secID','listDate','secFullName','secShortName','ticker','exchangeCD','contractType','contractObject','priceUnit','minChgPriceNum','minChgPriceUnit','priceValidDecimal','limitUpNum','limitUpUnit','limitDownNum','limitDownUnit','transCurrCD','contMultNum','contMultUnit','tradeMarginRatio','deliYear','deliMonth','lastTradeDate','firstDeliDate','lastDeliDate','deliMethod','deliGrade','tradeCommiNum','tradeCommiUnit','deliCommiNum','deliCommiUnit','listBasisPrice','settPriceMethod','deliPriceMethod','contractStatus','prodID']
想知道字段含义,可以自行到优矿网站查看
“选择合约所在目录”按钮点击
def choice_symbol_dir_btn_clicked(self):
path = QtWidgets.QFileDialog.getExistingDirectory(
self,
'选择合约所在目录',
SQLITE_FROM_DIR
)
if not path:
return
self.choice_symbol_dir_lineedit.setText(path)
pass
定义 SQLITE_FROM_DIR 常量:./tools/sqlite_from_dir/
“执行”按钮点击
更新合约的过程在子线程中进行,任务表示定义为 self.mark_str_step_one 常量,常量定义在 init_data()方法中
更新合约逻辑:
1 将下载所得的最新合约列表文件合并获取到“最新的合约列表”
2 工具中现存的合约都在 t_symbol_basemsg 中
3 将“最新的合约列表”与 t_symbol_basemsg 中的合约进行比对,筛出“新增的合约”
4 工具中现存的在线合约都在 t_online_symbol 中,在线合约即为“待更新日数据的合约”
至此,获得要下载日数据的合约列表。
sqlite3代码
在 sqlite_tool.py 文件中创建 query_all_ticker_deliYear_from_symbol_basemsg 方法,从 t_symbol_basemsg 表格中获取所有的合约名和交割年份
def query_all_ticker_deliYear_from_symbol_basemsg():
conn = sqlite3.connect(YOUKUANG_DB_NAME)
c = conn.cursor()
# s
sql_str = '''
select ticker,deliYear from t_symbol_basemsg
'''
c.execute(sql_str)
res_list = c.fetchall()
# e
conn.commit()
conn.close()
return res_list
pass
在 sqlite_tool.py 文件中创建 query_all_from_online_symbol 方法,从 t_online_symbol 表格中获取所有在线合约数据
def query_all_from_online_symbol():
conn = sqlite3.connect(YOUKUANG_DB_NAME)
c = conn.cursor()
# s
sql_str = '''
select product_code,ticker,deliYear,newest_date from t_online_symbol
'''
c.execute(sql_str)
res_list = c.fetchall()
# e
conn.commit()
conn.close()
return res_list
按钮点击后执行的代码
def excute_step_one_btn_clicked(self):
dir_path = self.choice_symbol_dir_lineedit.text()
if len(dir_path) <= 0:
QtWidgets.QMessageBox.information(
self,
'提示',
'请选择合约所在目录',
QtWidgets.QMessageBox.Yes
)
return
pre_map = {
'dir_path': dir_path,
'cur_deliYear': self.deliYear_spinbox.value()
}
self.start_caculate_thread(self.mark_str_step_one, pre_map)
pass
子线程代码
子线程中任务名为 self.mark_str_step_one 的执行代码,于 running_caculate_thread 方法中对应任务名下
dir_path = data['dir_path']
cur_deliYear = data['cur_deliYear']
exist_tickers_list = sqlite_tool.query_all_ticker_deliYear_from_symbol_basemsg()
exist_temp_tickers_list = []
for item in exist_tickers_list:
exist_temp_tickers_list.append(f"{item[0]}_{item[1]}")
pass
file_list = os.listdir(dir_path)
# 逐一读取合约文件,筛出新增的合约,合并到一个df中
new_df = pd.DataFrame()
for item in file_list:
file_path = dir_path + os.path.sep + item
df = pd.read_csv(file_path)
df['temp_ticker'] = df['ticker'].astype('str').str.cat(df['deliYear'].astype('str'),'_')
one_df = df.loc[(~df['temp_ticker'].isin(exist_temp_tickers_list)) & (df['deliYear']>=cur_deliYear)].copy()
if len(one_df)>0:
new_df = pd.concat([new_df,one_df])
pass
# 将新增的合约插入到 t_symbol_basemsg 中,并导出到 new.json文件
if len(new_df)>0:
pre_list = new_df.loc[:,self.symbol_basemsg_column_list].values.tolist()
sqlite_tool.batch_insert_symbol_basemsg(pre_list)
self.thread_out_log(f"新增合约{len(pre_list)}")
new_json = new_df['ticker'].values.tolist()
with open(SQLITE_TO_DIR + 'new.json','w',encoding='utf-8') as fw:
json.dump(new_json,fw)
pass
# 追加的ticker列表
self.thread_out_log(f"开始计算要追加的ticker列表:")
online_tickers = sqlite_tool.query_all_from_online_symbol()
df_online = pd.DataFrame(columns=self.online_symbol_column_list,data=online_tickers)
df_group = df_online.groupby(by='newest_date')
target_json_dir = SQLITE_TO_DIR + 'append' + os.path.sep
if not os.path.exists(target_json_dir):
os.mkdir(target_json_dir)
for name,group in df_group:
name_str = name.replace('-','')
append_json = group['ticker'].values.tolist()
target_file_path = target_json_dir + name_str + '.json'
with open(target_file_path,'w',encoding='utf-8') as fw:
json.dump(append_json,fw)
pre_map = {
"mark_str":self.mark_str_step_one,
"data":None
}
self.signal_excute.emit(pre_map)
pass
“新增合约json下载”按钮点击
def new_symbol_json_down_btn_clicked(self):
new_json_path = SQLITE_TO_DIR + 'new.json'
if not os.path.exists(new_json_path):
QtWidgets.QMessageBox.information(
self,
'提示',
'没有新增的合约',
QtWidgets.QMessageBox.Yes
)
return
path = QtWidgets.QFileDialog.getExistingDirectory(
self,
'选择要保存的路径',
'.'
)
if not path:
return
output_path = path + os.path.sep + 'new.json'
shutil.copy(new_json_path, output_path)
self.write_log('新增的合约列表导出完毕')
QtWidgets.QMessageBox.information(
self,
'提示',
'新增的合约列表导出完毕',
QtWidgets.QMessageBox.Yes
)
pass
“追加合约json下载”按钮点击
def append_symbol_json_down_btn_clicked(self):
append_json_dir = SQLITE_TO_DIR + 'append' + os.path.sep
file_list = os.listdir(append_json_dir)
if len(file_list) <= 0:
QtWidgets.QMessageBox.information(
self,
'提示',
'没有要追加的合约',
QtWidgets.QMessageBox.Yes
)
return
path = QtWidgets.QFileDialog.getExistingDirectory(
self,
'选择要保存的路径',
'.'
)
if not path:
return
for item in file_list:
src_file_path = append_json_dir + item
target_file_path = path + os.path.sep + item
shutil.copy(src_file_path, target_file_path)
self.write_log('待追加日数据的合约列表导出完毕')
QtWidgets.QMessageBox.information(
self,
'提示',
'待追加日数据的合约列表导出完毕',
QtWidgets.QMessageBox.Yes
)
pass