ylrz_saas_his_scrm/sql/_check_orphan.py

import pymysql
from collections import defaultdict

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(pymysql.cursors.DictCursor)
cur.execute('SELECT menu_id, parent_id, menu_type FROM tenant_sys_menu')
rows=cur.fetchall()
ids={r['menu_id'] for r in rows}
orph=[r for r in rows if r['parent_id'] not in ids and r['parent_id']!=0]
print('orphans', len(orph))
by=defaultdict(int)
for r in rows: by[r['menu_type']]+=1
print('types', dict(by))

# reachable from roots
roots=[r for r in rows if r['parent_id']==0]
ch=defaultdict(list)
for r in rows: ch[r['parent_id']].append(r)
seen=set()
stack=[r['menu_id'] for r in roots]
while stack:
    mid=stack.pop()
    if mid in seen: continue
    seen.add(mid)
    for child in ch.get(mid,[]):
        stack.append(child['menu_id'])
print('reachable', len(seen), 'total', len(rows), 'missing', len(rows)-len(seen))
missing=[r for r in rows if r['menu_id'] not in seen]
by2=defaultdict(int)
for r in missing: by2[r['menu_type']]+=1
print('missing by type', dict(by2))
c.close()