Python SQLite row factories: reject duplicate output names before mapping

Observed contract

One query returns a receipt ID and amount as separate names. A second uses aliases that differ only by case. The factory rejects that second row rather than letting a dictionary overwrite a value under a normalized key.

Boundary

This guard operates on the result shape after SQLite executes a query; it is not SQL authorization. It protects a local mapping contract, while the caller still needs parameter binding, query ownership, and explicit connection closure.

Executable case

import sqlite3

def unique_name_row(cursor, values):
    names = [column[0] for column in cursor.description]
    normalized = [name.casefold() for name in names]
    if len(set(normalized)) != len(normalized):
        raise ValueError("duplicate result column")
    return dict(zip(names, values, strict=True))

connection = sqlite3.connect(":memory:")
try:
    connection.row_factory = unique_name_row
    receipt = connection.execute(
        "SELECT 47 AS receipt_id, 26 AS amount_cents").fetchone()
    print("receipt", receipt)
    try:
        connection.execute("SELECT 47 AS total, 26 AS TOTAL").fetchone()
    except ValueError:
        print("duplicate_rejected", True)
finally:
    connection.close()

Output

receipt {'receipt_id': 47, 'amount_cents': 26}
duplicate_rejected True

Cost

The factory builds names, a normalized list, a set, and a dictionary for every fetched row. That adds O(c) work and storage for c columns per row; validate a stable query shape once if a high-volume path needs less overhead.

Common Mistakes

• Plain dict construction can hide duplicate aliases by overwriting one value.
• Set connection.row_factory before creating cursors that should inherit it.
• A with connection block manages transactions, not connection closure.

Connected lessons

• Python SQLite Row: duplicate result names make named access ambiguous
• Python sqlite3 transactions: bind values and roll back failed batches
• Python SQLite authorizer: deny a statement on the connection that executes it

Test this contract.