如何获取所有mysql元组结果并转换为json [英] 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

推荐答案

现在，在PyMysql中，有一种配置可将您的连接配置为使用cursorClass，默认情况下会生成Dictionary作为输出. (因此，当返回到API结果时，直接将其转换为JSON)

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)

从PyMysql的文档中:将连接配置为

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)

此结果的输出:

{'password': 'very-secret', 'id': 1}

这篇关于如何获取所有mysql元组结果并转换为json的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！