Python SQLite Row: duplicate result names make named access ambiguous

Operation contract

A selected receipt has distinct aliases for its ID and state, so named access is clear. A second projection deliberately assigns the same alias to both fields; its keys contain that name twice, while lookup by that name returns only one value. The program does not use the ambiguous projection for a business decision.

Failure boundary

A row factory changes Python result shape, not the database schema or SQL column identity. Assign unique aliases to every projected value before mapping rows into application records. This fixture uses an owned query and does not validate arbitrary received SQL.

Working program

import sqlite3

connection = sqlite3.connect(":memory:")
connection.row_factory = sqlite3.Row
try:
    connection.execute("CREATE TABLE receipts (receipt_id INTEGER, state TEXT)")
    connection.execute("INSERT INTO receipts VALUES (?, ?)", (47, "paid"))
    selected = connection.execute(
        "SELECT receipt_id AS receipt_key, state AS receipt_state FROM receipts"
    ).fetchone()
    print("record", selected["receipt_key"], selected["receipt_state"])
    ambiguous = connection.execute(
        "SELECT receipt_id AS result, state AS result FROM receipts"
    ).fetchone()
    print("duplicate_names", ambiguous.keys().count("result"))
    print("named_value", ambiguous["result"])
finally:
    connection.close()

Output

record 47 paid
duplicate_names 2
named_value 47

Costs and limits

Row objects keep the result values and name lookup structure for each row. Unique aliases cost little and prevent silent confusion when a query joins tables with repeated column names.

Common Mistakes

• Duplicate aliases make name lookup ambiguous even if positional values differ.
• row_factory does not validate a record's domain fields.
• Prefer a deliberate projection to SELECT * when joins add repeated names.

Connected lessons

• Python sqlite3 transactions: bind values and roll back failed batches
• Pandas joins: cardinality validation and missing lookup keys
• Python typing reference: annotations, structural checks and runtime validation differ

Test this contract.

Continue with Python SQLite row factories: reject duplicate output names before mapping.