← 返回首页

SQLite3 绑定层默认值八条:affinity、isolation_level 与 busy_timeout

sqlite3python数据库

我实际在本机跑了一轮 sqlite3 模块的默认值实验:一个临时目录、两个并发连接、一组故意塞错类型的行,逐条看它到底替你做了哪些决定。环境为 CPython 3.13.14、SQLite 3.53.1、Windows 11。与站内 10-28 那篇热备份文 划界:那篇答「备份拷贝时数据一致性怎么验」,本篇答「Python 绑定层在你没说话的时候替你做了什么决定」。本文不含任何联盟推广链接。

PythonSqlite
文中涉及的技术标识 · 图标来自 Simple Icons(CC0)

先给结论

TL;DR:列声明 INTEGER 不保证存的是整数,亲和性会把合式字面量改写后再落盘;隐式事务只包住 DML,SELECT 和 DDL 都在事务外;所以 CREATE TABLE 无法用 rollback 撤销;忙等默认五秒(Python 层给 SQLite 的「立即失败」垫了五秒);WAL 让读写不再互斥;rowcount 对 SELECT 恒为 -1;executescript 执行前会先把你的挂起事务提交掉;默认日期适配器从 3.12 起就已弃用。

// FLOW_01一条 INSERT 落库前要过的五道关卡5 NODES01STEP_01参数绑定02STEP_02亲和性改写03STEP_03隐式事务04STEP_04锁等待05STEP_05提交落盘END_OF_FLOW
一条 INSERT 落库前要过的五道关卡 · 依据本文步骤自绘示意图

列声明 INTEGER,存进去未必是整数

SQLite 是动态类型:值的类型跟着值走,不跟列走。列声明只是「建议」(官方叫 type affinity)。实测五列对照:

CREATE TABLE t(a INTEGER, b REAL, c NUMERIC, d TEXT, e BLOB);

往五列各塞同一批字符串,typeof() 读回来的结果是:

官方文档 datatype3 页写得很清楚:只要字符串能无损表达成整数,NUMERIC 亲和性就会转成整数,官方自己的例子是 '500.0' 存成 integer 500。这直接影响混类型排序:ORDER BY x 时 NULL 最前,数值 3、10 在中,文本 '10'、'apple' 在后,BLOB 永远垫底——数值 10 和字符串 '10' 是两个排序类。

还有一个特例:INTEGER PRIMARY KEY 是 rowid 别名,除了它之外的任何列都可以存任意类型。实测往 rowid 列塞 '123' 得到 integer 123,塞 'abc' 则直接拒绝:sqlite3.IntegrityError: datatype mismatch。

事务是谁开的:isolation_level 的隐式 BEGIN

默认参数下 isolation_level 是空字符串、autocommit 是 LEGACY_TRANSACTION_CONTROL(-1)。这个模式下,Python 在 INSERT、UPDATE、DELETE、REPLACE 之前隐式开事务——仅限这四种 DML。实测 in_transaction 的变化轨迹:

纯 SELECT 在新连接上跑完,in_transaction 仍是 False。官方文档对这条有逐字规定:隐式 BEGIN 只在「sql 是 INSERT/UPDATE/DELETE/REPLACE 且当前没有事务」时发生。

知道谁开事务的最大价值是解释下一节那个反直觉结果。顺带一句:schema 层面的改动能不能被检测出来,是另一条独立的问题,站内 Alembic autogenerate 实测 记录了它漏掉的四类场景。

CREATE TABLE 没被 rollback 吃掉

既然 DDL 不开事务,那建表语句就是以自动提交模式直接落盘的。实测两轮:

同一个语句两种命运,差别只在事务由谁开。想撤销 DDL,必须自己先开显式事务。另外 Python 3.6 之前 sqlite3 会在 DDL 前隐式 commit,这个行为已经移除,老文章里「DDL 自动提交」的表述要按「DDL 不开事务」重新理解。

显式 BEGIN 还有一个撞车点:隐式事务还开着的时候执行 BEGIN,报 sqlite3.OperationalError: cannot start a transaction within a transaction。rollback 之后(且没有新的 DML)才能显式 BEGIN 成功。

database is locked:默认等五秒不是立刻

「SQLite 遇锁立即报 database is locked」是流传最广的说法,但对 Python 用户不准确。分两层:

