当前位置：首页 > news >正文

PyQt6实例_50个流通领域重要生产资料市场价格查看工具

news 2025/8/16 21:23:06

1 使用

2 数据表创建

3 代码

4 数据更新

数据

1 使用

左轴，对应显示环比%

右轴，对应显示价格

可以选择年数，改变曲线显示的时长

把产品放在一个表格中，方便横向和纵向对比查看

2 数据表创建

数据库使用的postgreSQL

create table t_50product_price(
productType   varchar,
productName   varchar,
productUnit   varchar,
reportDate   varchar[],
price   float[],
qoq   float[]
);

productType 产品类别、productName 产品名、productUnit 产品单位、reportDate 数据对应日期、price 产品价格、qoq 产品价格环比

3 代码

import sys
import tracebackimport pandas as pd
from datetime import datetime
from PyQt6.QtGui import (
QFont,
QBrush,
QColor
)
from PyQt6.QtCore import (Qt, pyqtSignal,QObject,QThread,pyqtSlot
)
from PyQt6.QtWidgets import (QApplication,QLabel,QPushButton,QComboBox,QTableWidget,QTableWidgetItem,QWidget,QVBoxLayout,QHBoxLayout, QGridLayout,QInputDialog,QMessageBox,QGroupBox, QHeaderView
)
import pyqtgraph as pg
import psycopg2PRODUCTGOODS50_DICT = {'黑色金属':['无缝钢管','普通中板','热轧普通板卷','线材','螺纹钢','角钢'],'有色金属':['电解铜','铅锭','铝锭','锌锭'],'化工产品':['涤纶长丝','烧碱','甲醇','硫酸','纯苯','聚丙烯','聚乙烯','聚氯乙烯','苯乙烯','顺丁胶'],'石油天然气':['柴油','汽油92','汽油95','液化天然气','液化石油气','石蜡'],'煤炭':['大同混煤','山西优混','山西大混','无烟煤','普通混煤','焦炭','焦煤'],'非金属建材':['普通硅酸盐水泥_散装','普通硅酸盐水泥_袋装','浮法平板玻璃'],'农产品（主要用于加工）':['大豆','小麦','棉花','玉米','生猪','稻米','花生','豆粕'],'农业生产资料':['农药','复合肥','尿素'],'林产品':['天然橡胶','纸浆','瓦楞纸']
}def connect_db():try:conn = psycopg2.connect(database='',user='',password='',host='127.0.0.1',port=5432)except Exception as e:print(f'connection failed。{e}')else:return connpassdef res_before_any_year_of_oneDay(oneDay:datetime,a0:int)->str:dateStr = oneDay.strftime('%Y-%m-%d')yearInt = int(dateStr[0:4])yearInt00 = yearInt - a0return f"{yearInt00}{dateStr[4:]}"def trans_date_to_str(dateObj:datetime.date)->str:return dateObj.strftime('%Y-%m-%d')class StrAxisItem(pg.AxisItem):def __init__(self,ticks,*args,**kwargs):pg.AxisItem.__init__(self,*args,**kwargs)self.x_values = [x[0] for x in ticks]self.x_strings = [x[1] for x in ticks]passdef tickStrings(self, values, scale, spacing):strings = []for v in values:vs = v*scaleif vs in self.x_values:vstr = self.x_strings[self.x_values.index(vs)]else:vstr = ''strings.append(vstr)return strings
class Worker50ProductTool(QObject):signal_error = pyqtSignal(tuple)signal_result = pyqtSignal(dict)@pyqtSlot(object)def do_work(self, task_data: dict):res_data = {}conn = connect_db()cur = conn.cursor()try:sql_str = "select productName,productUnit,reportDate,price,qoq from t_50product_price;"cur.execute(sql_str)res = cur.fetchall()for one in res:df = pd.DataFrame(data={'dateStr':one[2],'a1':one[4],'a2':one[3]})df['o_date'] = pd.to_datetime(df['dateStr'])df.sort_values(by='o_date',ascending=True,inplace=True)res_data[one[0]] = [one[1],df]passpassexcept:conn.rollback()traceback.print_exc()exctype, value = sys.exc_info()[:2]self.signal_error.emit((exctype, value, traceback.format_exc()))passelse:self.signal_result.emit(res_data)finally:cur.close()conn.close()passpasspassclass Graph2YProductWidget(pg.PlotWidget):def __init__(self):super().__init__()self.init_data()def init_data(self):self.color_blue = QColor(0, 0, 255)self.color_red = QColor(255, 0, 0)self.color_0 = (255,255,0) # 纯黄self.x = Noneself.xTicks:list = []self.p2 = pg.ViewBox()self.legend2 = Noneself.whole_df = pd.DataFrame()self.whole_left_name:str = ''self.whole_right_name:str = ''passdef set_data(self,df:pd.DataFrame,left_name:str,right_name:str):'''dateStr 字符型timestamp 长整型a1 line 总市值 a2 bar 总营收:return:'''self.clear()if self.p2:self.p2.clear()passif self.legend2:self.legend2.clear()passif df.empty:returndf['x'] = range(len(df))self.x = df['x'].to_list()self.whole_df = dfself.whole_left_name = left_nameself.whole_right_name = right_nameself.xTicks = df.loc[:,['x','dateStr']].valuesself.addLegend()self.setLabel('left',left_name)y_left = df['a1'].to_list()y_right = df['a2'].to_list()curve_left = pg.PlotCurveItem(x=self.x,y=y_left,pen=pg.mkPen({'color': self.color_red, 'width': 2}), connect='finite',name=left_name)self.addItem(curve_left)scatters = pg.ScatterPlotItem(size=10,symbol='o',pen=pg.mkPen('g'),brush=pg.mkBrush('g'))scatters.setData(pos=[(i,j) for i,j in zip(self.x,y_left)])self.addItem(scatters)self.setLabel('right',right_name)self.p2.enableAutoRange()self.scene().addItem(self.p2)self.getAxis('right').linkToView(self.p2)self.p2.setXLink(self)self.legend2 = pg.LegendItem(offset=(0., 1.))self.legend2.setParentItem(self.p2)power = pg.PlotDataItem(antialias=True, pen=pg.mkPen({'color': self.color_blue, 'width': 4}))self.legend2.addItem(power, right_name)curve_right = pg.PlotCurveItem(x=self.x, y=y_right, pen=pg.mkPen({'color': self.color_blue, 'width': 2}),connect='finite')self.p2.addItem(curve_right)# 0cur_0 = pg.InfiniteLine(pos=(0, 0), angle=0, movable=False,pen=pg.mkPen({'color': self.color_0, 'width': 4, 'style' : Qt.PenStyle.DashLine}),label='左轴，0值')self.addItem(cur_0)horAxis = StrAxisItem(ticks=self.xTicks, orientation='bottom')self.setAxisItems({'bottom': horAxis})self.vLine = pg.InfiniteLine(angle=90, movable=False)self.hLine = pg.InfiniteLine(angle=0, movable=False)self.addItem(self.vLine, ignoreBounds=True)self.addItem(self.hLine, ignoreBounds=True)self.label = pg.TextItem()self.addItem(self.label, ignoreBounds=True)self.vb = self.getViewBox()self.vb.sigResized.connect(self.updateViews)self.scene().sigMouseMoved.connect(self.mouseMoved)self.enableAutoRange()passdef updateViews(self):self.p2.setGeometry(self.getViewBox().sceneBoundingRect())self.p2.linkedViewChanged(self.getViewBox(), self.p2.XAxis)passdef mouseMoved(self,evt):pos = evtif self.sceneBoundingRect().contains(pos):mousePoint = self.vb.mapSceneToView(pos)index = int(mousePoint.x())if index > 0 and index < len(self.x):df = self.whole_df.loc[self.whole_df['x']==index]dateStr = self.xTicks[index][1]html_str = f"<span style='font-size:20pt;color:#ffff00;'>日期:{dateStr} <br/>"if not df.empty:html_str += f"{self.whole_left_name}:{df.iloc[0]['a1']} %<br/>"html_str += f"{self.whole_right_name}:{df.iloc[0]['a2']} 元"passhtml_str += '</span>'self.label.setHtml(html_str)self.label.setPos(mousePoint.x(),mousePoint.y())self.vLine.setPos(mousePoint.x())self.hLine.setPos(mousePoint.y())passpasspasspassclass Product50ShowWidget(QWidget):signal_worker = pyqtSignal(object)def __init__(self):super().__init__()self.setWindowTitle('50个流通领域重要生产资料价格查看工具')self.label_title = QLabel('50个流通领域重要生产资料价格（旬数据）', alignment=Qt.AlignmentFlag.AlignHCenter)self.label_title.setStyleSheet("font-size:28px;color:#CC2EFA;")self.label_subTitle = QLabel('当前物资', alignment=Qt.AlignmentFlag.AlignHCenter)self.label_subTitle.setStyleSheet("font-size:26px;font-weight:bold;")groupbox000 = QGroupBox('物资')label000 = QLabel('类别')self.combox_type = QComboBox()self.combox_type.setFont(QFont('Arial', 20))self.combox_type.setSizeAdjustPolicy(QComboBox.SizeAdjustPolicy.AdjustToContents)self.combox_type.currentTextChanged.connect(self.combox_type_currentTextChanged)label001 = QLabel('生产资料')self.combox_goods = QComboBox()self.combox_goods.setFont(QFont('Arial', 20))self.combox_goods.setSizeAdjustPolicy(QComboBox.SizeAdjustPolicy.AdjustToContents)btn_000_jump = QPushButton('跳转',clicked=self.btn_000_clicked)btn_000_pre = QPushButton('上一个',clicked=self.btn_000_clicked)btn_000_next = QPushButton('下一个',clicked=self.btn_000_clicked)layout000 = QGridLayout()layout000.addWidget(label000,0,0,1,2)layout000.addWidget(self.combox_type,0,2,1,4)layout000.addWidget(label001, 1, 0, 1, 2)layout000.addWidget(self.combox_goods, 1, 2, 1, 4)layout000.addWidget(btn_000_jump,2,0,1,2)layout000.addWidget(btn_000_pre,2,2,1,2)layout000.addWidget(btn_000_next,2,4,1,2)groupbox000.setLayout(layout000)groupbox200 = QGroupBox('其他')self.label_year = QLabel('显示区间')self.label_year.setStyleSheet("font-size:26px;font-weight:bold;")label200 = QLabel('年数')self.combox_year = QComboBox()self.combox_year.setFont(QFont('Arial', 20))self.combox_year.setSizeAdjustPolicy(QComboBox.SizeAdjustPolicy.AdjustToContents)self.combox_year.currentTextChanged.connect(self.combox_year_currentTextChanged)btn_200_other = QPushButton('其他年数', clicked=self.btn_200_other_clicked)layout200 = QGridLayout()layout200.addWidget(self.label_year, 0, 0, 1, 4)layout200.addWidget(label200, 1, 0, 1, 1)layout200.addWidget(self.combox_year, 1, 1, 1, 3)layout200.addWidget(btn_200_other, 2, 0, 1, 4)groupbox200.setLayout(layout200)self.graph_widget = Graph2YProductWidget()layout900 = QHBoxLayout()layout900.addStretch(1)layout900.addWidget(groupbox000)layout900.addWidget(groupbox200)layout900.addStretch(1)layout = QVBoxLayout()layout.addWidget(self.label_title)layout.addWidget(self.label_subTitle)layout.addLayout(layout900)layout.addWidget(self.graph_widget)self.setLayout(layout)self.open_init()passdef open_init(self):self.waitting_close = Falseself.thread = Noneself.whole_year_map: dict = {'全部': 0,'1年':1,'2年':2,'3年':3,'5年': 5,'8年': 8}self.whole_year_int: int = 0self.whole_goods_map:dict = PRODUCTGOODS50_DICTself.whole_goods_list: list = []self.whole_goods_index: int = 0self.whole_res_map:dict = {}type_list = []for k,v in PRODUCTGOODS50_DICT.items():type_list.append(k)self.whole_goods_list.extend(v)self.combox_type.addItems(type_list)self.combox_year.addItems(list(self.whole_year_map.keys()))self.start_worker()self.widget_000 = Product50TableWidget()self.widget_000.show()passdef start_worker(self):self.worker = Worker50ProductTool()self.thread = QThread()self.thread.finished.connect(self.thread_finished)self.worker.signal_error.connect(self.worker_signal_error_emit)self.worker.signal_result.connect(self.worker_signal_result_emit)self.signal_worker.connect(self.worker.do_work)self.thread.start()task_data = {}self.signal_worker.emit(task_data)passdef btn_000_clicked(self):a0 = self.sender()txt = a0.text()if txt == '跳转':cur_good = self.combox_goods.currentText()self.whole_goods_index = self.whole_goods_list.index(cur_good)passelif txt == '上一个':if self.whole_goods_index<=0:self.whole_goods_index = len(self.whole_goods_list)-1else:self.whole_goods_index -= 1else:if self.whole_goods_index>=len(self.whole_goods_list)-1:self.whole_goods_index = 0else:self.whole_goods_index += 1passself.show_graph()passdef combox_type_currentTextChanged(self,cur_txt:str):goods_list = self.whole_goods_map.get(cur_txt)self.combox_goods.clear()self.combox_goods.addItems(goods_list)passdef combox_year_currentTextChanged(self,cur_txt:str):self.whole_year_int = self.whole_year_map.get(cur_txt)self.show_graph()passdef btn_200_other_clicked(self):txt, ok = QInputDialog.getInt(self, '输入整数', '年数')if ok and txt:self.whole_year_int = txtself.show_graph()passdef show_graph(self):cur_good = self.whole_goods_list[self.whole_goods_index]res = self.whole_res_map.get(cur_good,None)if res is None:returnself.label_subTitle.setText(cur_good)left_name = '环比(%)'right_name = f"{cur_good}({res[0]}/元)"df = res[1].copy()if self.whole_year_int > 0:maxD = df.iloc[-1]['o_date']preDateStr = res_before_any_year_of_oneDay(maxD, self.whole_year_int)df = df.loc[df['o_date'] >= preDateStr]preStr = df.iloc[0]['dateStr']afterStr = df.iloc[-1]['dateStr']self.label_year.setText(f"显示区间:{preStr}~{afterStr}")passself.graph_widget.set_data(df,left_name,right_name)passdef thread_finished(self):if self.waitting_close:self.thread = Noneif self.widget_000:self.widget_000.close()self.close()passQMessageBox.information(self, '提示', '计算完毕', QMessageBox.StandardButton.Ok)passdef worker_signal_error_emit(self,res:tuple):self.thread.quit()pre_str = f"报错啦。{res[-1]}"QMessageBox.information(self,'提示',pre_str,QMessageBox.StandardButton.Ok)passdef worker_signal_result_emit(self,res:dict):self.whole_res_map = resself.thread.quit()self.show_graph()passdef closeEvent(self,a0):answer = QMessageBox.question(self,'确认退出？','退出将中断操作，确定要退出么？',QMessageBox.StandardButton.Yes | QMessageBox.StandardButton.No)if answer == QMessageBox.StandardButton.Yes:if self.thread:if self.thread.isRunning():self.waitting_close = Trueself.thread.quit()a0.ignore()QMessageBox.information(self,'提示','正在关闭线程，请稍后',QMessageBox.StandardButton.Ok)else:if self.widget_000:self.widget_000.close()a0.accept()else:if self.widget_000:self.widget_000.close()a0.accept()passelse:a0.ignore()passpassclass Product50TableWidget(QWidget):def __init__(self):super().__init__()self.setWindowTitle('50个流通领域重要生产资料价格查看工具sub')self.label_title = QLabel('50个一起看', alignment=Qt.AlignmentFlag.AlignHCenter)self.label_title.setStyleSheet("font-size:28px;color:#CC2EFA;")groupbox100 = QGroupBox('年数')self.label_year = QLabel('显示区间')self.label_year.setStyleSheet("font-size:26px;font-weight:bold;")label100 = QLabel('年数')self.combox_year = QComboBox()self.combox_year.setFont(QFont('Arial', 20))self.combox_year.setSizeAdjustPolicy(QComboBox.SizeAdjustPolicy.AdjustToContents)self.combox_year.currentTextChanged.connect(self.combox_year_currentTextChanged)btn_100_other = QPushButton('其他年数', clicked=self.btn_100_other_clicked)layout100 = QGridLayout()layout100.addWidget(self.label_year, 0, 0, 1, 4)layout100.addWidget(label100, 1, 0, 1, 1)layout100.addWidget(self.combox_year, 1, 1, 1, 3)layout100.addWidget(btn_100_other, 2, 0, 1, 4)groupbox100.setLayout(layout100)self.table = QTableWidget()self.table.horizontalHeader().setSectionResizeMode(QHeaderView.ResizeMode.Stretch)layout900 = QHBoxLayout()layout900.addStretch(1)layout900.addWidget(groupbox100)layout900.addStretch(1)layout = QVBoxLayout()layout.addWidget(self.label_title)layout.addLayout(layout900)layout.addWidget(self.table)self.setLayout(layout)self.open_init()passdef open_init(self):self.whole_df:pd.DataFrame = pd.DataFrame()self.table_vertical_header:list = []self.vhead_num_map:dict = {}i = 0for k,v in PRODUCTGOODS50_DICT.items():i += 1self.table_vertical_header.append(k)for node in v:self.vhead_num_map[node] = iself.table_vertical_header.append(node)i += 1passpassself.table.setRowCount(len(self.table_vertical_header))self.table.setVerticalHeaderLabels(self.table_vertical_header)self.whole_year_map: dict = {'1年': 1,'2年': 2,'3年': 3,'全部': 0}self.whole_year_int: int = 0self.combox_year.addItems(list(self.whole_year_map.keys()))self.query_data()self.show_table()passdef query_data(self):conn = connect_db()cur = conn.cursor()try:sql_str = "select productName,reportDate,qoq from t_50product_price;"cur.execute(sql_str)res = cur.fetchall()df = pd.DataFrame()for one in res:df00 = pd.DataFrame(data={'date':one[1],'qoq':one[2]})df00['name'] = one[0]df = pd.concat([df,df00],ignore_index=True)passdf['o_date'] = pd.to_datetime(df['date'])df.sort_values(by='o_date',ascending=False,inplace=True)self.whole_df = dfpassexcept:traceback.print_exc()passfinally:cur.close()conn.close()passdef combox_year_currentTextChanged(self,cur_txt:str):cur_int = self.whole_year_map.get(cur_txt)if self.whole_year_int == cur_int:returnelse:self.whole_year_int = cur_intself.show_table()passdef btn_100_other_clicked(self):txt, ok = QInputDialog.getInt(self, '输入整数', '年数')if ok and txt:self.whole_year_int = txtself.show_table()passdef show_table(self):self.table.clearContents()df = self.whole_df.copy()if df.empty:returnif self.whole_year_int > 0:mp = df.iloc[0]['o_date']pre_ = res_before_any_year_of_oneDay(mp, self.whole_year_int)df = df.loc[df['o_date'] >= pre_].copy()preStr = df.iloc[-1]['date']afterStr = df.iloc[0]['date']self.label_year.setText(f"显示区间:{preStr}~{afterStr}")passelse:self.label_year.setText('显示区间：全区间')passdate_list = list(df['date'].unique())self.table.setColumnCount(len(date_list))self.table.setHorizontalHeaderLabels(date_list)df_group = df.groupby(by='name')font = QFont()font.setBold(True)font.setPointSize(18)for name,group in df_group:row_i = self.vhead_num_map.get(name)for i,one in group.iterrows():dateStr = one['date']val = one['qoq']j = date_list.index(dateStr)# QColor(0,128,0), #纯绿# QColor(255,165,0), #橙色item = QTableWidgetItem(str(val))if val < 0:item.setBackground(QBrush(QColor(0,128,0)))passelif val > 0:item.setBackground(QBrush(QColor(255,165,0)))passelse:# item.setBackground(QBrush(Qt.BrushStyle.NoBrush))item.setBackground(QBrush(QColor(255,255,0)))passitem.setFont(font)item.setForeground(QBrush(QColor(255,255,255)))item.setTextAlignment(Qt.AlignmentFlag.AlignCenter)self.table.setItem(row_i,j,item)passpasspassif __name__ == '__main__':app = QApplication([])mw = Product50ShowWidget()mw.showMaximized()app.exec()pass

