CODEWITHSUNDEEP
pythonmysql
ita
ita

Reputation: 89

fetching mysql data as python dictionary

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?

Upvotes: 3

Views: 8633

Answers (3)

Ravi Ranjan
Ravi Ranjan

Reputation: 125

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.

Upvotes: 1

unutbu
unutbu

Reputation: 879501

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

Upvotes: 2

Joe Young
Joe Young

Reputation: 5875

Instead of 

data_keys = data.keys()

Try:

data_keys = exp_cur.column_names

Source: 10.5.11 Property MySQLCursor.column_names

Upvotes: 2

Related Questions