Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

how to get all mysql tuple result and convert to json

Tags:

python

json

mysql

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
like image 885
Vincent Avatar asked Mar 11 '14 03:03

Vincent


2 Answers

Now, in PyMysql, there is a facility to configure your connection to use the cursorClass which by default generates Dictionary as the output. (And thus works directly when returning in the API result as it gets converted to JSON)

From the documentation of PyMysql: Configure your connection as

# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='user',
                             password='passwd',
                             db='db',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

result = cursor.fetchone()
        print(result)

Output for this result :

{'password': 'very-secret', 'id': 1}
like image 62
sumitb.mdi Avatar answered Nov 15 '22 08:11

sumitb.mdi


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.

like image 31
alecxe Avatar answered Nov 15 '22 08:11

alecxe