Flask-SQLAlchemy 多对多关系：正确配置用户角色模型与常见错误解析

import sqlalchemy as sa
import sqlalchemy.orm as so
from flask_sqlalchemy import SQLAlchemy
from flask_login import UserMixin
from flask_security.models import fsqla_v3 as fsqla
from datetime import datetime, timezone
from typing import Optional

# 假设 db 实例已在 app.py 中初始化
db = SQLAlchemy()

# 关联表定义
roles_user_table = db.Table(
    "roles_user_table",
    db.metadata,
    sa.Column("user_id", sa.Integer, sa.ForeignKey("user.id"), primary_key=True),
    sa.Column("role_id", sa.Integer, sa.ForeignKey("role.id"), primary_key=True) # 注意这里是 "role.id"
)

# 假设 User 和 Post 模型已定义，此处仅展示关键部分
class User(fsqla.FsUserMixin, db.Model):
    id: so.Mapped[int] = so.mapped_column(primary_key=True)
    email: so.Mapped[str] = so.mapped_column(sa.String(120), index=True, unique=True)
    # ... 其他用户属性

    # 错误的关系定义
    role: so.Mapped[list['Roles']] = so.relationship(
        "Roles",
        secondary=roles_user_table,
        primaryjoin=(roles_user_table.c.user_id == id),
        secondaryjoin=(roles_user_table.c.roles_id == id), # 错误：这里应该是 Role.id
        back_populates="name" # 错误：back_populates 应指向 Role 模型中的关系属性
    )

class Roles(db.Model, fsqla.FsRoleMixin): # 错误：类名通常应为单数 'Role'
    id: so.Mapped[int] = so.mapped_column(primary_key=True)
    name: so.Mapped[User] = so.relationship( # 错误：'name' 应是角色的名称字符串，而不是关系
        secondary=roles_user_table,
        back_populates="role"
    )
    # ...

# 尝试创建角色实例时触发错误
# admin_role = Roles(name="admin")
# sqlalchemy.exc.InvalidRequestError: When initializing mapper Mapper[User(user)], expression 'Role' failed to locate a name ('Role').
# If this is a class name, consider adding this relationship() to the <class 'app.models.User'> class after both dependent classes have been defined.

from datetime import datetime, timezone
from typing import Optional
import sqlalchemy as sa
import sqlalchemy.orm as so
from flask_sqlalchemy import SQLAlchemy
from flask_login import UserMixin
from werkzeug.security import generate_password_hash, check_password_hash
from flask_security.models import fsqla_v3 as fsqla
from hashlib import md5

# 假设 db 实例已在 app.py 中初始化
db = SQLAlchemy()

# 关联表定义 (保持不变)
roles_user_table = db.Table(
    "roles_user_table",
    db.metadata,
    sa.Column("user_id", sa.Integer, sa.ForeignKey("user.id"), primary_key=True),
    sa.Column("role_id", sa.Integer, sa.ForeignKey("role.id"), primary_key=True) # 注意这里是 "role.id"
)

class User(fsqla.FsUserMixin, db.Model):
    id: so.Mapped[int] = so.mapped_column(primary_key=True)
    email: so.Mapped[str] = so.mapped_column(sa.String(120), index=True, unique=True)
    password_hash: so.Mapped[Optional[str]] = so.mapped_column(sa.String(256))
    posts: so.WriteOnlyMapped['Post'] = so.relationship(back_populates='author')
    about_me: so.Mapped[Optional[str]] = so.mapped_column(sa.String(140))
    last_seen: so.Mapped[Optional[datetime]] = so.mapped_column(default=lambda: datetime.now(timezone.utc))

    # 正确的用户角色关系定义
    roles: so.Mapped[list['Role']] = so.relationship(
        "Role", # 目标类名，与 Role 模型对应
        secondary=roles_user_table,
        back_populates="users" # 指向 Role 模型中的 'users' 关系属性
    )

    def set_password(self, password):
        self.password_hash = generate_password_hash(password)

    def check_password(self, password):
        return check_password_hash(self.password_hash, password)

    def __repr__(self):
        return '<User {}>'.format(self.email)

    def avatar(self, size):
        digest = md5(self.email.lower().encode('utf-8')).hexdigest()
        return f'https://www.gravatar.com/avatar/{digest}?d=identicon&s={size}'

class Post(db.Model):
    id: so.Mapped[int] = so.mapped_column(primary_key=True)
    body: so.Mapped[str] = so.mapped_column(sa.String(140))
    timestamp: so.Mapped[datetime] = so.mapped_column(index=True, default=lambda: datetime.now(timezone.utc))
    user_id: so.Mapped[int] = so.mapped_column(sa.ForeignKey(User.id), index=True)
    author: so.Mapped[User] = so.relationship(back_populates='posts')

    def __repr__(self):
        return '<Post {}>'.format(self.body)

class Role(db.Model, fsqla.FsRoleMixin): # 修正：类名改为单数 'Role'
    id: so.Mapped[int] = so.mapped_column(primary_key=True)
    name: so.Mapped[str] = so.mapped_column(sa.String(80), unique=True) # 修正：'name' 是角色名称字符串
    description: so.Mapped[Optional[str]] = so.mapped_column(sa.String(255)) # 可选：角色描述

    # 正确的角色用户关系定义
    users: so.WriteOnlyMapped[list['User']] = so.relationship(
        secondary=roles_user_table,
        back_populates="roles" # 指向 User 模型中的 'roles' 关系属性
    )

    def __repr__(self):
        return '<Role {}>'.format(self.name)

from app import db, User, Role # 假设你的模型定义在 app.py 中

# 创建角色
admin_role = Role(name="admin", description="Administrator role")
editor_role = Role(name="editor", description="Editor role")
db.session.add_all([admin_role, editor_role])
db.session.commit()

# 创建用户
user1 = User(email="test1@example.com")
user1.set_password("password123")
db.session.add(user1)
db.session.commit()

# 给用户分配角色
user1.roles.append(admin_role)
user1.roles.append(editor_role)
db.session.commit()

# 验证关系
print(user1.roles) # 输出：[<Role admin>, <Role editor>]
print(admin_role.users) # 输出：[<User test1@example.com>]