Update value in SqlAlchemy many-to-many association table

Question

Given the tables below, how do I update what group a user is in? I feel like I have to be missing something obvious but I haven't found anything so far that shows how to do this.

group_permission_table = Table('group_permission', metadata,
    Column('group_name', Integer, ForeignKey('group.group_name', onupdate="CASCADE", ondelete="CASCADE")),
    Column('permission_name', Integer, ForeignKey('permission.permission_name', onupdate="CASCADE", ondelete="CASCADE"))
)

user_group_table = Table('user_group', metadata,
    Column('user_name', Integer, ForeignKey('user.user_name', onupdate="CASCADE", ondelete="CASCADE")),
    Column('group', Integer, ForeignKey('group.group_name', onupdate="CASCADE", ondelete="CASCADE"))
)

class Group(Base):
    """An ultra-simple group definition.
    """
    __tablename__ = 'group'

    group_name = Column("group_name", Text(), primary_key=True)

    users = relationship('User', secondary=user_group_table, backref='groups')

class User(Base):
    """Reasonably basic User definition. Probably would want additional
    attributes.
    """
    __tablename__ = 'user'

    user_name = Column("user_name", Text(), primary_key=True)
    _password = Column('password', Text)
    email = Column('email', Text())

Anybody? This has to be a fairly common problem. How do you update what is in an association table? If I can't update an item in there, how do I delete a row in there?

Answer 1

You do not need to delete anything from the M2N relation table explicitly. When you delete a user from the group.users and then commit your changes, the data in the relationship table will be updated (inserted or deleted accordingly). You can check this by logging the SQL statements with the echo=True

engine = create_engine(..., echo=True)

The deletion works the following way:

...
my_user = session.query(User).get(USR_ID)
my_group = session.query(User).get(GRP_ID)
my_group.users.append(my_user)
session.commit() # SA will insert a relationship row
my_group.users.remove(my_user)
session.commit() # SA will delete a relationship row