Sqlite and Python -- return a dictionary using fetchone()?

Question

There is actually an option for this in sqlite3. Change the row_factory member of the connection object to sqlite3.Row:

conn = sqlite3.connect('db', row_factory=sqlite3.Row)

or

conn.row_factory = sqlite3.Row

This will allow you to access row elements by name--dictionary-style--or by index. This is much more efficient than creating your own work-around.

The way I've done this in the past:

def dict_factory(cursor, row):
    d = {}
    for idx,col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

Then you set it up in your connection:

from pysqlite2 import dbapi2 as sqlite
conn = sqlite.connect(...)
conn.row_factory = dict_factory

This works under pysqlite-2.4.1 and python 2.5.4.

I was recently trying to do something similar while using sqlite3.Row(). While sqlite3.Row() is great for providing a dictionary-like interface or a tuple like interface, it didn't work when I piped in the row using **kwargs. So, needed a quick way of converting it to a dictionary. I realised that the Row() object can be converted to a dictionary simply by using itertools.

Python 2:

db.row_factory = sqlite3.Row
dbCursor = db.cursor()
dbCursor.execute("SELECT * FROM table")
row = dbCursor.fetchone()

rowDict = dict(itertools.izip(row.keys(), row))

Or in Python 3, more simply:

dbCursor = db.cursor()
dbCursor.execute("SELECT * FROM table")
row = dbCursor.fetchone()
rowDict = dict(zip([c[0] for c in dbCursor.description], row))

Similarly, you can use the dbCursor.fetchall() command and convert the entire set of rows to a list of dictionaries in a for loop.

Sure, make yourself a DictConnection and DictCursor as explained and shown at http://trac.edgewall.org/pysqlite.org-mirror/wiki/PysqliteFactories for example.

I know you're not asking this, but why not just use sqlalchemy to build an orm for the database? then you can do things like,

entry = model.Session.query(model.Votes).first()
print entry.bill, entry.senator_id, entry.vote

as an added bonus your code will be easily portable to an alternative database, and connections and whatnot will be managed for free.

I've used this:

def get_dict(sql):
    return dict(c.execute(sql,()).fetchall())

Then you can do this:

c = conn.cursor()
d = get_dict("select user,city from vals where user like 'a%'");

Now d is a dictionary where the keys are user and the values are city. This also works for group by

Simple solution, initialize a cursor object:

cursor = conn.cursor(buffered = True, dictionary = True)

Another option:

cursor = conn.cursor(MySQLdb.cursors.DictCursor)

Rest of Code:

query = "SELECT * FROM table"
cursor.execute(query)
row = cursor.fetchone()

Sources: mysql.connector.cursor , MySQLdb.cursors.DictCursor