fetching mysql data as python dictionary

Question

I have the below mysql table. I need to pull out the first two rows as a dictionary using python. I am using python 2.7.

 C1    C2    C3    C4    C5    C6    C7 

 25    33    76    87    56    76    47
 67    94    90    56    77    32    84
 53    66    24    93    33    88    99
 73    34    52    85    67    82    77

I use the following code

exp = MySQLdb.connect(host,port,user,passwd,db)
exp_cur = van.cursor(MySQLdb.cursors.DictCursor)
exp_cur.execute("SELECT * FROM table;")
data = exp_cur.fetchone() 
data_keys = data.keys() 
#print data_keys

The expected output (data_keys) is

['C1', 'C2', 'C3', 'C4', 'C5', 'C6', 'C7']

But I get

['C1', 'C3', 'C2', 'C5', 'C4', 'C7', 'C6']

What is the mistake in my code?

Possible duplicate: Python: use mysqldb to import a MySQL table as a dictionary? — bufh
– bufh, Commented Jul 31, 2015 at 12:24

Community · Accepted Answer · 2017-05-23 12:14:01Z

dict keys have no easily predictable order. To obtain the database table fields in the order in which they appear in the database, use the cursor's description attribute:

fields = [item[0] for item in cursor.description]

For example,

import MySQLdb
import MySQLdb.cursors as cursors
import config

connection = MySQLdb.connect(
    host=config.HOST, user=config.USER,
    passwd=config.PASS, db=config.MYDB,
    cursorclass=cursors.DictCursor)

with connection as cursor:
    cursor.execute('DROP TABLE IF EXISTS test')
    cursor.execute("""CREATE TABLE test (foo int, bar int, baz int)""")
    cursor.execute("""INSERT INTO test (foo, bar, baz) VALUES (%s,%s,%s)""", (1,2,3))
    cursor.execute('SELECT * FROM test')
    data = cursor.fetchone()
    fields = [item[0] for item in cursor.description]

data.keys() may return the fields in any order:

    print(data.keys())
    # ['baz', 'foo', 'bar']

But fields is always ('foo', 'bar', 'baz'):

    print(fields)
    # ('foo', 'bar', 'baz')

Joe Young · Accepted Answer · 2015-07-31 12:04:43Z

2

Instead of

data_keys = data.keys()

Try:

data_keys = exp_cur.column_names

Source: 10.5.11 Property MySQLCursor.column_names

answered Jul 31, 2015 at 12:04

Joe Young

5,9153 gold badges31 silver badges28 bronze badges

Comments

Ravi Ranjan · Accepted Answer · 2021-08-11 17:00:50Z

1

While creating the cursor pass an argument as dictionary=True.

example:

exp = MySQLdb.connect(host,port,user,passwd,db)
exp_cur = van.cursor(dictionary=True)

Now when you will fetch the data, you will get a dictionary as a result.

answered Aug 11, 2021 at 17:00

Ravi Ranjan

1251 gold badge2 silver badges12 bronze badges

Collectives™ on Stack Overflow

fetching mysql data as python dictionary

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related