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?](http://stackoverflow.com/questions/2180226/python-use-mysqldb-to-import-a-mysql-table-as-a-dictionary) — bufh, Jul 31 '15 at 12:24

score 2 · Answer 1 · answered Jul 31 '15 at 12:04

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 '15 at 12:04

Joe Young

5,749
3
28
27

score 2 · Accepted Answer · edited May 23 '17 at 12:14

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')

score 1 · Answer 3 · answered Aug 11 '21 at 17:00

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 '21 at 17:00

Ravi Ranjan

115
1
1
12

fetching mysql data as python dictionary

3 Answers3