SQLite Basics: Access Query Columns by Name with sqlite3.Row

Tuple positions become hard to follow when a query grows. Python can return rows with both named and positional access. This example also checks a subtle setup detail: an existing cursor keeps the row factory it had when it was created.

Prepare a scratch directory

Tested on Linux with CPython 3.12.14 and SQLite 3.53.1. You need Python with SQLite support, Bash, a text editor, and standard mktemp, rm, and rmdir utilities. No third-party Python packages are required. Run Python without -O so the checks remain enabled.

Keep the same terminal open. Stop if setup fails, before saving the source:

start_dir="$PWD"
work_dir="$(mktemp -d)" || exit 1
cd -- "$work_dir" || exit 1

Run the example

Save this as demonstrate-row-factory.py:

import sqlite3

connection = sqlite3.connect(":memory:")
try:
    old_cursor = connection.cursor()
    connection.row_factory = sqlite3.Row
    row = connection.execute(
        "SELECT 7 AS item_id, 'notebook' AS item_name, 4 AS quantity"
    ).fetchone()
    assert row["item_id"] == row[0] == 7
    assert row["ITEM_NAME"] == "notebook"
    print("Columns:", ", ".join(row.keys()))
    print(f"Item: {row['item_name']} ({row['quantity']})")
    print("As a dictionary:", dict(row))

    old_row = old_cursor.execute("SELECT 7 AS item_id").fetchone()
    assert isinstance(old_row, tuple)
    print("Earlier cursor type:", type(old_row).__name__)
    old_cursor.row_factory = sqlite3.Row
    named_row = old_cursor.execute("SELECT 7 AS item_id").fetchone()
    assert named_row["item_id"] == 7
    print("Updated cursor type:", type(named_row).__name__)

    joined = connection.execute(
        "SELECT a.id AS source_id, b.id AS target_id "
        "FROM (SELECT 1 AS id) AS a CROSS JOIN (SELECT 2 AS id) AS b"
    ).fetchone()
    assert dict(joined) == {"source_id": 1, "target_id": 2}
    print("Joined IDs:", joined["source_id"], joined["target_id"])
finally:
    connection.close()

Run it:

python3 -B demonstrate-row-factory.py

Observed output:

Columns: item_id, item_name, quantity
Item: notebook (4)
As a dictionary: {'item_id': 7, 'item_name': 'notebook', 'quantity': 4}
Earlier cursor type: tuple
Updated cursor type: Row
Joined IDs: 1 2

Read the result

The first row accepts both an index and a column name; the uppercase lookup also succeeds. The cursor created before the connection setting still produces a tuple. Updating that cursor explicitly changes its next result. Finally, the join assigns distinct labels to the two IDs.

Apply it carefully

Set the connection factory before creating cursors for predictable application code. Use explicit column lists and unique aliases, especially in joins. Duplicate labels cannot represent distinct fields reliably in a dictionary. A row is not a mutable dictionary; convert it only when the caller actually needs one. A query with no matching result still makes fetchone() return None, so check for that before indexing.

The official reference documents the API behavior. These checks exercise the controlled fixture above.

Clean up

Remove the saved source, return to the starting directory, then remove the empty practice directory:

rm -- demonstrate-row-factory.py
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

If the directory is not empty, inspect its contents before removing anything else.

Acknowledgements

This post was created with assistance from AI.