SAWarning: relationship X will copy column Q to column P, which conflicts with relationship(s): Y

在sqlalchemy之中，当一个字段对应多个relationship的时候。因为ORM要处理flush操作，而两个relationship可能都涉及到flush，以至于ORM无法同时兼顾。这时，sqlalchemy就会发出一个SAWarning。

为了避免该类事件，可以通过以下配置来实现。

假设，存在Parent和Child两个表，其中Child.parent_id的外键对应Parent.id这个字段

class Parent(Base):
    __tablename__ = "parent"
    id = Column(Integer, primary_key=True)


class Child(Base):
    __tablename__ = "child"
    id = Column(Integer, primary_key=True)
    parent_id = Column(ForeignKey("parent.id"))

1. 配置relationship.back_populates

如果，我们需要在Parent中处理children，并且在Child中来处理parent。那么可以使用如下方法。

class Parent(Base):
    __tablename__ = "parent"
    id = Column(Integer, primary_key=True)
    children = relationship("Child")


class Child(Base):
    __tablename__ = "child"
    id = Column(Integer, primary_key=True)
    parent_id = Column(ForeignKey("parent.id"))
    parent = relationship("Parent")

但是，这样处理会报如下warning：

SAWarning: relationship 'Child.parent' will copy column parent.id to column child.parent_id,
which conflicts with relationship(s): 'Parent.children' (copies parent.id to child.parent_id).

大概意思是Child.parent和Parent.children这两个relationship发生了冲突。

这时，我们就可以通过配置back_populates，来消除Warning，具体如下：

class Parent(Base):
    __tablename__ = "parent"
    id = Column(Integer, primary_key=True)
    children = relationship("Child", back_populates="parent")


class Child(Base):
    __tablename__ = "child"
    id = Column(Integer, primary_key=True)
    parent_id = Column(ForeignKey("parent.id"))
    parent = relationship("Parent", back_populates="children")

2. 配置relationship.overlaps

如果我们就是需要两个relationship对应同一个字段呢？比如，下面这种情况，Child.flag==1需要一个relationship，Child.flag==0也需要一个relationship。这时可以通过配置overlaps来实现，具体如下：

class Parent(Base):
    __tablename__ = "parent"
    id = Column(Integer, primary_key=True)
    c1 = relationship(
        "Child",
        primaryjoin="and_(Parent.id == Child.parent_id, Child.flag == 0)",
        backref="parent",
        overlaps="c2, parent",
    )
    c2 = relationship(
        "Child",
        primaryjoin="and_(Parent.id == Child.parent_id, Child.flag == 1)",
        overlaps="c1, parent",
    )


class Child(Base):
    __tablename__ = "child"
    id = Column(Integer, primary_key=True)
    parent_id = Column(ForeignKey("parent.id"))

    flag = Column(Integer)

3. 配置relationship.viewonly

还有一种情况，如果我们确实需要多次引用parent_id，但是只是在查询中使用，为了查询方便，根本没有想通过relationship来flush的目的。这时可以配置viewonly=True，具体如下：

class Parent(Base):
    __tablename__ = "parent"
    id = Column(Integer, primary_key=True)
    children = relationship("Child", back_populates="parent")
    children_num=relationship("Child", back_populates="parent",viewonly=True)


class Child(Base):
    __tablename__ = "child"
    id = Column(Integer, primary_key=True)
    parent_id = Column(ForeignKey("parent.id"))
    parent = relationship("Parent", back_populates="children")
    parent _num=relationship("Child", back_populates="children",viewonly=True)