load a relationship in a to_json method

Question

I have a fairly basic CRUDMixin

class CRUDMixin(object):
    """ create, read, update and delete methods for SQLAlchemy """
    id = db.Column(db.Integer, primary_key=True)

    @property
    def columns(self):
        return [ c.name for c in self.__table__.columns ]

    def read(self):
        """ return json of this current model """
        return dict([ (c, getattr(self, c)) for c in self.columns ])

    # ...

For something like an Article class which will subclass this, it might have a relationship with another class, like so:

author_id = db.Column(db.Integer, db.ForeignKey('users.id'))

The only real problem is that it will not return any user details in the json. Ideally, the json should look like this:

{
  'id': 1234,
  'title': 'this is an article',
  'body': 'Many words go here. Many shall be unread. Roman Proverb.',
  'author': {
    'id': 14
    'name': 'Thor',
    'joined': October 1st, 1994
  } 
}

As it is right now, it will just give author_id: 14.

Can I detect if a column is a relationship and load it as json as well in this way?

score 2 · Answer 1 · edited May 23 '17 at 12:27

You have to setup the entire relation by adding something like

author = db.relationship("Author") # I assume that you have an Author model

Then to json your result you have differents way to handle relations.

Take a look at this 2 responses :

jsonify a SQLAlchemy result set in Flask

How to serialize SqlAlchemy result to JSON?

You can also take a look at flask-restful which provide a method/decorator (marshal_with) to marshal your results in a good way with nested object (relations).

http://flask-restful.readthedocs.org/en/latest/fields.html#advanced-nested-field

load a relationship in a to_json method

1 Answers1