SQLite Script Fails but Earlier Changes Remain: Choose One Transaction Owner

If a Python migration runs several SQL statements and then fails, calling rollback() afterwards does not prove the earlier statements were undone. First identify the connection's transaction mode and whether a transaction was still active at the error. Test on a disposable database before changing a migration that touches real data.

Reproduce partial completion without touching a file

This small experiment deliberately references a missing table. On a legacy-mode connection, the first insert can survive the later error because the script does not open a transaction around both statements.

import sqlite3

con = sqlite3.connect(":memory:")  # legacy transaction control
try:
    con.execute("CREATE TABLE demo(value INTEGER)")
    try:
        con.executescript("""
            INSERT INTO demo VALUES(1);
            INSERT INTO missing VALUES(2);
        """)
    except sqlite3.OperationalError:
        print("transaction active:", con.in_transaction)
        con.rollback()
    print(con.execute("SELECT value FROM demo").fetchall())
finally:
    con.close()

The observed result in the legacy-mode test was False followed by [(1,)]. The error stopped later work; it did not make the whole script atomic. A successful rollback call with no pending transaction is therefore an inadequate completion check.

For an owned legacy connection, put the boundary inside one script

Use a fresh connection that your migration owns. Run one trusted script with its transaction boundary inside that same call, then handle failure explicitly. This example has no earlier work for the call to commit.

import sqlite3

con = sqlite3.connect(":memory:")
try:
    con.execute("CREATE TABLE demo(value INTEGER)")
    try:
        con.executescript("""
            BEGIN;
            INSERT INTO demo VALUES(1);
            INSERT INTO missing VALUES(2);
            COMMIT;
        """)
    except sqlite3.Error:
        if con.in_transaction:
            con.execute("ROLLBACK")
        assert con.execute("SELECT value FROM demo").fetchall() == []
        # In application code, report or re-raise the original failure.
    else:
        raise AssertionError("The deliberate failure did not occur")
finally:
    con.close()

Here, reaching COMMIT is the success path. The injected failure leaves an active transaction for the handler to roll back. Check the stored result as well as the exception. For a file-backed integration test, close the writer and inspect the database from a separate connection too.

Several script files need a single transaction contract

A BEGIN in the first file does not protect a loop of separate legacy-mode executescript() calls: the next call can commit that pending transaction. You can check that trap without a file:

import sqlite3

con = sqlite3.connect(":memory:")
try:
    con.execute("CREATE TABLE demo(value INTEGER)")
    con.executescript("BEGIN; INSERT INTO demo VALUES(1);")
    con.executescript("INSERT INTO demo VALUES(2);")
    con.rollback()
    assert con.execute("SELECT value FROM demo").fetchall() == [(1,), (2,)]
finally:
    con.close()

For trusted migration files, one approach is to assemble and validate a single script with one outer transaction. Files must follow that contract: embedded commits, rollbacks and statements with incompatible transaction requirements need separate handling. Include real statement separators and a newline between files so a final line comment cannot consume the next file. Do not split arbitrary SQL on semicolons; strings and trigger bodies can contain them. Prefer known individual statements with execute() when you need value parameters.

Python 3.12 and later: check the explicit autocommit setting

Current Python documentation qualifies the pre-script implicit commit by legacy transaction mode and a pending transaction. With autocommit=False, the connection maintains a transaction; blindly adding another BEGIN can fail. Choose connection-managed commit/rollback for that mode. With autocommit=True, Python connection commit() and rollback() do not manage an explicit SQL transaction; the experiment uses SQL ROLLBACK for that case.

Do not change a borrowed connection's mode to make a snippet work: that changes the caller's transaction contract. Check con.autocommit where supported, con.isolation_level, con.in_transaction, and the actual Python version. Local checks covered Python 3.11, 3.13 and 3.14, including explicit modes where available; they are not evidence about your production database.

Verify success, failure and resource cleanup separately

Add a success fixture to your migration's own integration tests, verify schema and rows after reopening, and test a failure after an early write. Keep an appropriate backup before a real migration. Transaction handling also does not close the connection: see the separate connection lifetime guide.