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
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}
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With