Skip to main content

NamedRow a Better Sqlite3 row_factory for Python

Currently, sqlite3 returns rows by tuple or sqlite3.Row for dict-style, index access. I constantly find myself wanting attribute access like namedtuple for rows. I find attribute access cleaner without the brackets and quoting field names. Unlike previous discussions, I don't want to use the namedtuple object. I appreciate the simple API and minimal memory consumption of sqlite3.Row and used it as my guide in creating sqlite3.NamedRow to allow attribute and dict-style access faster than sqlite3.Row.

Why a new object instead of adding attribute access to the existing sqlite3.Row? .. TEASER_END There is an existing member method keys and any table with the field "keys" would cause a hard to debug, easily avoidable, collision.


  • Optimized in C, so it will be faster than any python implementation.

  • Access columns by attribute for all valid names and by index for all names.

  • Iterate over fields by name/value pairs.

  • Works with standard functions len and contains.

  • Identical memory consumption to sqlite3.Row with two references: the data tuple and the cursor description.

  • Identical speed to sqlite3.Row if not faster. Timing usually has it slightly faster for index by name or attribute, but it is almost identical.

Compare to sqlite3.Row

NamedRow only has class dunder methods to allow any valid field name. When the field name would be an invalid attribute name, you have two options: either use the SQL AS in the select statement or index by name. To get the field names use the iterator or to the same from the cursor.description.

titles = [x[0] for x in named_row]
titles = [x[0] for x in cursor.description]
titles = dict(named_row).keys()

Name and dict access are no longer case-insensitive. There are three reasons for this.

  1. Case-insensitive comparison only works well for ASCII characters. In a Unicode world, case-insensitive edge cases create unnecessary errors. Looking at a several existing codebases, this feature of Row is almost never used and I believe is not needed in NamedRow.

  2. Case-insensitivity is not "pythonic": "special cases aren't special enough to break the rules". Where, row.Name, and row.NAME are all the same it gives off the faint code smell of something wrong. When case-insensitivity is needed and the query SELECT can not be modified, sqlite3.Row is still there.

  3. Code is simpler and easier to maintain without edge cases.

  4. It is faster.

Timing Results

NamedRow is faster than sqlite3.Row for index-by-name access. As the number of fields in the row increases, NamedRow significantly out performs sqlite3.Row in the graph below. Yes, we are already talking about small units of time, but when working with thousands of rows, the cumulative savings can be noticable.


I wanted to time the creation of the row_factory object and accessing each field within that object. I started by creating some repeatably random length fields.


def make_field(letter_idx: int) -> str:
    """Make a string of length between 4 and 15."""
    return FIELD_MAP[letter_idx] * rando.randrange(4, 15)

fields = [make_field(i) for i in range(number_of_fields)]

I use this to create my setup code.

import sqlite3
cursor = sqlite3.Connection(':memory:').cursor()
# fields a sequence of fieldnames
data = tuple(1 for x in range(len(fields)))

# Setup the factory_row
cursor.factory_row = sqlite3.NamedRow
select = ",".join([f"{i} as {field}" for i, field in enumerate(fields)])
cursor.execute("SELECT {select}")
  1. NamedRow is significanly faster than sqlite3.Row for index-by-name and attribute access.

  2. NamedRow attribute access is faster than index-by-name. The index incures a bit of overhead in determing if the index is by integer or by string which we see.

  3. NamedRow and sqlite3.Row have near identical performance when indexing by integer.

Usage Examples

>>> import sqlite3
>>> c = sqlite3.Connection(":memory:").cursor()
>>> c.row_factory = sqlite3.NamedRow
>>> named_row = c.execute("SELECT 'A' AS letter, '.-' AS morse, 65 AS ord").fetchone()

>>> len(named_row)
>>> 'letter' in named_row
>>> row == row
>>> hash(row)

#Index by number and range.
>>> named_row[0]
>>> named_row[1:]
('.-', 65)

#Index by column name.
>>> named_row[ord]

#Access by attribute.
>>> named_row.morse

#Iterate row for name/value pairs.
>>> dict(named_row)
{'letter': 'A', 'morse': '.-', 'ord': 65}
>>> tuple(named_row)
(('letter', 'A'), ('morse', '.-'), ('ord', 65))

Makefile for Python Projects

A project is more than just code. There is the virtual environment, static analysis, tests, packaging and uploading to PiPy. All of these involve some command line interaction. These commands are drilled into my head as I use them but fade after time. When I need to use them again, I have to either look back at my notes or google search. I wanted a different, better way.

Read more…

No More Adding *.swp in .gitignore

Every time I create a new git repo, I end up needing to create a new .gitignore file. Vim temp files keep showing up, begging to be put into the repository. It's like the gnats when you mow the lawn, you can ignore them for awhile, but then you just have to do something about it.

Read more…

Better Git Commit Messages with Vim

If you are using git commit -m to bang out your commit messages, I hope you really know what you are doing. I know I don't and if you have to ask yourself, you are not that person either. We should use a commit template to help us create a good commit message.

Read more…

Jidn, a New Theme for Nikola

It took me a while to settle on which static site generator I would use. The decision was only ever between two, Pelican and Nikola. I finally chose Nikola as it used existing components, which I feel allows its developers to focus more on their project and spread the load amongst many others. However, none of the themes really hit me.

Read more…

Vim Fails Syntax Highlighting on a Single File

I had an odd thing happen to me. A python file I was editing lost its syntax highlighting, but only that file. All the other python files still showed syntax correctly. Exiting and re-entering vim changed nothing I tried both of the following commands in vim, however, neither had any impact.

Read more…