SQLAlchemy:AmbiguousForeignKeysError对paren的双重引用

Question

对于可以具有角色的子组的组，我有以下简化代码：

from sqlalchemy import (
    CheckConstraint,
    Column,
    ForeignKey,
    PrimaryKeyConstraint,
    String,
    create_engine,
)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship

Base = declarative_base()


class Group(Base):
    __tablename__ = 'groups'
    name = Column(String, primary_key=True)
    group_roles = relationship('GroupRole', back_populates='group')


class GroupRole(Base):
    __tablename__ = 'group_roles'
    name = Column(String, ForeignKey(Group.name))
    group_name = Column(String, ForeignKey(Group.name))
    role = Column(String, nullable=False)
    __table_args__ = (
        CheckConstraint('name != group_name'),
        PrimaryKeyConstraint('name', 'group_name'),
    )
    group = relationship(Group,
                         foreign_keys=[name],
                         back_populates='group_roles')


engine = create_engine('sqlite:///:memory:')
Base.metadata.create_all(engine)
session = sessionmaker(bind=engine)()
session.add(Group(name='test'))

当我运行这个时，我得到sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship Group.group_roles - there are multiple foreign key path s linking the tables. Specify the 'foreign_keys' argument, providing a list of those columns which should be counted as containing a foreign key reference to the parent table.

我还尝试在GroupRole表下面添加以下内容，以代替Group中已经定义的对应行：

Group.group_roles = relationship('GroupRole', foreign_keys=[Column(String, ForeignKey(GroupRole.name))], back_populates='group')

但我也犯了同样的错误，看起来其他人不必这么做。我做错什么了？如何使我的GroupRole表引用我的Group表？我正在使用SQLAlchemy 1.3和Python3.7。你知道吗

Answer 1

您可以使代码只与backref关系上的GroupRole.group一起工作，例如：

class Group(Base):
    __tablename__ = 'groups'
    name = Column(String, primary_key=True)

class GroupRole(Base):
    __tablename__ = 'group_roles'
    name = Column(String, ForeignKey(Group.name))
    group_name = Column(String, ForeignKey(Group.name))
    role = Column(String, nullable=False)
    __table_args__ = (
        CheckConstraint('name != group_name'),
        PrimaryKeyConstraint('name', 'group_name'),
    )
    group = relationship(Group,
                         foreign_keys=[name],
                         backref='group_roles')

虽然不确定这是什么目的，但工作：

g = Group(name='test', group_roles=[
    GroupRole(group_name='testrole', name='test', role='testrole'),
    GroupRole(group_name='testrole2', name='test', role='testrole2')
])
session.add(g)