模型创建

flask 一般是使用 flask-sqlalchemy 作为关系模型对象，编写代码，然后生成数据表。可以通过代码实现对数据表的修改，十分方便。

在切换数据库的时候，也只需要修改一下 URI 地址，就可以在 MySQL 与 sqlite 之间无缝切换。在我们的课程里面，选择 MySQL 作为数据库，如果只是学习，SQLite 其实也够用了。

使用之间请确保对 flask-sqlalchemy 有一定了解，如果没有可以看我之前的文档关系对象模型。

创建数据模型

定义一个模型基类

pear_admin/orms/_base.py

python

from pear_admin.extensions import db


class BaseORM(db.Model):
    __abstract__ = True

    def save(self):
        db.session.add(self)
        db.session.commit()

    def delete(self):
        db.session.delete(self)
        db.session.commit()

封装部门类

department.py

python

import sqlalchemy as sa
from sqlalchemy.orm import relationship
from ._base import BaseORM


class DepartmentORM(BaseORM):
    __tablename__ = "ums_department"
    id = sa.Column(sa.Integer, primary_key=True, comment="部门ID")
    name = sa.Column(sa.String(50), comment="部门名称")
    leader = sa.Column(sa.String(50), comment="负责人")
    enable = sa.Column(sa.Boolean, comment="状态(1开启,0关闭)", default=True)

    pid = sa.Column(sa.Integer, sa.ForeignKey("ums_department.id"), default=1)

    def json(self):
        return {
            "id": self.id,
            "pid": self.pid,
            "name": self.name,
            "leader": self.leader,
            "enable": self.enable,
        }

封装 rights 权限类

rights.py

python

import sqlalchemy as sa
from sqlalchemy.orm import relationship

from ._base import BaseORM


class RightsORM(BaseORM):
    __tablename__ = "ums_rights"

    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String(20), nullable=False, comment="权限名称")
    code = sa.Column(sa.String(30), comment="权限标识")
    type = sa.Column(sa.String(30), comment="权限类型")
    url = sa.Column(sa.String(30), comment="路径地址")

    icon_sign = sa.Column(sa.String(128), comment="图标")
    status = sa.Column(sa.Boolean, default=True, comment="是否开启")
    sort = sa.Column(sa.Integer, default=0)
    open_type = sa.Column(sa.String(128), comment="打开方式")
    pid = sa.Column(
        sa.Integer,
        sa.ForeignKey("ums_rights.id"),
        default=0,
        comment="父类编号",
    )

    def json(self):
        return {
            "id": self.id,
            "name": self.name,
            "code": self.code,
            "type": self.type,
            "url": self.url,
            "icon_sign": self.icon_sign,
            "status": self.status,
            "sort": self.sort,
            "open_type": self.open_type,
            "pid": self.pid,
        }

    def menu_json(self):
        type_map_dict = {"menu": 0, "path": 1}

        return {
            "id": self.id,
            "pid": self.pid,
            "title": self.name,
            "type": type_map_dict[self.type],
            "href": self.url,
            "icon": self.icon_sign,
            "sort": self.sort,
            "openType": self.open_type,
        }

封装角色类

role.py

python

import sqlalchemy as sa
from sqlalchemy.orm import relationship, backref
from ._base import BaseORM


class RoleORM(BaseORM):
    __tablename__ = "ums_role"

    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String(20), nullable=False, comment="角色名称")
    code = sa.Column(sa.String(20), nullable=False, comment="角色标识符")
    desc = sa.Column(sa.Text)

    rights_ids = sa.Column(
        sa.String(512),
        comment="权限ids,1,2,5。冗余字段，用户缓存用户权限",
    )

    def json(self):
        return {
            "id": self.id,
            "name": self.name,
            "code": self.code,
            "desc": self.desc,
            "rights_ids": self.rights_ids,
        }

封装用户类

user.py

python

from datetime import datetime
import sqlalchemy as sa
from sqlalchemy.orm import relationship, backref

from werkzeug.security import check_password_hash, generate_password_hash

from ._base import BaseORM


class UserORM(BaseORM):
    __tablename__ = "ums_user"

    id = sa.Column(sa.Integer, primary_key=True, comment="自增id")
    nickname = sa.Column(sa.String(128), nullable=False, comment="昵称")
    username = sa.Column(sa.String(128), nullable=False, comment="登录名")
    password_hash = sa.Column(sa.String(102), nullable=False, comment="登录密码")
    mobile = sa.Column(sa.String(32), nullable=False, comment="手机")
    email = sa.Column(sa.String(64), nullable=False, comment="邮箱")
    avatar = sa.Column(sa.Text, comment="头像地址")
    create_at = sa.Column(
        sa.DateTime,
        nullable=False,
        comment="创建时间",
        default=datetime.now,
    )
    department_id = sa.Column(
        sa.Integer, sa.ForeignKey("ums_department.id"), default=1, comment="部门id"
    )

    def json(self):
        return {
            "id": self.id,
            "username": self.username,
            "nickname": self.nickname,
            "mobile": self.mobile,
            "email": self.email,
            "create_at": self.create_at.strftime("%Y-%m-%d %H:%M:%S"),
        }

    @property
    def password(self):
        return self.password_hash

    @password.setter
    def password(self, password):
        self.password_hash = generate_password_hash(password)

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

关系表创建

pear_admin/orms/__init__.py

python

from pear_admin.extensions import db
import sqlalchemy as sa