两个连接对拍:连接 A 用 BEGIN IMMEDIATE 持写锁,连接 B 以 timeout=0 写入,0.5 毫秒即失败,报 sqlite3.OperationalError: database is locked;B 改设 PRAGMA busy_timeout=3000 后再写,A 在 0.6 秒时放锁,B 等了 658 毫秒后写入成功。也就是说报不报错取决于你等不等得起,默认你有五秒。

一个容易被锁死的位置:连接 A 有未提交的 DML(哪怕只是一条 INSERT 没 commit),连接 B 的 CREATE TABLE 也要排队拿写锁——DDL 写 schema 同样要锁。实测 B 直接等满超时报 database is locked。找「谁没 commit」比改超时更能根治这类报错。

WAL 模式改变读写关系:默认 journal_mode 是 delete,切到 WAL 后,A 持写锁期间 B 的读查询照常返回。代价是磁盘上多两个伴生文件,实测 close 之前 -wal 12,392 字节、-shm 32,768 字节,close 后两个伴生文件消失、主库从 4,096 字节涨到 8,192 字节——WAL 内容在关闭时合并回主库。

rowcount 与 lastrowid 的三个口径

Cursor.rowcount 只对 INSERT、UPDATE、DELETE、REPLACE 有效,其余语句(包括 SELECT)恒为 -1。实测四种情形:

lastrowid 的正确用法是紧跟单条 INSERT 的 execute 之后;INSERT 成功前的初始值是 None。官方还提醒 WITHOUT ROWID 表不记录 lastrowid。

executescript 先提交再执行

executescript() 在 legacy 事务控制下有一个官方写明的行为:如果当前有挂起事务,先隐式 COMMIT 再执行脚本。实测后果是脚本之前的待提交行救不回来:

con.execute("INSERT INTO t(x) VALUES('pending')")
con.executescript("CREATE TABLE t2(x); INSERT INTO t2 VALUES(1);")
con.rollback()   # pending 行仍然在

rollback 之后 count 一下,pending 行还在——它在脚本执行前就被提交了。in_transaction 也从 True 变成 False。想让它可回滚,就把脚本也放进显式事务,或者别在挂起事务未决时调 executescript。

跨线程:ProgrammingError 与三种并发姿势

默认 check_same_thread=True,别的线程碰连接直接报错,原文连线程 id 都给你:

sqlite3.ProgrammingError: SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 30876 and this is thread id 31088.

三种可行姿势,代价各不同:

共享连接时注意上一节的事实:一个线程的未提交事务,会让别的线程(或别的连接)写库排队,行数还会因「用哪个连接看」而不同——未提交行只有本连接可见,实测本连接 count 为 1、并发连接 count 为 0、第三方连接写入直接 database is locked,rollback 后归零。

日期只存不读:detect_types 与弃用的适配器

默认 detect_types=0,datetime 塞进去会被适配器转成 ISO 文本落盘:实测存入 2026-11-21 09:30:00,typeof 是 text,读回来是 str。想读回 datetime 要在 connect 时给 detect_types=sqlite3.PARSE_DECLTYPES,读回即 datetime 与 date 对象。

两个新坑:

官方文档还有一条值得抄进备忘录:默认 timestamp 转换器忽略数据库里的 UTC 偏移,恒返回 naive datetime。要保偏移就自己注册 offset-aware 转换器。

SQL 层的数字陷阱与 STRICT 的边界

绑定层之外,SQL 表达式层也有几处和直觉相反:

约束冲突的报错原文是 sqlite3.IntegrityError: UNIQUE constraint failed: t.id,官方解法是 UPSERT:INSERT INTO t VALUES('a',2) ON CONFLICT(id) DO UPDATE SET n=excluded.n,实测冲突行被更新。

STRICT 表(3.37 起)常被当成「严格类型救星」,实测插 '123' 进 INTEGER 列照样存成 integer 123——STRICT 约束的是「必须可转换到声明类型」,不是「禁止转换」。想要逐字节原样存储,声明 BLOB 或自己控制类型。

绑定参数的报错会指错方向

占位符和实参数量不匹配时报错很误导:

sqlite3.OperationalError: table t has 2 columns but 1 values were supplied

