目录
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 traceback
import 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 psycopg2
PRODUCTGOODS50_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 conn
pass
def res_before_any_year_of_oneDay(oneDay:datetime,a0:int)->str:
dateStr = oneDay.strftime('%Y-%m-%d')
yearInt = int(dateStr[0:4])
yearInt00 = yearInt - a0
return 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]
pass
def tickStrings(self, values, scale, spacing):
strings = []
for v in values:
vs = v*scale
if 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]
pass
pass
except:
conn.rollback()
traceback.print_exc()
exctype, value = sys.exc_info()[:2]
self.signal_error.emit((exctype, value, traceback.format_exc()))
pass
else:
self.signal_result.emit(res_data)
finally:
cur.close()
conn.close()
pass
pass
pass
class 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 = None
self.xTicks:list = []
self.p2 = pg.ViewBox()
self.legend2 = None
self.whole_df = pd.DataFrame()
self.whole_left_name:str = ''
self.whole_right_name:str = ''
pass
def 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()
pass
if self.legend2:
self.legend2.clear()
pass
if df.empty:
return
df['x'] = range(len(df))
self.x = df['x'].to_list()
self.whole_df = df
self.whole_left_name = left_name
self.whole_right_name = right_name
self.xTicks = df.loc[:,['x','dateStr']].values
self.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)
# 0
cur_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()
pass
def updateViews(self):
self.p2.setGeometry(self.getViewBox().sceneBoundingRect())
self.p2.linkedViewChanged(self.getViewBox(), self.p2.XAxis)
pass
def mouseMoved(self,evt):
pos = evt
if 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']} 元"
pass
html_str += '</span>'
self.label.setHtml(html_str)
self.label.setPos(mousePoint.x(),mousePoint.y())
self.vLine.setPos(mousePoint.x())
self.hLine.setPos(mousePoint.y())
pass
pass
pass
pass
class 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()
pass
def open_init(self):
self.waitting_close = False
self.thread = None
self.whole_year_map: dict = {
'全部': 0,
'1年':1,
'2年':2,
'3年':3,
'5年': 5,
'8年': 8
}
self.whole_year_int: int = 0
self.whole_goods_map:dict = PRODUCTGOODS50_DICT
self.whole_goods_list: list = []
self.whole_goods_index: int = 0
self.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()
pass
def 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)
pass
def 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)
pass
elif txt == '上一个':
if self.whole_goods_index<=0:
self.whole_goods_index = len(self.whole_goods_list)-1
else:
self.whole_goods_index -= 1
else:
if self.whole_goods_index>=len(self.whole_goods_list)-1:
self.whole_goods_index = 0
else:
self.whole_goods_index += 1
pass
self.show_graph()
pass
def 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)
pass
def combox_year_currentTextChanged(self,cur_txt:str):
self.whole_year_int = self.whole_year_map.get(cur_txt)
self.show_graph()
pass
def btn_200_other_clicked(self):
txt, ok = QInputDialog.getInt(self, '输入整数', '年数')
if ok and txt:
self.whole_year_int = txt
self.show_graph()
pass
def 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:
return
self.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}")
pass
self.graph_widget.set_data(df,left_name,right_name)
pass
def thread_finished(self):
if self.waitting_close:
self.thread = None
if self.widget_000:
self.widget_000.close()
self.close()
pass
QMessageBox.information(self, '提示', '计算完毕', QMessageBox.StandardButton.Ok)
pass
def worker_signal_error_emit(self,res:tuple):
self.thread.quit()
pre_str = f"报错啦。{res[-1]}"
QMessageBox.information(self,'提示',pre_str,QMessageBox.StandardButton.Ok)
pass
def worker_signal_result_emit(self,res:dict):
self.whole_res_map = res
self.thread.quit()
self.show_graph()
pass
def 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 = True
self.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()
pass
else:
a0.ignore()
pass
pass
class 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()
pass
def open_init(self):
self.whole_df:pd.DataFrame = pd.DataFrame()
self.table_vertical_header:list = []
self.vhead_num_map:dict = {}
i = 0
for k,v in PRODUCTGOODS50_DICT.items():
i += 1
self.table_vertical_header.append(k)
for node in v:
self.vhead_num_map[node] = i
self.table_vertical_header.append(node)
i += 1
pass
pass
self.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 = 0
self.combox_year.addItems(list(self.whole_year_map.keys()))
self.query_data()
self.show_table()
pass
def 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)
pass
df['o_date'] = pd.to_datetime(df['date'])
df.sort_values(by='o_date',ascending=False,inplace=True)
self.whole_df = df
pass
except:
traceback.print_exc()
pass
finally:
cur.close()
conn.close()
pass
def combox_year_currentTextChanged(self,cur_txt:str):
cur_int = self.whole_year_map.get(cur_txt)
if self.whole_year_int == cur_int:
return
else:
self.whole_year_int = cur_int
self.show_table()
pass
def btn_100_other_clicked(self):
txt, ok = QInputDialog.getInt(self, '输入整数', '年数')
if ok and txt:
self.whole_year_int = txt
self.show_table()
pass
def show_table(self):
self.table.clearContents()
df = self.whole_df.copy()
if df.empty:
return
if 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}")
pass
else:
self.label_year.setText('显示区间：全区间')
pass
date_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)))
pass
elif val > 0:
item.setBackground(QBrush(QColor(255,165,0)))
pass
else:
# item.setBackground(QBrush(Qt.BrushStyle.NoBrush))
item.setBackground(QBrush(QColor(255,255,0)))
pass
item.setFont(font)
item.setForeground(QBrush(QColor(255,255,255)))
item.setTextAlignment(Qt.AlignmentFlag.AlignCenter)
self.table.setItem(row_i,j,item)
pass
pass
pass
if __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 json
conn = 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
])
pass
cur.executemany(sql_str,data_list)
conn.commit()
pass
except:
conn.rollback()
traceback.print_exc()
pass
finally:
cur.close()
conn.close()
pass
pass数据
通过网盘分享的文件：t_50product_price.sql
链接: https://pan.baidu.com/s/1EFhd5ew4v6AZhcU3xmd0mA?pwd=u7an 提取码: u7an
用 下面的命令导入（最后的路径换成自己的）
psql -U postgres -h 127.0.0.1 -p 5432 -d db_stock -f E:/temp005/t_50product_price.sql
PS:B站有发对应的讲解视频，如果有疑惑的可以用标题搜索看视频。