how to get all mysql tuple result and convert to json

Question

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

Solution 1

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}

Solution 2

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.

Solution 3

Your return statement is inside the for loop, so after a single iteration it will return immediately with the value of myresult.

Comments

[Vincent]

this still returns 1 row only.