SQLAlchemy 同时实现一对一和一对多 (AmbiguousForeignKeysError)

Question

我正在使用SQLAlchemy，想要在同一个父类上实现一对一和一对多的关系。这样做是为了更简单地跟踪主要的子实体。

但是我遇到了一个错误：

AmbiguousForeignKeysError: 无法确定父子表之间的连接条件，关系Customer.contact存在多个外键路径连接这些表。请指定'foreign_keys'参数，提供一个包含那些应该被视为指向父表的外键引用的列的列表。

我是不是做错了什么，还是说这根本不可能？

下面是一个代码示例：

class Customer(Base):

    __tablename__ = 'customer'

    id = Column(Integer, primary_key=True)
    contact_id = Column(Integer, ForeignKey('contact.id'))
    address_id = Column(Integer, ForeignKey('address.id'))

    contact = relationship('Contact', backref=backref("contact", uselist=False))
    address = relationship('Address', backref=backref("address", uselist=False))

    contact_list = relationship('Contact')
    address_list = relationship('Address')


class Contact(Base):

    __tablename__ = 'contact'

    id = Column(Integer, primary_key=True)
    customer_id = Column(Integer, ForeignKey(
        'customer.id',
        use_alter=True, name='fk_contact_customer_id_customer',
        onupdate='CASCADE', ondelete='SET NULL'
    ))
    first_name = Column(String(32))
    last_name = Column(String(32))


class Address(Base):

    __tablename__ = 'address'

    id = Column(Integer, primary_key=True)
    customer_id = Column(Integer, ForeignKey(
        'customer.id',
        use_alter=True, name='fk_address_customer_id_customer',
        onupdate='CASCADE', ondelete='SET NULL'
    ))
    label = Column(String(32))

谢谢

Answer 1

看起来这个问题的解决办法在文档的后面有提到：SQLAlchemy不知道该用哪个外键，所以你需要像下面这样在relationship(foreign_keys=[])中指定这些外键，使用Column对象：

class Contact(Base):
    # ...
    customer_id = Column(Integer, ForeignKey(
        'customer.id',
        use_alter=True, name='fk_contact_customer_id_customer',
        onupdate='CASCADE', ondelete='SET NULL'
    ))
    # ...


class Customer(Base):
    # ...
    contact_id = Column(Integer, ForeignKey('contact.id'))
    #...
    contact = relationship('Contact', uselist=False, foreign_keys=[contact_id])
    contact_list = relationship('Contact', foreign_keys=[Contact.customer_id])
    #...