How do you make Python's sqlite3 return rows as something other than plain tuples?

middleshould knowfreq 45%

answer

Rows need not be tuples
One attribute on the connection
The callable takes cursor and row
Column labels live on the cursor
sqlite3.Row indexes by name too

basics

~20 s

Assign a callable to Connection.row_factory, most often the built-in sqlite3.Row, which yields rows addressable by column name as well as index. A custom factory receives the cursor and the raw tuple and can return a dict, a namedtuple or any object.

solid answer

~40 s

Connection.row_factory takes a callable invoked as factory(cursor, row) for each fetched row, where row is the plain tuple. Setting it to sqlite3.Row gives lightweight row objects that support integer indexing, case-insensitive access by column name, len, iteration, and a keys() method - and dict(row) converts one when you need a real mapping. Cursors created afterwards inherit the connection's factory, and Cursor.row_factory overrides it for one cursor. For any other shape you write the factory yourself, taking the column names from Cursor.description, whose first element of each entry is the column label. The names come from the labels of the result set, so alias computed columns with AS, and beware two columns that end up with the same label, because a dict factory silently keeps only the last.

`Connection.row_factory` takes a callable invoked as `factory(cursor, row)` for each fetched row, where `row` is the plain tuple. Setting it to `sqlite3.Row` gives lightweight row objects that support integer indexing, case-insensitive access by column name, `len`, iteration, and a `keys()` method - and `dict(row)` converts one when you need a real mapping. Cursors created afterwards inherit the connection's factory, and `Cursor.row_factory` overrides it for one cursor. For any other shape you write the factory yourself, taking the column names from `Cursor.description`, whose first element of each entry is the column label. The names come from the *labels* of the result set, so alias computed columns with `AS`, and beware two columns that end up with the same label, because a dict factory silently keeps only the last.

code

python · 9 lines

python

import sqlite3

con = sqlite3.connect(':memory:')
con.row_factory = sqlite3.Row
con.execute('CREATE TABLE place(name TEXT, lat REAL)')
con.execute("INSERT INTO place VALUES ('Oslo', 59.91)")
row = con.execute('SELECT name, lat FROM place').fetchone()
print(row['name'], row[1], row.keys(), dict(row))
con.close()

go deeper

for a junior

Know that fetched rows are tuples by default and that assigning sqlite3.Row to the connection's row_factory lets you index them by column name. Be able to write those two lines from memory.

for a middle

Explain the factory protocol - a callable taking the cursor and the raw tuple - and build a dict factory from cursor.description. Know that cursors inherit the connection's factory and can override it.

for a senior

Show where shaping belongs: one place at connection setup, aliased columns so labels are unambiguous, and a cheap factory because it runs per row. Be ready to justify dataclass rows over dicts at an application boundary.

for a principal

Decide the codebase-wide row contract and where it is enforced: whether raw rows escape the data-access layer at all, and whether the cost of mapping rows to domain objects is paid in a factory, a mapper, or a library you adopt.

The default and why it hurts

By default a fetch from sqlite3 gives you a plain tuple, so code reads row[3] and every reader has to count columns in the SELECT to know what that is. Worse, adding a column to the query in the middle shifts every index after it, and nothing raises - the code just starts using the wrong value. Row shaping fixes this at the boundary rather than at each call site.

The factory protocol

Connection.row_factory holds a callable. For every row produced, sqlite3 calls it as factory(cursor, row): the cursor is the one that ran the query, and row is the tuple that would otherwise have been returned. Whatever the callable returns is what fetchone, fetchmany, fetchall and iteration over the cursor hand back. Two scoping rules matter: a cursor created after the attribute is set inherits it, and Cursor.row_factory set on an individual cursor wins for that cursor. Set it on the connection immediately after connect and you generally never think about it again.

sqlite3.Row

The module ships one factory. sqlite3.Row is a C type built for exactly this job:

