由于具有相同关系的延迟加载和动态版本而导致的 SQLAlchemy DELETE 错误

2021-12-30 00:00:00 python sqlalchemy mysql

这是一些示例代码:

users_groups = Table('users_groups', Model.metadata,
    Column('user_id', Integer, ForeignKey('users.id')),
    Column('group_id', Integer, ForeignKey('groups.id'))
)

class User(Model):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)


class Group(Model):
    __tablename__ = 'groups'
    id = Column(Integer, primary_key=True)

    users = relationship('User', secondary=users_groups, lazy='select', backref='groups')
    users_dynamic = relationship('User', secondary=users_groups, lazy='dynamic')

所以这里发生的情况是，如果您像这样将一群用户添加到一个组中:

So what happens here is that if you add a bunch of users to a group like so:

g = Group()
g.users = [User(), User(), User()]
session.add(g)
session.commit()

然后尝试删除该组

session.delete(g)
session.commit()

您将收到某种形式的此错误:

You will get some form of this error:

DELETE statement on table 'users_groups' expected to delete 3 row(s); Only 0 were matched.

删除关系的第二个版本(在我的情况下是动态版本)解决了这个问题.我什至不知道从哪里开始理解为什么会发生这种情况.在整个 SQLAlchemy 模型中，我在许多情况下一直使用 2 个版本的各种关系，以便在特定情况下轻松使用最合适的查询策略.这是第一次出现意外问题.

Removing the 2nd version of the relationship (the dynamic one in my case) fixes this problem. I am not even sure where to begin in terms of understanding why this is happening. I have been using 2 versions of various relationships in many cases throughout my SQLAlchemy models in order to make it easy to use the most appropriate query-strategy given a situation. This is the first time it has caused an unexpected issue.

欢迎任何建议.

推荐答案

Group.users 和 Group.users_dynamic 关系都试图调和这样一个事实，即 Group 被删除以及能够管理 User() 他们引用的对象；一个关系成功而第二个关系失败，因为关联表中的行已经被删除.最直接的解决方案是将除一个相同关系之外的所有关系标记为仅查看:

both the Group.users and Group.users_dynamic relationships are attempting to reconcile the fact that the Group is being deleted along with being able to manage the User() objects they refer to; one relationship succeeds while the second one fails, as the rows in the association table were already deleted. The most straightforward solution is to mark all but one of the identical relationships as viewonly:

class Group(Base):
    __tablename__ = 'groups'
    id = Column(Integer, primary_key=True)

    users = relationship('User', secondary=users_groups, lazy='select', backref='groups')
    users_dynamic = relationship('User', viewonly=True, secondary=users_groups, lazy='dynamic')

如果你仍然想让两个关系处理某种程度的突变，你需要小心地做这件事，因为 SQLAlchemy 不知道如何同时协调两个关系的变化，所以像这样的冲突如果您对两种关系进行等效的突变，则可能会继续发生(如双插入等).为了自己处理删除"问题，您还可以尝试将 Group.users_dynamic 设置为passive_deletes=True:

if you're still wanting to have both relationships handle some degree of mutations, you'd need to do this carefully as SQLAlchemy doesn't know how to coordinate among changes in two relationships at the same time, so conflicts like this can continue to happen (like double inserts, etc) if you make equivalent mutations on both relationships. To just take care of the "delete" issue by itself, you can also try setting Group.users_dynamic to passive_deletes=True:

class Group(Base):
    __tablename__ = 'groups'
    id = Column(Integer, primary_key=True)

    users = relationship('User', secondary=users_groups, lazy='select', backref='groups')
    users_dynamic = relationship('User', passive_deletes=True, secondary=users_groups, lazy='dynamic')

相关文章