Use sqlite3.Row with duplicate column aliases carefully

By Batu · English technical notes

Also published in our primary archive.

Use sqlite3.Row with duplicate column aliases carefully

When sqlite3.Row receives SELECT 1 AS value, 2 AS value, use positional access if you must inspect both columns, but correct the SQL with unique aliases before using name lookup. In this example, row['value'] silently resolves to the first value, while row[0] and row[1] show the actual pair: 1,2.

The in-memory connection uses sqlite3.Row as its row_factory; a small table and seed row are printed to make the fixture explicit, although the duplicate-alias query itself is a constants-only query. Printing row.keys() exposes the duplicate names. The naïve key lookup is not an exception and is therefore easy to mistake for a reliable result. The first assertions record the observed ambiguity and preserve access to both positions. The corrected query renames the expressions first_value and second_value, after which named access has one meaning per requested column.

Python documents sqlite3.Row as a row type supporting both index and case-insensitive name access, with minimal overhead (Python sqlite3 documentation). Duplicate result names are a query-shape problem, not a guarantee that the driver can disambiguate keys for you. No newer Python-only API is used; this requires Python 3 with the standard-library sqlite3 module. The assertions demonstrate this exact query and row factory, not every driver’s duplicate-name behavior.

AI assistance disclosure: Batu Lab Notes used AI assistance to draft this reproducible synthetic example; the assertions define its claimed result.

import sqlite3

con = sqlite3.connect(":memory:")
con.row_factory = sqlite3.Row
con.execute("CREATE TABLE probe(id INTEGER PRIMARY KEY)")
con.execute("INSERT INTO probe VALUES (1)")
print(con.execute("SELECT sql FROM sqlite_master WHERE name='probe'").fetchone()[0])
print("seed=probe.id:1")

row = con.execute("SELECT 1 AS value, 2 AS value").fetchone()
print("naive columns=" + ",".join(row.keys()))
print(f"naive key value={row['value']}")
assert row["value"] == 1
assert (row[0], row[1]) == (1, 2)
print(f"positions={row[0]},{row[1]}")

fixed = con.execute("SELECT 1 AS first_value, 2 AS second_value").fetchone()
assert fixed["first_value"] == 1
assert fixed["second_value"] == 2
print(f"unique aliases={fixed['first_value']},{fixed['second_value']}")

CREATE TABLE probe(id INTEGER PRIMARY KEY)
seed=probe.id:1
naive columns=value,value
naive key value=1
positions=1,2
unique aliases=1,2