ylrz_saas_his_scrm/sql/check_other_children_visible.py

# -*- coding: utf-8 -*-
import pymysql

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

c = pymysql.connect(**M)
cur = c.cursor()
cur.execute('SELECT COUNT(*) FROM tenant_sys_menu WHERE parent_id=35300')
print('direct children', cur.fetchone()[0])
cur.execute(
    "SELECT visible, COUNT(*) FROM tenant_sys_menu WHERE parent_id=35300 GROUP BY visible"
)
print('direct by visible', cur.fetchall())
cur.execute(
    "SELECT menu_id, menu_name, visible, menu_type FROM tenant_sys_menu "
    "WHERE parent_id=35300 ORDER BY order_num LIMIT 8"
)
print('sample direct:')
for r in cur.fetchall():
    print(' ', r)

# count all descendants visible status
cur.execute('SELECT menu_id, parent_id FROM tenant_sys_menu')
rows = cur.fetchall()
children_map = {}
for mid, pid in rows:
    children_map.setdefault(pid, []).append(mid)
menu_vis = {r[0]: r[1] for r in cur.execute('SELECT menu_id, visible FROM tenant_sys_menu') or []}
cur.execute('SELECT menu_id, visible FROM tenant_sys_menu')
menu_vis = dict(cur.fetchall())

def descendants(root):
    out = []
    stack = list(children_map.get(root, []))
    while stack:
        mid = stack.pop()
        out.append(mid)
        stack.extend(children_map.get(mid, []))
    return out

desc = descendants(35300)
vis0 = sum(1 for i in desc if menu_vis.get(i) == '0')
vis1 = sum(1 for i in desc if menu_vis.get(i) == '1')
print('total descendants', len(desc), 'visible=0', vis0, 'visible=1', vis1)
cur.close()
c.close()