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

Answer 1

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.