Using a namedtuple factory with Python SQLite
Using collections.namedtuple as row factory for the Python sqlite3 module.
This post is from 2010. Some of the content may be outdated.
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