sqlite-utils 4.0rc2:主要由 Claude Fable 编写

sqlite-utils 4.0rc2, mostly written by Claude Fable

西蒙·威利森 Simon Willison · 2026-07-05 · Simon Willison's Weblog ↗

打开互动全文版(逐段中英对照 + 针对本篇提问)→

摘要 · Abstract

赞助方:Atlassian —给你的智能体一个计划,而非提示。新的 Jira 功能为 AI 原生软件开发解锁了完整上下文。现在可以直接从 Jira 向 Claude、Cursor 或 GitHub Copilot 分配任务。了解更多

Sponsored by: Atlassian — Give your agents a plan. Not a prompt. New Jira capabilities unlock full-context for AI-native software development. Assign tasks to Claude, Cursor, or GitHub Copilot, now directly from Jira. Learn more

核心贡献 · Key contributions

局限 · Limitations

章节 · Sections(共 4)

全文 · Full text(逐段中英对照)

sqlite-utils 4.0rc2:主要由 Claude Fable 编写(花费约 149.25 美元) sqlite-utils 4.0rc2, mostly written by Claude Fable (for about $149.25)

几周前我写过关于 sqlite-utils 4.0rc1 发布的文章。由于我们的 Max 订阅上还能再用几天 Claude Fable,我决定看看它能否帮我推进到一个让我真正放心的 4.0 稳定版,因为我尽量遵循 SemVer,并且希望不兼容的主版本越少越好。

I wrote about the sqlite-utils 4.0rc1 release a couple of weeks ago. Since we only have Claude Fable on our Max subscriptions for a few more days, I decided to see if it could help me get to a 4.0 stable release that I felt truly comfortable about, since I try to keep to SemVer and like my incompatible major versions to be as rare as possible.

我在 iPhone 上的 Claude Code for web 中,用下面这段提示词开始:

I started with this prompt, in Claude Code for web on my iPhone:

发布稳定版 4.0 之前的最终审查——非常重要,要找出任何如果以后修复就会变成破坏性变更的最后一刻问题

Final review before shipping a stable 4.0 release - very important to spot any last minute things that would be a breaking change if we fix them later

这是它为我生成的初始报告。其中有一些我此前尚未遇到的**重大**问题——有 5 个被 Fable 归类为“发布阻塞项”。以下是其中最严重的一个:

Here's that initial report it created for me. There were some _significant_ problems that I hadn't myself encountered yet—5 that Fable categorized as "release blockers". Here's the worst of the bunch:

1. delete_where() 从不提交,并会污染连接(数据丢失)

1. delete_where() never commits and poisons the connection (data loss)

`Table.delete_where()`(sqlite_utils/db.py:2948)通过裸的 `self.db.execute()` 执行其 DELETE,没有 `atomic()` 包装——对比 db.py:2944 处的 `Table.delete()`,后者正确进行了包装。连接被留在 `in_transaction=True` 状态,因此后续每次 `_atomic()` 调用都会走 savepoint 分支(db.py:430-440),也永远不会提交。

`Table.delete_where()` (sqlite_utils/db.py:2948) runs its DELETE via a bare `self.db.execute()` with no `atomic()` wrapper — compare `Table.delete()` at db.py:2944, which wraps correctly. The connection is left with `in_transaction=True`, so every subsequent `_atomic()` call takes the savepoint branch (db.py:430-440) and never commits either.

端到端复现:

Reproduced end-to-end:

```python db = sqlite_utils.Database("dw.db") ```

```python db = sqlite_utils.Database("dw.db") ```

```python db["t"].insert_all([{"id": i} for i in range(3)], pk="id") ```

```python db["t"].insert_all([{"id": i} for i in range(3)], pk="id") ```

```python db["t"].delete_where("id = ?", [0]) # 此时 conn.in_transaction 为 True ```

```python db["t"].delete_where("id = ?", [0]) # conn.in_transaction is now True ```

db["t"].insert({"id": 50})

db["t"].insert({"id": 50})

db["u"].insert({"a": 1})

db["u"].insert({"a": 1})

重新打开:行是 [0, 1, 2] ——删除操作、第 50 行以及表 u 全都消失了。

Reopen: rows are [0, 1, 2] — the delete, row 50, AND table u are all gone.

