ylrz_saas_his_scrm/sql/simulate_tenant_menu_api.py

# -*- coding: utf-8 -*-
"""Simulate /tenant/tenant/tenantMenu/list API tree output."""
import pymysql

M = dict(
    host='cq-cdb-8fjmemkb.sql.tencentcdb.com',
    port=27220,
    user='root',
    password='Ylrz_1q2w3e4r5t6y',
    database='ylrz_saas',
    charset='utf8mb4',
)

def build_tree(rows, parent_id=0):
    tree = []
    for r in rows:
        if r['parent_id'] == parent_id:
            node = dict(r)
            node['children'] = build_tree(rows, r['menu_id'])
            tree.append(node)
    tree.sort(key=lambda x: (x.get('order_num') or 0, x['menu_id']))
    return tree

def print_tree(nodes, depth=0, max_depth=2):
    for n in nodes:
        vis = 'show' if n['visible'] == '0' else 'hide'
        print('  ' * depth + '- [%s] %s (id=%s, path=%s, order=%s)' % (
            n['menu_type'], n['menu_name'], n['menu_id'], n['path'], n['order_num']))
        if depth < max_depth and n.get('children'):
            print_tree(n['children'], depth + 1, max_depth)

c = pymysql.connect(**M)
cur = c.cursor(pymysql.cursors.DictCursor)
cur.execute(
    'SELECT menu_id, menu_name, parent_id, order_num, path, component, menu_type, visible, status '
    'FROM tenant_sys_menu ORDER BY parent_id, order_num, menu_id'
)
rows = cur.fetchall()
tree = build_tree(rows, 0)

print('=== TOP LEVEL (parent_id=0) count=%s ===' % len(tree))
print_tree(tree, 0, 1)

print('\n=== VISIBLE TOP LEVEL only ===')
print_tree([n for n in tree if n['visible'] == '0'], 0, 2)

print('\n=== system subtree (32372) ===')
sys_node = next((n for n in tree if n['menu_id'] == 32372), None)
if sys_node:
    print_tree([sys_node], 0, 2)

print('\n=== qw subtree (32361) ===')
qw_node = next((n for n in tree if n['menu_id'] == 32361), None)
if qw_node:
    print_tree([qw_node], 0, 2)

cur.close()
c.close()