Files
yanxuan/脚本/fix_school_id_map.py

118 lines
4.6 KiB
Python
Raw Permalink Blame History

This file contains invisible Unicode characters
This file contains invisible Unicode characters that are indistinguishable to humans but may be processed differently by a computer. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
# -*- coding: utf-8 -*-
"""跨源院校 ID 映射:解决"同一个学校在不同表里 school_id 不同"的问题。
实证:沈阳药科大学在 zgc_major_lines 是 117,在 ggyx_dim_school 是 198。
校名可对上,ID 对不上 → 所有用 school_id 做跨表 JOIN 的地方都会漏。
做法:
1. 建 dim_school_id_map(src_system, src_id, src_name, ggyx_school_id, ggyx_name, match_type)
2. 对存在孤儿的表,按校名回填 ggyx_school_id
3. 统计映射率
"""
import re
import sqlite3
DB = r'D:\择校\数据库\zexiao_2027.db'
con = sqlite3.connect(DB, timeout=600)
def norm(s):
s = (s or '').replace('(', '(').replace(')', ')').replace(' ', '')
return re.sub(r'\s+', '', s)
# ggyx 基准
GG = {}
for sid, nm in con.execute('SELECT school_id, sch_name FROM ggyx_dim_school'):
GG[norm(nm)] = int(sid)
print('ggyx 校名基准: %d' % len(GG))
# 需要映射的表:(表名, 校名列, school_id 列)
TARGETS = [
('zgc_major_lines', 'school_name', 'school_id'),
('core_hist_lines', None, 'school_id'),
('core_contacts', None, 'school_id'),
('core_notices', None, 'school_id'),
('core_policies', None, 'school_id'),
('notices_all', None, 'school_id'),
('src_contacts', None, 'school_id'),
('src_line_from_name', None, 'school_id'),
('src_notice_bodies', None, 'school_id'),
('src_notice_policies', None, 'school_id'),
('tuition_notice_extract', None, 'school_id'),
]
con.execute('DROP TABLE IF EXISTS dim_school_id_map')
con.execute('''CREATE TABLE dim_school_id_map (
src_system TEXT, src_id TEXT, src_name TEXT,
ggyx_school_id INTEGER, ggyx_name TEXT, match_type TEXT,
PRIMARY KEY (src_system, src_id))''')
total_map = 0
for t, namecol, idcol in TARGETS:
cols = [r[1] for r in con.execute('PRAGMA table_info("%s")' % t)]
if not cols:
print(' %-26s 表不存在,跳过' % t)
continue
# 找校名列
if namecol is None:
namecol = next((c for c in ('sch_name', 'school_name', '院校', 'school', 'dwmc') if c in cols), None)
if not namecol:
print(' %-26s 无校名列,跳过' % t)
continue
rows = con.execute('SELECT DISTINCT CAST(%s AS TEXT), "%s" FROM "%s"' % (idcol, namecol, t)).fetchall()
mapped = 0
for sid, nm in rows:
if sid is None or nm is None:
continue
gid = GG.get(norm(nm))
if gid is None:
continue
con.execute('''INSERT OR REPLACE INTO dim_school_id_map
(src_system, src_id, src_name, ggyx_school_id, ggyx_name, match_type)
VALUES (?,?,?,?,?,?)''',
(t, str(sid), nm, gid, nm, 'by_name'))
mapped += 1
total_map += mapped
print(' %-26s 校 %4d → 映射 %4d (%.0f%%)' % (t, len(rows), mapped, 100.0 * mapped / max(len(rows), 1)))
con.commit()
print()
print('dim_school_id_map 共 %d 条' % con.execute('SELECT COUNT(*) FROM dim_school_id_map').fetchone()[0])
# 给存在孤儿的表加派生列并回填
print()
print('=== 回填 ggyx_school_id 派生列 ===')
for t, namecol, idcol in TARGETS:
cols = [r[1] for r in con.execute('PRAGMA table_info("%s")' % t)]
if not cols or 'school_id_ggyx' not in cols and 'school_id_ggyx' in []:
pass
if not cols:
continue
if 'school_id_ggyx' not in cols:
con.execute('ALTER TABLE "%s" ADD COLUMN school_id_ggyx INTEGER' % t)
n = con.execute('''UPDATE "%s" SET school_id_ggyx = (
SELECT ggyx_school_id FROM dim_school_id_map m
WHERE m.src_system=? AND m.src_id=CAST("%s".%s AS TEXT))''' % (t, t, idcol), (t,)).rowcount
# 已是 ggyx 体系的表直接沿用
n2 = con.execute('''UPDATE "%s" SET school_id_ggyx=CAST(%s AS INTEGER)
WHERE school_id_ggyx IS NULL
AND CAST(%s AS INTEGER) IN (SELECT school_id FROM ggyx_dim_school)''' % (t, idcol, idcol)).rowcount
con.commit()
fill = con.execute('SELECT COUNT(*) FROM "%s" WHERE school_id_ggyx IS NOT NULL' % t).fetchone()[0]
tot = con.execute('SELECT COUNT(*) FROM "%s"' % t).fetchone()[0]
print(' %-26s 按名映射 %6d + 原生ID %6d → 覆盖 %d/%d (%.0f%%)'
% (t, n, n2, fill, tot, 100.0 * fill / max(tot, 1)))
print()
print('=== 验证:孤儿是否消除(用 school_id_ggyx)===')
for t, _, _ in TARGETS:
cols = [r[1] for r in con.execute('PRAGMA table_info("%s")' % t)]
if 'school_id_ggyx' not in cols:
continue
orphan = con.execute('''SELECT COUNT(DISTINCT school_id_ggyx) FROM "%s"
WHERE school_id_ggyx IS NOT NULL AND school_id_ggyx NOT IN (SELECT school_id FROM ggyx_dim_school)''' % t).fetchone()[0]
print(' %-26s 剩余孤儿 %d' % (t, orphan))
con.close()