第 14 讲 | 数据库与 Excel 双向同步本讲目标把数据库和 Excel 打通——“给我个表我导进去”、“导出来发给领导”。同时讲清一个关键判断什么时候用 QtSql什么时候用 pandas。配套代码cases/c14_excel_sync.py下载地址在底部一、为什么这一讲换用 pandas sqlite3前两讲用的都是 Qt 的QtSql模块。这一讲换成 Python 标准库的sqlite3加 pandas。操作的是同一个.db文件只是访问路径不同。判断标准只有一条需求用什么原因界面表格实时联动数据库QtSql ModelQt 的模型能直接喂给 QTableView批量导入导出 Excel、统计计算pandas sqlite3read_sql/to_sql/groupby一行搞定具体对比一下导出 5000 条记录到 Excel这件事用 QtSql 的写法queryQSqlQuery(db)query.exec(SELECT student, class, subject, score FROM scores)rows[]whilequery.next():rows.append([query.value(0),query.value(1),query.value(2),query.value(3)])# 然后还要用 openpyxl 逐行写入、设格式……用 pandas 的写法connsqlite3.connect(db_path)dfpd.read_sql(SELECT * FROM scores,conn)df.to_excel(成绩导出.xlsx,indexFalse)四行。这就是选 pandas 的理由。二、sqlite3 的基本用法importsqlite3fromutils.dbimportdb_path connsqlite3.connect(db_path(demo.db))# 查询rowsconn.execute(SELECT student, class, score FROM scores WHERE class ?,(1班,)).fetchall()# 返回 [((张伟, 1班, 88.0), ...]# 带参数用元组或列表不要用 f-string 拼接curconn.execute(INSERT INTO scores(student, class, subject, score) VALUES (?,?,?,?),(李雷,1班,语文,92.0))print(cur.lastrowid)# 刚插入行的自增 ID# 提交conn.commit()# 或者用 with 自动提交推荐withconn:conn.execute(DELETE FROM scores WHERE id ?,(1,))conn.close()with conn:是自动提交/回滚块内没异常就 commit有异常就 rollback。它不会关闭连接和文件操作的with open()不同所以还是要conn.close()。让查询结果能用列名访问conn.row_factorysqlite3.Row curconn.execute(SELECT id, name FROM contacts)rowcur.fetchone()print(row[name],row[0])# 两种方式都可以sqlite3.Row让结果既能按索引取也能按列名取比纯元组方便很多。本工程的utils/db.py里的connect()就默认开了这个。打开外键约束conn.execute(PRAGMA foreign_keys ON)SQLite 默认不校验外键这是历史遗留设计为了兼容旧版本。如果你的表之间有关联一定要手动打开。三、Excel → 数据库列名映射是第一个坎Excel 里的列名是中文“学生”“班级”“科目”“成绩”数据库字段是英文student/class/subject/score。导入时要转换defimport_task(report,excel_file:str,db_file:str)-dict:importpandasaspd report(10,f读取{Path(excel_file).name})dfpd.read_excel(excel_file)# ★ 列名兼容映射同一含义的不同写法都支持rename{学生:student,姓名:student,班级:class,科目:subject,成绩:score,分数:score}dfdf.rename(columns{k:vfork,vinrename.items()ifkindf.columns})missing[cforcinSCORE_COLSifcnotindf.columns]ifmissing:raiseValueError(fExcel 缺少必要的列{missing}\n需要学生 / 班级 / 科目 / 成绩)...三个设计要点① 一个含义支持多个写法用户给你的表可能写学生也可能写姓名写成绩也可能写分数。rename字典里都列上兼容性大大提升。这是给同事用的工具必须考虑的。② 缺列时给出明确提示raiseValueError(fExcel 缺少必要的列{missing}\n需要学生 / 班级 / 科目 / 成绩)不能只说导入失败要告诉用户缺什么、应该有什么。③ 后面的代码只跟英文列名打交道统一转成英文后后面的处理逻辑就稳定了不用每处都判断到底是学生还是姓名。数据清洗和写入dfdf[SCORE_COLS].copy()# 只取需要的列且固定顺序df[score]pd.to_numeric(df[score],errorscoerce)# 转不了就变 NaNbadint(df[score].isna().sum())dfdf.dropna(subset[score])# 丢掉 NaNreport(55,f共{len(df)}条有效记录丢弃无法解析的成绩{bad}条)connsqlite3.connect(db_path(db_file))withconn:conn.execute(DELETE FROM scores)# 全量导入先清空df.to_sql(scores,conn,if_existsappend,indexFalse)conn.close()四个要点①errorscoerce把转换失败的变成NaN而不是抛异常。之后统计有多少个 NaN报给用户而不是悄悄丢掉。② 先 DELETE 再 append这是全量覆盖的语义。如果期望的是追加就删掉 DELETE 那行并考虑如何处理重复。③if_existsappend其他选项是fail表存在就报错和replace删表重建。用append先 DELETE 再插入能保留表的索引和约束。④indexFalse不加的话pandas 会把行号当成一列写进数据库多出一个叫index的字段。这是 to_sql 最经典的坑。四、数据库 → Excel多 Sheet 是加分项defexport_task(report,db_file:str,out_file:str,only_class:str)-dict:importpandasaspd connsqlite3.connect(db_path(db_file))ifonly_class:dfpd.read_sql(SELECT student, class, subject, score FROM scores WHERE class ?,conn,params(only_class,))else:dfpd.read_sql(SELECT student, class, subject, score FROM scores,conn)conn.close()ifdf.empty:raiseValueError(没有查询到数据无法导出)dfdf.rename(columns{student:学生,class:班级,subject:科目,score:成绩})# ★ 同一个文件写两个 Sheet明细 统计summary(df.groupby([班级,科目])[成绩].agg([count,mean,max,min]).round(1).reset_index().rename(columns{count:人数,mean:平均分,max:最高分,min:最低分}))Path(out_file).parent.mkdir(parentsTrue,exist_okTrue)withpd.ExcelWriter(out_file,enginexlsxwriter)aswriter:df.to_excel(writer,sheet_name成绩明细,indexFalse)summary.to_excel(writer,sheet_name班级统计,indexFalse)关键点①params传参数pd.read_sql(... WHERE class ?,conn,params(1班,))和手写 SQL 一样要参数化不要 f-string 拼接。② 一个文件多个 Sheet 用ExcelWriterwithpd.ExcelWriter(out_file,enginexlsxwriter)aswriter:df1.to_excel(writer,sheet_name明细,indexFalse)df2.to_excel(writer,sheet_name统计,indexFalse)enginexlsxwriter是必须指定的本工程 requirements 里装了 XlsxWriter。不写的话 pandas 会用 openpyxl功能上也能用但 xlsxwriter 在样式和性能上更好。③ 导出的列名换回中文数据库里是英文导出给人看的 Excel 要中文。这个转换在导出时做。④ 附一个统计 Sheet领导拿到明细表往往还想看汇总。顺手多带一个统计 Sheet是让工具显得专业的小细节。五、用 pandas 做统计三行出一张表# ① 各科统计by_subject(df.groupby(subject)[score].agg([count,mean,max,min]).round(1).sort_values(mean,ascendingFalse))by_subject.columns[人数,平均分,最高分,最低分]# ② 各班排名by_class(df.groupby(class)[score].agg([count,sum,mean]).round(1).sort_values(sum,ascendingFalse))by_class.columns[记录数,总分,平均分]# ③ 学生总分 Top10by_student(df.groupby(student)[score].sum().round(1).sort_values(ascendingFalse).head(10).to_frame(总分))groupby 的链式写法在数据分析里极其常见值得熟悉df.groupby(分组列)[要计算的列].agg([函数列表]).round(小数位).sort_values(排序).head(n)把结果贴到界面上不需要额外的表格控件pandas 的 DataFrame 转成字符串直接扔进QTextEditself.txt.append(【各科统计】\nstr(info[subject])\n)输出长这样【各科统计】 人数 平均分 最高分 最低分 subject 数学 40 86.3 99.8 58.2 语文 40 82.1 98.5 55.0 英语 40 79.6 96.3 52.4 【班级排名按总分】 记录数 总分 平均分 class 2班 120 9964.0 83.0 1班 120 9812.5 81.8 3班 120 9765.3 81.4pandas 默认的对齐输出在等宽字体下非常整齐所以那个 QTextEdit 一定要设等宽字体self.txt.setStyleSheet(font-family:Consolas,Microsoft YaHei,monospace; font-size:12px;)中文字符宽度和英文不一样对齐会略微错位——这是等宽字体的局限但整体可读性没问题。想要完美对齐可以用tabulate库属于锦上添花。六、界面四个按钮的流水线self.btn_demoQPushButton(① 生成示例 Excel)self.btn_importQPushButton(② 从 Excel 导入)self.btn_exportQPushButton(③ 导出到 Excel)self.btn_statsQPushButton(④ 计算统计)self.btn_refreshQPushButton(刷新表格)按钮上带序号 ①②③④用户一眼知道操作顺序。这是个小细节但对于给不熟悉电脑的同事用的工具效果很明显。任务复用上一讲的TaskThreaddef_run_task(self,fn,*args)-None:ifself.taskandself.task.isRunning():returnself.taskTaskThread(fn,*args)self.task.progress.connect(lambdap,m:mandself.txt.append(m))self.task.finished.connect(self.on_finished)self.task.failed.connect(lambdae:QMessageBox.critical(self,失败,e))self.task.start()一个包装方法管四个任务因为它们签名都是fn(report, *args)且返回 dict。这就是第 8 讲把TaskThread设计成通用工具的收益。七、三个坑坑 1to_sql忘了indexFalse数据库会多出一个index字段下次导出时它又会被当成一列导到 Excel。记住所有to_excel/to_sql都加indexFalse。坑 2Excel 里的数字被读成字符串单元格格式是文本时成绩88读进来是88字符串直接入库后排序会出现100 88这种诡异结果。用pd.to_numeric(..., errorscoerce)统一转一遍转不了的就丢掉并报告。坑 3中文列名读取时的空格Excel 表头是学生 带个尾部空格时rename里的学生匹配不上。稳妥做法是先清洗列名df.columns[c.strip()forcindf.columns]这一行几乎应该写进每个读 Excel 的脚本里。八、小结两条路各有分工界面联动用 QtSql第 12、13 讲批量处理用 pandas本讲导入的重点是列名映射支持多种写法、缺列给明确提示导出的加分项是统计 Sheet领导拿到明细还想看汇总三条保命规则indexFalse、to_numeric(errorscoerce)、批量删 NaN 前先报告数量数据库部分到此结束。你现在能做出数据能存、能查、能和 Excel 互导的完整应用。下一讲数据可视化。前面所有工具都在处理数据但从下一讲开始我们让数据画出来——柱状图、饼图、折线图用 Qt 自带的 QtCharts 模块。好处是和界面同一个渲染引擎缩放不糊、动画原生不用把 matplotlib 塞进 Qt 里折腾。本讲是《PyQt6 客户端编程从入门到实战》专栏的第 14 讲。专栏共 22 讲、22 个可运行案例。下载地址git clone https://gitee.com/wuyikui555/pyqt6-client.git。
