如何构建自引用关联数据结构？SQLAlchemy外键歧义问题求解

我尝试构建由Node组成的层级结构，每个Node同时关联多个“上游”节点和“下游”节点，但运行代码时出现以下错误：

sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition
between parent/child tables on relationship Node.links_up - there are
multiple foreign key paths 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.

我已在Link类的定义中设置了foreign_keys参数，但错误仍然存在。Node类没有外键，无法在其中设置该参数。这已是简化后的版本，我尝试用“secondary joins”实现Node.nodes_up和Node.nodes_down关系但完全失败，文档中有关联对象和邻接关系的示例，但没有两者结合的案例。我更倾向于用back_populates而非backref实现。

原代码如下：

from sqlalchemy import Column, Integer, String, ForeignKey, create_engine
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Node(Base):
    __tablename__ = 'node'
    id            = Column(Integer, primary_key=True)
    name          = Column(String(100))

    links_up = relationship('Link', back_populates='node_down')
    links_down = relationship('Link', back_populates='node_up')

class Link(Base):
    __tablename__ = 'link'
    up_id         = Column(ForeignKey('node.id'), primary_key=True)
    down_id       = Column(ForeignKey('node.id'), primary_key=True)

    node_up = relationship('Node',
                           foreign_keys=[up_id],
                           back_populates='links_down')

    node_down = relationship('Node',
                             foreign_keys=[down_id],
                             back_populates='links_up')

engine = create_engine('sqlite:///test.db')
Base.metadata.create_all(engine)
Session = sessionmaker(engine)

db = Session()
r = Node(name='Parent')
db.add(r)
db.commit()

问题原因与修复方案 #

错误的核心是：Node类的relationship没有明确指定关联Link表的哪个外键。虽然Link类中已经定义了foreign_keys，但Node这边的双向关联需要明确告知SQLAlchemy，当前Node的links_up/links_down对应Link表的哪一列外键。

修改后的代码如下（标注修改部分）：

from sqlalchemy import Column, Integer, String, ForeignKey, create_engine
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Node(Base):
    __tablename__ = 'node'
    id            = Column(Integer, primary_key=True)
    name          = Column(String(100))

    # 修改：添加foreign_keys参数，指定关联Link的down_id
    links_up = relationship('Link', back_populates='node_down', foreign_keys='Link.down_id')
    # 修改：添加foreign_keys参数，指定关联Link的up_id
    links_down = relationship('Link', back_populates='node_up', foreign_keys='Link.up_id')

class Link(Base):
    __tablename__ = 'link'
    up_id         = Column(ForeignKey('node.id'), primary_key=True)
    down_id       = Column(ForeignKey('node.id'), primary_key=True)

    node_up = relationship('Node',
                           foreign_keys=[up_id],
                           back_populates='links_down')

    node_down = relationship('Node',
                             foreign_keys=[down_id],
                             back_populates='links_up')

engine = create_engine('sqlite:///test.db')
Base.metadata.create_all(engine)
Session = sessionmaker(engine)

db = Session()
r = Node(name='Parent')
db.add(r)
db.commit()

补充：直接获取上下游Node对象 #

如果需要跳过Link中间表，直接获取上游/下游的Node对象，可以在Node类中添加以下关联：

class Node(Base):
    __tablename__ = 'node'
    id            = Column(Integer, primary_key=True)
    name          = Column(String(100))

    links_up = relationship('Link', back_populates='node_down', foreign_keys='Link.down_id')
    links_down = relationship('Link', back_populates='node_up', foreign_keys='Link.up_id')

    # 直接获取上游节点列表
    nodes_up = relationship('Node',
                           secondary='link',
                           primaryjoin='Node.id == Link.down_id',
                           secondaryjoin='Node.id == Link.up_id',
                           back_populates='nodes_down')
    # 直接获取下游节点列表
    nodes_down = relationship('Node',
                             secondary='link',
                             primaryjoin='Node.id == Link.up_id',
                             secondaryjoin='Node.id == Link.down_id',
                             back_populates='nodes_up')

这样就能通过node.nodes_up直接拿到所有上游Node，node.nodes_down拿到所有下游Node，无需通过Link中间表操作。

内容的提问来源于stack exchange，提问作者musbur