Flask SQLAlchemy select child object - return JSONB column

Question

I have a class with a one-many relationship. I would like to return all the parent’s children in the relationship ; specifically I’d like to return all the JSONB objects in my children tables.

These are my class:

class Parent(db.Model):
    __tablename__ = ‘parent220416'
    id = db.Column(db.Integer, primary_key=True)
    children = db.relationship(‘Child’, backref=‘Parent’, lazy='dynamic')

class Child(db.Model):
    __tablename__ = ‘child220416'
    id = db.Column(db.Integer, primary_key=True)
    parentid = db.Column(db.Integer, db.ForeignKey('words220416.id'))
    data = db.Column(JSONB)

Then with Flask Restful, I’m trying to select all the child like this:

class ParentChild(Resource):
    def get(self, id):
           result = db.session.query(Parent).get(id)
           result_child = result.children
           return {'child': result_child}

There is an error:

raise TypeError(repr(o) + " is not JSON serializable")
TypeError: <sqlalchemy.orm.dynamic.AppenderBaseQuery object at 0x106178da0> is not JSON serializable

score 1 · Accepted Answer · answered Apr 26 '16 at 15:30

1

If you want to get all of the data objects for each Child of the Parent. you could do the following query:

result_child = db.session.query(Child.data).filter(Child.parentid == id).all()

If you want to use the children relationship, it is possible to iterate over it:

result_child = [child.data for child in result.children]

answered Apr 26 '16 at 15:30

Phillip Martin

1,910
15
30

It works but if I understand I don't really exploit the relationship declared in my 2 classes... Is there a best practice to exploit this relationship? – Chester Mc Allister Apr 27 '16 at 07:59
The second of the two options uses the relationship. But I understand what you mean; the utilization of the relationship seems pretty shallow. I haven't found any way to more deeply use the relationship for the purpose you want, and iterating over a relationship is pretty common (although I don't particularly like it, which is why I gave the first option). If you don't like iterating over the relationship, you can try serializing the children with something like [marshmallow](https://marshmallow.readthedocs.org/en/latest/). – Phillip Martin Apr 27 '16 at 12:09

Flask SQLAlchemy select child object - return JSONB column

1 Answers1