Files
yanxuan/脚本/fix_dirhist_enroll.py

60 lines
2.5 KiB
Python
Raw Permalink Blame History

This file contains ambiguous Unicode characters
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 -*-
"""缺口 3 修复:dir_hist 2027 行招生人数空 → 用 chsi_2027_dir 官方数回填(§1.2a 口径)。
注意:只回填 年份=2027(chsi 只有 2027 届,2026 不能拿 2027 数冒充)。
可回滚:回填前快照被改行到 运行暂存\ggyx采集\dir_hist_回填备份_20261022.csv
"""
import csv
import os
import sqlite3
DB = r'D:\择校\数据库\zexiao_2027.db'
BAK = r'D:\择校\运行暂存\ggyx采集\dir_hist_回填备份_20261022.csv'
con = sqlite3.connect(DB, timeout=600)
con.row_factory = sqlite3.Row
# chsi 专业级总数(§1.2a):组(院系,学习方式)内 专业行MAX/方向行SUM,再跨组求和
chsi = {}
for r in con.execute("""
SELECT dwmc, zydm, SUM(gval) total FROM (
SELECT dwmc, yxsdm, zydm, xxfs,
COALESCE(MAX(CASE WHEN enroll_str LIKE '专业%' THEN CAST(enroll_num AS INTEGER) END),
SUM(CASE WHEN enroll_str LIKE '研究方向%' THEN CAST(enroll_num AS INTEGER) END), 0) gval
FROM chsi_2027_dir
WHERE enroll_num GLOB '[0-9]*'
GROUP BY 1,2,3,4)
GROUP BY dwmc, zydm"""):
chsi[(r['dwmc'], r['zydm'])] = r['total']
# 待回填行:2027 + 招生人数空 + chsi 有值
rows = con.execute("""
SELECT rowid, 年份, 院校, 专业代码, 专业名称, 招生人数, 来源, 证据级别 FROM dir_hist
WHERE IFNULL(招生人数,'')='' AND 年份='2027'""").fetchall()
todo = [r for r in rows if (r['院校'], r['专业代码']) in chsi]
print('2027 空行 %d,其中 chsi 可回填 %d' % (len(rows), len(todo)))
# 备份
os.makedirs(os.path.dirname(BAK), exist_ok=True)
with open(BAK, 'w', encoding='utf-8-sig', newline='') as f:
w = csv.writer(f)
w.writerow(['rowid', '年份', '院校', '专业代码', '专业名称', '招生人数_旧', '来源_旧', '证据级别_旧'])
for r in todo:
w.writerow([r['rowid'], r['年份'], r['院校'], r['专业代码'], r['专业名称'],
r['招生人数'], r['来源'], r['证据级别']])
print('备份 →', BAK)
# 回填
n = 0
for r in todo:
v = chsi[(r['院校'], r['专业代码'])]
con.execute("""UPDATE dir_hist SET 招生人数=?, 来源=来源||'+研招网2027', 证据级别='官方(chsi回填)'
WHERE rowid=?""", (str(v), r['rowid']))
n += 1
con.commit()
print('回填 %d 行' % n)
# 验证
left = con.execute("SELECT COUNT(*) FROM dir_hist WHERE IFNULL(招生人数,'')='' AND 年份='2027'").fetchone()[0]
print('2027 招生人数空 剩余:', left)
con.close()