第 12 讲 | SQLite 与手写 SQL让数据留下来本讲目标搞懂 PyQt 的数据库模块用原生 SQL 做一个通讯录管理程序。先手写 SQL 是有意为之——理解了 SQL下一讲用 Model 类时你才知道它替你做了什么。配套代码cases/c12_sqlite_crud.py最底部有下载地址一、先回答为什么不用 Excel 存数据前面几讲的工具都是读文件 → 处理 → 写文件为什么突然要上数据库存法适合不适合JSON / ini配置项几十条数据一多就全量读写慢CSV一次性导入导出不支持修改单行、无索引、无并发Excel人看的报表程序当数据库用会很慢且容易损坏SQLite程序的结构化数据需要多机并发写入的场景SQLite 的四个优点这也是它成为桌面软件首选的原因零配置不用装服务、不用起进程一个.db文件就是整个数据库单文件拷走这个文件就是完整备份用户换电脑直接复制标准 SQL学到的语法以后换 MySQL/PostgreSQL 基本通用Python 标准库自带import sqlite3直接用不需要 pip 安装一个直觉判断数据需要被程序反复查询和修改就用数据库只是偶尔导出给人看就用 Excel。通讯录要支持搜索、修改、删除某一条——这就是数据库的典型场景。二、PyQt 给的两条路怎么选QtSql 模块QSqlDatabasePython 标准库sqlite3和 Qt 的集成强能直接喂给QSqlTableModel无需要自己转成列表适用场景界面表格绑定数据库批量数据处理、和 pandas 配合本课程用在哪第 12、13 讲第 14 讲为什么第 14 讲换用 sqlite3 pandas因为批量导入导出 Excel这件事pandas 的read_sql/to_sql一行搞定用 QtSql 得自己写循环几百行。记住这个分界需要界面和数据库实时联动→ QtSql Model第 13 讲需要批量计算和文件互导→ sqlite3 pandas第 14 讲两条路操作的是同一个.db文件可以混用。本课程就是混用的。三、QSqlDatabase连接管理fromPyQt6.QtSqlimportQSqlDatabase,QSqlQuery# 添加一个 SQLite 数据库连接dbQSqlDatabase.addDatabase(QSQLITE)db.setDatabaseName(str(db_path))# 指定 .db 文件路径ifnotdb.open():raiseRuntimeError(f数据库打开失败{db.lastError().text()})三个必须知道的细节①addDatabase是连接池不是新建连接Qt 内部维护一个按连接名索引的字典。不传连接名时用的是默认名qt_sql_default_connection。所以下面这段代码有个隐蔽的坑defopen_db(path1):dbQSqlDatabase.addDatabase(QSQLITE)# 用默认连接名db.setDatabaseName(path1)db.open()defopen_another(path2):dbQSqlDatabase.addDatabase(QSQLITE)# ✘ 又是默认名覆盖了上一个db.setDatabaseName(path2)db.open()# 这是对同一个连接重新指向了 path2解决给每个连接起名字。CONN_NAMEcontact_connifQSqlDatabase.contains(CONN_NAME):QSqlDatabase.removeDatabase(CONN_NAME)# 先移除旧的避免重复警告dbQSqlDatabase.addDatabase(QSQLITE,CONN_NAME)db.setDatabaseName(str(db_path))db.open()contains()判断 removeDatabase()清理这两行是允许重复打开同一个库比如用户点两次重新连接的必要保护。不加的话 Qt 会打印警告严重时会导致连接错乱。② 连接有名字后续操作都要带上queryQSqlQuery(db)# 显式指定连接# 或者queryQSqlQuery()# 用默认连接只适用于单一连接的简单场景本课程的所有案例都用QSqlQuery(self.db)显式指定这是更稳的写法。③ 资源释放连接不用了要关db.close()QSqlDatabase.removeDatabase(CONN_NAME)对于桌面工具程序退出时操作系统会回收不关也行。但如果你要换一个数据库文件这种功能不先 remove 就会一直连着旧文件。四、QSqlQuery执行 SQL基础用法queryQSqlQuery(db)query.exec(SELECT id, name FROM contacts)whilequery.next():# ★ 逐行遍历cidquery.value(0)# 按索引取namequery.value(name)# 或按列名取print(cid,name)query.next()是游标前进返回 False 表示到底了。query.value(i)取当前行的第 i 列。参数绑定必须用不能用字符串拼接# ✘ 危险且麻烦的写法sqlfSELECT * FROM contacts WHERE name {name}query.exec(sql)两个问题SQL 注入如果name里包含单引号SQL 就崩了如果是恶意的 OR 11就能绕过条件查到所有数据转义麻烦名字里有、、反斜杠的情况你得自己处理正确写法qQSqlQuery(db)q.prepare(SELECT id, name, phone FROM contacts WHERE name LIKE ? OR phone LIKE ?)q.addBindValue(f%{keyword}%)q.addBindValue(f%{keyword}%)ifnotq.exec():raiseRuntimeError(q.lastError().text())要点SQL 里用?占位SQLite 的占位符是?MySQL 也是PostgreSQL 用%saddBindValue()按顺序绑定exec()返回 False 表示失败必须检查lastError()把异常和 SQL 一起抛出来封装一层出错时把 SQL 语句一起带上排查时能立刻定位def_query(self,sql:str,params:list|NoneNone)-QSqlQuery:qQSqlQuery(self.db)q.prepare(sql)forpin(paramsor[]):q.addBindValue(p)ifnotq.exec():raiseRuntimeError(fSQL 执行失败{q.lastError().text()}\nSQL:{sql})self.last_sqlself._pretty(sql,paramsor[])returnq五、一个教学利器把执行过的 SQL 回显出来这是本讲我最推荐的实践。在封装里顺手记录可读版本的 SQLstaticmethoddef_pretty(sql:str,params:list)-str:把 ? 占位符替换成真实值只为了打印好看千万别拿去执行outsqlforpinparams:vf{p}ifisinstance(p,str)else(NULLifpisNoneelsestr(p))outout.replace(?,v,1)returnout然后在界面上开一个绿色底的小窗口实时显示INSERT INTO contacts(name, phone, dept, email) VALUES (张伟, 13800000001, 研发部, zwx.com) -- 查询到 7 条记录 UPDATE contacts SET name张伟, phone13800000001, dept研发部, emailzwx.com WHERE id3 DELETE FROM contacts WHERE id3这个功能对学习者的价值极大每点一次按钮你都能看到这一下到底发了什么 SQL不用猜。同时它也是个调试工具——出问题时用户截图给你SQL 一眼就能看出问题。注意_pretty只是用于显示绝对不能拿去执行那就等于字符串拼接回到注入问题了。六、DAO 分层把 SQL 关在一个类里不要在界面的槽函数里直接写 SQL。看这个结构classContactRepo:通讯录数据访问对象DAOdef__init__(self,file_name:strdemo.db)-None:init_demo_tables(file_name)# 建表幂等seed_demo_data(file_name)# 塞演示数据表空才塞...建立连接...defquery_all(self,keyword:str)-list[dict]:...# 查询defadd(self,name,phone,dept,email)-str:...# 新增defupdate(self,cid,name,phone,dept,email)-str:...# 修改defdelete(self,cid:int)-str:...# 删除defcount_by_dept(self)-list[tuple[str,int]]:...# 统计一个真实的坑方法名不要叫list上面这个查询方法我最初取的第一个名字就是list。理由是它返回一个列表叫list最直观。然后count_by_dept直接报错TypeError: function object is not subscriptable原因类体是自上而下执行的命名空间。当 Python 执行到defcount_by_dept(self)-list[tuple[str,int]]:时list这个名字在类的命名空间里已经被上一个方法占用了——list现在指的是ContactRepo.list这个函数对象而不是内置类型。于是list[tuple[str, int]]变成了给一个函数对象做下标报错。为什么def query_all(...) - list[dict]自己不出错因为写那行注解时list这个方法名还没被赋值到类命名空间里所以它找到的是内置list。同一个文件里上面一行的注解好好的下面一行的注解就炸了——就是这么隐蔽。三种解法按推荐度排序解法说明换名字本讲采用改成query_all/fetch_all。最省事最不容易踩顶部加from __future__ import annotations注解变成字符串永不在运行时求值。能治但是掩盖而非修正用typing.ListList[tuple[str, int]]。老写法现在不推荐结论方法名避开list/dict/str/id/type/input/format/filter/all/any这些内置名字。这不只是美学问题在类体里它是会真的报错的。同样的道理模块级的函数叫list也会遮蔽内置list只是报错点会更晚、更难查。界面上就变成这样defon_add(self)-None:dataself._collect()ifnotdata:returntry:self._sql(self.repo.add(*data))# ★ 一行调用看不到 SQLself.clear_form()self.reload()exceptRuntimeErrorasexc:QMessageBox.critical(self,新增失败,str(exc))分层的三个好处界面代码干净只有业务流转没有 SQL 字符串SQL 集中在一处要加索引、改表结构只动 DAO可测试ContactRepo不依赖界面可以直接在命令行里测试返回类型用list[dict]而不是 QSqlQuery 对象让 DAO 的接口和 Qt 解耦调用方不需要知道底层用的是 QtSql 还是 sqlite3。七、界面部分表格 表单defreload(self)-None:rowsself.repo.query_all(self.edit_search.text().strip())self.table.setRowCount(len(rows))forr,rowinenumerate(rows):forc,keyinenumerate((id,name,phone,dept,email)):itemQTableWidgetItem(str(row[key]))ifkeyid:item.setData(0x0100,row[id])# ★ 记住真实主键self.table.setItem(r,c,item)这一行item.setData(0x0100, row[id])值得琢磨表格里显示的 ID 是给人看的第 1 行、第 2 行但数据库里的真实主键可能不连续删过记录。选中某行要更新/删除时必须用真实主键不能用行号。把主键藏在 item 的 UserRole 里选中时取出来用defon_row_selected(self)-None:itemsself.table.selectedItems()ifnotitems:returnrowitems[0].row()self.current_idint(self.table.item(row,0).data(0x0100))# ★ 取真实主键self.edit_name.setText(self.table.item(row,1).text())...这是数据库表格开发里的头号坑用行号当 ID删掉一行之后再编辑另一行改的就是错的记录了。搜索要实时self.edit_search.textChanged.connect(self.reload)每敲一个键就重新查询。数据量几千条以内SQLite 的LIKE查询几毫秒就返回体验很好。数据量真的很大时再加防抖用 QTimer 延迟 300ms 再查不用一开始就过度设计。八、三个坑坑 1忘了检查exec()的返回值QSqlQuery.exec()失败时只是返回 False控制台不会有任何提示。每个 exec 后面都该看lastError()尤其是在开发阶段。坑 2用行号当主键上面详述最高频坑 3SQLite 没有真正的日期类型SQLite 里日期就是字符串或数字。用datetime(now, localtime)存本地时间注意加localtime默认存的是 UTC比较时按字符串比较也是正确的因为格式固定为YYYY-MM-DD HH:MM:SS。九、小结SQLite 是桌面软件的最佳选择零配置、单文件、标准 SQL连接有名字addDatabase(QSQLITE, conn_name)配contains()removeDatabase()防重复参数绑定必须用prepareaddBindValue杜绝注入和转义问题DAO 分层SQL 关在一个类里界面只调方法主键藏在 UserRole 里永远不要用行号当 ID手写 SQL 把底子打好了但你也会发现表格的增删改要写不少样板代码——取值、拼 SQL、刷新表格、处理选中行……每个表都要来一遍。下一讲Model/View 模式。Qt 提供了一套把数据库表直接映射成表格的机制一行 SQL 都不用写在表格里双击改数字就自动写回数据库。我们还会讲QSortFilterProxyModel这个不查数据库就能排序筛选的神器以及它带来的那个必踩的坑——代理模型和源模型的行号不一致。本讲是《PyQt6 客户端编程从入门到实战》专栏的第 12 讲。专栏共 22 讲、22 个可运行案例。代码下载地址git clone https://gitee.com/wuyikui555/pyqt6-client.git。