Python - Returning the output as a dictionary - MySQL

Question

I have coded a class to handle mysql connecting using the external modules but when I get data I want it to be returned like:

{ 'id': '1', 'username': 'Test'}

it's currently returned:

(1, u'Test')

The code which I use is:

def executeQuery(self, query):
    if(self.mysqlSuccess):
        cursor = self.cnx.cursor()
        cursor.execute(query)
        for row in cursor:
            print row
executeQuery('SELECT * FROM `users`')

not exactly a duplicated, but very close (and maybe the answer that op needs) : http://stackoverflow.com/questions/209840/map-two-lists-into-a-dictionary-in-python — Rod, Jul 23 '15 at 14:00
possible duplicate of [Python: use mysqldb to import a MySQL table as a dictionary?](http://stackoverflow.com/questions/2180226/python-use-mysqldb-to-import-a-mysql-table-as-a-dictionary) — Joe Doherty, Jul 23 '15 at 14:02

score 17 · Answer 1 · answered Jul 23 '15 at 14:09

17

I found an answer to my problem when your defining the cursor you do it like this

cursor = db.cursor(dictionary=True)

and the mysql package I was using is mysql.connector

answered Jul 23 '15 at 14:09

Pie-thorn

271
3
6

I've been using namedtuple for weeks to acomplish a simliar result, thanks – Fernando Popoca Ortiz Nov 12 '21 at 19:05

score 5 · Answer 2 · answered Jul 23 '15 at 13:59

You can use a DictCursor when initializing your connection to MySQL.

import MySQLdb.cursors

cnx = MySQLdb.connect(user='joe', passwd='password', db='dbname',
                      cursorclass=MySQLdb.cursors.DictCursor)

If you are using a diff mysql package then just check the docs for DictCursor.

Python - Returning the output as a dictionary - MySQL

2 Answers2