from .department import DepartmentORM
from .rights import RightsORM
from .role import RoleORM
from .user import UserORM

user_role = sa.Table(
    "ums_user_role",  # 用户-角色中间表名称
    db.metadata,
    sa.Column("id", sa.Integer, primary_key=True, autoincrement=True, comment="标识"),
    sa.Column("user_id", sa.Integer, sa.ForeignKey("ums_user.id"), comment="用户编号"),
    sa.Column("role_id", sa.Integer, sa.ForeignKey("ums_role.id"), comment="角色编号"),
)

role_rights = sa.Table(
    "ums_role_rights",  # 用户-权限中间表名称
    db.metadata,
    sa.Column("id", sa.Integer, primary_key=True, autoincrement=True, comment="标识"),
    sa.Column("rights_id", sa.Integer, sa.ForeignKey("ums_rights.id"), comment="用户编号"),
    sa.Column("role_id", sa.Integer, sa.ForeignKey("ums_role.id"), comment="角色编号"),
)

添加关系

也就是 relationship

部门关系

department.py

python

import sqlalchemy as sa
from sqlalchemy.orm import relationship
from ._base import BaseORM


class DepartmentORM(BaseORM):
    __tablename__ = "ums_department"
    id = sa.Column(sa.Integer, primary_key=True, comment="部门ID")
    name = sa.Column(sa.String(50), comment="部门名称")
    leader = sa.Column(sa.String(50), comment="负责人")
    enable = sa.Column(sa.Boolean, comment="状态(1开启,0关闭)", default=True)

    users = relationship("UserORM", backref="department")

    pid = sa.Column(sa.Integer, sa.ForeignKey("ums_department.id"), default=1)

    parent = relationship(  # //[!code ++]
        "DepartmentORM", back_populates="children", remote_side=[id]  # //[!code ++]
    )  # 自关联 //[!code ++]
    children = relationship("DepartmentORM", back_populates="parent")  # //[!code ++]

权限关系

rights.py

python

import sqlalchemy as sa
from sqlalchemy.orm import relationship

from ._base import BaseORM


class RightsORM(BaseORM):
    __tablename__ = "ums_rights"

    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String(20), nullable=False, comment="权限名称")
    code = sa.Column(sa.String(30), comment="权限标识")
    type = sa.Column(sa.String(30), comment="权限类型")
    url = sa.Column(sa.String(30), comment="路径地址")

    icon_sign = sa.Column(sa.String(128), comment="图标")
    status = sa.Column(sa.Boolean, default=True, comment="是否开启")
    sort = sa.Column(sa.Integer, default=0)
    open_type = sa.Column(sa.String(128), comment="打开方式")
    pid = sa.Column(
        sa.Integer,
        sa.ForeignKey("ums_rights.id"),
        default=0,
        comment="父类编号",
    )

    parent = relationship(  # //[!code ++]
        "RightsORM", back_populates="children", remote_side=[id]  # //[!code ++]
    )  # 自关联  //[!code ++]
    children = relationship("RightsORM", back_populates="parent")  # //[!code ++]

角色关系

role.py

python

import sqlalchemy as sa
from sqlalchemy.orm import relationship, backref
from ._base import BaseORM


class RoleORM(BaseORM):
    __tablename__ = "ums_role"

    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String(20), nullable=False, comment="角色名称")
    code = sa.Column(sa.String(20), nullable=False, comment="角色标识符")
    desc = sa.Column(sa.Text)

    rights_ids = sa.Column(
        sa.String(512),
        comment="权限ids,1,2,5。冗余字段，用户缓存用户权限",
    )

    rights_list = relationship(  # //[!code ++]
        "RightsORM", secondary="ums_role_rights", backref=backref("role")  # //[!code ++]
    )  # //[!code ++]

用户关系

user.py

python

from datetime import datetime
import sqlalchemy as sa
from sqlalchemy.orm import relationship, backref

from ._base import BaseORM


class UserORM(BaseORM):
    __tablename__ = "ums_user"

    id = sa.Column(sa.Integer, primary_key=True, comment="自增id")
    nickname = sa.Column(sa.String(128), nullable=False, comment="昵称")
    username = sa.Column(sa.String(128), nullable=False, comment="登录名")
    password_hash = sa.Column(sa.String(102), nullable=False, comment="登录密码")
    mobile = sa.Column(sa.String(32), nullable=False, comment="手机")
    email = sa.Column(sa.String(64), nullable=False, comment="邮箱")
    avatar = sa.Column(sa.Text, comment="头像地址")
    create_at = sa.Column(
        sa.DateTime,
        nullable=False,
        comment="创建时间",
        default=datetime.now,
    )
    department_id = sa.Column(
        sa.Integer, sa.ForeignKey("ums_department.id"), default=1, comment="部门id"
    )

    role_list = relationship(  # //[!code ++]
        "RoleORM",  # //[!code ++]
        secondary="ums_user_role",  # //[!code ++]
        backref=backref("user"),  # //[!code ++]
        lazy="dynamic",  # //[!code ++]
    )  # //[!code ++]

模型创建 ​

创建数据模型 ​

封装部门类 ​

封装 rights 权限类 ​

封装角色类 ​

封装用户类 ​

关系表创建 ​

添加关系 ​

部门关系 ​

权限关系 ​

角色关系 ​

用户关系 ​

模型创建

创建数据模型

封装部门类

封装 rights 权限类

封装角色类

封装用户类

关系表创建

添加关系

部门关系

权限关系

角色关系

用户关系