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

Question

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?

Answer 1

In the five years since the question was asked and then answered, a very simple solution has arisen. Any new code can simply wrap the connection object with a row factory. Code example:

import sqlite3

conn = sqlite3.connect('./someFile')
conn.row_factory = sqlite3.Row        // Here's the magic!

cursor = conn.execute("SELECT name, age FROM someTable")
for row in cursor:
    print(row['name'])

Here are some fine docs. Enjoy!

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.