如何在SQLAlchemy中借助第三张表建立两张表的关联关系？

错误原因 #

你用UserSports.sport_id.has(sport='baseball')的写法不对，sport_id是数据库字段（Column），.has()方法是给relationship属性用的，用来过滤关联表的条件，所以才会报错说它不是ColumnProperty。

先修正模型里的低级语法错误 #

你的代码里有不少拼写、括号闭合、字段缺失的问题，先把这些问题修复：

• UserSports的sport_id外键写错成了states.id，应该是sports.id，而且括号没闭合
• Baseball表缺失user_sport_id字段（对应原SQL里的b.user_sport_id = us.id），且user_id的字段定义有语法错误
• Sports表的Mappped拼写错误，应该是Mapped
• 多个关系定义的括号没闭合

修正关联关系的写法 #

要实现你要的SQL JOIN逻辑，正确的primaryjoin有两种写法：

写法1：直接关联Sports表字段

在UserSports的baseball关系里，写完整的JOIN条件：

baseball: Mapped['Baseball'] = relationship(
    back_populates='user_sports',
    primaryjoin="and_(UserSports.id == Baseball.user_sport_id, Sports.sport == 'baseball', UserSports.sport_id == Sports.id)"
)

写法2：利用已有的Sports关联

因为UserSports已经和Sports有sports这个relationship，所以可以用.has()过滤，适合模型定义时的延迟引用场景：

baseball: Mapped['Baseball'] = relationship(
    back_populates='user_sports',
    primaryjoin="and_(UserSports.id == Baseball.user_sport_id, UserSports.sports.has(sport='baseball'))"
)

完整修正后的模型代码 #

from sqlalchemy import ForeignKey, TEXT
from sqlalchemy.orm import Mapped, mapped_column, relationship, Base
from typing import List

class UserSports(Base):
    __tablename__ = "user_sports"
    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    user_id: Mapped[int] = mapped_column(nullable=False)
    sport_id: Mapped[int] = mapped_column(ForeignKey('sports.id'), nullable=False)
    
    sports: Mapped['Sports'] = relationship(back_populates='user_sports')
    baseball: Mapped['Baseball'] = relationship(
        back_populates='user_sports',
        primaryjoin="and_(UserSports.id == Baseball.user_sport_id, UserSports.sports.has(sport='baseball'))"
    )

class Baseball(Base):
    __tablename__ = "baseball"
    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    user_id: Mapped[int] = mapped_column(ForeignKey('users.id'), nullable=False)
    user_sport_id: Mapped[int] = mapped_column(ForeignKey('user_sports.id'), nullable=False)  # 新增关联字段
    position: Mapped[str] = mapped_column(TEXT, nullable=False)
    team: Mapped[str] = mapped_column(TEXT, nullable=False)
    number: Mapped[int] = mapped_column(nullable=False)
    
    user_sports: Mapped['UserSports'] = relationship(back_populates='baseball')

class Sports(Base):
    __tablename__ = "sports"
    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    sport: Mapped[str] = mapped_column(TEXT, nullable=False)
    
    user_sports: Mapped[List["UserSports"]] = relationship(back_populates='sports')

额外说明 #

• Baseball表必须加user_sport_id字段，因为原SQL里是通过这个字段和user_sports关联的，之前的模型漏掉了这一点
• 保持SQLAlchemy 2.0声明式语法的规范性，比如把nullable=False放在mapped_column的参数中

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