60 lines
2.5 KiB
Python
60 lines
2.5 KiB
Python
# -*- 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()
|