时间戳

首页 > 解决方案 > SQLALchemy中如何在具有多个连接路径的表上建立双向关系？

python - SQLALchemy中如何在具有多个连接路径的表上建立双向关系？

问题描述

sqlalchemy 文档中给出的示例是，

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

Base = declarative_base()

class Customer(Base):
    __tablename__ = 'customer'
    id = Column(Integer, primary_key=True)
    name = Column(String)

    billing_address_id = Column(Integer, ForeignKey("address.id"))
    shipping_address_id = Column(Integer, ForeignKey("address.id"))

    billing_address = relationship("Address", foreign_keys=[billing_address_id])
    shipping_address = relationship("Address", foreign_keys=[shipping_address_id])

class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    street = Column(String)
    city = Column(String)
    state = Column(String)
    zip = Column(String)

我正在尝试一个类似的例子（不能在这里放置这么多代码）如果我做类似的事情它就不起作用：

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

Base = declarative_base()

class Customer(Base):
    __tablename__ = 'customer'
    id = Column(Integer, primary_key=True)
    name = Column(String)

    billing_address_id = Column(Integer, ForeignKey("address.id"))
    shipping_address_id = Column(Integer, ForeignKey("address.id"))

    billing_address = relationship("Address", foreign_keys=[billing_address_id], back_populates('bill_addr'))
    shipping_address = relationship("Address", foreign_keys=[shipping_address_id], back_populates('ship_addr'))

class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    street = Column(String)
    city = Column(String)
    state = Column(String)
    zip = Column(String)

    bill_addr = relationship("Customer", back_populates('billing_address'))
    ship_addr = relationship("Customer", back_populates('shipping_address'))

我有两个疑问：
Q1）上述关系是双向的吗？
Q2) 如何在具有多个连接路径的表之间建立双向关系？

编辑：就我而言，我收到以下错误：

sqlalchemy.exc.AmbiguousForeignKeysError
sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables
on relationship User.expenses - 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.

标签： pythonsqlalchemyflask-sqlalchemy

解决方案

如果我使用“backref”而不是“back_populates”，它会起作用。我将“backref”放在两个外键都存在的一侧的关系中，并删除了另一张表上的关系。

推荐阅读