Python将mysql查询结果转换为json
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/43796423/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Python converting mysql query result to json
提问by edmamerto
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
我正在尝试实现 REST API，其中一部分是将数据格式化为 json。我能够从 mysql 数据库中检索数据，但是我收到的对象不是我所期望的。这是我的代码
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)
Outcome:
结果：
((1L, u'my_username', u'my_password'),)
How do I achieve to return a json format like this:
我如何实现返回这样的 json 格式：
{
"id":1,
"username":"my_username",
"password":"my_password"
}
回答by Mani
You can use cursor description to extract row headers:
row_headers=[x[0] for x in cursor.description]after the execute statement. Then you can zip it with the result of sql to produce json data.
So your code will be something like:
您可以使用游标描述来提取行标题：
row_headers=[x[0] for x in cursor.description]在执行语句之后。然后就可以用sql的结果进行压缩，生成json数据。所以你的代码将是这样的：
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)
In the return statement you can use jsonifyinstead of json.dumpsas suggested by RickLan in the comments.
在 return 语句中，您可以使用jsonify代替json.dumpsRickLan 在评论中的建议。
回答by Mike Tung
From your output it seems like you are getting a tuple back? In which case you should be able to just map it.
从您的输出中，您似乎得到了一个元组？在这种情况下，您应该能够对其进行映射。
from flask import Flask, jsonify
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()
payload = []
content = {}
for result in rv:
content = {'id': result[0], 'username': result[1], 'password': result[2]}
payload.append(content)
content = {}
return jsonify(payload)
if __name__ == '__main__':
app.run(debug=True)
回答by micheal nayebare
content = {} # seems like a double declaration
for result in rv:
content = {'id': result[0], 'username': result[1], 'password': result[2]}
payload.append(content)
content = {} # why do have this line code still works
return jsonify(payload)
