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

@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 — shmee
– shmee, Commented Jul 24, 2018 at 8:34
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' — user9704406
– user9704406, Commented Jul 24, 2018 at 9:56

deChristo · Accepted Answer · 2018-07-24 09:38:18Z

5

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.

answered Jul 24, 2018 at 9:38

deChristo

1,8882 gold badges21 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user9704406 Over a year ago

Can't get to import the MySqlDB module

deChristo Over a year ago

@Gidx88 follow the instructions here: github.com/farcepest/MySQLdb1/blob/master/INSTALL

shapiy · Accepted Answer · 2019-04-08 12:25:53Z

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)

answered Apr 8, 2019 at 12:25

shapiy

1,15513 silver badges15 bronze badges

Collectives™ on Stack Overflow

MySQL connector with Python: getting data by field name

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related