Flask and SQLalchemy append relationship without loading objects every time

Question

I am working a rest api with python flask and SQLalchemy. I have 2 classes Parent and Child:

Class Parent(db.Model):
    id = db.Column(db.Integer, nullable=False, autoincrement=True, primary_key=True)
    name = db.Column(db.String, nullable=False)

    children = relationship('Child',
                                secondary=parent_has_children,
                                back_populates='parents'
                                )


Class Child(db.Model):
    id = db.Column(db.Integer, nullable=False, autoincrement=True, primary_key=True)
    name = db.Column(db.String, nullable=False)

    parents = relationship('Parent',
                                secondary=parent_has_children,
                                back_populates='children'
                                )

   parent_has_children = db.Table('parent_has_children', db.metadata,
                            db.Column('parent_id', db.Integer, ForeignKey('Parent.id')),
                            db.Column('child_id', db.Integer, ForeignKey('Child.id'))
                            )

I have a many to many relationship and for that reason i am using a secondary table.Lets say i have a route who recieves a child_id and a parent_id and building their relationship:

@app.route('/buildrelationship', methods=['POST'])
def buildrelationship():
    child_id= request.json['student_id']
    parent_id = request.json['parent_id']
    child = Child.query.get(child_id)
    parent = Parent.query.get(parent_id)
    parent.children.append(child)
    db.session.commit()

This way i added relationship between parent a child but i had to get the parent and the child from database first and then add relationship. The request.json may have a list of children to append to a parent or a list o parents to append to a particular child.In this case i have to query as many times as the length of list to take the parent or child and then append the relationship.Is there any better way to append relationship instead of load parent and child objects every time?

Doobeh Doobeh · Accepted Answer · 2018-01-18T14:33:59

It's possible to reduce the querying, but you want to lean on the ORM as much as possible. If you didn't do the query to resolve the POSTed data to the Child or Parent object and instead, say, directly inserted into the M2M table the id's presented-- you could cause a bit of a headache with your databases integrity.

You only have to fire one update query-- if you first iterate once through your list of Children you could end up with a children = [Child<1>, Child<2>, Child<3>] list, then you could just Parent.children.append(children)

If, you were really dealing with tens/hundreds of thousands of child objects per POST and time, memory, etc was actually an issue, you could flip to bulk loading the data, pretty much totally skipping the ORM layer and all the safety features it's helping you with.

First you would want to get a list of your current child objects so you could make sure you're not going to cause an integrity error (again, safety gloves are off, and you're pretty odd if you're doing this without good reason).

existing = {x.id for x in Child.query.all()}
# lets say: existing = {1,3,4,5,6}

Next you'd get your list of POSTed child ids:

target = request.json['student_id']
# lets say target = [1,2,3,3,3]

So, we could now filter down on what actually needs to get inserted, cleaning up anything that might cause us trouble:

children_to_insert = {x for x in target if x in existing}
# active_children = {1,3}

Build a list of dictionaries to represent our M2M table data:

parent_id = request.json['parent_id'] 
bulk = [{'parent_id': parent_id, 'child_id': x} for x in children_to_insert]

# Then we'd bulk operation it into the database:

db.engine.execute(
    parent_has_children.insert(bulk)
)

I've skipped all the other integrity checking you would want (does the parent exist? does it already have children?) but you hopefully get the point, which is, just use the ORM and don't try and go around it's back without a very good reason.

Flask and SQLalchemy append relationship without loading objects every time

1 Answers