Python: use mysqldb to import a MySQL table as a dictionary?

Question

Anyone know how I can use mysqldb to turn a MySQL table, with lots of rows, into a list of dictionary objects in Python?

I mean turning a set of MySQL rows, with columns 'a', 'b', and 'c', into a Python object that that looks like this:

data = [ { 'a':'A', 'b':(2, 4), 'c':3.0 }, { 'a':'Q', 'b':(1, 4), 'c':5.0 }, { 'a':'T', 'b':(2, 8), 'c':6.1 } ]

Thanks :)

Solution 1

MySQLdb has a separate cursor class for this, the DictCursor. You can pass the cursor class you want to use to MySQLdb.connect():

import MySQLdb.cursors
MySQLdb.connect(host='...', cursorclass=MySQLdb.cursors.DictCursor)

Solution 2

If you need to use more cursors and only one needs to be MySQLdb.cursors.DictCursor you can do:

import MySQLdb
db = MySQLdb.connect(host='...', db='...', user='...t', passwd='...')

list_cursor = db.cursor()
dict_cursor = db.cursor(MySQLdb.cursors.DictCursor)

Solution 3

I think with mysql.connector is much more easier to convert a select to a dict than MySQLdb, and also more Python version supported:

cursor = conn.cursor(dictionary=True)

---

Detailed example:

import mysql.connector # pip install mysql-connector-python

mydb = mysql.connector.connect(host="localhost", user="user", passwd="pass", database="dbname")
cursor = conn.cursor(dictionary=True)
sql = "SELECT * FROM `table` WHERE 1"
mycursor.execute(sql)
rows = mycursor.fetchall()
for row in rows:
    row["col"]

Comments

[AP257]

Perfect, thank you! For anyone else reading, you also need to add "import MySQLdb.cursors" to the top of your Python script to include it.

[Thomas Wouters]

You must have missed the fact that I actually included the MySQL.cursors import right there in the code snippet.

[cregox]

For anyone else reading, you also need to add MySQLdb.connect(host='...', cursorclass=MySQLdb.cursors.DictCursor) somewhere after import MySQLdb.cursors for the snippet to work. (sorry, I couldn't resist! :D)

[David Lavieri]

Need to add import MySQLdb.cursors otherwise it raise an error in python 3.4

[Flimm]

@DavidLavieri The additional import statement is not needed in Python 3.6 with mysqlclient 1.3.12

[Sebastian]

This should be the accepted answer - much easier and no extra imports required.