Flask Sqlalchemy model return different result by calling model.dict(sometimes show backrefs and sometimes don't)

Question

I want to get results from a query and send it with jsonify to the client, I create a function that gets an instance of the model, use its 'dict' attribute delete unused values like '_sa_instance_state' and then return new dictionary as a jsonified object to client. Here is my code:

def display(Object):
        data= Object.__dict__.copy()
        del data['_sa_instance_state']
        return data`

user = User.query.first()

@app.route('/')
def main():
       return jsonify(display(user))

Sometimes i get this error

TypeError: Object of type Admin is not JSON serializable

where admin is another model that has a backref in User model. and weird thing happens here, because sometime without changing anything I don't get the error. When i tried to find out what's happening in a jupyter notebook, I see that __dict__ doesn't show the backrefs in jupyter notebook but sometimes it show the backrefs when I call it from the flask app.

Martijn Pieters · Accepted Answer · 2019-02-06T11:24:21.027

Don't use __dict__ on SQLAlchemy models. Yes, backreferences may be present, depending on how you configured reference loading, and so can other SQLAlchemy-specific information.

You could use runtime introspection to get the InstanceState instance. You can then decide what to reflect in JSON and what should be ignored:

from operator import attrgetter
from sqlalchemy import inspect

def display(object, only_loaded=False):
    insp = inspect(object)
    assert insp.is_instance
    value = attrgetter('loaded_value' if only_loaded else 'value')
    return {
        name: value(attr) for name, attr in insp.attrs.items()
        if name not in insp.mapper.relationships
    }

The above uses the Mapper.relationships namespace to skip any related objects. Another way of handling related objects is to recursively turn those into dictionaries too:

def display(object, include_related=True, only_loaded=False, seen=None):
    insp = inspect(object)
    assert insp.is_instance
    value = attrgetter('loaded_value' if only_loaded else 'value')
    result = {
        name: value(attr) for name, attr in insp.attrs.items()
        if name not in insp.mapper.relationships
    }
    if include_related:
        if seen is None:
            seen = set()
        seen.add(id(object))
        for name, rel in insp.mapper.relationships.items():
            related = value(insp.attrs[name])
            if related is not None:
                if rel.uselist:
                    displays = result[name] = []
                    for r in related:
                        if id(r) in seen:
                            continue
                        displays.append(display(r, True, only_loaded, seen))
                else:
                    if id(related) in seen:
                        continue
                    result[name] = display(related, True, only_loaded)
    return result

but take into account that with bi-directional relationship objects you can quickly end up reflecting a much larger chunk of your database than you may have intended.

Personally, I find it easier to just add a as_dict() method to my models that produce the dictionary to reflect, or use a Flask add-on that provides a reflection API.

Flask Sqlalchemy model return different result by calling model.__dict__(sometimes show backrefs and sometimes don't)

1 Answers1

Flask Sqlalchemy model return different result by calling model.dict(sometimes show backrefs and sometimes don't)