Tuesday, May 11, 2010

Convert query result to dictionary like json in Python

If you wish get a result of a query but like to return each row like a dictionary {Field name: value, ...} inside a array, you can make in a single line:

query_result = [ dict(line) for line in [ zip([ column[0] for column in cursor.description ], row) for row in cursor.fetchall() ] ]

I will try explain the line.

The cursor.fetchall() will return the row, but we don't know the name of field, so to get this information we need request to a cursor.description to give me the fields.
The cursor.description have more then a name of field, so we use a column[0] to get the name of field.
To give a list of columns we use a for to give a sequence to me :
[ column[0] for column in cursor.description ]

Now we need give the name for each field and a value returned in a row, for this we use a function zip, but we can't forget that each row need be in a sequence resulted a this command :
[ zip([ column[0] for column in cursor.description ], row) for row in cursor.fetchall() ]

This will result a sequence line:
[ [(Field, value), (Field, value), ...],  [(Field, value), (Field, value), ...], ]

Finally we convert this lines to a dictionary using the function dict(). resulting the line above.

this is a example (In this case I use the mysql connection):

try:
# Load the connection
connection = MySQLdb.Connection(host='host_name', user='user_name', passwd='password', db='db')
cursor = connection.cursor()
cursor.execute(query)
query_result = [ dict(line) for line in [zip([ column[0] for column in cursor.description], row) for row in cursor.fetchall()] ]
print query_result
except Exception, e:
print "Error [%r]" % (e)
sys.exit(1)
finally:
if cursor:
cursor.close()


NOTE: This will not result a JSON result is only a sequence of dictionary that will be like JSON if you use a simple result (date will return like: datetime.datetime(2010, 4, 23, 14, 44, 6))

2 comments:

Teguh Eko Budiarto said...

nice description! it explains easily. Just to let everyone know, that we have it implemented in wcc.helpers.common.dictfetchall

please take a look and feel free to use it.

Unknown said...

Hey really awesome blog and unique content . i think it's very useful my us. so please help me..my skype account just been hacked ,,and they changed everything ..this my skype because i lost my mobile phone .the one steal my mobile the one also hacked my fb account.please help me.thanks visit more info Skype Support and Call +1-800-231-4635 USA (Toll Free).