index access, row[0], still works, so it is a drop-in replacement for the tuple;
name access, row['name'], works and is case-insensitive with respect to the column label;
row.keys() returns the column names, which is the easy way to inspect a result set;
it supports len, iteration and equality, and it is more compact than a dict per row;
dict(row) converts it when a caller genuinely needs a mutable mapping, for example to serialise.

What it deliberately does not offer is attribute access - there is no row.name - and it is immutable, so you cannot patch a value into a row on its way out.

Writing your own

A dict factory is four lines and the canonical example of the protocol:

python

def dict_factory(cursor, row):
    return {column[0]: value for column, value in zip(cursor.description, row)}

Cursor.description is a sequence of seven-item tuples, one per column, and in sqlite3 only the first item - the column label - is ever filled in; the rest are None, because SQLite does not report the other DB-API fields. The same pattern builds a collections.namedtuple or a dataclass instance, which is the version worth reaching for when the rows flow deep into the application and you want them typed and attribute-addressable. Keep the factory cheap: it runs once per row, so building an expensive object there turns a large result set into a large allocation problem.

Where the names come from

The labels in description come from the result set, not from the schema. SELECT count(*) FROM place gives a column literally labelled count(*); alias it, SELECT count(*) AS n, and the key becomes n. Two columns can share a label - SELECT a.id, b.id FROM ... - and a dict factory will keep only the second, silently. sqlite3.Row has the same ambiguity for name lookup. Aliasing every non-trivial column in the SELECT is the habit that avoids the whole class of problem.

Related but distinct knobs

row_factory shapes the container. Two neighbouring settings shape the values, and interviewers sometimes conflate them:

Connection.text_factory decides what TEXT columns become. It is str by default; setting it to bytes hands back raw bytes, which is the escape hatch for a file whose text is not valid UTF-8 and would otherwise raise on decode.
detect_types=sqlite3.PARSE_DECLTYPES on connect, together with converters registered by sqlite3.register_converter, turns stored values back into Python objects based on declared column types. Note that the default adapters and converters for date and datetime were deprecated in Python 3.12; register your own rather than relying on them.

Choosing in practice

Use sqlite3.Row as the default: it costs nothing, removes index-counting, and keeps tuple compatibility for code that already indexed. Use a dict factory at a serialisation boundary where the rows are about to become JSON. Use a dataclass or namedtuple factory when rows are domain objects that travel far from the query and deserve names, types and behaviour. Do not mix three shapes across one codebase - the value of row shaping is that a reader knows what a row is without checking which connection produced it.