4 数据更新

一般4号发布上月下旬的数据，14日发布本月上旬数据，24日发布本月中旬数据

发布网站：国家统计局 https://www.stats.gov.cn/sj/

网页上的表格可以直接拖动选中复制下来

新建一个Excel直接黏贴进去

按照一下的产品顺序替换“产品名称”

,螺纹钢,线材,普通中板,热轧普通板卷,无缝钢管,角钢,电解铜,铝锭,铅锭,锌锭,硫酸,烧碱,甲醇,纯苯,苯乙烯,聚乙烯,聚丙烯,聚氯乙烯,顺丁胶,涤纶长丝,液化天然气,液化石油气,汽油95,汽油92,柴油,石蜡,无烟煤,普通混煤,山西大混,山西优混,大同混煤,焦煤,焦炭,普通硅酸盐水泥_袋装,普通硅酸盐水泥_散装,浮法平板玻璃,稻米,小麦,玉米,棉花,生猪,大豆,豆粕,花生,尿素,复合肥,农药,天然橡胶,纸浆,瓦楞纸

（截图是更新三个发布的数据，所以数据有三列，如果只更新一个数据，那就只有一列）

将数据改造成json文件

更新代码

def updateData():import jsonconn = connect_db()cur = conn.cursor()try:reportDate_list = ['2025-07-20','2025-07-30','2025-08-10']sql_str = "update t_50product_price set reportDate=array_cat(reportDate,%s),price=array_cat(price,%s),qoq=array_cat(qoq,%s) where productName=%s;"with open('../task/ok.json','r',encoding='utf-8') as fr:obj_json = json.load(fr)data_list = []for k,v in obj_json.items():data_list.append([reportDate_list,v[0],v[1],k])passcur.executemany(sql_str,data_list)conn.commit()passexcept:conn.rollback()traceback.print_exc()passfinally:cur.close()conn.close()passpass