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 · Accepted Answer · 2018-01-18 17:49:58Z

2

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.

edited Jan 18, 2018 at 17:49

answered Jan 18, 2018 at 14:33

Doobeh

9,48042 silver badges32 bronze badges

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

3 Comments

Ilja Everilä Over a year ago

They could also lean on the RDBMS and its constraints, if set up correctly, in which case the bulk method you've shown is just as safe.

Doobeh Over a year ago

For sure, though a lot of people are using SQLite3 without ever realizing that they need to turn on an option to get the FK integrity controls that they probably expect are built in.

Ilja Everilä Over a year ago

That's also true.

Collectives™ on Stack Overflow

Flask and SQLalchemy append relationship without loading objects every time

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related