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

Question

I'm using sqlite3 in python 2.5. I've created a table that looks like this:

   create table votes (
      bill text,
      senator_id text,
      vote text)

I'm accessing it with something like this:

v_cur.execute("select * from votes")
row = v_cur.fetchone()
bill = row[0]
senator_id = row[1]
vote = row[2]

What I'd like to be able to do is have fetchone (or some other method) return a dictionary, rather than a list, so that I can refer to the field by name rather than position. For example:

bill = row['bill'] 
senator_id = row['senator_id']
vote = row['vote']

I know you can do this with MySQL, but does anyone know how to do it with SQLite?

Thanks!!!

Answer 1

Sure, make yourself a DictConnection and DictCursor as explained and shown at http://oss.itsystementwicklung.de/trac/pysqlite/wiki/PysqliteFactories for example.

Answer 2

You could do this:

fields = ['bill', 'senator_id', 'vote']
v_cur.execute('select %s from votes' % ', '.join(fields))
row = v_cur.fetchone()
row_dict = {}
for i in range(row):
    row_dict[fields[i]] = row[i]

Answer 3

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.

Answer 4

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.

Answer 5

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.

Answer 6

Another general approach is to simply persist any Python object with the data structure which one requires. Take a look at the y_serial Python module: http://yserial.sourceforge.net

Hope this helps your project... it uses SQLite and should be fast and simple enough to implement in 10 minutes.