SQLAlchemy InvalidRequestError：初始化User映射器时'_user'未定义

问题根源 #

你的代码存在三个核心错误：

1. 多对多关系配置逻辑错误：User与Project通过带额外字段（role）的UserProject表关联，这种场景不能直接用secondary参数（secondary仅适用于无额外字段的纯关联表）。
2. secondary参数值错误：User、Project、UserProject类中的secondary参数填了不存在的表名（_user、_project、_user_project），导致SQLAlchemy找不到对应名称。
3. 关系映射方向混乱：部分relationship的关联目标和参数不匹配，引发初始化错误。

修复后的完整代码 #

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship, Mapped, DeclarativeBase, mapped_column
import uuid as uuid_pkg


class Base(DeclarativeBase):
    pass


class User(Base):
    __tablename__ = "user"
    __table_args__ = {"schema": "public"}
    id: Mapped[int] = mapped_column(primary_key=True, comment="Уникальный идентификатор пользователя")
    uuid: Mapped[uuid_pkg.UUID] = mapped_column(unique=True, insert_default=uuid_pkg.uuid4,
                                                comment="Уникальный uuid4 пользователя")
    email: Mapped[str] = mapped_column(unique=True, comment="Почта, по которой пользователь регистрировался")
    hashed_password: Mapped[str] = mapped_column(comment="Хеш пароля от аккаунта")
    
    # User与UserProject是一对多
    _user_projects: Mapped[list["UserProject"]] = relationship(back_populates="_user", lazy="joined")
    # User与SubscriptionPlan是一对多
    _subscriptions: Mapped[list["SubscriptionPlan"]] = relationship(back_populates="_user", lazy="joined")
    
    # 可选：通过UserProject间接关联Project（多对多）
    @property
    def projects(self):
        return [up._project for up in self._user_projects]


class Project(Base):
    __tablename__ = "project"
    __table_args__ = {"schema": "public"}
    id: Mapped[int] = mapped_column(primary_key=True)
    uuid: Mapped[uuid_pkg.UUID] = mapped_column(unique=True, insert_default=uuid_pkg.uuid4)
    name: Mapped[str] = mapped_column(nullable=False)
    
    # Project与Notification是一对多
    _notifications: Mapped[list["Notification"]] = relationship(back_populates="_project", lazy="joined")
    # Project与UserProject是一对多
    _user_projects: Mapped[list["UserProject"]] = relationship(back_populates="_project", lazy="joined")
    
    # 可选：通过UserProject间接关联User（多对多）
    @property
    def users(self):
        return [up._user for up in self._user_projects]


class UserProject(Base):
    __tablename__ = "user_project"
    __table_args__ = {"schema": "public"}
    id: Mapped[int] = mapped_column(primary_key=True)
    user_id: Mapped[int] = mapped_column(ForeignKey(column=User.id, ondelete='cascade', onupdate='cascade'))
    project_id: Mapped[int] = mapped_column(ForeignKey(column=Project.id, ondelete='cascade', onupdate='cascade'))
    role: Mapped[str] = mapped_column(nullable=False)
    
    # UserProject与User是多对一
    _user: Mapped["User"] = relationship(back_populates="_user_projects", lazy="joined")
    # UserProject与Project是多对一
    _project: Mapped["Project"] = relationship(back_populates="_user_projects", lazy="joined")


class Notification(Base):
    __tablename__ = "notification"
    __table_args__ = {"schema": "public"}
    id: Mapped[int] = mapped_column(primary_key=True)
    project_id: Mapped[int] = mapped_column(ForeignKey(Project.id, ondelete='cascade', onupdate='cascade'))
    email: Mapped[str] = mapped_column(nullable=True)
    telegram_id: Mapped[int] = mapped_column(nullable=True)
    vk_domain: Mapped[str] = mapped_column(nullable=True)
    website: Mapped[str] = mapped_column(nullable=True)
    
    # Notification与Project是多对一
    _project: Mapped["Project"] = relationship(back_populates="_notifications", lazy="joined")


class SubscriptionPlan(Base):
    __tablename__ = "subscription_plan"
    __table_args__ = {"schema": "public"}
    id: Mapped[int] = mapped_column(primary_key=True)
    user_id: Mapped[int] = mapped_column(ForeignKey(User.id, ondelete='cascade', onupdate='cascade'))
    name: Mapped[str] = mapped_column(nullable=False)
    event_volume: Mapped[int] = mapped_column(nullable=False)
    current_event_volume: Mapped[int] = mapped_column(nullable=False)
    project_volume: Mapped[int] = mapped_column(nullable=False)
    data_retention: Mapped[int] = mapped_column(nullable=False)
    
    # SubscriptionPlan与User是多对一
    _user: Mapped["User"] = relationship(back_populates="_subscriptions", lazy="joined")

关键修复点说明 #

• 把User、Project与UserProject的关系调整为一对多/多对一，因为UserProject包含额外字段role，属于关联实体而非纯关联表。
• 移除了所有错误的secondary参数，改用back_populates双向绑定关系。
• 将字段名改为复数形式（如_user_projects），符合ORM中"一个对象对应多个关联对象"的语义惯例。
• 增加了可选的@property方法，方便通过User直接获取关联的Project集合，反之亦然。

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