Use connection total_changes for a controlled fixture update

By Batu · English technical notes

Use connection total_changes for a controlled fixture update

For a controlled SQLite fixture update, capture Connection.total_changes immediately before and after the statement, then subtract. This reports the modifications attributed to that bounded interval on the same connection.

The fixture seeds three members. Its update sets enabled for Ada and Bryn, so the planned outcome is two changed rows. The naive boundary appears next: after that update, a no-op INSERT OR IGNORE is run for the already-present primary key. Reading that second cursor’s rowcount returns zero. It describes the no-op insert, not the earlier update, so using it as accumulated bookkeeping loses the two update changes.

The correction deliberately captures before, executes only the controlled update, and computes after - before before the no-op insert. The assertion and output show exactly 2. Python defines total_changes as the count of rows modified, inserted, or deleted since the connection opened, which is why the delta—not the raw lifetime value—is the useful fixture measurement.

Keep the interval narrow. If triggers, other SQL, or unrelated helper calls run between the two readings, their modifications can contribute to the delta. Also, this count says rows were modified according to SQLite; it is not a business-level proof that all intended effects occurred. total_changes is available in Python 3’s standard-library sqlite3 module.

AI-assistance disclosure: Batu Lab Notes used AI assistance to draft and check this synthetic example.

Sources: Python sqlite3 Connection.total_changes and Python sqlite3 Cursor.rowcount.

import sqlite3


con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE members (name TEXT PRIMARY KEY, enabled INTEGER)")
con.executemany(
    "INSERT INTO members VALUES (?, ?)",
    [("Ada", 0), ("Bryn", 0), ("Cy", 0)],
)
schema = con.execute(
    "SELECT sql FROM sqlite_master WHERE name = 'members'"
).fetchone()[0]
seed_rows = con.execute("SELECT name, enabled FROM members ORDER BY name").fetchall()

before = con.total_changes
con.execute("UPDATE members SET enabled = 1 WHERE name IN ('Ada', 'Bryn')")
after = con.total_changes
controlled_changes = after - before
naive_rowcount = con.execute(
    "INSERT OR IGNORE INTO members VALUES ('Ada', 1)"
).rowcount
rows = con.execute("SELECT name, enabled FROM members ORDER BY name").fetchall()

assert naive_rowcount == 0
assert controlled_changes == 2
assert rows == [("Ada", 1), ("Bryn", 1), ("Cy", 0)]
print(f"schema: {schema}")
print(f"seed rows: {seed_rows}")
print(f"naive rowcount after no-op insert: {naive_rowcount}")
print(f"controlled total_changes delta: {controlled_changes}")
print(f"corrected rows: {rows}")
con.close()

schema: CREATE TABLE members (name TEXT PRIMARY KEY, enabled INTEGER)
seed rows: [('Ada', 0), ('Bryn', 0), ('Cy', 0)]
naive rowcount after no-op insert: 0
controlled total_changes delta: 2
corrected rows: [('Ada', 1), ('Bryn', 1), ('Cy', 0)]