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 script | Pending row before the script | Rows after failure and reopening |
|---|---|---|
| Explicitly selected legacy mode, no SQL transaction wrapper | None | 1 |
| Legacy mode, no SQL wrapper | 99 | 1, 99 |
Legacy mode, BEGIN and COMMIT inside script | None | None |
Legacy mode, BEGIN and COMMIT inside script | 99 | 99 |
autocommit=False, no SQL wrapper | 99 | None |
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.

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
| Requirement | What to establish in a disposable test |
|---|---|
| Earlier pending work and the script must succeed or fail together | The 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 work | Begin with no unrelated pending work; verify the script’s transaction boundaries and failure path |
| The SQL contains its own transaction commands | Review those commands before adding any outer wrapper; do not blindly prepend BEGIN |
| A failure has already occurred | Inspect 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.