How to return data from a MySQL query in a Flask app?
24,767
Use Flask's built-in jsonify
function, as it is already extended to work with dates:
from Flask import jsonify
@app.route('/temp')
def temp():
# Load database results
# and then ...
return jsonify(data=cur.fetchall())
The data will be returned as an object with a single key (data
) containing an array of rows (which will either be represented as arrays or objects depending on what fetchall
returns rows as).
If you need to serialize more types (as in your case, you are getting back date
rather than datetime
instances, you will need to override Flask's json_encoder
property with a subclass of JSONEncoder
that knows how to handle your types:
class SpecializedJSONEncoder(JSONEncoder):
def default(o):
if isinstance(o, date):
return date.strftime("%Y-%m-%d")
else:
super(SpecializedJSONEncoder, self).default(o)
And then you can set it on your Flask
instance:
app.json_encoder = SpecializedJSONEncoder
You will now be able to handle date
s as well as datetime
s.
Author by
Hugo
Updated on February 14, 2020Comments
-
Hugo about 4 years
I have the following code:
import flask as fk import MySQLdb import JSONEncoder class SpecializedJSONEncoder(JSONEncoder): def default(o): if isinstance(o, date): return date.strftime("%Y-%m-%d") else: super(SpecializedJSONEncoder, self).default(o) app = fk.Flask(__name__) app.json_encoder = SpecializedJSONEncoder app.debug = True @app.route("/") def home(): return "Hello world" @app.route("/temp") def temp(): db = MySQLdb.connect("localhost", "root", "","test") cur = db.cursor() query = "SELECT DATE(DTM), POM, ROUND(MIN(TMP),1) FROM dados_meteo WHERE POM = %s AND DATE(DTM) >= %s AND DATE(DTM) <= %s" param = ("Faro", "2013-12-01", "2013-12-05") cur.execute(query, param) data = cur.fetchall() return data.json_encoder() if __name__ == "__main__": app.run()
The error returned is: ImportError: No module named JSONEncoder
-
Hugo about 10 yearsThank you @SeanVieira but the error keeps the same :-(
-
Hugo about 10 yearsMay I keep this code on top? import flask as fk from flask import jsonify import MySQLdb as mdb app = fk.Flask(name) app.debug = True
-
Sean Vieira about 10 years@Hugo - yes, of course - I only put in the part that changed :-)
-
Hugo about 10 yearsThanks once again. But the error keeps the same :-(
-
Hugo about 10 yearsDo I have to create a specific datetime parser?
-
Sean Vieira about 10 years@Hugo - yes, you will - example added.
-
Hugo about 10 yearsI have the code ready. Not working. I am sorry, but could I send it to you. It's not suposed to paste it here, right?
-
Sean Vieira about 10 years@Hugo - if it is still related to the encoding problem, I would edit your question and add the updated code and any errors you are getting.
-
Hugo about 10 years