How can I reference columns by their names in python calling SQLite?

Question

Hi,

I have some code which I've been using to query MySQL, and I'm hoping to use it with SQLite. My real hope is that this will not involve making too many changes to the code. Unfortunately, the following code doesn't work with SQLite:

cursor.execute(query)

rows = cursor.fetchall()

data = []
for row in rows
  data.append(row["column_name"])

This gives the following error:

 TypeError: tuple indices must be integers

Whereas if I change the reference to use a column number, it works fine:

  data.append(row[1])

Can I execute the query in such a way that I can reference columns by their names?

Thanks!

Ben

Answer 1

I'm not sure if this is the best approach, but here's what I typically do to retrieve a record set using a DB-API 2 compliant module:

cursor.execute("""SELECT foo, bar, baz, quux FROM table WHERE id = %s;""", 
                  (interesting_record_id,))

for foo, bar, baz, quux in cursor.fetchall():
    frobnicate(foo + bar, baz * quux)

The query formatting method is one of the DB-API standards, but happens to be the preferred method for Psycopg2; other DB-API adapters might suggest a different convention which will be fine.

Writing queries like this, where implicit tuple unpacking is used to work with the result set, has typically been more effective for me than trying to worry about matching Python variable names to SQL column names (which I usually only use to drop prefixes, and then only if I'm working with a subset of the column names such that the prefixes don't help to clarify things anymore), and is much better than remembering numerical column IDs.

This style also helps you avoid SELECT * FROM table..., which is just a maintenance disaster for anything but the simplest tables and queries.

So, not exactly the answer you were asking for, but possibly enlightening nonetheless.

Answer 2

To access columns by name, use the row_factory attribute of the Connection instance. It lets you set a function that takes the arguments cursor and row, and return whatever you'd like. There's a few builtin to pysqlite, namely sqlite3.Row, which does what you've asked.

Answer 3

The SQLite API supports cursor.description so you can easily do it like this

headers = {}
for record in cursor.fetchall():
    if not headers:
        headers = dict((desc[0], idx) for idx,desc in cursor.description))
    data.append(record[headers['column_name']])

A little long winded but gets the job done. I noticed they even have it in the factory.py file under dict_factory.