Python sqlite3 transactions: bind values and roll back failed batches

Last updated: 30 Sept 20264 min read

tutorial

A SQLite transaction groups changes so a failure can roll back the group rather than leaving a partially inserted batch.

Download Python source kit

Operation contract

The fixture creates an in-memory receipt table with a positive-amount check. Two bound inserts run in one transaction: the second violates the check, so the first is rolled back too. A later valid insert commits and the final query returns that one row. SQL values use placeholders rather than string formatting.

Failure and ownership boundary

The connection context controls commit/rollback, not connection closure; the fixture closes it in finally. Its in-memory database also disappears after closure. Target-database isolation, concurrent writers, durable files and migration recovery require their own tests. Python inventory project: validate a batch before replacing stored state and Java JDBC transactions: atomic updates and rollback address related failure rules.

Working program

import sqlite3

connection = sqlite3.connect(":memory:")
try:
    connection.execute("CREATE TABLE receipt (id INTEGER PRIMARY KEY, amount INTEGER CHECK(amount > 0))")
    try:
        with connection:
            connection.execute("INSERT INTO receipt VALUES (?, ?)", (41, 125))
            connection.execute("INSERT INTO receipt VALUES (?, ?)", (42, -1))
    except sqlite3.IntegrityError:
        print("batch rolled back")
    print(connection.execute("SELECT count(*) FROM receipt").fetchone()[0])
    with connection:
        connection.execute("INSERT INTO receipt VALUES (?, ?)", (43, 75))
    print(connection.execute("SELECT id, amount FROM receipt ORDER BY id").fetchall())
finally:
    connection.close()

Output

batch rolled back
0
[(43, 75)]

Costs and limits

The test inserts three tiny candidate rows and queries a count and one result. Database plan, disk durability, locking schedules and pooled connection behavior are outside this in-memory fixture.