数据表基本class设计：

    1. #encoding: utf-8
    2. from apps.app import db
    3. from apps.configs.db_config import DB_CONFIG
    4. from datetime import datetime
    5. import time
    6. import copy
    7. from sqlalchemy.ext.declarative import declared_attr
    8. from sqlalchemy_serializer import SerializerMixin
    9. from sqlalchemy import text
    10. from apps.app import logger
    13. def current_timestamp():
    14.     """
    15.     返回毫秒级时间戳
    16.     :return:
    17.     """
    18.     return int(round(time.time() * 1000))
    20. class BaseModel(db.Model, SerializerMixin):
    21.     """
    22.     设为db基类。
    23.     SerializerMixin为数据序列化插件
    24.     """
    25.     # Flask-SQLAlchemy创建table时,如何声明基类（这个类不会创建表,可以被继承）
    26.     # 方法就是把__abstract__这个属性设置为True,这个类为基类，不会被创建为表！
    27.     __abstract__ = True
    29.     # 添加配置设置编码
    30.     # __table_args__ = {
    31.     #     'mysql_charset': DB_CONFIG['mysql']['charset']
    32.     # }
    34.     @declared_attr
    35.     def __tablename__(cls):
    36.         # 将表类名自动小写，_分割，去掉末尾的"Model"字样
    37.         if "_" in cls.__name__:
    38.             name =cls.__name__
    39.         else:
    40.             name = ''.join([('_' + ch.lower()) if ch.isupper() else ch
    41.                     for ch in cls.__name__]).strip('_')
    42.             if name[-6:] == "_model":
    43.                 name = name[:-6]
    45.         _table_prefix = DB_CONFIG['mysql']['prefix']
    46.         return _table_prefix + name if _table_prefix else name
    48.     # 每个表都自动带上创建时间、更新时间
    49.     # server_default表示数据库字段默认值，会写入到数据库字段定义中。且值只接受字符串，不接收整型、布尔型等，需要用text()函数格式化。
    50.     create_time = db.Column(db.BigInteger, default=current_timestamp, server_default=text('0'), comment='创建时间')   # 存储毫秒级时间戳
    51.     # create_time = db.Column(db.Integer, default=time.time, comment='创建时间')    # 存储秒级时间戳
    52.     create_by = db.Column(db.String(100), default="", server_default='', comment='创建人')
    53.     update_time = db.Column(db.DateTime, default=datetime.now, server_default=text('CURRENT_TIMESTAMP'), onupdate=datetime.now, server_onupdate=text('CURRENT_TIMESTAMP'), comment='更新时间')
    54.     update_by = db.Column(db.String(100), default="", server_default='', comment='更新人')
    57.     def toDict(self):
    58.         """
    59.         返回dict结果
    60.         用法：
    61.             r = model.query.……
    62.             > r.toDict()  # 输出dict结果数据
    63.             > r.toDict().__str__()    # 输出字符串结果数据
    64.         :return: c_dict
    65.         """
    66.         # 使用深拷贝，避免引用对象self.__dict__缺少"_sa_instance_state"属性
    67.         c_dict = copy.deepcopy(self.__dict__)
    68.         for (k, v) in c_dict.items():
    69.             # 将datetime内容格式化为[Y-m-d H:M:S]
    70.             if isinstance(v, datetime):
    71.                 c_dict[k] = v.strftime("%Y-%m-%d %H:%M:%S")
    72.         try:
    73.             if "_sa_instance_state" in c_dict:
    74.                 del c_dict["_sa_instance_state"]
    75.             # if "create_time" in c_dict:
    76.                 # del c_dict["create_time"]
    77.             if "update_time" in c_dict:
    78.                 del c_dict["update_time"]
    79.         except Exception as e:
    80.             logger.error(e)
    82.         return c_dict

    用户相关表结构定义：

    1. # encoding:utf-8
    2. # 用户模型
    4. from apps.app import db
    5. from apps.models.base import BaseModel
    6. from apps.configs.db_config import DB_CONFIG
    7. import shortuuid
    8. from werkzeug.security import generate_password_hash, check_password_hash
    9. from apps.models.enums import GenderEnumModel, DomainEnumModel
    10. from flask_login import UserMixin, current_user, AnonymousUserMixin
    11. from apps.app import logger
    14. class UserPermissionModel(BaseModel):
    15.     """
    16.     权限集表
    18.     角色权限关系：
    19.         UserPermissionModel：存储每个页面/操作权限的权限编码；
    20.         UserRoleModel：角色表；
    21.         role_to_permission：角色的权限通过角色权限关联表进行一对多关联；
    22.         UserModel：用户可多选角色；
    23.         role_to_user：用户与角色通过用户角色关联表进行一对多关联；
    24.     """
    25.     # __tablename__ = DB_CONFIG['mysql']['prefix'] + 'user_permission'
    26.     __table_args__ = {'comment': '菜单权限表'}  # 添加索引和表注释
    28.     # 序列化排除字段
    29.     serialize_rules = ("-roles",)
    31.     id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    32.     name = db.Column(db.String(100), default='', comment="权限名称")
    33.     code = db.Column(db.String(50), nullable=False, comment='权限编码', unique=True)
    34.     parent_id = db.Column(db.Integer, default=0, comment="上级权限ID")
    35.     type = db.Column(db.Integer, comment="权限类型：1 目录，2 菜单，3 按钮，9 接口")
    36.     url = db.Column(db.String(100), default="", comment='请求链接')
    37.     is_frame = db.Column(db.Integer, default=0, comment="是否外链：0 否，1 是")
    38.     explain = db.Column(db.String(255), default='', comment="说明")
    39.     domain = db.Column(db.Enum(DomainEnumModel), default=DomainEnumModel.ADMIN, unique=False, comment='权限所属域')
    41.     sort_num = db.Column(db.Integer, default=10, comment='排序码，数值越小越靠前')
    42.     is_default = db.Column(db.Integer, default=0, comment="是否默认拥有的权限：0 非默认；1 默认权限")
    43.     status = db.Column(db.Integer, default=0, comment='状态：0 正常/显示，-1 禁用/隐藏')
    44.     is_delete = db.Column(db.Integer, default=0, comment='是否删除：0 正常，1 表示已删除')
    46.     # # 上级权限id
    47.     # parent_id = db.Column(db.Integer, db.ForeignKey('%s.id' % (DB_CONFIG['mysql']['prefix'] + 'user_permission')), nullable=True, comment='上级权限id')
    48.     # # 当上级只有一个时，直接关联上级即可。自引用UserPermissionModel.id，需加入remote_side参数
    49.     # parent = db.relationship('UserPermissionModel', remote_side=[id], backref="children")
    51.     def default_permission(self, domain=None):
    52.         """
    53.         查询未删除的默认权限集
    54.         :param domain: 如果传入站点标识，则只查询该站点的默认权限集
    55.         :return:
    56.         """
    57.         # 查询条件
    58.         q = [UserPermissionModel.is_delete==0]
    59.         if domain:
    60.             q.append(UserPermissionModel.domain==domain)
    61.         return UserPermissionModel.query.filter(*q).all()
    63.     def all_permission(self, domain=None):
    64.         """
    65.         查询全部未删除的权限集
    66.         :param domain: 如果传入站点标识，则只查询该站点的全部权限集
    67.         :return:
    68.         """
    69.         # 查询条件
    70.         q = [UserPermissionModel.is_delete == 0]
    71.         if domain:
    72.             q.append(UserPermissionModel.domain == domain)
    73.         return UserPermissionModel.query.filter(*q).all()
    75.     def has_permission(self, code):
    76.         """
    77.         判断权限是否有入库
    78.         :param code <UserPermissionModel.code>:
    79.         :return:
    80.         """
    81.         permission = UserPermissionModel.query.filter(UserPermissionModel.is_delete==0, UserPermissionModel.code==code).first()
    82.         if permission:
    83.             return True
    84.         else:
    85.             return False
    87. # 角色-权限 关联表
    88. role_to_permission = db.Table(
    89.     DB_CONFIG['mysql']['prefix'] + 'user_role_to_permission',
    90.     db.Column('role_id', db.String(100), db.ForeignKey('%s.id' % (DB_CONFIG['mysql']['prefix'] + 'user_role')),
    91.               primary_key=True),
    92.     db.Column('permission_id', db.Integer, db.ForeignKey('%s.id' % UserPermissionModel.__tablename__), primary_key=True)
    93. )
    96. class UserRoleModel(BaseModel):
    97.     # 用户角色表
    98.     # __tablename__ = DB_CONFIG['mysql']['prefix'] + 'user_role'
    99.     __table_args__ = {'comment': '用户角色表'}  # 添加索引和表注释
    101.     serialize_rules = ("-users", "-permissions")
    102.     # serialize_only = ("-users",)
    104.     id = db.Column(db.String(100), primary_key=True, default=shortuuid.uuid, comment='唯一标识')
    105.     code = db.Column(db.String(100), unique=False, comment='角色编码')
    106.     name = db.Column(db.String(50), default='', comment='角色名称')
    107.     desc = db.Column(db.String(100), default='', comment='角色介绍')
    108.     domain = db.Column(db.Enum(DomainEnumModel), default=DomainEnumModel.ADMIN, unique=False, comment='角色所属域')
    109.     sort_num = db.Column(db.Integer, default=10, comment='排序码，数值越小越靠前')
    110.     is_default = db.Column(db.Integer, default=0, comment='默认角色：0 非默认，1 默认')
    111.     status = db.Column(db.Integer, default=0, comment='状态：0 正常，-1 禁用')
    112.     is_delete = db.Column(db.Integer, default=0, comment='是否删除：0 正常，1 表示已删除')
    113.     # 角色权限
    114.     permissions = db.relationship('UserPermissionModel', secondary=role_to_permission, backref='roles')
    116.     def toDict(self):
    117.         # 重载基类toDict方法
    118.         c_dict = BaseModel.toDict(self)
    120.         try:
    121.             if "domain" in c_dict:
    122.                 # 枚举类型处理
    123.                 c_dict["domain_name"] = self.domain.name
    124.                 c_dict["domain_value"] = self.domain.value
    125.                 del c_dict["domain"]
    126.         except Exception as e:
    127.             logger.error(e)
    129.         return c_dict
    131.     @property
    132.     def role_info(self):
    133.         info = {
    134.             # 'id': self.id,
    135.             'name': self.name,
    136.             'code': self.code,
    137.             'domain': self.domain.name,
    138.             'status': self.status
    139.         }
    140.         return info
    143. # 用户-角色 关联表
    144. role_to_user = db.Table(
    145.     DB_CONFIG['mysql']['prefix'] + 'user_role_to_user',
    146.     db.Column('role_id', db.String(100), db.ForeignKey('%s.id' % UserRoleModel.__tablename__), primary_key=True),
    147.     db.Column('user_id', db.String(100), db.ForeignKey('%s.id' % (DB_CONFIG['mysql']['prefix'] + 'user')),
    148.               primary_key=True)
    149. )
    152. class UserModel(BaseModel, UserMixin):
    153.     # 用户信息表
    154.     __tablename__ = DB_CONFIG['mysql']['prefix'] + 'user'
    155.     __table_args__ = {'comment': '用户信息表'}  # 添加索引和表注释
    157.     # 序列化排除字段
    158.     serialize_rules = ("-_password",)
    159.     # serialize_only = ("username", "telephone")
    161.     id = db.Column(db.String(100), primary_key=True, default=shortuuid.uuid, comment='用户标识')
    162.     username = db.Column(db.String(50), nullable=False, comment='用户名')
    163.     telephone = db.Column(db.String(11), unique=False, default="", comment='手机号')
    164.     _password = db.Column(db.String(100), nullable=False, comment='密码')
    165.     email = db.Column(db.String(50), default="", comment='邮箱')
    166.     avatar_url = db.Column(db.String(100), default="", comment='头像地址')
    167.     signature = db.Column(db.String(100), default="", comment='个性签名')
    168.     realname = db.Column(db.String(50), default="", comment='姓名')
    169.     province_id = db.Column(db.Integer, default=0, comment='省份id')
    170.     city_id = db.Column(db.Integer, default=0, comment='城市id')
    171.     region_id = db.Column(db.Integer, default=0, comment='区域id')
    172.     gender = db.Column(db.Enum(GenderEnumModel), default=GenderEnumModel.UNKNOW, comment='性别')
    173.     login_error_num = db.Column(db.Integer, default=0, comment='登录错误次数，每次登录成功清零')
    174.     last_login_time = db.Column(db.DateTime, comment='最后登录时间')
    176.     is_developer = db.Column(db.Integer, default=0, comment="是否开发者：0 否，1 是")
    177.     status = db.Column(db.Integer, default=0, comment='状态：0 正常，-1 禁用')
    178.     active = db.Column(db.Integer, default=1, comment='是否可用：0 不可用，1 表示可用')
    179.     is_delete = db.Column(db.Integer, default=0, comment='是否删除：0 正常，1 表示已删除')
    181.     # 上级用户
    182.     # superior_user_id = db.Column(db.String(100), db.ForeignKey('%s.id' % UserModel.__tablename__), nullable=True, default="", comment='上级用户id')
    184.     # 当上级用户有多个时，使用多对多的关系
    185.     # superior_user = db.relationship('UserModel', secondary=superior_users, backref='users')
    186.     # 当上级用户只有一个时，直接关联上级用户即可。自引用user.id，需加入remote_side参数
    187.     # superior_user = db.relationship('UserModel', remote_side=[id])
    189.     # 用户角色
    190.     roles = db.relationship('UserRoleModel', secondary=role_to_user, backref='users')
    192.     def __init__(self, user_id=None, *args, **kwargs):
    193.         if "password" in kwargs:
    194.             self.password = kwargs.get('password')
    195.             kwargs.pop("password")
    196.         super(UserModel, self).__init__(*args, **kwargs)
    198.     # def toDict(self):
    199.     #     # 重载基类toDict方法
    200.     #     c_dict = BaseModel.toDict(self)
    201.     #
    202.     #     try:
    203.     #         roles = []
    204.     #         for role in self.roles:
    205.     #             roles.append(role.toDict())
    206.     #         c_dict["roles"] = roles
    207.     #
    208.     #         if "_password" in c_dict:
    209.     #             del c_dict["_password"]
    210.     #         if "gender" in c_dict:
    211.     #             # 枚举类型处理
    212.     #             c_dict["gender_name"] = self.gender.name
    213.     #             c_dict["gender_value"] = self.gender.value
    214.     #             del c_dict["gender"]
    215.     #     except Exception as e:
    216.     #         logger.err(e)
    217.     #
    218.     #     return c_dict
    220.     @property
    221.     def jwt_login_time_dict(self):
    222.         """
    223.         将jwt_login_time格式化为dict
    224.         :return:
    225.         """
    226.         jwt_login_times = {}
    227.         for v in self.jwt_login_time:
    228.             jwt_login_times[v.id] = round(v.login_time / 1000000, 6)
    229.         return jwt_login_times
    231.     @property
    232.     def password(self):
    233.         return self._password
    235.     @password.setter
    236.     def password(self, raw_password):
    237.         # password赋值时，自动加密
    238.         self._password = generate_password_hash(raw_password)
    240.     def check_password(self, raw_password):
    241.         result = check_password_hash(self.password, raw_password)
    242.         return result
    244.     @property
    245.     def is_authenticated(self):
    246.         return True
    248.     @property
    249.     def is_active(self):
    250.         if self.status == 0:
    251.             return True
    252.         else:
    253.             return False
    254.         # return self.active
    256.     @property
    257.     def is_anonymous(self):
    258.         return False
    260.     @property
    261.     def permissions(self):
    262.         """
    263.         返回该用户的所有权限
    264.         :return:
    265.         """
    266.         # 开发者直接返回所有权限
    267.         if self.is_developer:
    268.             return UserPermissionModel.all_permission("all")
    269.         # 如果没有关联角色，返回空
    270.         if not self.roles:
    271.             return []
    272.         # 定义权限集合
    273.         all_permissions = []
    274.         # 循环合并多角色的权限，最后去重
    275.         for role in self.roles:
    276.             all_permissions.extend(role.permissions)
    277.         return list(set(all_permissions))
    279.     def can(self, permission):
    280.         """
    281.         判断用户是否有传入权限
    282.         :param permission <UserPermissionModel.code>: 权限编码
    283.         :return:
    284.         """
    285.         result = False
    286.         has_per = UserPermissionModel.has_permission(permission)
    287.         # 如果权限有入库
    288.         if has_per:
    289.             # 查询当前用户所有权限
    290.             all_permissions = self.permissions
    291.             for p in all_permissions:
    292.                 # 传入权限属于该用户
    293.                 if permission == p.code:
    294.                     result = True
    295.         else:
    296.             # 如果权限没有入库，直接返回通过
    297.             result = True
    298.         return result
    300.     @property
    301.     def user_info(self):
    302.         """
    303.         返回用户个人信息
    304.         :return:
    305.         """
    306.         # info = {
    307.         #     "name": self.realname,
    308.         #     "status": self.status,
    309.         #     "is_delete": self.is_delete,
    310.         #     "email": self.email,
    311.         #     "telephone": self.telephone,
    312.         #     "avatar_url": self.avatar_url,
    313.         #     "id": self.id
    314.         # }
    315.         # roles = []
    316.         # for role in self.roles:
    317.         #     roles.append(role.role_info)
    318.         # info["roles"] = roles
    319.         # return info
    320.         return self.to_dict(only=('id', 'username','realname', 'status', 'is_delete', 'email', 'telephone', 'avatar_url', 'roles'))
    322.     def __repr__(self):
    323.         return '<User %r>' % (self.username)
    326. # # 上级用户关联表，如有多个用户上级，则需要用该表
    327. # superior_users = db.Table(
    328. #     'bbx_superior_users',
    329. #     db.Column('superior_user_id', db.String(100), db.ForeignKey('%s.id' % UserModel.__tablename__), primary_key=True),
    330. #     db.Column('user_id', db.String(100), db.ForeignKey('%s.id' % UserModel.__tablename__), primary_key=True)
    331. # )
    334. class AnonymousUser(AnonymousUserMixin):
    335.     # 匿名用户
    336.     def __init__(self, **kwargs):
    337.         super(AnonymousUser, self).__init__(**kwargs)
    339.     @property
    340.     def id(self):
    341.         return None
    343.     @property
    344.     def is_active(self):
    345.         return False
    347.     @property
    348.     def is_authenticated(self):
    349.         return False
    351.     @property
    352.     def is_anonymous(self):
    353.         return True
    355.     @property
    356.     def user_info(self):
    357.         return None
    359.     @property
    360.     def permissions(self):
    361.         return None
    363.     def can(self, permissions):
    364.         return False
    366.     # def get_id(self):
    367.     #     return None
    370. class UserJwtLoginTimeModel(BaseModel):
    371.     # 用户JWT登录时间表
    372.     # __tablename__ = DB_CONFIG['mysql']['prefix'] + 'user_jwt_login_time'
    373.     __table_args__ = {'comment': '用户JWT登录时间表'}  # 添加索引和表注释
    375.     # 序列化排除字段
    376.     serialize_rules = ("-user",)
    378.     id = db.Column(db.String(100), primary_key=True, comment='cid')
    379.     user_id = db.Column(db.String(100), db.ForeignKey('%s.id' % UserModel.__tablename__), comment='用户ID')
    380.     login_time = db.Column(db.BigInteger, comment='jwt登录时间')
    382.     user = db.relationship('UserModel', backref='jwt_login_time')
    385. class UserLoginLogModel(BaseModel):
    386.     # 用户登录日志表
    387.     # __tablename__ = DB_CONFIG['mysql']['prefix'] + 'user_login_log'
    388.     __table_args__ = {'comment': '用户登录日志表'}  # 添加索引和表注释
    390.     # 序列化排除字段
    391.     serialize_rules = ("-user",) ,
    393.     id = db.Column(db.Integer, primary_key=True, autoincrement=True, comment='唯一标识')
    394.     user_id = db.Column(db.String(100), db.ForeignKey('%s.id' % UserModel.__tablename__), comment='用户ID')
    395.     pass_error = db.Column(db.Integer, default=0, comment='登录结果：0 成功，1 失败')
    396.     login_account = db.Column(db.String(100), default='', comment='登录账号')
    397.     login_msg = db.Column(db.String(100), default='', comment='登录信息')
    398.     ip = db.Column(db.String(20), default='', comment='登录IP')
    399.     client = db.Column(db.String(100), default='', comment='客户端')
    401.     user = db.relationship('UserModel', backref='login_log')