mysql 拆分元组,如何获取所有mysql元组结果并转换为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

解决方案

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}