这是一个非常严重的 bug!很高兴我没有发布那个版本,尽管至少它是我可以在 4.0.1 点版本中修复的 bug,而不是一个会迫使我发布 5.0 的设计缺陷。

That’s a really bad bug! Very glad I didn’t ship that, although at least it would have been a bug I could fix in a 4.0.1 point release, not a design flaw that would force a 5.0.

在 37 个提示、34 次提交以及 30 个独立文件中的 +1,321 -190 行代码更改的过程中,我们依次处理了整个反馈集,并在此过程中进行了其他几项设计改进。

Over the course of 37 prompts, 34 commits and +1,321 -190 code changes over 30 separate files, we worked through the entire set of feedback in turn, making several other design improvements along the way.

编码智能体的一个奇怪之处在于,像这样的更难任务实际上提供了 _更多_ 同时做其他事情的机会,因为智能体有时需要 10-15 分钟来反复处理一个新任务。我出去享受了半月湾的七月四日游行,偶尔从手机上查看并提示 Fable 进行下一步。

A weird thing about coding agents is that harder tasks like this one actually provide _more_ opportunity to do other things at the same time, since the agent sometimes needs 10-15 minutes to churn away on a new task. I went out to enjoy the Half Moon Bay 4th of July parade, occasionally checking in and prompting the next step for Fable from my phone.

完整细节在 PR 和这个共享记录中。我切换到笔记本电脑进行最终审查,通过 GitHub 的 PR 界面进行。

Full details in the PR and this shared transcript. I switched to my laptop for the final review, which I conducted through GitHub's PR interface.

最重要的更改涉及事务处理,这是早期 RC 中的标志性新功能。新的 RC 现在包括关于新事务模型的全面文档,我将在此完整引用其介绍:

The most significant changes relate to transaction handling, which was the signature new feature in the earlier RC. The new RC now includes comprehensive documentation on the new transaction model, the intro to which I'll quote here in full:

这个库中所有写入数据库的方法——insert()、upsert()、update()、delete()、delete_where()、transform()、create_table()、create_index()、enable_fts() 以及其他方法——都在自己的事务中运行,并在返回前提交。方法调用完成后,您的更改会立即保存到磁盘:

Every method in this library that writes to the database—insert(), upsert(), update(), delete(), delete_where(), transform(), create_table(), create_index(), enable_fts() and the rest—runs inside its own transaction and commits it before returning. Your changes are saved to disk as soon as the method call finishes:

db = Database("data.db")

db = Database("data.db")

db.table("news").insert({"headline": "Dog wins award"})

db.table("news").insert({"headline": "Dog wins award"})

新行已经保存——无需调用 commit()。

The new row is already saved - no commit() required.

对通过 db.execute() 执行的原始 SQL 同样如此——写语句一旦运行即被提交。

The same applies to raw SQL executed with db.execute()—a write statement is committed as soon as it has run.

你永远不需要调用 commit(),也不需要关闭数据库来持久化更改。只有两种情形需要你考虑事务:

You never need to call commit(), and you do not need to close the database to persist your changes. There are exactly two situations where you need to think about transactions:

1. 你希望将多个写操作组合在一起,使它们要么全部成功、要么全部失败——请使用 db.atomic()。

1. You want to group several write operations together, so they either all succeed or all fail—use db.atomic().

2. 你正在使用 db.begin() 自行管理事务,在这种情况下,直到你提交之前,什么都不会被提交——库永远不会提交你开启的事务。

2. You are managing a transaction yourself with db.begin(), in which case nothing is committed until you commit—the library will never commit a transaction you opened.

在审阅 Fable 的文档时——我发现先审阅文档的修改是建立对变更初步理解的绝佳方式——我注意到了这个细节:

In reviewing Fable’s documentation—I find that reviewing the documentation edits first is an _excellent_ way to build an initial understanding of what has changed—I spotted this detail:

db.atomic() 和自动的每方法事务是为 Python 默认事务处理模式下的连接设计的。不支持使用 Python 3.12+ 的 sqlite3.connect(..., autocommit=True)或 autocommit=False 选项创建的连接,因为在这些连接上 commit() 和 rollback() 的行为不同。

