Using a namedtuple factory with Python SQLite

Posted on June 8, 2010 in python sqlite

The sqlite3 module provides sqlite.Row for optimized mapping access by column name, but you can achieve equivalent, lightweight behavior using collections.namedtuple.

The python sqlite3 module ships with sqlite.Row, a highly optimized row_factory. It supports mapping access by column name. With python version 2.6 namedtuple was added to the collections module. Namedtuples are used to create tuple-like objects that have fields accessible by attribute lookup as well as being indexable and iterable. So they are a perfect alternative for the sqlite.Row factory;

from collections import namedtuple

def namedtuple_factory(cursor, row):
    """
    Usage:
    con.row_factory = namedtuple_factory
    """
    fields = [col[0] for col in cursor.description]
    Row = namedtuple("Row", fields)
    return Row(*row)

Usage:

>>> import sqlite3 >>> conn = sqlite3.connect(":memory:") 
>>> c = conn.cursor() 
>>> c.execute('''create table stocks (date text, trans text, symbol text, qty real, price real)''') 
<sqlite3.Cursor object at 0x8cf4d10> 

>>> c.execute("""insert into stocks values ('2006-01-05','BUY','RHAT',100,35.14)""") 
<sqlite3.Cursor object at 0x8cf4d10> 

>>> conn.commit() 
>>> c.close() 
>>> conn.row_factory = namedtuple_factory 
>>> c = conn.cursor() 
>>> c.execute("select \* from stocks") 
<sqlite3.Cursor object at 0x8cf4d40> 

>>> r = c.fetchone() 
>>> type(r) 
<class '__main__.Row'> 

>>> r Row(date=u'2006-01-05', trans=u'BUY', symbol=u'RHAT', qty=100.0, price=35.140000000000001) 
>>> r.date 
u'2006-01-05' 
>>> for member in r:
...     print member 
2006-01-05 BUY RHAT 100.0 35.14