How to build a list of dicts from a SQLite3 database query in Python?

Question

In trying to produce a list of dicts from a SQLite3 query in Python 2.x, I can do something like:

import sqlite3
cur = sqlite3.connect('filename.db').cursor()
query = cur.execute('SELECT * FROM A_TABLE')
colname = [ d[0] for d in query.description ]
result_list = []
for r in query.fetchall():
  row = {}
  for i in range(len(colname)):
    row[colname[i]] = r[i]
  result_list.append(row)
  del row

cur.close()
cur.connection.close()
print result_list

With this, I try to get a list of dictionaries, with each key-value pair indicating the column name and the assigned value for the row.

But in spite of working and being reasonably understandable, the nested for cycles makes me think that there might be a Python idiom that simplifies this -- some clever use of comprehensions, specifically. I suspect that it might be possible to resort to generators, but I still don't quite understand how to use them. So I ask: is there a way to "compactify" the code and get the intended list of dictionaries by some use of comprehensions of generators and/or dicts?

The code in the question is EXACTLY what I was looking for. Just wanted to say thank you for posting it as it helped me out a lot! — Frak
– Frak, Commented Apr 2, 2018 at 13:56

David K. Hess · Accepted Answer · 2015-09-24 12:26:21Z

7

Try this:

import sqlite3
cur = sqlite3.connect('filename.db').cursor()
query = cur.execute('SELECT * FROM A_TABLE')
colname = [ d[0] for d in query.description ]
result_list = [ dict(zip(colname, r)) for r in query.fetchall() ]
cur.close()
cur.connection.close()
print result_list

edited Sep 24, 2015 at 12:26

answered Dec 6, 2012 at 1:26

David K. Hess

17.3k2 gold badges53 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bill C · Accepted Answer · 2012-12-06 01:26:05Z

1

You can replace the inner for-loop with a dictionary comprehension like so:

row = dict((colname[i], r[i]) for i in range(len(colname)))

You can also replace the entire thing with a dictionary comprehension nested inside a list comprehension:

result_list = [dict((colname[i], r[i]) for i in range(len(colname)) for r in query.fetchall()]

I might actually recommend sticking with the outer for-loop however, as that seems more readable. Also, it seems to me that the del row is unnecessary. Can't you just rely on Python's garbage collector?

answered Dec 6, 2012 at 1:26

Bill C

1257 bronze badges

Comments

Bryan Herbst · Accepted Answer · 2014-12-15 03:15:28Z

1

@David K. Hess is right, except there is a typo. Line three should read:

query = cur.execute('SELECT * FROM A_TABLE')

I couldn't edit only three characters and don't have the reputation to comment.

edited Dec 15, 2014 at 3:15

Bryan Herbst

67.4k10 gold badges137 silver badges120 bronze badges

answered Dec 15, 2014 at 2:54

spk593

112 bronze badges

1 Comment

David K. Hess Over a year ago

Made the edit. In a case like this, if you comment on my answer, I'll be notified. Unfortunately, I don't get notified if another answer is added.

Collectives™ on Stack Overflow

How to build a list of dicts from a SQLite3 database query in Python?

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related