db.atomic() and the automatic per-method transactions are designed for connections in Python’s default transaction handling mode. Connections created with the Python 3.12+ sqlite3.connect(..., autocommit=True) or autocommit=False options are not supported, because commit() and rollback() behave differently on those connections.

我承认我没有考虑过 sqlite-utils 会如何应对 Python 3.12 中新增的 autocommit 设置。结果发现,“在这些连接上行为不同”几乎等同于整个测试套件失败,因此我与模型合作,确保这种差异不会破坏库的工作方式。

I admit I hadn’t thought about how sqlite-utils would react to the more recent autocommit setting, added in Python 3.12. It turns out “behave differently on those connections” equated to almost the entire test suite failing, so I worked with the model to ensure that this difference would not break how the library works.

GPT-5.5 的最终审查 And a final review by GPT-5.5

我曾经认为让一个模型审查另一个模型的工作有些荒谬——感觉奇怪地迷信。问题是*它确实有效*——我已经开始习惯性地让 Anthropic 最好的模型审查 OpenAI 的工作,反之亦然,因为这种情况经常出现有趣的结果,足够有价值。

I used to think that the idea of having one model review the work of another was somewhat absurd—it felt weirdly superstitious. The problem is *it really does work*—I’ve started habitually having Anthropic’s best model review OpenAI’s work and vice versa, because I’ve had that turn up interesting results often enough to be valuable.

我向 Codex Desktop 和 GPT-5.5 xhigh 提示如下:

I prompted Codex Desktop and GPT-5.5 xhigh with the following:

审查自上次 RC 以来的更改。同时确认变更日志是最新的。

Review changes since the last RC. Also confirm that the changelog is up-to-date.

这足以发现两个值得调查的问题:

Which was enough to turn up two issues worth investigating:

* [P1] sqlite_utils/db.py:663 `db.query()` 现在仅在调用 `db.execute()` 之后拒绝非行语句,而 sqlite_utils/db.py:705 会先自动提交这些写入。因此 `db.query("update ...")` 会引发 ValueError,但更新已经提交。对于一个被记录为“只能用于返回行的 SQL”的方法来说,这是一个令人惊讶的副作用。

* [P1] sqlite_utils/db.py:663 `db.query()` now rejects non-row statements only after calling `db.execute()`, and sqlite_utils/db.py:705 auto-commits those writes first. So `db.query("update ...")` raises ValueError but the update is already committed. That is a surprising side effect for a method documented as “can only be used with SQL that returns rows.”

* [P1] sqlite_utils/db.py:672 通过 db.query() 执行 INSERT ... RETURNING 时,只有在返回的生成器被完全耗尽后才会提交。若不迭代就调用 db.query(“insert ... returning ...”),或使用常见的 next(db.query(...)) 用法,事务会保持打开状态,写入可能在关闭时被回滚。这与 docs/changelog.rst:15 和 docs/python-api.rst:232 的说法相矛盾,它们声称无需迭代即可生效。

* [P1] sqlite_utils/db.py:672 INSERT ... RETURNING through db.query() only commits after the returned generator is fully exhausted. db.query("insert ... returning ...") without iteration, or the common next(db.query(...)) usage, leaves the transaction open and the write can be rolled back on close. This contradicts docs/changelog.rst:15 and docs/python-api.rst:232, which say it takes effect without iteration.

我把这段内容粘贴到一个全新的 Fable 会话中,它运行了一些实验来确认该问题:

I pasted that into a fresh Fable session, which ran some experiments to confirm the problem:

两个发现都得到了确认。db.query() 先调用 self.execute()(该调用会自动提交写入),然后才检查 cursor.description——因此 db.query(“update ...”)在抛出 ValueError 之前就已经提交了更新。而 INSERT ... RETURNING 的提交逻辑位于返回生成器的末尾,所以除非你耗尽迭代器,否则它永远不会触发——next(db.query(...)) 或未迭代的调用会让事务保持打开状态,这与 changelog 和文档的承诺相矛盾。

Both findings were confirmed. db.query() called self.execute() first, which auto-commits writes, and only then checked cursor.description — so db.query("update ...") committed the update before raising ValueError. And the INSERT ... RETURNING commit lived at the end of the returned generator, so it never fired unless you exhausted the iterator — next(db.query(...)) or an un-iterated call left the transaction open, contradicting what the changelog and docs promise.

