57 lines
2.0 KiB
Python
57 lines
2.0 KiB
Python
# -*- coding: utf-8 -*-
|
|
"""对比 ggyx.db 与 zexiao_2027.db 中 ggyx_* 镜像表的行数,找未入库的量。只读。"""
|
|
import sqlite3, sys, io
|
|
|
|
sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding="utf-8")
|
|
|
|
SRC = r"D:\择校\数据库\ggyx.db"
|
|
DST = r"D:\择校\数据库\zexiao_2027.db"
|
|
|
|
def row_counts(path, tables_sql):
|
|
con = sqlite3.connect(f"file:{path}?mode=ro", uri=True)
|
|
con.execute("PRAGMA busy_timeout=3000")
|
|
names = [r[0] for r in con.execute(tables_sql).fetchall()]
|
|
out = {}
|
|
for n in names:
|
|
try:
|
|
out[n] = con.execute(f'SELECT COUNT(*) FROM "{n}"').fetchone()[0]
|
|
except Exception as e:
|
|
out[n] = f"ERR {e}"
|
|
con.close()
|
|
return out
|
|
|
|
src = row_counts(SRC, "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'")
|
|
dst = row_counts(DST, "SELECT name FROM sqlite_master WHERE type='table' AND name LIKE 'ggyx\\_%' ESCAPE '\\'")
|
|
|
|
# 源表名 -> 镜像表名: ggyx_<name>
|
|
print(f"{'table':28s} {'ggyx.db':>12s} {'zexiao ggyx_*':>14s} {'delta':>12s}")
|
|
total_src = total_dst = 0
|
|
missing = []
|
|
for n, c in sorted(src.items()):
|
|
mirror = f"ggyx_{n}"
|
|
d = dst.get(mirror, "MISSING")
|
|
if isinstance(c, int) and isinstance(d, int):
|
|
delta = d - c
|
|
total_src += c; total_dst += d
|
|
flag = "" if delta == 0 else " <-- DELTA"
|
|
if delta != 0:
|
|
missing.append((n, c, d, delta))
|
|
print(f"{n:28s} {c:>12,d} {d:>14,d} {delta:>+12,d}{flag}")
|
|
else:
|
|
print(f"{n:28s} {str(c):>12s} {str(d):>14s}")
|
|
missing.append((n, c, d, None))
|
|
|
|
print()
|
|
print(f"TOTAL src={total_src:,} dst={total_dst:,} delta={total_dst-total_src:+,}")
|
|
print()
|
|
print("=== 未入库(有差异)的表 ===")
|
|
for n, c, d, delta in missing:
|
|
print(f" {n}: src={c} dst={d} delta={delta}")
|
|
|
|
# 主库侧多出来的 ggyx_* 表(源库里没有的)
|
|
extra = [m for m in dst if m[5:] not in src]
|
|
print()
|
|
print("=== 主库侧多出的 ggyx_* 表 ===")
|
|
for m in sorted(extra):
|
|
print(f" {m}: {dst[m]:,}")
|