PyQt6实例_A股日数据维护工具_权息数据增量更新线程

目录

前置：

代码：

1 工作类

2 数据库交互

3 主界面启用子线程

视频：

---

前置：

1 本系列将以 “PyQt6实例_A股日数据维护工具” 开头放置在“PyQt6实例”专栏
专栏地址 https://blog.csdn.net/m0_37967652/category_12929760.html
2 日数据可在“数据库”专栏，“PostgreSQL_”开头系列博文中获取
3 权息数据可以在“随想”专栏，“A股复权计算_”开头系列博文中获取

代码：

1 工作类

class Worker_ExDiv(QObject):signal_finished =pyqtSignal(str)signal_error = pyqtSignal(tuple)@pyqtSlot(object)def do_work(self,task_data:dict):try:''''splits': splits_csv_path,'allotment':allotment_csv_path,'equdiv':equdiv_csv_path'''splits_csv_path = task_data['splits']allotment_csv_path = task_data['allotment']equdiv_csv_path = task_data['equdiv']df_splits = pd.read_csv(splits_csv_path, encoding='utf-8')df_allotment = pd.read_csv(allotment_csv_path, encoding='utf-8')df_equdiv = pd.read_csv(equdiv_csv_path, encoding='utf-8')col_splits = ['ticker', 'reTradeDate', 'splitsRatio']col_equdiv = ['ticker', 'exDivDate', 'bonusShareListDate', 'perShareDivRatio', 'perShareTransRatio','perCashDiv']col_allotment = ['ticker', 'exRightsDate', 'allotmentRatio', 'allotmentPrice']target_col = ['ticker', 'exDate', 'perShareTransRadio', 'perCashDiv', 'allotmentRatio', 'allotmentPrice']df_equdiv['perShareDivRatio'] = df_equdiv['perShareDivRatio'].fillna(0)df_equdiv['perShareTransRatio'] = df_equdiv['perShareTransRatio'].fillna(0)df_equdiv['perCashDiv'] = df_equdiv['perCashDiv'].fillna(0)df_allotment['allotmentRatio'] = df_allotment['allotmentRatio'].fillna(0)df_allotment['allotmentPrice'] = df_allotment['allotmentPrice'].fillna(0)# 如果 exDivDate 和 bonusShareListDate 都为空，说明该行不需要除权除息df_equdiv.dropna(how='all', subset=['exDivDate', 'bonusShareListDate'], inplace=True)# 拆股信息，只取拆股率大于等于1的数据df_splits = df_splits.loc[df_splits['splitsRatio'] >= 1].copy()df_splits['ticker'] = df_splits['secID'].str.slice(0, 6)df_equdiv['ticker'] = df_equdiv['secID'].str.slice(0, 6)df_allotment['ticker'] = df_allotment['secID'].str.slice(0, 6)df_splits = df_splits.loc[:, col_splits].copy()df_equdiv = df_equdiv.loc[:, col_equdiv].copy()df_allotment = df_allotment.loc[:, col_allotment].copy()df_equdiv['exDivDate'] = df_equdiv['exDivDate'].fillna(df_equdiv['bonusShareListDate'])df_equdiv['perShareTransRadio'] = df_equdiv['perShareDivRatio'] + df_equdiv['perShareTransRatio']df = pd.DataFrame(columns=['ticker', 'exDate', 'perShareTransRadio', 'perCashDiv'])df['ticker'] = df_equdiv['ticker'].to_list()df['exDate'] = df_equdiv['exDivDate'].to_list()df['perShareTransRadio'] = df_equdiv['perShareTransRadio'].to_list()df['perCashDiv'] = df_equdiv['perCashDiv'].to_list()df_allotment.rename(columns={'exRightsDate': 'exDate'}, inplace=True)df_splits.rename(columns={'reTradeDate': 'exDate'}, inplace=True)df = pd.merge(df, df_allotment, on=['ticker', 'exDate'], how='outer')df = pd.merge(df, df_splits, on=['ticker', 'exDate'], how='outer')df['perShareTransRadio'] = df['perShareTransRadio'].fillna(0)df.loc[df['splitsRatio'].notnull(), 'perShareTransRadio'] = df['perShareTransRadio'] + df['splitsRatio'] - 1df['perCashDiv'] = df['perCashDiv'].fillna(0)df['allotmentRatio'] = df['allotmentRatio'].fillna(0)df['allotmentPrice'] = df['allotmentPrice'].fillna(0)df['date_o'] = pd.to_datetime(df['exDate'])df.sort_values(by='date_o', inplace=True)df = df.loc[:, target_col]postgresql_utils.incrementtal_update_exdiv(df)passexcept Exception:traceback.print_exc()exctype,value = sys.exc_info()[:2]self.signal_error.emit(('exdiv_thread',exctype,value,traceback.format_exc()))passfinally:self.signal_finished.emit('exdiv')passpass