这是包含修复的 PR,以及完整的 Claude Code 记录。审查这段代码帮助我对 SQLite 事务语义的边界情况建立了更好的心智模型!

Here's the PR with the fix, and the full Claude Code transcript. Reviewing this code helped me build a better mental model of the edge cases of SQLite transaction semantics!

预估(未补贴)成本为 149.25 美元 For an estimated (unsubsidized) cost of $149.25

我升级到了 Claude Max 每月 200 美元的套餐(此前是每月 100 美元),以在 7 月 7 日“Fablepocalypse”之前的剩余时间里提高我的 Fable 额度——到那时,即便是 Claude Max 订阅用户也必须为该模型支付完整的 API 费用。

I upgraded to the Claude Max $200/month plan (I was previously on $100/month) to increase my Fable allowance for the remaining time until the July 7th Fablepocalypse, when even Claude Max subscribers will have to pay full API cost for the model.

我很好奇,如果我直接按这些费用付费,会花多少钱。起初我以为这些数字我拿不到,因为我是通过 Claude Code for web 远程运行这项工作的;后来我意识到,我可以在那个已有的会话中运行 AgentsView 来获得该成本估算!

I was curious as to how much this would have cost me if I had been paying those costs directly. At first I thought those numbers weren't available to me since I had run the work remotely using Claude Code for web, and then I realized I could run AgentsView inside that existing session to get that cost estimate!

运行 “uvx agentsview --help”,然后用该工具计算本次会话的成本

Run "uvx agentsview --help" and then use that tool to calculate the cost of this session

Claude 弄明白了如何使用 session list --include-children 命令,并得出了以下结果:

Claude figured out how to use the session list --include-children command and came out with the following:

我非常庆幸自己用的是那个订阅!我真该听从自己的建议,更多地使用由更便宜模型驱动的子智能体。

I'm very glad I'm on that subscription! I really should have followed my own advice and leaned more heavily into subagents with cheaper models.

以下是 claude.ai/settings/usage 目前向我显示的内容:

Here's what claude.ai/settings/usage is showing me right now:

sqlite-utils 4.0rc2 完整发布说明 The full release notes for sqlite-utils 4.0rc2

以下是 RC 版本的完整发布说明。我让 Fable 在每次更改落地时将这些说明添加到变更日志的“未发布”部分,并随时进行审查。这带来了一个巧妙的效果:变更日志的提交历史成为了发布中每个更改的简明摘要。

Here are the full release notes for the RC. I had Fable add these to an “Unreleased” section of the changelog as each change landed, reviewing them as it went. This has the neat side effect that the commit history of the changelog acts as a concise summary of each of the changes that went into the release.

过去我有一项政策,即手动编写发布说明,但说实话,这些说明比我本人编写的要好。发布说明是一个很好的例子,说明我可以将写作外包给智能体,因为它们需要无聊、可预测且准确。

In the past I’ve had a policy of writing release notes by hand, but honestly these are better than I would have created myself. Release notes are a great example of writing that I’m OK to outsource to agents because they need to be boring, predictable and accurate.

破坏性变更:

Breaking changes:

* 使用 db.execute() 执行的写语句现在会自动提交,除非已经打开了事务,在这种情况下它们会加入该事务。以前,它们会打开一个隐式事务,该事务会一直保持打开状态,直到有东西提交它——在同一连接上读取时写入似乎有效,但当连接关闭时会被静默回滚。依赖于回滚未提交的 db.execute() 写入的代码应首先使用新的 db.begin() 方法打开显式事务。事务模型在“事务与保存更改”中有完整文档。

* Write statements executed with db.execute() are now committed automatically, unless a transaction is already open in which case they join it. Previously they opened an implicit transaction that stayed open until something committed it—writes appeared to work when read on the same connection but were silently rolled back when the connection closed. Code that relied on rolling back uncommitted db.execute() writes should use the new db.begin() method to open an explicit transaction first. The transaction model is documented in full at Transactions and saving your changes.

