SQLAlchemy的许多到许多用户，组，角色

Question

我试图与用户组，角色表结构，其中一个用户可以属于多个组，对各组中的多个角色集成。

，我发现这是一个类似的问题，但它不允许多个角色：Many-to-many declarative SQLAlchemy definition for users, groups, and roles

我有以下表结构，并希望能够访问在下面这种方式的作用：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')