2 数据库交互

def incrementtal_update_exdiv(df:pd.DataFrame)->None:ticker_list = df['ticker'].to_list()ticker_list_str = '\',\''.join(ticker_list)ticker_list_str = '\'' + ticker_list_str + '\''sql_str = f"select ticker,exDate,perShareTransRadio,perCashDiv,allotmentRatio,allotmentPrice from t_exdividend where ticker in ({ticker_list_str});"conn = connect_db()cur = conn.cursor()cur.execute(sql_str)res = cur.fetchall()df00 = Nonefor one_node in res:ticker = one_node[0]df_node = pd.DataFrame({'exDate':one_node[1],'perShareTransRadio':one_node[2],'perCashDiv':one_node[3],'allotmentRatio':one_node[4],'allotmentPrice':one_node[5]})df_node['ticker'] = tickerif df00 is None:df00 = df_node.copy()else:df00 = pd.concat([df00,df_node])passdf = pd.concat([df,df00])df.drop_duplicates(inplace=True)df_group = df.groupby(by='ticker')data_list = []for ticker, group in df_group:one_node = (ticker,group['exDate'].to_list(),group['perShareTransRadio'].to_list(),group['perCashDiv'].to_list(),group['allotmentRatio'].to_list(),group['allotmentPrice'].to_list())data_list.append(one_node)passsql_insert_str = '''insert into t_exdividend (ticker,exDate,perShareTransRadio,perCashDiv,allotmentRatio,allotmentPrice) values (%s,%s,%s,%s,%s,%s);'''sql_delete_str = f"delete from t_exdividend where ticker in ({ticker_list_str});"try:cur.execute(sql_delete_str)cur.executemany(sql_insert_str,data_list)conn.commit()passexcept Exception as e:print(f'error: {e}')conn.rollback()passfinally:cur.close()conn.close()passpass

3 主界面启用子线程

