title: How to build a list of dicts from a SQLite3 database query in Python?
body: <p>In trying to produce a list of <code>dict</code>s from a SQLite3 query in Python 2.x, I can do something like:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>But in spite of working and being reasonably understandable, the nested <code>for</code> 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?</p>