The line that causes the autoflush is
You’ve not defined your relationship loading strategies, so they default to
"select". Since you’re accessing the
Team.users relationship attribute for the first time, it will emit a SELECT in order to fetch the related objects.
You’ve also made changes to the session indirectly in
a.user = User.query.filter_by(id=current_user.id).first()
which due to save-update cascade, on by default, places the new
Association instance to the session as well.
In order to keep the DB’s and session’s state consistent SQLAlchemy has to flush your changes to the DB before the query is emitted, and so the half initialized
Association is sent to the DB before being associated with the
Temporarily disable the autoflush feature for that particular append because you know you’re adding a new
with db.session.no_autoflush: t.users.append(a)
The SELECT will still be emitted, though.
Reverse the operation. Instead of appending the
Associationinstance to the
Team.userscollection, assign the
# Instead of t.users.append(a): a.team = t # Mr.
A less obvious solution is to eager load the contents of the relationship when you fetch the
Teaminstance, so that no SELECT is necessary:
t = db.session.query(Team).options(db.joinedload(Team.users)).first()
or not load them at all using the
is redundant. The fetched
Team instance is already in the session – otherwise the lazy load of
Team.users could not have proceeded in the first place.