Vincent Vincent - 2 months ago 11
JSON Question

how to get all mysql tuple result and convert to json

I was able to get a single data from a table. but when i'm trying to get all the data on my table, i got only a single row.

cnn.execute(sql)
rows = cnn.fetchall()
column = [t[0] for t in cnn.description]
for row in rows:
myjson = {column[0]: row[0], column[1]: row[1], column[2]: row[2], column[3]: row[3], column[4]: row[4], column[5]: row[5], column[6]: row[6], column[7]: row[7], column[8]: row[8], column[9]: row[9], column[10]: row[10], column[11]: row[11], column[12]: row[12], column[13]: row[13], column[14]: row[14], column[15]: row[15], column[16]: row[16], column[17]: row[17], column[18]: row[18], column[19]: row[19], column[20]: row[20]}
myresult = json.dumps(myjson, indent=3)
return myresult

Answer

You don't need to specify a "hardcoded" key-value mapping, use zip() instead (or itertools.izip()).

Also, collect the rows in a list and only then dump the results to json:

def dictfetchall(cursor):
    """Returns all rows from a cursor as a list of dicts"""
    desc = cursor.description
    return [dict(itertools.izip([col[0] for col in desc], row)) 
            for row in cursor.fetchall()]

Usage:

results = dictfetchall(cursor)
json_results = json.dumps(results)

Hope that helps.

Comments