Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
485 views
in Technique[技术] by (71.8m points)

python - SQLAlchemy DELETE Error caused by having a both lazy-load AND a dynamic version of the same relationship

Here is some example code:

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

and then try to delete the group

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.

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.

Any advice is welcome.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...