118 lines
4.6 KiB
Python
118 lines
4.6 KiB
Python
# -*- 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()
|