github.com/hoffmann Peter Hoffmann on Stackoverflow @peterhoffmann on twitter Peter Hoffmann on Facebook Contact me per email Subscribe to Atom Feed

Peter Hoffmann

Software Engineer
prev page next page

Using a namedtuple factory with python sqlite

Posted on June 8, 2010
#python #sqlite

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