Automagically propagating deletion when using a bidirectional association_proxy

Question

I'm using a bidirectional association_proxy to associate properties Group.members and User.groups. I'm having issues with removing a member from Group.members. In particular, Group.members.remove will successfully remove an entry from Group.members, but will leave a None in place of the corresponding entry in User.groups.

More concretely, the following (minimal-ish) representative code snippet fails its last assertion:

import sqlalchemy as sa

from sqlalchemy.orm import Session
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative import declarative_base


Base = declarative_base()


class Group(Base):
    __tablename__ = 'group'
    id = sa.Column(sa.Integer, autoincrement=True, primary_key=True)
    name = sa.Column(sa.UnicodeText())
    members = association_proxy('group_memberships', 'user',
            creator=lambda user: GroupMembership(user=user))


class User(Base):
    __tablename__ = 'user'
    id = sa.Column(sa.Integer, autoincrement=True, primary_key=True)
    username = sa.Column(sa.UnicodeText())
    groups = association_proxy('group_memberships', 'group',
            creator=lambda group: GroupMembership(group=group))


class GroupMembership(Base):
    __tablename__ = 'user_group'
    user_id = sa.Column(sa.Integer, sa.ForeignKey('user.id'), primary_key=True)
    group_id = sa.Column(sa.Integer, sa.ForeignKey('group.id'), primary_key=True)

    user = sa.orm.relationship(
            'User',
            backref=sa.orm.backref('group_memberships', cascade="all, delete-orphan"))
    group = sa.orm.relationship(
            'Group',
            backref=sa.orm.backref('group_memberships', cascade="all, delete-orphan"),
            order_by='Group.name')


if __name__ == '__main__':
    engine = sa.create_engine('sqlite://')
    Base.metadata.create_all(engine)
    session = Session(engine)

    group = Group(name='group name')
    user = User(username='user name')
    group.members.append(user)
    session.add(group)
    session.add(user)
    session.flush()
    assert group.members == [user]
    assert user.groups == [group]
    group.members.remove(user)
    session.flush()
    assert group.members == []
    assert user.groups == []  # This assertion fails, user.groups is [None]

I've tried to follow the answers to SQLAlchemy relationship with association_proxy problems and How can SQLAlchemy association_proxy be used bi-directionally? but they do not seem to help.

Answer 1

I discovered your problem almost entirely by accident, as I was trying to figure out what's going on.

Because there wasn't any data in the db, I added a session.commit(). It turns out that (from the linked answer):

The changes aren't persisted permanently to disk, or visible to other transactions until the database receives a COMMIT for the current transaction (which is what session.commit() does).

Because you are just .flush()ing the changes, sqlalchemy never re-queries the database. You can verify this by adding:

import logging
logging.getLogger('sqlalchemy').setLevel(logging.INFO)
logging.getLogger('sqlalchemy').addHandler(logging.StreamHandler())

And then simply running your code. It will display all of the queries that are run as they happen. Then you can change session.flush() to session.commit() and then re-run, and you'll see that several SELECT statements are run after your commit.

It looks like either session.expire(user) or session.refresh(user) will force a refresh of the user, as well. I'm not sure if there's a way to force the update to propagate to the other object without being explicit about it (or if that's even desirable).