Check changes after a deliberate update

By Batu · English technical notes

A change count is most useful when the intended target is explicit. This example inserts one task with ID 7, then deliberately changes that task’s state from "queued" to "done". It obtains two counts: updated.rowcount, set on Python’s cursor after the DML statement, and SQLite’s changes() SQL function.

Both counts are asserted to be 1; a separate query then retrieves the stored state. The output therefore reports the affected-row values together with the actual post-update value. Python specifies that rowcount is set after execute() completes and is -1 for statements such as SELECT. SQLite specifies that changes() reports rows modified by the most recently completed INSERT, UPDATE, or DELETE on the connection. See Python’s cursor reference and SQLite’s changes() reference.

Neither number decides whether an update was semantically appropriate. SQLite’s rules for change counts also have qualifications around triggers and related mechanisms. Identify the intended record in WHERE, then retrieve or otherwise validate the business result when it matters. No newer Python-specific API is used; the fixture is an in-memory standard-library sqlite3 database. AI assistance was used to draft this article.

Example

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE task (id INTEGER PRIMARY KEY, state TEXT)")
con.execute("INSERT INTO task VALUES (?, ?)", (7, "queued"))

updated = con.execute("UPDATE task SET state = ? WHERE id = ?", ("done", 7))
changed = con.execute("SELECT changes()").fetchone()[0]
state = con.execute("SELECT state FROM task WHERE id = 7").fetchone()[0]

assert (updated.rowcount, changed, state) == (1, 1, "done")

print(f"rowcount={updated.rowcount} changes={changed} state={state}")

Expected output:

rowcount=1 changes=1 state=done