以下是某系统用户管理数据库设计
用户角色的多对多关系实现
# 多对多 SysUser -> Role sys_user_role = Table('sys_user_role', Base.metadata, Column('sys_user_id', ForeignKey('sys_user.id', ondelete='CASCADE'), primary_key=True), Column('role_id', ForeignKey('role.id', ondelete='CASCADE'), primary_key=True)) # 系统用户 class SysUser(Base): __tablename__ = 'sys_user' # ID id = Column(UUID, primary_key=True, default=lambda: str(uuid.uuid4()), unique=False) # 用户名 username = Column(String(80), unique=False) # 姓名 name = Column(String(80), unique=False) # 密码 password = Column(String(80), unique=False) # 登录 token token = Column(String(80), unique=True) # 登录 ip ip = Column(String(80), unique=False) # 登录 时间 login_time = Column(DateTime(), unique=False) # 尝试 登录时间 try_time = Column(DateTime(), unique=False) # 尝试 登录次数 try_count = Column(Integer, unique=False) # 多对多 SysUser -> Role # role_list = relationship('Role', secondary=sys_user_role, back_populates='sys_user_list', cascade='delete, delete-orphan', passive_deletes=True) def __init__(self, username, name): self.username = username self.name = name # 角色 class Role(Base): __tablename__ = 'role' # ID id = Column(UUID, primary_key=True, default=lambda: str(uuid.uuid4()), unique=False) # 名称 name = Column(String(80), unique=False) # 多对多 Role -> Authority # authority_list = relationship('Authority', secondary=role_authority, back_populates='role_list', cascade='delete, delete-orphan', passive_deletes=True) # 多对多 SysUser -> Role sys_user_list = relationship('SysUser', secondary=sys_user_role, backref=backref("role_list")) def __init__(self, name): self.name = name