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