mwan mwan - 1 year ago 454
Python Question

jsonify a SQLAlchemy result set in Flask

I'm trying to jsonify a SQLAlchemy result set in Flask/Python.

The Flask mailing list suggested the following method :

return jsonify(json_list = qryresult)

However I'm getting the following error back:

TypeError: <flaskext.sqlalchemy.BaseQuery object at 0x102c2df90>
is not JSON serializable

What am I overlooking here?

I have found this question: How to serialize SqlAlchemy result to JSON? which seems very similar however I didn't know whether Flask had some magic to make it easier as the mailing list post suggested.

Edit: for clarification, this is what my model looks like

class Rating(db.Model):

__tablename__ = 'rating'

id = db.Column(db.Integer, primary_key=True)
fullurl = db.Column(db.String())
url = db.Column(db.String())
comments = db.Column(db.Text)
overall = db.Column(db.Integer)
shipping = db.Column(db.Integer)
cost = db.Column(db.Integer)
honesty = db.Column(db.Integer)
communication = db.Column(db.Integer)
name = db.Column(db.String())
ipaddr = db.Column(db.String())
date = db.Column(db.String())

def __init__(self, fullurl, url, comments, overall, shipping, cost, honesty, communication, name, ipaddr, date):
self.fullurl = fullurl
self.url = url
self.comments = comments
self.overall = overall
self.shipping = shipping
self.cost = cost
self.honesty = honesty
self.communication = communication = name
self.ipaddr = ipaddr = date

Answer Source

It seems that you actually haven't executed your query. Try following:

return jsonify(json_list = qryresult.all())

[Edit]: Problem with jsonify is, that usually the objects cannot be jsonified automatically. Even Python's datetime fails ;)

What I have usually done, is to add an extra property (like serialize) to classes that need to be serialized:

def dump_datetime(value):
    """Deserialize datetime object into string form for JSON processing."""
    if value is None:
        return None
    return [value.strftime("%Y-%m-%d"), value.strftime("%H:%M:%S")]

class Foo(db.Model):
    # ... SQLAlchemy defs here..
    def __init__(self, ...):
       # = ...

    def serialize(self):
       """Return object data in easily serializeable format"""
       return {
           'id'         :,
           'modified_at': dump_datetime(self.modified_at),
           # This is an example how to deal with Many2Many relations
           'many2many'  : self.serialize_many2many
    def serialize_many2many(self):
       Return object's relations in easily serializeable format.
       NB! Calls many2many's serialize property.
       return [ item.serialize for item in self.many2many]

And now for views I can just do:

return jsonify(json_list=[i.serialize for i in qryresult.all()])

Hope this helps ;)