SQL多对多用户、组、角色

Question

我正在尝试与用户组角色表结构集成，其中用户可以属于许多组，并且每个组上有多个角色。

我发现了与此类似的问题，但是它不允许多个角色： 用户、组和角色的多对多声明性 SQLAlchemy 定义

我有以下表结构，并希望能够以以下方式访问角色：user.groups[0].roles

class Role(Base):
    __tablename__ = 'roles'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(Unicode(16), unique=True)
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(Unicode(16), unique=True)
class Group(Base):
    __tablename__ = 'groups'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(Unicode(16), unique=True)
class UserGroup(Base):
    __tablename__ = 'user_group_role'
    id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey('users.id', ondelete='CASCADE'), nullable=False)
    group_id = Column(Integer, ForeignKey('groups.id', ondelete='CASCADE'), nullable=False)
    role_id = Column(Integer, ForeignKey('roles.id', ondelete='CASCADE'), nullable=False)

Answer 1

这是 Turbogears 默认全栈快速入门中的一个示例。

from sqlalchemy import Table, ForeignKey, Column
from sqlalchemy.types import Unicode, Integer, DateTime
from sqlalchemy.orm import relation, synonym
from .model import DeclarativeBase, metadata, DBSession

# This is the association table for the many-to-many relationship between
# groups and permissions.
group_permission_table = Table('tg_group_permission', metadata,
                           Column('group_id', Integer,
                                  ForeignKey('tg_group.group_id',
                                             onupdate="CASCADE",
                                             ondelete="CASCADE"),
                                  primary_key=True),
                           Column('permission_id', Integer,
                                  ForeignKey('tg_permission.permission_id',
                                             onupdate="CASCADE",
                                             ondelete="CASCADE"),
                                  primary_key=True))

# This is the association table for the many-to-many relationship between
# groups and members - this is, the memberships.
user_group_table = Table('tg_user_group', metadata,
                     Column('user_id', Integer,
                            ForeignKey('tg_user.user_id',
                                       onupdate="CASCADE",
                                       ondelete="CASCADE"),
                            primary_key=True),
                     Column('group_id', Integer,
                            ForeignKey('tg_group.group_id',
                                       onupdate="CASCADE",
                                       ondelete="CASCADE"),
                            primary_key=True))

class Group(DeclarativeBase):
    __tablename__ = 'tg_group'
    group_id = Column(Integer, autoincrement=True, primary_key=True)
    group_name = Column(Unicode(16), unique=True, nullable=False)
    users = relation('User', secondary=user_group_table, backref='groups')
class User(DeclarativeBase):
    __tablename__ = 'tg_user'
    user_id = Column(Integer, autoincrement=True, primary_key=True)
    user_name = Column(Unicode(16), unique=True, nullable=False)
    email_address = Column(Unicode(255), unique=True, nullable=False)
    display_name = Column(Unicode(255))

class Permission(DeclarativeBase):
    __tablename__ = 'tg_permission'
    permission_id = Column(Integer, autoincrement=True, primary_key=True)
    permission_name = Column(Unicode(63), unique=True, nullable=False)
    description = Column(Unicode(255))
    groups = relation(Group, secondary=group_permission_table,
                  backref='permissions')