SQLite3 绑定层默认值八条:affinity、isolation_level 与 busy_timeout
我实际在本机跑了一轮 sqlite3 模块的默认值实验:一个临时目录、两个并发连接、一组故意塞错类型的行,逐条看它到底替你做了哪些决定。环境为 CPython 3.13.14、SQLite 3.53.1、Windows 11。与站内 10-28 那篇热备份文 划界:那篇答「备份拷贝时数据一致性怎么验」,本篇答「Python 绑定层在你没说话的时候替你做了什么决定」。本文不含任何联盟推广链接。
先给结论
TL;DR:列声明 INTEGER 不保证存的是整数,亲和性会把合式字面量改写后再落盘;隐式事务只包住 DML,SELECT 和 DDL 都在事务外;所以 CREATE TABLE 无法用 rollback 撤销;忙等默认五秒(Python 层给 SQLite 的「立即失败」垫了五秒);WAL 让读写不再互斥;rowcount 对 SELECT 恒为 -1;executescript 执行前会先把你的挂起事务提交掉;默认日期适配器从 3.12 起就已弃用。
列声明 INTEGER,存进去未必是整数
SQLite 是动态类型:值的类型跟着值走,不跟列走。列声明只是「建议」(官方叫 type affinity)。实测五列对照:
CREATE TABLE t(a INTEGER, b REAL, c NUMERIC, d TEXT, e BLOB);
往五列各塞同一批字符串,typeof() 读回来的结果是:
'123'进 INTEGER 列,存成 integer 123'1e3'进 INTEGER 列,存成 integer 1000 —— 指数写法也算合式字面量'12.0'进 NUMERIC 列,存成 integer 12 —— 无损转整数时优先整数- 整数
7进 REAL 列,存成 real 7.0 '1e3'进 BLOB 列,原样存 text —— BLOB 亲和性不做任何转换'abc'五列全保持 text —— 非合式字面量不改写
官方文档 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 的变化轨迹:
- 建表(DDL)之后:False
- INSERT 之后:True
- SELECT 之后:True(不是 SELECT 开的,是沿用前面 DML 开的)
- rollback 之后:False
纯 SELECT 在新连接上跑完,in_transaction 仍是 False。官方文档对这条有逐字规定:隐式 BEGIN 只在「sql 是 INSERT/UPDATE/DELETE/REPLACE 且当前没有事务」时发生。
知道谁开事务的最大价值是解释下一节那个反直觉结果。顺带一句:schema 层面的改动能不能被检测出来,是另一条独立的问题,站内 Alembic autogenerate 实测 记录了它漏掉的四类场景。
CREATE TABLE 没被 rollback 吃掉
既然 DDL 不开事务,那建表语句就是以自动提交模式直接落盘的。实测两轮:
- 直接
CREATE TABLE t2然后rollback():t2 残留,rollback 无效 - 先手动
BEGIN,在显式事务里建 t2,再rollback():t2 消失,回滚成功
同一个语句两种命运,差别只在事务由谁开。想撤销 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 用户不准确。分两层:
- SQLite C API 默认没有 busy handler,遇锁立即返回 SQLITE_BUSY——这层说法成立
- Python 的
connect(timeout=)默认 5.0 秒,文档原话「Default five seconds」,实测对应PRAGMA busy_timeout读数 5000
两个连接对拍:连接 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。实测四种情形:
executemany插三行:rowcount 3,但lastrowid是 None——官方明确 executemany 后 lastrowid 不更新- SELECT 之后:rowcount -1,想知道几行只能 len(fetchall())
- DELETE 两行:rowcount 2,可用
- UPDATE 匹配零行:rowcount 0,可用来判断「有没有改到东西」
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.
三种可行姿势,代价各不同:
check_same_thread=False共享连接:官方写明写操作可能需要用户自己序列化,实测加一把线程锁后多线程写入正常- 每线程各自开连接:没有 ProgrammingError,但要处理连接间的锁竞争(回到 busy_timeout 那节)
- 连接池 + 单写多读:写路径收敛到一条连接,读连接并发
共享连接时注意上一节的事实:一个线程的未提交事务,会让别的线程(或别的连接)写库排队,行数还会因「用哪个连接看」而不同——未提交行只有本连接可见,实测本连接 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 对象。
两个新坑:
- 默认适配器从 Python 3.12 起已弃用。实测 3.13.14 插入 datetime 时各报一条 DeprecationWarning,官方建议改用 register_adapter 自定义
- 列声明 DATE 却塞完整 datetime:适配器按 timestamp 格式存成
2026-11-21 09:30:00,date 转换器读回时炸出ValueError: invalid literal for int() with base 10: b'21 09:30:00'——声明类型和实际塞的东西必须是同一种格式 - 列声明 TIMESTAMP 塞了非日期串,PARSE_DECLTYPES 读回报
ValueError: not enough values to unpack (expected 2, got 1)——转换器不校验格式,坏数据在读取时才爆
官方文档还有一条值得抄进备忘录:默认 timestamp 转换器忽略数据库里的 UTC 偏移,恒返回 naive datetime。要保偏移就自己注册 offset-aware 转换器。
SQL 层的数字陷阱与 STRICT 的边界
绑定层之外,SQL 表达式层也有几处和直觉相反:
SELECT 1/2得 0,两操作数都是整数时除法取整;1/2.0才得 0.5SELECT '10'+2得 12,算术表达式会把文本强转数值;'abc'+2得 2(文本转不成数就按 0 算)SELECT 0.1+0.2得 0.30000000000000004,IEEE 754 双精度,和 Python 一致
约束冲突的报错原文是 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。
可用的绑定形态实测:
?占位符配序列;:name占位符配 dict,且:x重复出现时同一值可填多处?1编号占位符可用,VALUES(?1, ?1)一值填两列- None 绑定为 NULL(typeof 是 null);True 绑定成 integer 1;float 绑定成 real
- dict、set 属于不支持类型,直接 ProgrammingError
踩坑与报错
- database is locked(0.5 毫秒即失败):
timeout=0或 C API 裸用时的真实行为。先查有没有连接挂着未提交事务(DDL 写 schema 也拿写锁),再调 busy_timeout,顺序反了只会把偶发变必现 - cannot start a transaction within a transaction:隐式事务还开着就手动 BEGIN。先
in_transaction判断,或干脆只管理一种事务来源 - CREATE TABLE 回滚不掉:DDL 不在隐式事务里。要回滚就得自己 BEGIN
- invalid literal for int() with base 10: b'21 09:30:00':声明 DATE 列塞了 datetime。声明类型与写入格式对齐,或统一用 TIMESTAMP 列
- Error binding parameter 1: type 'dict' is not supported:绑定层不收容器。转成 JSON 字符串或元组字段
- table t has 2 columns but 1 values were supplied:优先数占位符和参数个数,别急着去看表结构
这套结论的边界
全部结论来自本机 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 真实评测
🔗 精选推荐工具
使用以下链接支持我们持续产出高质量内容(点击可直接前往购买):
作为亚马逊联盟会员,符合条件的购买会为我们带来佣金,不会增加你的购买成本。