问题根本不在表有 2 列,而在你只给了 1 个绑定参数。绑错类型同样报在别处:传 dict 给 qmark 占位符,报 sqlite3.ProgrammingError: Binding 1 has no name, but you supplied a dictionary (which has only names).;把 dict 当位置参数传,报 sqlite3.ProgrammingError: Error binding parameter 1: type 'dict' is not supported。

可用的绑定形态实测:

踩坑与报错

这套结论的边界

全部结论来自本机 CPython 3.13.14(SQLite 3.53.1、Windows 11)实测加官方文档对读,未覆盖:autocommit 参数在未来版本改为 False 后的行为变化、interactive 模式、load_extension、以及在 Linux/SQLite 旧版本上亲和性转换是否有版本差异。官方文档明确说 autocommit 默认值「will change to False in a future Python release」,升级大版本时事务行为值得重测一轮。备份与拷贝场景请配合站内 SQLite 热备份篇使用,配置文件被静默改值的姊妹问题见 YAML 解析篇。

FAQ

Q:INTEGER 列明明声明了类型,为什么读出来是整数而不是我塞进去的字符串?

因为 SQLite 的列声明只是亲和性建议,真正决定存储类型的是值本身。'123' 是可无损转换的合式字面量,INTEGER 亲和性就会把它以 integer 落盘,typeof() 也就报 integer。要让字符串原样保存,把列声明成 TEXT 或 BLOB,或者干脆存进无亲和性的列。

Q:默认已经有五秒忙等了,还需要手动设 busy_timeout 吗?

要看你的写入可能排队多久。五秒对单机小库通常够用,但如果另一边有长事务(比如一次大批量 executemany 没提交),五秒会被吃满然后报 database is locked。我的做法是先查未提交事务,再按实测的锁等待上限把 PRAGMA busy_timeout 调到两到三倍,而不是盲目调大超时。

Q:CREATE TABLE 回滚不掉,迁移脚本要怎么写得安全?

把整段迁移放进显式事务:先 BEGIN,再执行你的 DDL 与数据变更,最后 commit() 或 rollback()。注意别在已有隐式事务时再 BEGIN(会报 cannot start a transaction within a transaction),也别在挂起事务里调 executescript(它会先提交)。测试时用「建表 → rollback → 再查 sqlite_master」验证,比读代码可靠。

Q:都 3.37 了,STRICT 表是不是就不会悄悄改类型了?

不是。STRICT 约束的是「值必须能转换成声明类型」,不是「禁止转换」。实测插 '123' 进 STRICT 表的 INTEGER 列,照样存成 integer 123。它挡的是 'abc' 这类完全转不过去的值,报错也更早更明确,这一点确实有用。

最后

sqlite3 的这些默认值单看每一条都有理由,合在一起的意思是:你写的 SQL 和实际落库的内容之间隔着一个绑定层,这个层的决定不报错、不打日志。验证成本很低——建个临时库、两个连接、一组错类型数据,十分钟就能把自己的业务库过一遍。相比之下,等类型改写污染了数据再排查,代价要大得多。站内 DuckDB 实测篇、Alembic autogenerate 校验篇与本文同属「数据库层的静默决定」这条线,按需取用。

👉 立即参与小米 MiMo 开放平台:国内领先的 AI 大模型开放平台,高性价比推理服务

👉 立即参与阿里云 AI:汇集爆款 AI 产品,热门模型专属权益优惠券助力企业创新加速

📌 本文由 AI 辅助生成并经人工审核发布 | TechPassive — AI 驱动的内容测试站点,专注于效率工具与 SaaS 真实评测

🔗 精选推荐工具

使用以下链接支持我们持续产出高质量内容(点击可直接前往购买):

作为亚马逊联盟会员,符合条件的购买会为我们带来佣金,不会增加你的购买成本。

☁️ DigitalOcean 云服务器 ⚡ Vultr 高性能 VPS 🤖 QoderWork 中国版(推荐有奖) ☁️ 阿里云爆款 AI 产品 📚 WordPress 实用书单 🧠 AI / 大模型书单 🏗️ 系统设计/云架构书单 🐳 Docker/K8s 书单 🐧 Linux 系统管理书单 🐍 Python 编程书单 🗄️ 数据库/PostgreSQL 书单 🚀 DevOps/SRE 书单 🤖 小米 MiMo 开放平台
← 返回首页