如何在SQLAlchemy中为多外键配置反向关联（back_populate）

现有代码（通过反射生成） #

Base = declarative_base()
Base.metadata.bind = Engine

class ResourceDB(Base):
    __tablename__ = 'resources'
    __table_args__ = {'autoload': True, 'extend_existing': True}
    resource_id = Column('resource_id', Integer, primary_key=True)
    primaryKey = 'resource_id'

class GameDB(Base):
    __tablename__ = 'games'
    __table_args__ = {'autoload': True, 'extend_existing': True}
    game_id = Column('game_id', Integer, primary_key=True)
    primaryKey = 'game_id'
    resource_egm_id = Column(Integer, ForeignKey("resources.resource_id"))
    resource_vm_id = Column(Integer, ForeignKey("resources.resource_id"))

    resourceEgmId = relationship("ResourceDB", foreign_keys=[resource_egm_id], lazy="joined", join_depth=1)
    resourceVmId = relationship("ResourceDB", foreign_keys=[resource_vm_id], lazy="joined", join_depth=1)

需求 #

目前从Game对象可以正常引用两个Resource对象，但需要实现从Resource对象引用所有关联它的Game——一个Resource可能被多个Game通过resource_egm_id或resource_vm_id关联，且resources表没有game_id字段。

尝试的方案及报错 #

方案1：使用back_populates配置双向关联 #

在ResourceDB中添加关联，同时修改GameDB的关联：

# 在ResourceDB中添加
games = relationship("GameDB", back_populates="allResources", foreign_keys="[GameDB.resource_egm_id, GameDB.resource_vm_id]")
# 在GameDB中替换原有两个关联为
allResources = relationship("ResourceDB", back_populates="games", foreign_keys=[resource_egm_id, resource_vm_id])

报错信息：

Could not determine join condition between parent/child tables on relationship ResourceDB.games - 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.

方案2：修改ResourceDB关联中使用表名 #

调整ResourceDB的关联代码：

games = relationship("GameDB", back_populates="Resources", foreign_keys="[games.resource_egm_id, games.resource_vm_id]")

报错信息：

One or more mappers failed to initialize - can't proceed with initialization of other mappers. Triggering mapper: 'mapped class ResourceDB->resources'. Original exception was: 'Table' object has no attribute 'resource_egm_id'

方案3：使用backref定义关联（有进展但仍有问题） #

仅在GameDB中用backref定义关联：

resourceEgmId = relationship("ResourceDB", foreign_keys=[resource_egm_id], backref="gamesEgm", lazy="joined", join_depth=2)
resourceVmId = relationship("ResourceDB", foreign_keys=[resource_vm_id], backref="gamesVm",lazy="joined", join_depth=2)

此时能正常获取Resource数据，但因使用会话上下文管理器，访问Game端数据时报错：

Parent instance is not bound to a Session; lazy load operation of attribute 'gamesEgm' cannot proceed

之前通过设置lazy="joined"解决过类似懒加载问题，但使用backref时无法在ResourceDB类中设置该参数。

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