ylrz_saas_his_scrm/sql/compare_missing_ids.py

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

conn = pymysql.connect(
    host='cq-cdb-8fjmemkb.sql.tencentcdb.com', port=27220,
    user='root', password='Ylrz_1q2w3e4r5t6y',
    database='ylrz_saas', charset='utf8mb4',
)
cur = conn.cursor()

cur.execute('SELECT menu_id FROM tenant_sys_menu_bak')
bak_ids = set(r[0] for r in cur.fetchall())
cur.execute('SELECT menu_id FROM tenant_sys_menu')
cur_ids = set(r[0] for r in cur.fetchall())

missing = sorted(bak_ids - cur_ids)
extra = sorted(cur_ids - bak_ids)
print('missing_count', len(missing))
print('extra_count', len(extra))
print('missing_first20', missing[:20])
print('missing_last20', missing[-20:])

# check if missing are contiguous ranges
if missing:
    ranges = []
    start = prev = missing[0]
    for x in missing[1:]:
        if x == prev + 1:
            prev = x
        else:
            ranges.append((start, prev))
            start = prev = x
    ranges.append((start, prev))
    print('missing_ranges_sample', ranges[:15], '... total_ranges', len(ranges))

cur.close()
conn.close()