3

I'm currently doing this:

    cursor.execute('SELECT thing_id, thing_name FROM things')
    things = [];
    for row in cursor.fetchall():
        things.append(dict([('thing_id',row[0]),
                             ('thing_name',row[1])
                             ]))

Is there some shorthand I can use to do this, or should I write a little helper function?

awesoon
  • 32,469
  • 11
  • 74
  • 99
penitent_tangent
  • 762
  • 2
  • 8
  • 18

2 Answers2

5

Using list comprehension:

things = [{'thing_id': row[0], 'thing_name': row[1]} for row in cursor.fetchall()]

or using list comprehension with zip:

things = [dict(zip(['thing_id', 'thing_name'], row)) for row in cursor.fetchall()]

If you use Cursor.description attribute, you can get column names:

names = [d.name for d in c.description]
things = [dict(zip(names, row)) for row in cursor.fetchall()]
falsetru
  • 357,413
  • 63
  • 732
  • 636
  • Perfect, thanks. I've gone with your second code block and specifically articulated column names in my select statement. – penitent_tangent Dec 02 '15 at 06:09
  • @Drewdin, What `dict` do you mean? – falsetru Feb 17 '18 at 00:19
  • @falsetru I wanted to get just the dict, things = {'stuff':1}, without it being inside of the list. things = [{'stuff':1}]. I guess its required because it a list comprehension? Thanks – Drewdin Feb 19 '18 at 13:27
3

You could use MySQLdb.cursors.DictCursor class instead of MySQLdb.cursors.Cursor by passing cursor class to cursor method:

In [9]: cur = conn.cursor(MySQLdb.cursors.DictCursor)

In [10]: cur.execute('SELECT * FROM test_table')
Out[10]: 3L

In [11]: cur.fetchall()
Out[11]: 
({'create_time': datetime.datetime(2015, 12, 2, 10, 22, 23),
  'id': 1L,
  'name': 'Bob'},
 {'create_time': datetime.datetime(2015, 12, 2, 10, 22, 34),
  'id': 2L,
  'name': 'Stive'},
 {'create_time': datetime.datetime(2015, 12, 2, 10, 22, 37),
  'id': 3L,
  'name': 'Alex'})
awesoon
  • 32,469
  • 11
  • 74
  • 99
  • Interesting approach - ta! – penitent_tangent Dec 02 '15 at 06:09
  • I've tried this but each time I get "ImportError: No module named mysqldb ". Running Debain Stretch. I installed "sudo apt-get install python-mysqldb". Tried this but got install errors " sh: 1: mysql_config: not found Traceback (most recent call last): …" – RDK May 07 '19 at 15:15
  • Have you tried this one? https://stackoverflow.com/questions/7475223/mysql-config-not-found-when-installing-mysqldb-python-interface – awesoon May 07 '19 at 15:16
  • Still struggling. I have followed the comments/answers and have done or repeated them. My program throws the error on the "import mysqldb" line. Except for that the code is working fine, I just wanted to try your DictCursor idea. – RDK May 07 '19 at 17:27
  • Spoke too soon. The code works but I don't get the dictionary object from the fetchall method. – RDK May 07 '19 at 17:49
  • I think it might be better to ask another question, it is hard to say what's wrong without actual code – awesoon May 08 '19 at 06:17