### The default and why it hurts By default a fetch from sqlite3 gives you a plain tuple, so code reads `row[3]` and every reader has to count columns in the SELECT to know what that is. Worse, adding a column to the query in the middle shifts every index after it, and nothing raises - the code just starts using the wrong value. Row shaping fixes this at the boundary rather than at each call site. ### The factory protocol `Connection.row_factory` holds a callable. For every row produced, sqlite3 calls it as `factory(cursor, row)`: the cursor is the one that ran the query, and `row` is the tuple that would otherwise have been returned. Whatever the callable returns is what `fetchone`, `fetchmany`, `fetchall` and iteration over the cursor hand back. Two scoping rules matter: a cursor created after the attribute is set inherits it, and `Cursor.row_factory` set on an individual cursor wins for that cursor. Set it on the connection immediately after `connect` and you generally never think about it again. ### sqlite3.Row The module ships one factory. `sqlite3.Row` is a C type built for exactly this job: - index access, `row[0]`, still works, so it is a drop-in replacement for the tuple; - name access, `row['name']`, works and is case-insensitive with respect to the column label; - `row.keys()` returns the column names, which is the easy way to inspect a result set; - it supports `len`, iteration and equality, and it is more compact than a dict per row; - `dict(row)` converts it when a caller genuinely needs a mutable mapping, for example to serialise. What it deliberately does not offer is attribute access - there is no `row.name` - and it is immutable, so you cannot patch a value into a row on its way out. ### Writing your own A dict factory is four lines and the canonical example of the protocol: ```python def dict_factory(cursor, row): return {column[0]: value for column, value in zip(cursor.description, row)} ``` `Cursor.description` is a sequence of seven-item tuples, one per column, and in sqlite3 only the first item - the column label - is ever filled in; the rest are `None`, because SQLite does not report the other DB-API fields. The same pattern builds a `collections.namedtuple` or a dataclass instance, which is the version worth reaching for when the rows flow deep into the application and you want them typed and attribute-addressable. Keep the factory cheap: it runs once per row, so building an expensive object there turns a large result set into a large allocation problem. ### Where the names come from The labels in `description` come from the result set, not from the schema. `SELECT count(*) FROM place` gives a column literally labelled `count(*)`; alias it, `SELECT count(*) AS n`, and the key becomes `n`. Two columns can share a label - `SELECT a.id, b.id FROM ...` - and a dict factory will keep only the second, silently. `sqlite3.Row` has the same ambiguity for name lookup. Aliasing every non-trivial column in the SELECT is the habit that avoids the whole class of problem. ### Related but distinct knobs `row_factory` shapes the *container*. Two neighbouring settings shape the *values*, and interviewers sometimes conflate them: - `Connection.text_factory` decides what TEXT columns become. It is `str` by default; setting it to `bytes` hands back raw bytes, which is the escape hatch for a file whose text is not valid UTF-8 and would otherwise raise on decode. - `detect_types=sqlite3.PARSE_DECLTYPES` on `connect`, together with converters registered by `sqlite3.register_converter`, turns stored values back into Python objects based on declared column types. Note that the default adapters and converters for date and datetime were deprecated in Python 3.12; register your own rather than relying on them. ### Choosing in practice Use `sqlite3.Row` as the default: it costs nothing, removes index-counting, and keeps tuple compatibility for code that already indexed. Use a dict factory at a serialisation boundary where the rows are about to become JSON. Use a dataclass or namedtuple factory when rows are domain objects that travel far from the query and deserve names, types and behaviour. Do not mix three shapes across one codebase - the value of row shaping is that a reader knows what a row is without checking which connection produced it.

Is sqlite3.Row a dict? What does it refuse to do?
No. It is a compact immutable row type that supports integer indexing, case-insensitive lookup by column label, len, iteration and a keys method, but not attribute access and not item assignment. It is deliberately cheaper than a dict per row. When a caller needs a real mapping - to mutate it or serialise it - convert explicitly with dict(row).
A dict row factory drops a column when two tables in a join both have an id column. Why, and what is the fix?
The keys come from the result-set labels in cursor.description, and both columns report the same label, so the second overwrites the first in the dict. sqlite3.Row is ambiguous in the same way for name lookup. The fix is in the SQL: alias each column explicitly, for example a.id AS place_id and b.id AS hit_id, so every column in the result has a distinct label.
Can different queries on the same connection return differently shaped rows?
Yes. Create a cursor with Connection.cursor and set Cursor.row_factory on it; that cursor's rows use its own factory while the connection's default applies everywhere else. It is useful for a single reporting query that wants dicts inside a codebase that otherwise uses sqlite3.Row, though a codebase where the shape varies per query is harder to read than one that commits to a default.

saying these in an interview costs you the question

Claims sqlite3 returns dicts by default
Thinks sqlite3.Row supports attribute access
Sets row_factory but reads the previous cursor
Expects cursor.description to carry column types
Assumes duplicate column labels raise an error

How do you make Python's sqlite3 return rows as something other than plain tuples?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

The default and why it hurts

The factory protocol

sqlite3.Row

Writing your own

Where the names come from

Related but distinct knobs

Choosing in practice

▾common follow-ups3 questions

▾red flags5 flags

§ context

▾related questions5 questions

▾related topics3 topics

The default and why it hurts

The factory protocol

sqlite3.Row

Writing your own

Where the names come from

Related but distinct knobs

Choosing in practice

answer

go deeper

context