Set row_factory to sqlite3.Row for named columns

By Batu · English technical notes

Also published in our primary archive.

By default, rows fetched through Python’s sqlite3 module are tuples. Setting con.row_factory = sqlite3.Row before running the query causes newly created cursors to return sqlite3.Row objects instead. Those rows retain positional access and add lookup by selected column name.

The example inserts the concrete inventory pair ("A-17", 4) and selects sku and quantity. Its assertions exercise both interfaces: row[1] returns the second selected value, while row["sku"] retrieves the value associated with that column name. tuple(row) gives a compact, stable representation for the second printed line. Python documents sqlite3.Row as a row factory with indexed and case-insensitive named access, and its row-factory guide explains assignment on the connection. See also the sqlite3.Row reference.

Named access can make code clearer when query columns move, but it does not verify that the query expresses the correct business meaning. Names come from selected columns or aliases, and a misspelled name still fails at lookup time. sqlite3.Row is an established standard-library feature; no newer Python-specific API is required here. AI assistance was used to draft this article.

Example

import sqlite3

con = sqlite3.connect(":memory:")
con.row_factory = sqlite3.Row
con.execute("CREATE TABLE inventory (sku TEXT, quantity INTEGER)")
con.execute("INSERT INTO inventory VALUES (?, ?)", ("A-17", 4))

row = con.execute("SELECT sku, quantity FROM inventory").fetchone()

assert row["sku"] == "A-17"
assert row[1] == 4
assert tuple(row) == ("A-17", 4)

print(f"{row['sku']}:{row['quantity']}")
print(tuple(row))

Expected output:

A-17:4
('A-17', 4)