ylrz_saas_his_scrm/sql/inspect_tenant_menu.py

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

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

conn = pymysql.connect(**DB)
cur = conn.cursor()
cur.execute('SELECT id, tenant_code, tenant_name, db_name, status FROM tenant_info ORDER BY id')
print('TENANTS:')
for r in cur.fetchall():
    print(r)

checks = [
    ('template_count', 'SELECT COUNT(*) FROM tenant_sys_menu'),
    ('visible_roots', "SELECT COUNT(*) FROM tenant_sys_menu WHERE parent_id=0 AND visible='0' AND menu_type='M'"),
    ('bad_admin', "SELECT COUNT(*) FROM tenant_sys_menu WHERE component LIKE 'admin/%' AND visible='0'"),
    ('empty_M_dirs', "SELECT COUNT(*) FROM tenant_sys_menu m WHERE menu_type='M' AND visible='0' AND NOT EXISTS (SELECT 1 FROM tenant_sys_menu c WHERE c.parent_id=m.menu_id AND c.menu_type<>'F')"),
    ('path_dup_qw', """SELECT COUNT(*) FROM (SELECT parent_id,path FROM tenant_sys_menu WHERE parent_id IN (35001,35002,35003,35004,35005,35006,35007) AND menu_type<>'F' AND visible='0' GROUP BY parent_id,path HAVING COUNT(*)>1) t"""),
]
print('STATS:')
for name, sql in checks:
    cur.execute(sql)
    print(' ', name, cur.fetchone()[0])

cur.execute("SELECT menu_id, menu_name, parent_id, path, visible FROM tenant_sys_menu WHERE parent_id IN (35100,35101,35102,35105,35106) AND visible='0' ORDER BY parent_id, order_num LIMIT 30")
print('SYSTEM_SAMPLE:', cur.fetchall())

cur.close()
conn.close()