SQLModel多对多关系失效求助：无法获取关联流派数据

在使用SQLModel实现Band与Genre的多对多关联时，查询Band数据并尝试获取关联Genre时触发NoForeignKeysError，报错信息如下：

sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between parent/child tables on relationship Genre.bands - there are no foreign keys linking these tables. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression

相关代码如下：

Base = declarative_base()

class Band_Genre(SQLModel, table=True):
    genre_id: Optional[int] = Field(default=None, foreign_key="FK_Band_Genre_Genre", primary_key=True)
    band_id: Optional[int] = Field(default=None, foreign_key="FK_Band_Genre_Band", primary_key=True)

class Genre(SQLModel, table=True):
    genre_id: int = Field(
        default=None,
        primary_key=True,
        description="Genre ID",
    )
    genre: Optional[str] = Field(
        default=None,
        description="Name of the genre",
    )
    band_links: List["Band"] = Relationship(back_populates='genres', link_model=Band_Genre)

    def __str__(self):
        return f"{self.genre_id}: {self.genre}"
    
class Band(SQLModel, table=True):
    band_id: Optional[int] = Field(
        default=None,
        primary_key=True,
        description="Band ID",
    )
    band_name: Optional[str] = Field(
        default=None,
        description="Name of the band",
    )
    genres: List[Genre] = Relationship(back_populates='bands',link_model=Band_Genre)
    
    def __str__(self):
        return f"{self.band_id}: {self.band_name}"
 

engine = create_engine(
    f"mssql+pyodbc://{uid}:{pwd}@{svr}/{db}?driver=ODBC+Driver+18+for+SQL+Server&TrustServerCertificate=yes",
)

Session = sessionmaker(bind=engine)

session = Session()
statement = select(Band)
results = session.execute(statement)

for r in results.all():
    for g in r.genres:
        print(g)

问题根源有三处，逐一修正即可解决：

• 中间表外键引用错误：Band_Genre的foreign_key需指向关联表的表名.列名，而非自定义约束名；
• 关联字段名不匹配：双向关联的back_populates参数需对应对方类的关系字段名，否则SQLAlchemy无法识别关联关系；
• 未显式加载关联数据：默认查询不会自动加载关联对象，需通过加载选项显式指定。

修正后的完整代码：

from typing import Optional, List
from sqlmodel import SQLModel, Field, Relationship, select, create_engine
from sqlalchemy.orm import sessionmaker, selectinload

class Band_Genre(SQLModel, table=True):
    genre_id: Optional[int] = Field(default=None, foreign_key="genre.genre_id", primary_key=True)
    band_id: Optional[int] = Field(default=None, foreign_key="band.band_id", primary_key=True)

class Genre(SQLModel, table=True):
    genre_id: int = Field(
        default=None,
        primary_key=True,
        description="Genre ID",
    )
    genre: Optional[str] = Field(
        default=None,
        description="Name of the genre",
    )
    bands: List["Band"] = Relationship(back_populates='genres', link_model=Band_Genre)

    def __str__(self):
        return f"{self.genre_id}: {self.genre}"
    
class Band(SQLModel, table=True):
    band_id: Optional[int] = Field(
        default=None,
        primary_key=True,
        description="Band ID",
    )
    band_name: Optional[str] = Field(
        default=None,
        description="Name of the band",
    )
    genres: List[Genre] = Relationship(back_populates='bands', link_model=Band_Genre)
    
    def __str__(self):
        return f"{self.band_id}: {self.band_name}"
 

engine = create_engine(
    f"mssql+pyodbc://{uid}:{pwd}@{svr}/{db}?driver=ODBC+Driver+18+for+SQL+Server&TrustServerCertificate=yes",
)

# 初始化表结构（首次运行需要）
SQLModel.metadata.create_all(engine)

Session = sessionmaker(bind=engine)

session = Session()
# 显式加载关联的Genre数据
statement = select(Band).options(selectinload(Band.genres))
results = session.execute(statement).scalars().all()

for band in results:
    print(f"Band: {band}")
    for genre in band.genres:
        print(f"  Genre: {genre}")

核心修正说明：

1. 中间表Band_Genre的外键改为"genre.genre_id"和"band.band_id"，对应主表的实际表名与主键列；
2. Genre类的关系字段名改为bands，与Band类中back_populates='bands'完全匹配，确保双向关联生效；
3. 查询时添加options(selectinload(Band.genres))，一次性加载所有关联的Genre数据，避免延迟加载引发的异常。

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