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

Phillip Martin · Accepted Answer · 2016-04-26 15:30:15Z

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, 2016 at 15:30

Phillip Martin

1,9801 gold badge17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Chester Mc Allister Over a year ago

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?

Phillip Martin Over a year ago

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.

Collectives™ on Stack Overflow

Flask SQLAlchemy select child object - return JSONB column

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related