如何获取所有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

5 个解决方案

#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)

现在,在PyMysql中,有一个工具可以配置连接以使用cursorClass,默认情况下会生成Dictionary作为输出。 (因此当它转换为JSON时,在API结果中返回时直接工作)

From the documentation of PyMysql: Configure your connection as

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

# 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}

#2

You don't need to specify a "hardcoded" key-value mapping, use zip() instead (or itertools.izip()).

您不需要指定“硬编码”键值映射,而是使用zip()(或itertools.izip())。

Also, collect the rows in a list and only then dump the results to json:

此外,收集列表中的行,然后将结果转储到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.

希望有所帮助。

#3

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

您的return语句位于for循环中,因此在单次迭代后,它将立即返回myresult的值。

#4

Yeah, @metatoaster is right,

是的,@ METatoaster是对的,

Try with this:

试试这个:

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

#5

#imports 
import collections
import MySQLdb
import json

#connect to database
conn = MySQLdb.connect(host= "localhost", user="root", passwd="abc",  db="mydatabase")

#Fetch rows
sql  = "SELECT * from userstable"
cursor = conn.cursor()
cursor.execute(sql)
data = cursor.fetchall()

#Converting data into json
user_list = []
for row in data :
    d = collections.OrderedDict()
    d['firstName']  = row[1] #name
    d['lastName']   = row[2] #lname
    d['email']      = row[3] #email
    user_list.append(d)

return json.dumps(user_list)


##Result
[{"firstName":"jame","lastName":"king","email":"test@gmail.com"}]

#1