Explain sqlite3 cursor rowcount after SELECT

By Batu · English technical notes

Explain sqlite3 cursor rowcount after SELECT

After a sqlite3 SELECT, do not assume cursor.rowcount is the result length. In Python’s sqlite3 module it remains -1 for statements other than INSERT, UPDATE, DELETE, and REPLACE; fetch the rows and use len() when the result set is intentionally small enough to materialize.

This in-memory fixture makes the mismatch explicit. It creates a three-row tasks table, executes an ordered SELECT, and prints the naive value before fetching: -1. It then calls fetchall(), which returns three tuples, yet rowcount is still -1. The assertions distinguish the cursor metadata from the actual fetched collection.

The corrected result is len(rows) == 3. That count is exact for this fixture because the program deliberately fetched every result into a list. It is not a recommendation to call fetchall() merely to count a large production query: doing so allocates all returned rows in Python. When all you need is a database count, write a deliberate SELECT COUNT(*); when you need to process many rows, iterate or fetch in chunks and maintain a count if appropriate.

The documented limitation is important for tests too: rowcount is useful for qualifying modification statements, not for asserting how many rows a SELECT produced. This example requires Python 3 and only standard-library sqlite3.

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

Sources: Python sqlite3 Cursor.rowcount and Python sqlite3 Cursor.fetchall.

import sqlite3


con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE tasks (id INTEGER PRIMARY KEY, label TEXT)")
con.executemany(
    "INSERT INTO tasks(label) VALUES (?)",
    [("lint",), ("test",), ("ship",)],
)
schema = con.execute(
    "SELECT sql FROM sqlite_master WHERE name = 'tasks'"
).fetchone()[0]
seed_rows = con.execute("SELECT id, label FROM tasks ORDER BY id").fetchall()

cursor = con.execute("SELECT label FROM tasks ORDER BY id")
naive_rowcount = cursor.rowcount
rows = cursor.fetchall()
corrected_count = len(rows)

assert naive_rowcount == -1
assert cursor.rowcount == -1
assert corrected_count == 3
print(f"schema: {schema}")
print(f"seed rows: {seed_rows}")
print(f"naive SELECT rowcount: {naive_rowcount}")
print(f"fetched rows: {rows}")
print(f"corrected len(fetchall()): {corrected_count}")
con.close()

schema: CREATE TABLE tasks (id INTEGER PRIMARY KEY, label TEXT)
seed rows: [(1, 'lint'), (2, 'test'), (3, 'ship')]
naive SELECT rowcount: -1
fetched rows: [('lint',), ('test',), ('ship',)]
corrected len(fetchall()): 3