Python converting mysql query result to json
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:
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 jsonify
instead of json.dumps
as suggested by RickLan in the comments.
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)
There is, perhaps, a simpler way to do this: return a dictionary and convert it to JSON.
Just pass dictionary=True
to the cursor constructor as mentioned in MySQL's documents.
import json
import mysql.connector
db = mysql.connector.connect(host='127.0.0.1',
user='admin',
passwd='password',
db='database',
port=3306)
# This line is that you need
cursor = db.cursor(dictionary=True)
name = "Bob"
cursor.execute("SELECT fname, lname FROM table WHERE fname=%s;", (name))
result = cursor.fetchall()
print(f"json: {json.dumps(result)}")
Which will print -
json: [{'fname': "Bob", 'lname': "Dole"}, {'fname': "Bob", 'lname': "Marley"}]
(Assuming those Bobs are in the table.)
Note that types are preserved this way, a good thing, BUT will need to be transformed, parsed, or serialized into a string; for instance, if there is a date, the SQL query may return a datetime
object, which will need to be parsed or serialized depending on your next step. A great way to serialize is in this answer.