* db.query() 现在在调用时立即执行其 SQL,而不是等到返回的生成器首次迭代时才执行。行在迭代期间仍然是惰性获取的。SQL 错误现在在调用点引发,诸如 INSERT ... RETURNING 之类的语句会立即执行并提交,无需迭代其结果,而传递一个不返回任何行的语句——以前是静默无操作——现在会引发 ValueError,建议改用 db.execute()。以这种方式被拒绝的语句在引发错误之前会回滚,因此对数据库没有影响。

* db.query() now executes its SQL as soon as it is called, rather than waiting until the returned generator is first iterated. Rows are still fetched lazily during iteration. SQL errors are now raised at the call site, statements such as INSERT ... RETURNING are executed and committed immediately without needing to iterate over their results, and passing a statement that returns no rows—previously a silent no-op—now raises a ValueError recommending db.execute() instead. A statement rejected this way is rolled back before the error is raised, so it has no effect on the database.

* Python API 的校验错误现在抛出 ValueError,而不再是 AssertionError。此前,无效参数——例如不带任何列的 create_table()、对不存在的表调用 transform(),或同时传入 ignore=True 和 replace=True——都是通过裸 assert 语句来拒绝的,而当 Python 以 -O 标志运行时,这些语句会被静默跳过。此前针对这些情况捕获 AssertionError 的代码,现在应改为捕获 ValueError。

* Python API validation errors now raise ValueError instead of AssertionError. Previously invalid arguments—such as create_table() with no columns, transform() on a table that does not exist, or passing both ignore=True and replace=True—were rejected using bare assert statements, which are silently skipped when Python runs with the -O flag. Code that caught AssertionError for these cases should catch ValueError instead.

* 如果某条记录缺少任一主键列的值,或某个主键列的值为 None,table.upsert() 和 table.upsert_all() 现在会抛出 PrimaryKeyRequired。此前,这类记录——它们永远无法匹配到已有行——会被悄悄插入为全新的行,或者在插入已经发生之后触发令人困惑的 KeyError。

* table.upsert() and table.upsert_all() now raise PrimaryKeyRequired if a record is missing a value for any primary key column, or has a value of None for one. Previously such records—which can never match an existing row—were quietly inserted as brand new rows, or triggered a confusing KeyError after the insert had already taken place.

* 如果在事务打开期间调用 db.enable_wal() 和 db.disable_wal(),现在会抛出 sqlite_utils.db.TransactionError。此前,它们会在更改日志模式时作为副作用静默提交已打开的事务,从而破坏 db.atomic() 以及用户自行管理的事务的回滚保证。

* db.enable_wal() and db.disable_wal() now raise a sqlite_utils.db.TransactionError if called while a transaction is open. Previously they would silently commit the open transaction as a side effect of changing the journal mode, breaking the rollback guarantee of db.atomic() and of user-managed transactions.

* View 类不再有 enable_fts() 方法。它存在的唯一目的就是抛出 NotImplementedError,因为视图不支持全文搜索——现在调用它会改为抛出 AttributeError,并且该方法不再出现在 API 参考文档中。当 sqlite-utils enable-fts 命令指向一个视图时,会显示一条清晰的错误信息。

* The View class no longer has an enable_fts() method. It existed only to raise NotImplementedError, since full-text search is not supported for views—calling it now raises AttributeError instead, and the method no longer appears in the API reference. The sqlite-utils enable-fts command shows a clean error when pointed at a view.

* 空操作标志 -d/--detect-types 已从 insert 和 upsert 命令中移除。自 4.0a1 起,类型检测对 CSV/TSV 数据就已是默认行为,因此该标志不起任何作用——使用它的调用只需将其去掉即可。--no-detect-types 仍可用于禁用检测。

* The no-op -d/--detect-types flag has been removed from the insert and upsert commands. Type detection has been the default for CSV/TSV data since 4.0a1, so the flag did nothing—invocations using it should simply drop it. --no-detect-types remains available to disable detection.

* 如果传入的数据库连接是使用 Python 3.12+ 的 `sqlite3.connect(..., autocommit=True)` 或 `autocommit=False` 选项创建的,`Database()` 现在会抛出 `sqlite_utils.db.TransactionError`。在这些连接上,`commit()` 和 `rollback()` 的行为不同,之前会导致库执行的每次写入在连接关闭时被静默丢弃。

