Row Factories — Dict-Like Access

~10 min · python, row-factory, ergonomics

Level 0Scout

0 XP0/80 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

Stop indexing rows by integer

By default, sqlite3 returns each row as a tuple. You access columns by index — fragile, hard to read, breaks when the SELECT order changes. The fix is one line:

conn.row_factory = sqlite3.Row

Now each row is a sqlite3.Row — accessible by index or by column name, with a stable iteration order. It's not a dict, but dict(row) converts it cleanly when you need one (e.g., to JSON-serialize).

Tip: Set row_factory = sqlite3.Row on every connection in production code. The runtime overhead is negligible; the readability win is enormous; the bug class 'someone added a column and shifted indices' disappears.

Code

sqlite3.Row in action·python

import sqlite3, json

conn = sqlite3.connect('demo.db')
conn.row_factory = sqlite3.Row

row = conn.execute('SELECT id, email, username FROM users LIMIT 1').fetchone()

print(row['email'])              # 'a@x.com' — by name
print(row[1])                    # 'a@x.com' — by index still works
print(row.keys())                # ['id', 'email', 'username']

# Convert to a real dict (e.g., for JSON)
print(json.dumps(dict(row)))

Custom row factory — return your own dataclass·python

import sqlite3
from dataclasses import dataclass

@dataclass
class User:
    id: int
    email: str
    username: str

def user_factory(cursor, row):
    cols = [c[0] for c in cursor.description]
    return User(**dict(zip(cols, row)))

conn = sqlite3.connect('demo.db')
conn.row_factory = user_factory
for user in conn.execute('SELECT id, email, username FROM users'):
    print(user.email)

External links

sqlite3 — Row factories

Exercise

Take a query you wrote earlier that returned tuples. Switch the connection to sqlite3.Row and refactor the calling code to access columns by name. Then write a custom row factory that returns instances of a dataclass. Pick which one you'd use for what kind of code.

Progress

Progress is local-only — sign in to sync across devices.

← Previousexecutemany — Fast Bulk Operations Next →Transactions in Python — The Implicit Model

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.