MySQL connector with Python: getting data by field name

Question

I have to retrieve data from a database. Right now i am using the following code:

import mysql.connector
connection = mysql.connector.connect(user, password, host, database)
cursor = connection.cursor()
cursor.execute(*Query*)
data = cursor.fetchall()
name = data[0][0]
surname = data[0][1]

I would need to access the data by field name instead of raw indexes. For instance something like that

name = data[0]['name']
surname = data[0]['surname']

Thanks

• Chetan Ameta: try cursor = cnx.cursor(dictionary=True) in python 3
• user9704406: i am running python 2.7
• shmee: @Gidx88 what is the version of your connector package? According to the docs dictionary is available since Connector/Python 2.0.0. Apparently not restricted to Python 3
• user9704406: I think dictionary is actually working but for some reasons i won't find my key. This is the code print("Countries in Europe:") for row in cursor: print("* {mykey}".format(Name=row['mykey'])), i have a column named "mykey" but i get a Keyerror: 'mykey'

Answer 1

One option is to use MySQLdb. Then you can change:

cursor = connection.cursor()

To:

cursor = connection.cursor(MySQLdb.cursors.DictCursor) 

and access the values by field names instead indexes.

• user9704406: Can't get to import the MySqlDB module
• deChristo: @Gidx88 follow the instructions here: github.com/farcepest/MySQLdb1/blob/master/INSTALL

Answer 2

According to mysql-connector-python documentation, there are two ways to achieve this.

Manually:

cursor = cnx.cursor()
cursor.execute(...)
row = dict(zip(cursor.column_names, cursor.fetchone()))

With MySQLCursorDict class:

cursor = cnx.cursor(dictionary=True)