* `Database()` now raises a `sqlite_utils.db.TransactionError` if passed a connection created with the Python 3.12+ `sqlite3.connect(..., autocommit=True)` or `autocommit=False` options. `commit()` and `rollback()` behave differently on those connections, which previously caused every write made by the library to be silently discarded when the connection closed.

其他内容:

Everything else:

* 修复了一个 bug:`table.delete_where()`、`table.optimize()` 和 `table.rebuild_fts()` 没有提交更改,导致连接处于打开的事务中。它们的工作——以及任何后续写入——可能在连接关闭时被静默回滚。现在这三个方法都使用 `db.atomic()`,与其他写入方法保持一致。

* Fixed a bug where `table.delete_where()`, `table.optimize()` and `table.rebuild_fts()` did not commit their changes, leaving the connection inside an open transaction. Their work—and any subsequent writes—could then be silently rolled back when the connection was closed. All three now use `db.atomic()`, consistent with the other write methods.

* `sqlite-utils drop-table` 命令现在拒绝删除视图,而 `drop-view` 拒绝删除表。之前如果名称匹配,每个命令都会静默删除错误类型的对象。现在两者都会退出并报错,提示应使用的正确命令。

* The `sqlite-utils drop-table` command now refuses to drop a view, and `drop-view` refuses to drop a table. Previously each would silently drop the wrong type of object if the name matched. Both now exit with an error suggesting the correct command to use.

* 新迁移系统应用的迁移现在在事务中运行,同时记录迁移已应用。如果迁移引发异常,其更改将回滚并保持待处理状态,因此在修复错误后可以安全地重新应用。无法在事务中运行的迁移(例如执行 `VACUUM` 的迁移)可以使用 `@migrations(transactional=False)` 选择退出——参见迁移和事务。

* Migrations applied by the new migrations system now run inside a transaction, together with the record of the migration having been applied. If a migration raises an exception its changes are rolled back and it stays pending, so it can be safely re-applied after the error is fixed. Migrations that cannot run inside a transaction, such as those executing `VACUUM`, can opt out using `@migrations(transactional=False)`—see Migrations and transactions.

* `table.upsert()` 和 `table.upsert_all()` 现在能检测现有表的主键或复合主键,因此当向已有主键的表执行 upsert 时,不再需要 `pk=` 参数。

* `table.upsert()` and `table.upsert_all()` now detect the primary key or compound primary key of an existing table, so the `pk=` argument is no longer required when upserting into a table that already has a primary key.

* 现在可以使用 `db.table(table_name).insert({})` 向现有表插入一行完全由默认值组成的记录,使用 `INSERT INTO ... DEFAULT VALUES` 语法。(#759)

* `db.table(table_name).insert({})` can now be used to insert a row consisting entirely of default values into an existing table, using `INSERT INTO ... DEFAULT VALUES`. (#759)

* 对 `sqlite-utils migrate` 命令的改进:`--stop-before` 的值如果不匹配任何已知迁移,现在会报错而不是被静默忽略;`--stop-before` 现在能正确处理仍使用旧版 `sqlite_migrate.Migrations` 类的迁移文件;`--list` 现在是只读操作,不再创建数据库文件或迁移跟踪表。`migrations.applied()` 现在按迁移应用顺序返回迁移。

* Improvements to the `sqlite-utils migrate` command: `--stop-before` values that do not match any known migration are now an error instead of being silently ignored, `--stop-before` now works correctly with migration files that still use the older `sqlite_migrate.Migrations` class, and `--list` is now a read-only operation that no longer creates the database file or the migrations tracking table. `migrations.applied()` now returns migrations in the order they were applied.

* 新增 `db.begin()`、`db.commit()` 和 `db.rollback()` 方法,用于手动控制事务,作为 `db.atomic()` 上下文管理器的替代方案。

* New `db.begin()`, `db.commit()` and `db.rollback()` methods for taking manual control of transactions, as an alternative to the `db.atomic()` context manager.

* 新增文档:**事务与保存更改** 描述了事务如何工作以及更改何时提交,新的 **升级** 页面详细说明了在主要版本之间迁移所需的更改。

* New documentation: **Transactions and saving your changes** describes how transactions work and when changes are committed, and a new **Upgrading** page details the changes needed to move between major versions.

互动版:针对本篇提问 →

接着读 · Read next