SQLAlchemy从数据库内读取出树形结构菜单

首先Sqlalchemy是不可以直接读取出有条件限制的树形结构菜单，只能自己实现一个

分析

首先分析一下树状结构，树状结构由多个互相关联的节点组成，所以需要一个节点类，节点类里面字段必须与菜单表里面的必要字段一样

class TreeMenuNode:
    """树形菜单节点"""

    def __init__(self, pk, title, code, icon_class, url, order, child, parent):
        self.pk = pk
        self.title = title
        self.code = code
        self.icon_class = icon_class
        self.url = url
        self.order = order
        self.child = child
        self.parent = parent

    def count(self):
        if not self.child:
            return 0
        return len(self.child)


class HandleTreeMenu:
    """处理树形侧边栏菜单"""

    def __init__(self, menus):
        self.menus = menus
        self.tree_menu = []

    def __iter__(self):
        if self.tree_menu:
            return iter(self.tree_menu)
        return iter([])

    @staticmethod
    def get_complete_menu_structure(_menu_list):
        """得到完整的菜单结构"""
        menu_list = copy.copy(_menu_list)
        for menu in menu_list:
            if menu.parent and menu.parent not in menu_list:
                menu_list.append(menu.parent)
                menu_list.extend(HandleTreeMenu.get_complete_menu_structure(menu_list))
        return menu_list

    def convert_to_tree(self):
        """转换BaseQuery为MenuTree结构"""
        complete_menu = HandleTreeMenu.get_complete_menu_structure(self.menus)
        # 所有顶级菜单
        top_menu = [menu for menu in complete_menu if not menu.parent]
        for menu in top_menu:
            current_menu = TreeMenuNode(menu.pk, menu.title, menu.code, menu.icon_class, menu.url, menu.order, [], None)
            current_menu = self._recursive_menu(menu, current_menu)
            self.tree_menu.append(current_menu)

    def _recursive_menu(self, base_menu, base_tree_menu):
        """由上往下递归菜单得到所有菜单并转换为TreeMenuNode格式"""
        complete_menu = HandleTreeMenu.get_complete_menu_structure(self.menus)
        child_menu = [menu for menu in complete_menu if menu.parent == base_menu]  # 父节点为base_menu的子节点

        for menu in child_menu:
            current_menu = TreeMenuNode(menu.pk, menu.title, menu.code, menu.icon_class, menu.url, menu.order, [], base_tree_menu)
            current_menu = self._recursive_menu(menu, current_menu)
            base_tree_menu.child.append(current_menu)
        return base_tree_menu

# 菜单表
class Menu(BaseModel):
    """侧边栏菜单模型"""
    JK__model_name = '菜单'

    pk = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String, nullable=False, comment='菜单标题')
    code = db.Column(db.String, nullable=False, comment='权限代码')
    icon_class = db.Column(db.String, nullable=True, comment='图标类')
    url = db.Column(db.String, nullable=True, comment='链接地址')
    order = db.Column(db.Integer, default=0, comment='排列顺序')

    child_pk = db.Column(db.Integer, db.ForeignKey('menu.pk'))

    parent = db.relationship(
        'Menu',
        remote_side=[pk],
        backref=backref('child', lazy='dynamic'),
    )

    def __init__(self, title, code, icon_class, url, order):
        self.title = title
        self.code = code
        self.icon_class = icon_class
        self.url = url
        self.order = order


if main == '__main__':
	# 使用方法
	menus = Person.menu.all()  # 这里是直接在数据库里面查询出来的, 用户私有权限表
	tree_menu = HandleTreeMenu(menus)# 创建一个对象
    tree_menu.convert_to_tree() # 执行转换方法
    print(tree_menu.tree_menu) # 得到已经转换的菜单