SQLAlchemy多对多关联配置报错：InvalidRequestError求助

问题背景 #

配置带额外字段的SQLAlchemy多对多关联时，出现映射器初始化失败的错误，涉及UserORM、ProjectORM和关联表UserProjectORM三个模型。

原模型代码 #

ProjectORM 代码

class ProjectORM(AbstractModel):
    __tablename__ = "projects"

    status: Mapped[EnumStatus]
    title: Mapped[str] = mapped_column(String(255), unique=True)
    description: Mapped[str | None]

    category_id: Mapped[int] = mapped_column(ForeignKey("categories.id"))
    category: Mapped[CategoryORM] = relationship(back_populates="projects")

    users: Mapped[list["UserProjectORM"]] = relationship(back_populates="projects")
    resources: Mapped[list["ResourceORM"]] = relationship(back_populates="project")

    time_created: Mapped[time_created]

UserORM 代码（位于另一个文件）

class UserORM(AbstractModel):
    __tablename__ = "users"

    username: Mapped[str] = mapped_column(String(35), unique=True)
    age: Mapped[str] = mapped_column(String(3), nullable=True)
    email: Mapped[str] = mapped_column(String(155), unique=True)

    surname: Mapped[str] = mapped_column(String(100))
    firstname: Mapped[str] = mapped_column(String(100))
    patronymic: Mapped[str] = mapped_column(String(100), nullable=True)
    about: Mapped[str] = mapped_column(nullable=True)
    file_path: Mapped[str] = mapped_column(nullable=True)

    hashed_password: Mapped[str] = mapped_column(String(125))
    is_active: Mapped[bool] = mapped_column(default=False)
    time_created: Mapped[time_created]

    tokens: Mapped[list["TokenORM"]] = relationship(back_populates="user")
    projects: Mapped[list["project.UserProjectORM"]] = relationship(back_populates="users")

UserProjectORM 代码（关联表）

class UserProjectORM(AbstractModel):
    __tablename__ = "users_projects"

    user_id: Mapped[int] = mapped_column(ForeignKey("users.id"), primary_key=True)
    project_id: Mapped[int] = mapped_column(ForeignKey("projects.id"), primary_key=True)
    user: Mapped["user.UserORM"] = relationship(back_populates="projects")
    project: Mapped["ProjectORM"] = relationship(back_populates="users")

    is_author: Mapped[bool] = mapped_column(Boolean, default=False)
    datatime_joined: Mapped[time_created]

time_created 定义

time_created = Annotated[
    datetime.datetime,
    mapped_column(DateTime(timezone=True), server_default=func.now()),
]

错误信息 #

sqlalchemy.exc.InvalidRequestError: One or more mappers failed to initialize - can't proceed with initialization of other mappers. Triggering mapper: 'Mapper[ProjectORM(projects)]'. Original exception was: Mapper 'Mapper[UserProjectORM(users_projects)]' has no property 'projects'.  If this property was indicated from other mappers or configure events, ensure registry.configure() has been called.


/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/mapper.py:4240: InvalidRequestError

解决方案 #

错误根源是relationship的back_populates参数值与关联表中对应的属性名称不匹配：

• ProjectORM的users关系中，back_populates应指向UserProjectORM的project属性（单数），而非projects
• UserORM的projects关系中，back_populates应指向UserProjectORM的user属性（单数），而非users

修正后的代码 #

修正后的 ProjectORM

class ProjectORM(AbstractModel):
    __tablename__ = "projects"

    status: Mapped[EnumStatus]
    title: Mapped[str] = mapped_column(String(255), unique=True)
    description: Mapped[str | None]

    category_id: Mapped[int] = mapped_column(ForeignKey("categories.id"))
    category: Mapped[CategoryORM] = relationship(back_populates="projects")

    # 修正back_populates为"project"
    users: Mapped[list["UserProjectORM"]] = relationship(back_populates="project")
    resources: Mapped[list["ResourceORM"]] = relationship(back_populates="project")

    time_created: Mapped[time_created]

修正后的 UserORM

class UserORM(AbstractModel):
    __tablename__ = "users"

    username: Mapped[str] = mapped_column(String(35), unique=True)
    age: Mapped[str] = mapped_column(String(3), nullable=True)
    email: Mapped[str] = mapped_column(String(155), unique=True)

    surname: Mapped[str] = mapped_column(String(100))
    firstname: Mapped[str] = mapped_column(String(100))
    patronymic: Mapped[str] = mapped_column(String(100), nullable=True)
    about: Mapped[str] = mapped_column(nullable=True)
    file_path: Mapped[str] = mapped_column(nullable=True)

    hashed_password: Mapped[str] = mapped_column(String(125))
    is_active: Mapped[bool] = mapped_column(default=False)
    time_created: Mapped[time_created]

    tokens: Mapped[list["TokenORM"]] = relationship(back_populates="user")
    # 修正back_populates为"user"
    projects: Mapped[list["project.UserProjectORM"]] = relationship(back_populates="user")

关联表 UserProjectORM 无需修改

class UserProjectORM(AbstractModel):
    __tablename__ = "users_projects"

    user_id: Mapped[int] = mapped_column(ForeignKey("users.id"), primary_key=True)
    project_id: Mapped[int] = mapped_column(ForeignKey("projects.id"), primary_key=True)
    user: Mapped["user.UserORM"] = relationship(back_populates="projects")
    project: Mapped["ProjectORM"] = relationship(back_populates="users")

    is_author: Mapped[bool] = mapped_column(Boolean, default=False)
    datatime_joined: Mapped[time_created]

修改后，映射器即可正确识别双向关联的属性名称，初始化错误会被解决。

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