python - Sqlalchemy One - 多一合一

Question

我有两个模型:用户和组。

用户可以在一个组中，因此:

class User(db.Model):
    # other fields
    group_id = db.Column(db.Integer(), db.ForeignKey('group.id'))

但另一方面，我也会有一些有关创建该特定组的用户的信息:

class Group(db.Model):
    # other fields
    users = db.relationship("User", backref='group')
    created_by = db.Column(db.Integer(), db.ForeignKey('user.id'))

结果是:

sqlalchemy.exc.CircularDependencyError: Can't sort tables for DROP; an unresolvable foreign key dependency exists between tables: group, user.  Please ensure that the ForeignKey and ForeignKeyConstraint objects involved in the cycle have names so that they can be dropped using DROP CONSTRAINT.

我试过 use_alter=True ，但它给了我:

sqlalchemy.exc.CompileError: Can't emit DROP CONSTRAINT for constraint ForeignKeyConstraint(

Answer 1

有趣的是，我希望您能收到 AmbiguousForeignKeyError但是你似乎得到了一个 CircularDependencyError ?根据 the docs这是由两种情况引起的:

• In a Session flush operation, if two objects are mutually dependent on each other, they can not be inserted or deleted via INSERT or DELETE statements alone; an UPDATE will be needed to post-associate or pre-deassociate one of the foreign key constrained values. The post_update flag described at Rows that point to themselves / Mutually Dependent Rows can resolve this cycle.
• In a MetaData.sorted_tables operation, two ForeignKey or ForeignKeyConstraint objects mutually refer to each other. Apply the use_alter=True flag to one or both, see Creating/Dropping Foreign Key Constraints via ALTER.

我不确定您正在执行什么导致此特定错误，但很可能您可以通过解决模棱两可的引用来解决它。

模棱两可的引用是由于 SQLAlchemy 无法弄清楚当有多个引用(在这种情况下是 users 和 created_by)时如何执行连接。这可以通过指定关系应该如何连接来解决，这可以通过提供它应该使用的特定外键或通过明确确定连接条件来完成。

您可以在此处看到这些应用于您的示例:

class User(Base):
    # Other setup / fields

    group_id = Column(Integer, ForeignKey('group.id'))

class Group(Base):
    # Other setup / fields

    created_by_id = Column(Integer, ForeignKey('user.id'), nullable=False)
    created_by = relationship("User", foreign_keys=[created_by_id])
    users = relationship("User", backref="group", primaryjoin=id==User.group_id)

关于关系连接的文档: http://docs.sqlalchemy.org/en/latest/orm/join_conditions.html#configuring-how-relationship-joins