Python将MySQL查询结果转换为JSON [英] Python converting mysql query result to json

问题描述

我正在尝试实现REST API，其中一部分是将数据格式化为json.我能够从mysql数据库中检索数据，但是我收到的对象不是我期望的.这是我的代码

I'm trying to implement REST APIs and part of it is formatting data into json. I am able to retrieve data from a mysql database, however the object i receive is not what I expect. here is my code

from flask import Flask
from flask.ext.mysqldb import MySQL

app = Flask(__name__)
app.config['MYSQL_HOST'] = '127.0.0.1'
app.config['MYSQL_USER'] = 'root'
app.config['MYSQL_PASSWORD'] = 'password'
app.config['MYSQL_DB'] = 'hello_db'
mysql = MySQL(app)

@app.route('/hello')
def index():
   cur = mysql.connection.cursor()
   cur.execute('''SELECT * FROM Users WHERE id=1''')
   rv = cur.fetchall()
   return str(rv)

if __name__ == '__main__':
   app.run(debug=True)

结果:

((1L, u'my_username', u'my_password'),)

如何实现返回这样的json格式:

How do I achieve to return a json format like this:

{
 "id":1, 
 "username":"my_username", 
 "password":"my_password"
}

推荐答案

您可以使用游标描述来提取行标题，如下所示 在执行语句后，row_headers = [x [0]对于cursor.description中的x]`.然后，您可以将其与sql的结果一起压缩以生成json数据. 因此您的代码将是

You can use cursor description to extract row headers like this row_headers=[x[0] for x in cursor.description]` after execute statement. Then you can zip it with the result of sql to produce json data. so your code will be

from flask import Flask
from flask.ext.mysqldb import MySQL
import json
app = Flask(__name__)
app.config['MYSQL_HOST'] = '127.0.0.1'
app.config['MYSQL_USER'] = 'root'
app.config['MYSQL_PASSWORD'] = 'password'
app.config['MYSQL_DB'] = 'hello_db'
mysql = MySQL(app)

@app.route('/hello')
def index():
   cur = mysql.connection.cursor()
   cur.execute('''SELECT * FROM Users WHERE id=1''')
   row_headers=[x[0] for x in cur.description] #this will extract row headers
   rv = cur.fetchall()
   json_data=[]
   for result in rv:
        json_data.append(dict(zip(row_headers,result)))
   return json.dumps(json_data)

if __name__ == '__main__':
   app.run(debug=True)

这篇关于Python将MySQL查询结果转换为JSON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！