flask-sqlalchemy query through ajax jquery: json answer?

Question

I have a jquery script sending an ajax post request to a route on my flask server. I want the route to compute a SQLAlchemy select query and return the result to the jquery script.

The problem is that I cannot find a simple and generic way to convert directly the sqlalchemy query result to json, or other format understandable by javascript.

So for the moment I have to programm a long dedicated "serialize()" function for all of my sqlalchemy Classes to cast str() the hazardous types like datetime, and then jsonify it.

But I want to have only one function to directly convert in one line the sqlalchemy requests results, not a specialized function for each sqlalchemy class.

Here is an example of what I am doing now:

The route in VIEWS:

@app.route('/ajax/programme', methods=['POST'])
def retrieve_programme():
    if request.method == 'POST':
        shows_list = Shows.query.all()
        result = []
        for i in shows_list:
            result.append(i.serialize(['id', 'date', 'title']))
        return json.dumps(result)

My SHOWS model, with the serialize function:

class Shows(db.Model):
    __tablename__ = "shows"

    id = Column(db.Integer, ForeignKey("programmation.id"), primary_key=True)
    date = Column(db.DATETIME)
    title = Column(db.VARCHAR(50))
    short_description = Column(db.VARCHAR(200))
    type = Column(db.VARCHAR(20))
    background_image = Column(MEDIUMBLOB)
    content = Column(LONGTEXT)

    def serialize(self, whatTo):
        result = {}
        if 'id' in whatTo:
            result['id'] = self.id
        if 'date' in whatTo:
            result['date'] = str(self.date)
        if 'title' in whatTo:
            result['title'] = self.title
        if 'short_description' in whatTo:
            result['short_description'] = self.short_description
        if 'type' in whatTo:
            result['type'] = self.type
        if 'background_image' in whatTo:
            result['background_image'] = self.background_image
        if 'content' in whatTo:
            result['content'] = self.content
        return result

Jquery:

$.post("http://127.0.0.1:5000/ajax/programme", {year: '2017', semester: 1}, function(result){
    console.log(result);
});

So I can't believe that flask and js are uncompatible to this point, or that sqlalchemy devs didn't develop something. I must miss something !

Thank you

Why does SQLAlchemy, an ORM, have to presume how you want to serialize your objects? What if you wanted XML? What if you're not even using HTTP but instead want to serialize to a binary representation? Regardless, a popular serialization framework often used with SQLAlchemy is marshmallow. — univerio
I agree with @univerio. It's not SQLAlchemy's job to know how to serialize your objects. A common paradigm is to add an as_dict() method to your model, where you explicitly declare the data-points to be returned. This can easily be handled by json.dumps. — YellowShark

TheArchitect TheArchitect · Accepted Answer · 2017-03-11T01:11:15

You might want to consider https://jsonpickle.github.io which can serialize any Python object:

shows_list = Shows.query.all()
return Response(jsonpickle.encode(shows_list), mimetype='application/json')

You should consider making your instances transient or detached though before doing so to be 100% clean with SQLAlchemy. http://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.make_transient

SQLAlchemy and jsonpickle both instrument classes with hidden fields to be able to deserialize properly, so those can pollute the payload if you don't care to re-attach them to a session later. If your use case is transient/read-only access you can wipe those with code along the lines as below:

for show in show_list:
    sqla_session.make_transient(show)
    show._sa_instance_state = None
return Response(jsonpickle.encode(shows_list, unpicklable=False),
                mimetype='application/json')

flask-sqlalchemy query through ajax jquery: json answer?

1 Answers