如何用SQLModel/SQLAlchemy实现论文引用关联表？

这个错误的核心原因是Citation表同时用citing_id和cited_id两个外键关联Paper表，ORM无法自动区分每个双向关系对应的外键路径，必须显式指定foreign_keys参数来明确关联规则。

修复后的完整代码如下：

from typing import List, Optional
from sqlmodel import create_engine, Field, Relationship, Session, SQLModel

sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}" 
engine = create_engine(sqlite_url, echo=True)

class Paper(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    # 指定该关系对应Citation表的citing_id外键
    bibliography: List["Citation"] = Relationship(
        back_populates="citing", foreign_keys="Citation.citing_id"
    )
    # 指定该关系对应Citation表的cited_id外键
    cited_by: List["Citation"] = Relationship(
        back_populates="cited", foreign_keys="Citation.cited_id"
    )

class Citation(SQLModel, table=True):
    citing_id: Optional[int] = Field(default=None, primary_key=True, foreign_key="paper.id")
    # 指定该关系绑定当前类的citing_id外键
    citing: "Paper" = Relationship(
        back_populates="bibliography", foreign_keys=[citing_id]
    )
    cited_id: Optional[int] = Field(default=None, primary_key=True, foreign_key="paper.id")
    # 指定该关系绑定当前类的cited_id外键
    cited: "Paper" = Relationship(
        back_populates="cited_by", foreign_keys=[cited_id]
    )

if __name__ == "__main__":
    SQLModel.metadata.create_all(engine)
    
    # 测试示例：创建论文并建立引用关系
    with Session(engine) as session:
        paper1 = Paper()
        paper2 = Paper()
        session.add_all([paper1, paper2])
        session.commit()
        
        # paper2引用paper1
        citation = Citation(citing_id=paper2.id, cited_id=paper1.id)
        session.add(citation)
        session.commit()
        
        # 验证关联是否生效
        session.refresh(paper2)
        print(f"paper2引用的论文数量: {len(paper2.bibliography)}")
        session.refresh(paper1)
        print(f"paper1被引用的次数: {len(paper1.cited_by)}")

关键修改说明 #

• 在Paper.bibliography的Relationship中，通过foreign_keys="Citation.citing_id"明确该关系对应记录"引用者"的外键
• 在Paper.cited_by的Relationship中，通过foreign_keys="Citation.cited_id"明确该关系对应记录"被引用者"的外键
• 在Citation类的两个Relationship中，分别用foreign_keys=[citing_id]和foreign_keys=[cited_id]绑定当前关系对应的外键字段，确保双向关联的一致性

修改后ORM就能准确识别每个关联的外键路径，解决模糊外键的错误。

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