class MainWindow(QMainWindow):signal_daily = pyqtSignal(object)signal_exdiv = pyqtSignal(object)def __init__(self):super().__init__()self.setWindowTitle('股票日数据维护')self.setMinimumSize(QSize(800,600))self.btn_download = QPushButton('下载某个股票未复权数据',clicked=self.btn_download_clicked)self.btn_download_adj = QPushButton('下载某个股票前复权数据',clicked=self.btn_download_adj_clicked)self.btn_download_ex = QPushButton('下载某个股票的权息数据',clicked=self.btn_download_ex_clicked)groupbox1 = QGroupBox('日数据更新')groupbox2 = QGroupBox('权息数据更新')label11 = QLabel('增量日数据csv文件所在目录')label21 = QLabel('拆股数据csv文件位置:')label22 = QLabel('配股数据csv文件位置:')label23 = QLabel('分红数据csv文件位置:')self.lineedit_daily_dir = QLineEdit()self.lineedit_sqlits_file = QLineEdit()self.lineedit_allotment_file = QLineEdit()self.lineedit_equdiv_file = QLineEdit()self.btn_daily_dir = QPushButton('打开文件夹',clicked=self.btn_daily_dir_clicked)self.btn_splits_file = QPushButton('打开文件',clicked=self.btn_splits_file_clicked)self.btn_allotment_file = QPushButton('打开文件',clicked=self.btn_allotment_file_clicked)self.btn_equdiv_file = QPushButton('打开文件',clicked=self.btn_equdiv_file_clicked)self.btn_execute_daily = QPushButton('执行',clicked=self.btn_execute_daily_clicked)self.btn_execute_ex = QPushButton('执行',clicked=self.btn_execute_ex_clicked)self.btn_current_data = QPushButton('查看数据表中最新数据',clicked=self.btn_current_data_clicked)label31 = QLabel('运行日志：')label32 = QLabel('备忘信息：')self.btn_add_notes = QPushButton('添加',clicked=self.btn_add_notes_clicked)self.textedit_log = QTextEdit()self.table_widget = QTableWidget()self.table_widget.setColumnCount(3)self.table_widget.setHorizontalHeaderLabels(['时间','项','备忘内容'])layout00 = QHBoxLayout()layout00.addWidget(self.btn_download)layout00.addWidget(self.btn_download_adj)layout00.addWidget(self.btn_download_ex)layout11 = QHBoxLayout()layout11.addWidget(label11)layout11.addWidget(self.lineedit_daily_dir)layout11.addWidget(self.btn_daily_dir)layout12 = QHBoxLayout()layout12.addStretch(1)layout12.addWidget(self.btn_current_data)layout12.addWidget(self.btn_execute_daily)layout13 = QVBoxLayout()layout13.addLayout(layout11)layout13.addLayout(layout12)groupbox1.setLayout(layout13)layout21 = QHBoxLayout()layout21.addWidget(label21)layout21.addWidget(self.lineedit_sqlits_file)layout21.addWidget(self.btn_splits_file)layout22 = QHBoxLayout()layout22.addWidget(label22)layout22.addWidget(self.lineedit_allotment_file)layout22.addWidget(self.btn_allotment_file)layout23 = QHBoxLayout()layout23.addWidget(label23)layout23.addWidget(self.lineedit_equdiv_file)layout23.addWidget(self.btn_equdiv_file)layout24 = QHBoxLayout()layout24.addStretch(1)layout24.addWidget(self.btn_execute_ex)layout25 = QVBoxLayout()layout25.addLayout(layout21)layout25.addLayout(layout22)layout25.addLayout(layout23)layout25.addLayout(layout24)groupbox2.setLayout(layout25)layout31 = QVBoxLayout()layout31.addWidget(label31)layout31.addWidget(self.textedit_log)layout32 = QHBoxLayout()layout32.addWidget(label32)layout32.addWidget(self.btn_add_notes)layout33 = QVBoxLayout()layout33.addLayout(layout32)layout33.addWidget(self.table_widget)layout34 = QHBoxLayout()layout34.addLayout(layout31)layout34.addLayout(layout33)layout = QVBoxLayout()layout.addLayout(layout00)layout.addWidget(groupbox1)layout.addWidget(groupbox2)layout.addLayout(layout34)widget = QWidget()widget.setLayout(layout)self.setCentralWidget(widget)self.open_init()passdef open_init(self):self.daily_worker = Noneself.daily_worker_thread = Noneself.exdiv_worker = Noneself.exdiv_worker_thread = Nonepassdef btn_download_clicked(self):passdef btn_download_adj_clicked(self):passdef btn_download_ex_clicked(self):passdef btn_daily_dir_clicked(self):passdef btn_splits_file_clicked(self):passdef btn_allotment_file_clicked(self):passdef btn_equdiv_file_clicked(self):passdef btn_execute_daily_clicked(self):pre_dir = self.lineedit_daily_dir.text()if not pre_dir or len(pre_dir.strip())<=0:self.message_dialog('请选择日数据csv文件所在文件夹')returnfile_list = os.listdir(pre_dir)if not file_list or len(file_list)<=0:self.message_dialog('所选文件夹为空')returnself.daily_worker = Worker_Daily()self.daily_worker_thread = QThread()self.daily_worker.signal_finished.connect(self.worker_signal_finished)self.daily_worker.signal_error.connect(self.thread_signal_error)self.signal_daily.connect(self.daily_worker.do_work)self.daily_worker.moveToThread(self.daily_worker_thread)self.daily_worker_thread.start()self.btn_execute_daily.setEnabled(False)self.lineedit_daily_dir.setEnabled(False)self.btn_daily_dir.setEnabled(False)task_data = {'pre_dir':pre_dir}self.signal_daily.emit(task_data)self.insert_log_msg('日数据开始更新.')passdef worker_signal_finished(self,thread_name:str):if thread_name == 'daily':self.btn_execute_daily.setEnabled(True)self.lineedit_daily_dir.setEnabled(True)self.btn_daily_dir.setEnabled(True)self.insert_log_msg('日数据更新完毕。')self.daily_worker_thread.exit()passelif thread_name == 'exdiv':self.btn_execute_ex.setEnabled(True)self.lineedit_sqlits_file.setEnabled(True)self.lineedit_allotment_file.setEnabled(True)self.lineedit_equdiv_file.setEnabled(True)self.btn_splits_file.setEnabled(True)self.btn_allotment_file.setEnabled(True)self.btn_equdiv_file.setEnabled(True)self.insert_log_msg('权息数据更新完毕')self.exdiv_worker_thread.exit()else:passpassdef thread_signal_error(self,error:tuple):self.insert_log_msg(f"{error[0]} {error[-1]}")passdef btn_execute_ex_clicked(self):splits_csv_path = self.lineedit_sqlits_file.text()allotment_csv_path = self.lineedit_allotment_file.text()equdiv_csv_path = self.lineedit_equdiv_file.text()if not splits_csv_path or len(splits_csv_path.strip()) <= 0:self.message_dialog('请选择拆股csv文件')returnif not allotment_csv_path or len(allotment_csv_path.strip()) <= 0:self.message_dialog('请选择配股csv文件')returnif not equdiv_csv_path or len(equdiv_csv_path.strip()) <= 0:self.message_dialog('请选择分红csv文件')returnself.exdiv_worker = Worker_ExDiv()self.exdiv_worker_thread = QThread()self.exdiv_worker.signal_finished.connect(self.worker_signal_finished)self.exdiv_worker.signal_error.connect(self.thread_signal_error)self.signal_exdiv.connect(self.exdiv_worker.do_work)self.exdiv_worker.moveToThread(self.exdiv_worker_thread)self.exdiv_worker_thread.start()self.btn_execute_ex.setEnabled(False)self.lineedit_sqlits_file.setEnabled(False)self.lineedit_allotment_file.setEnabled(False)self.lineedit_equdiv_file.setEnabled(False)self.btn_splits_file.setEnabled(False)self.btn_allotment_file.setEnabled(False)self.btn_equdiv_file.setEnabled(False)task_data = {'splits': splits_csv_path,'allotment':allotment_csv_path,'equdiv':equdiv_csv_path}self.signal_exdiv.emit(task_data)self.insert_log_msg('权息数据开始更新.')passdef btn_add_notes_clicked(self):passdef btn_current_data_clicked(self):passdef message_dialog(self,msg:str):QMessageBox.information(self,'提示',msg,QMessageBox.StandardButton.Ok)def insert_log_msg(self,msg:str):pre_str = f"{datetime.now().strftime('%Y-%m-%d %H:%M:%S')} {msg}"self.textedit_log.append(pre_str)pass

视频：

https://www.bilibili.com/video/BV1X9ZUYVEZt/
https://www.bilibili.com/video/BV1X9ZUYVEL7/