How to return data from a MySQL query in a Flask app?

Question

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

I was trying to get a JSON result that I could use on a Angular client. Is it possible? — Hugo
– Hugo, Commented Feb 11, 2014 at 18:52
You can't change what fetchall returns, but it should be possible to parse it into an appropriate format. — Jayanth Koushik
– Jayanth Koushik, Commented Feb 11, 2014 at 19:01
@JayanthKoushik I am now using simplejson for serializing. But I am getting the following error TypeError: datetime.date(2013, 12, 1) is not JSON serializable. Could you help me? — Hugo
– Hugo, Commented Feb 11, 2014 at 21:41
Also interesting is Displaying database results in Python Flask: ValueError: dictionary update sequence element #0 has length 6; 2 is required, could be a duplicate of the Q/A here. — questionto42
– questionto42, Commented Feb 1, 2022 at 0:26

Sean Vieira · Accepted Answer · 2014-02-12 20:15:28Z

16

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 dates as well as datetimes.

edited Feb 12, 2014 at 20:15

answered Feb 12, 2014 at 2:58

Sean Vieira

161k34 gold badges321 silver badges297 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Hugo Over a year ago

Thank you @SeanVieira but the error keeps the same :-(

Hugo Over a year ago

May 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 Over a year ago

@Hugo - yes, of course - I only put in the part that changed :-)

Hugo Over a year ago

Thanks once again. But the error keeps the same :-(

Hugo Over a year ago

Do I have to create a specific datetime parser?

|

Collectives™ on Stack Overflow

How to return data from a MySQL query in a Flask app?

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related