It's annoying how Python's sqlite3
module always returns a list of tuples! When I am querying a single column, I would prefer to get a plain list.
e.g. when I execute
SELECT somecol FROM sometable
and call
cursor.fetchall()
it returns
[(u'one',), (u'two',), (u'three',)]
but I'd rather just get
[u'one', u'two', u'three']
Is there a way to do this?
sqlite3.Connection
has a row_factory
attribute.
The documentation states that:
You can change this attribute to a callable that accepts the cursor and the original row as a tuple and will return the real result row. This way, you can implement more advanced ways of returning results, such as returning an object that can also access columns by name.
To return a list of single values from a SELECT
, such as an id
, you can assign a lambda to row_factory
which returns the first indexed value in each row; e.g:
import sqlite3 as db
conn = db.connect('my.db')
conn.row_factory = lambda cursor, row: row[0]
c = conn.cursor()
ids = c.execute('SELECT id FROM users').fetchall()
This yields something like:
[1, 2, 3, 4, 5, 6] # etc.
You can also set the row_factory
directly on the cursor object itself. Indeed, if you do not set the row_factory
on the connection before you create the cursor, you must set the row_factory
on the cursor:
c = conn.cursor()
c.row_factory = lambda cursor, row: {'foo': row[0]}
You may redefine the row_factory
at any point during the lifetime of the cursor object, and you can unset the row factory with None
to return default tuple-based results:
c.row_factory = None
c.execute('SELECT id FROM users').fetchall() # [(1,), (2,), (3,)] etc.
Update in python 3.9+
It gets even easier in more recent versions of python: row_factory
can now be set to sqlite3.Row
which will parse the tuple into a dictionary-like object according using names from the table schema automatically.
c = conn.cursor()
c.row_factory = sqlite3.Row