Fetch every row from SQLite UPDATE RETURNING before reading rowcount

By Batu · English technical notes

Also published in our primary archive.

Fetch every row from SQLite UPDATE RETURNING before reading rowcount

For Python sqlite3, fetch the complete UPDATE ... RETURNING result before reading Cursor.rowcount. Immediately after execute(), rowcount can be 0 because the cursor has not yet completed its result stream. fetchall() gives you the returned rows; after that complete fetch, rowcount reports the number of directly modified rows.

This in-memory fixture changes two pending tasks. The first printed value demonstrates the timing trap: it is 0, not evidence that the update changed no rows. The returned IDs are the row-level payload requested by RETURNING; the final 2 is the modification count. The assertions deliberately check both facts independently, so neither is used as a substitute for the other.

Python’s documentation specifies that rowcount is updated only after the statement has run to completion, which requires fetching resulting rows. SQLite documents that RETURNING produces one result row for each directly modified row. SQLite RETURNING requires SQLite 3.35.0 or later; this example otherwise uses longstanding Python sqlite3 APIs and requires no newer Python-only API.

Do not rely on the displayed order of RETURNING rows in a general query: SQLite does not guarantee that order. This small fixture prints its observed IDs, while the assertion compares a sorted copy.

Sources: Python sqlite3.Cursor.rowcount and SQLite RETURNING.

AI assistance disclosure: This article was drafted with AI assistance and verified against the cited documentation and a synthetic in-memory example.

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE task (id INTEGER PRIMARY KEY, state TEXT NOT NULL)")
con.executemany(
    "INSERT INTO task (state) VALUES (?)",
    [("pending",), ("pending",)],
)

cur = con.execute(
    "UPDATE task SET state = 'done' "
    "WHERE state = 'pending' RETURNING id"
)
print("rowcount before fetch:", cur.rowcount)

returned_ids = cur.fetchall()
print("returned ids:", returned_ids)
print("rowcount after fetch:", cur.rowcount)

assert sorted(returned_ids) == [(1,), (2,)]
assert cur.rowcount == 2
con.close()

rowcount before fetch: 0
returned ids: [(1,), (2,)]
rowcount after fetch: 2