103 lines
4.1 KiB
Python
103 lines
4.1 KiB
Python
# -*- coding: utf-8 -*-
|
||
"""补齐专业维度表:把各表里出现但 dim_major 缺失的 major_id 补进去。
|
||
|
||
来源优先级:ggyx_major_hot_rank(有 maj_code)> src_major_catalog(有专业代码)> ggyx_ssd_master(仅名称)
|
||
背景:2024-2025 新增专业(低空技术与工程、文明学、金融科技等)未同步进 dim_major,
|
||
导致 5 张表的 major_id 成为孤儿,跨表匹配失败。
|
||
"""
|
||
import sqlite3
|
||
|
||
DB = r'D:\择校\数据库\zexiao_2027.db'
|
||
con = sqlite3.connect(DB, timeout=600)
|
||
|
||
before = con.execute('SELECT COUNT(*) FROM ggyx_dim_major').fetchone()[0]
|
||
print('修复前 dim_major: %d 行' % before)
|
||
|
||
cols = [r[1] for r in con.execute('PRAGMA table_info(ggyx_dim_major)')]
|
||
print('字段:', cols)
|
||
|
||
# 1) hot_rank:有 major_id + maj_code + maj_name
|
||
rows = con.execute('''SELECT h.major_id, MAX(h.maj_code), MAX(h.maj_name)
|
||
FROM ggyx_major_hot_rank h
|
||
LEFT JOIN ggyx_dim_major d ON d.major_id=h.major_id
|
||
WHERE d.major_id IS NULL AND IFNULL(h.maj_code,'')<>''
|
||
GROUP BY h.major_id''').fetchall()
|
||
print('hot_rank 待补: %d' % len(rows))
|
||
|
||
n1 = 0
|
||
for mid, code, name in rows:
|
||
code = (code or '').strip()
|
||
b = code[:4] if len(code) >= 4 else ''
|
||
try:
|
||
con.execute('''INSERT INTO ggyx_dim_major
|
||
(major_id, major_name, show_name, show_two, b_code, c_code, a_code, type, is_manage, chose_manage, have_ssd_2025)
|
||
VALUES (?,?,?,?,?,?,?,?,?,?,?)''',
|
||
(mid, name, name, 1, b, code, b[:2], 1, 0, 1, 0))
|
||
n1 += 1
|
||
except Exception as e:
|
||
print(' 跳过 %s: %s' % (mid, str(e)[:40]))
|
||
con.commit()
|
||
print(' → 补入 %d 行' % n1)
|
||
|
||
# 2) src_major_catalog:有 专业代码
|
||
rows2 = con.execute('''SELECT s.major_id, MAX(s.专业代码), MAX(s.专业名称)
|
||
FROM src_major_catalog s
|
||
LEFT JOIN ggyx_dim_major d ON d.major_id=s.major_id
|
||
WHERE d.major_id IS NULL AND IFNULL(s.专业代码,'')<>''
|
||
GROUP BY s.major_id''').fetchall()
|
||
print('catalog 待补: %d' % len(rows2))
|
||
n2 = 0
|
||
for mid, code, name in rows2:
|
||
code = (code or '').strip()
|
||
b = code[:4] if len(code) >= 4 else ''
|
||
try:
|
||
con.execute('''INSERT INTO ggyx_dim_major
|
||
(major_id, major_name, show_name, show_two, b_code, c_code, a_code, type, is_manage, chose_manage, have_ssd_2025)
|
||
VALUES (?,?,?,?,?,?,?,?,?,?,?)''',
|
||
(mid, name, name, 1, b, code, b[:2], 1, 0, 1, 0))
|
||
n2 += 1
|
||
except Exception:
|
||
pass
|
||
con.commit()
|
||
print(' → 补入 %d 行' % n2)
|
||
|
||
# 3) ssd_master:只有名称,无代码
|
||
rows3 = con.execute('''SELECT s.major_id, MAX(s.major_name)
|
||
FROM ggyx_ssd_master s
|
||
LEFT JOIN ggyx_dim_major d ON d.major_id=s.major_id
|
||
WHERE d.major_id IS NULL AND IFNULL(s.major_name,'')<>''
|
||
GROUP BY s.major_id''').fetchall()
|
||
print('ssd_master 待补(无专业码): %d' % len(rows3))
|
||
n3 = 0
|
||
for mid, name in rows3:
|
||
try:
|
||
con.execute('''INSERT INTO ggyx_dim_major
|
||
(major_id, major_name, show_name, show_two, b_code, c_code, a_code, type, is_manage, chose_manage, have_ssd_2025)
|
||
VALUES (?,?,?,?,?,?,?,?,?,?,?)''',
|
||
(mid, name, name, 1, '', '', '', 1, 0, 1, 0))
|
||
n3 += 1
|
||
except Exception:
|
||
pass
|
||
con.commit()
|
||
print(' → 补入 %d 行' % n3)
|
||
|
||
after = con.execute('SELECT COUNT(*) FROM ggyx_dim_major').fetchone()[0]
|
||
print()
|
||
print('修复后 dim_major: %d 行(+%d)' % (after, after - before))
|
||
|
||
# 验证孤儿
|
||
print()
|
||
print('=== 验证各表孤儿 major_id ===')
|
||
for t in ['ggyx_major_hot_rank', 'ggyx_ssd_master', 'src_major_catalog', 'ggyx_school_adjust_major', 'fact_admission']:
|
||
n = con.execute('''SELECT COUNT(DISTINCT m.major_id) FROM "%s" m
|
||
LEFT JOIN ggyx_dim_major d ON d.major_id=m.major_id WHERE d.major_id IS NULL''' % t).fetchone()[0]
|
||
print(' %-28s 剩余孤儿 %d' % (t, n))
|
||
|
||
# 无专业码的样本
|
||
print()
|
||
print('无专业码的专业(前 5):')
|
||
for r in con.execute("SELECT major_id, major_name FROM ggyx_dim_major WHERE IFNULL(c_code,'')='' LIMIT 5"):
|
||
print(' ', r)
|
||
print('无专业码总数:', con.execute("SELECT COUNT(*) FROM ggyx_dim_major WHERE IFNULL(c_code,'')=''").fetchone()[0])
|
||
con.close()
|