SQLAlchemy DELETE由具有相同关系的延迟加载和动态版本引起的错误 [英] SQLAlchemy DELETE Error caused by having a both lazy-load AND a dynamic version of the same relationship

问题描述

这是一些示例代码:

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关系都试图调和组正在被删除以及能够管理User()对象的事实.他们指的是；一种关系成功，而第二种关系失败，因为关联表中的行已被删除.最直接的解决方案是将除了一个相同的关系以外的所有关系都标记为viewonly:

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')

这篇关于SQLAlchemy DELETE由具有相同关系的延迟加载和动态版本引起的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！