Avoiding select after flush when assigning to child relationship

Question

I have the following SQLAlchemy models:

class Parent(Base):
    id = Column(Integer, primary_key=True)

class Child(Base):
    id = Column(Integer, primary_key=True)
    title = Column(String, nullable=False)
    parent_id = Column(Integer, ForeignKey(Parent.id), nullable=False)

    parent = relationship(Parent, 
                          backref=backref('children',
                                          order_by=id,
                                          cascade='all, delete-orphan'))

And then I want to create a parent and some children, all in the same statement:

p = Parent()
DBSession.add(p)

# some unrelated code runs which triggers a flush

p.children = [Child(title=title) for title in titles]

That p.children = assignment statement triggers an unwanted SELECT statement, because SQLAlchemy has to clear out any pre-existing Child objects pointing to the Parent. I realize I can eliminate the SELECT statement with the lazy='noload' option on the backref, but I need this relationship to function normally (i.e. to fetch results from the DB) in other contexts.

Is there a way I can make a property that returns the value of a noload relationship, if it has been set, and otherwise uses a regular relationship to load the results from the DB? Or am I even thinking about this the right way?

Answer 1

Sam in the comments pointed me to the right answer, DBSession.no_autoflush:

with DBSession.no_autoflush:
    p = Parent()
    DBSession.add(p)

    # some unrelated code runs which triggers a flush

    p.children = [Child(title=title) for title in titles]

This eliminates the unwanted SELECT statement when assigning to p.children.