Python SQLite Testing Migrations

Python sqlite3 executescript: Why a Failed Migration Can Leave Committed Changes

The Prompt Shelf ·

You put a migration inside with con:, the second SQL statement fails, and you expect the first change to disappear. With Python’s sqlite3.executescript(), that expectation can be wrong. The transaction mode and the boundary inside the script matter.

This article is for developers who maintain a small Python migration or data-maintenance runner, including code written with an assistant. It does not test an AI product or recommend executing generated SQL against a production database.

The question is what persisted, not whether an exception appeared

Our fixture creates an empty items table in a disposable database. Each case gets a separate file. The failing script inserts row 1, then attempts an insert into a nonexistent table. Some cases first insert row 99 without committing it.

Every failing case raises OperationalError. We then close the connection, open a new connection, and inspect the saved rows. Checking on a new connection distinguishes persisted state from changes still visible only to the original connection.

We ran Python 3.14.3, SQLite 3.51.3, on macOS. No real database, credentials, network service, or migration framework was used.

Mode and scriptPending row before the scriptRows after failure and reopening
Explicitly selected legacy mode, no SQL transaction wrapperNone1
Legacy mode, no SQL wrapper991, 99
Legacy mode, BEGIN and COMMIT inside scriptNoneNone
Legacy mode, BEGIN and COMMIT inside script9999
autocommit=False, no SQL wrapper99None

A sixth, successful control used legacy mode with BEGIN and COMMIT inside the script and inserted rows 1 and 2. Both were present after reopening. The rollback cases therefore do not merely demonstrate a runner that never writes anything.

Tested missing-table failure: legacy mode preserves rows 1 and 99; autocommit=False leaves neither after rollback and reopening. Both scripts omit SQL BEGIN.

The diagram compares the two pending-row cases without SQL BEGIN. The table above also covers explicit BEGIN; its result must not be inferred from this diagram.

Why with con: did not protect the first cases

Python documents two relevant rules: in LEGACY_TRANSACTION_CONTROL, executescript() first commits a pending transaction; it does not otherwise supply transaction control for the script. The connection context manager handles an open transaction on exit, but does not create one merely by entering the block. Python sqlite3 reference

SQLite also distinguishes automatically started transactions from explicit BEGIN transactions. Without a transaction spanning the script, a completed statement can already be committed before a later statement fails. An error does not universally mean the entire transaction was automatically rolled back. SQLite transaction documentation

Our fourth case is the important boundary: adding BEGIN inside the script rolled back row 1, but it did not recover pending row 99. That earlier row had already been committed before the script’s BEGIN was reached. Wrapping only the new script cannot retroactively include earlier work.

Reproduce only in a disposable fixture

The companion lab ZIP uses the standard library and explicitly selects the mode in each case. It requires Python 3.12 or later for the autocommit argument; only the runtime recorded above was tested.

Extract the ZIP into a new disposable writable folder, open its sqlite-script-lab directory, and run:

python3 reproduce.py

It creates and removes temporary databases, prints the six outcomes, and writes results.json beside the script. Running it again replaces that result file. It does not accept a real database path. If an assertion fails, keep the runtime version and failure output rather than changing the expected result to make it pass.

The deliberate SQL failure is:

INSERT INTO items VALUES(1);
INSERT INTO missing VALUES(2);

For the explicit transaction cases, the harness places BEGIN; before those statements and COMMIT; afterward. The exception escapes the with con: block and is caught outside it. This placement is intentional: swallowing an error inside the context can change the transaction’s exit behavior.

The autocommit=False case is a comparison, not a suggestion to change every existing runner blindly. It rolls back the pending row and script insert in this fixture. Mixing that mode with an additional BEGIN is a different program, and is not tested here.

Choose a transaction contract before changing the runner

RequirementWhat to establish in a disposable test
Earlier pending work and the script must succeed or fail togetherThe selected mode and runner must keep them in the same transaction; test a sentinel inserted before the script
Only this script is one unit of workBegin with no unrelated pending work; verify the script’s transaction boundaries and failure path
The SQL contains its own transaction commandsReview those commands before adding any outer wrapper; do not blindly prepend BEGIN
A failure has already occurredInspect persisted state before retrying; do not infer rollback from the exception alone

Do not split arbitrary SQL at semicolons to manufacture a transaction wrapper. Strings, triggers and other SQL constructs make that a parsing problem. Do not interpolate untrusted values into a script. This lab uses fixed SQL literals and tests one missing-table error; it is not a general migration executor.

For a review request, record the Python/SQLite versions, connection mode, whether work was pending before the call, where transaction commands occur, and what an independently reopened connection sees after an injected failure. Include a successful control and an expected failure. A project instruction that says only “use transactions” leaves these decisions unspecified.

What remains outside this experiment

We did not test concurrent writers, process crashes, disk-full errors, power loss, external side effects, Windows, an ORM, or an agent’s execution behavior. We did not establish that arbitrary generated SQL is safe. autocommit=True is also outside this experiment.

Transaction recovery does not replace a tested backup. The existing SQLite WAL backup guide addresses whether a copied database actually contains committed data. This experiment addresses what a failing SQL batch commits in the first place. The timeout guide addresses process termination, which is another separate boundary.

No paid service is required. The useful outcome is a failure test for the actual transaction contract—not confidence based solely on a context manager, a caught exception, or an assistant’s statement that a migration was rolled back.

Related Articles

Explore the collection

Browse all AI coding rules — CLAUDE.md, .cursorrules, AGENTS.md, and more.

Browse Rules