Files
yanxuan/脚本/website_export_v2.py

238 lines
12 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 -*-
"""网站全量数据导出 v2:2027官方目录(105,411行) + 历史目录 + 倒推,支持全量下钻。
导出到 D:/择校/website/data/:
stats.json 全局统计
schools.json 全部院校(含2027目录专业数/方向数/历史覆盖/热度)
detail/{dwdm}.json 每校全量2027目录:院系→专业→研究方向(人数/科目/学制/备注/学习方式)
majors.json 全部专业索引(专业码→名称/门类/招收院校数)
"""
import json
import os
import re
import sqlite3
import time
DB = r'D:\择校\数据库\zexiao_2027.db'
OUT = r'D:\择校\website\data'
def log(*a):
print('[%s] %s' % (time.strftime('%H:%M:%S'), ' '.join(str(x) for x in a)), flush=True)
def parse_enroll(s):
"""'专业:2(不含推免)' → 2;并返回 kind:'major'=专业级(方向行重复出现) / 'dir'=方向级"""
if not s:
return None, None
s = str(s)
m = re.search(r'(\d+)', s)
n = int(m.group(1)) if m else None
kind = 'major' if s.startswith('专业') else 'dir'
return n, kind
def enroll_group(con, dwdm=None):
"""正确口径的招生人数聚合(规范 §1.2a):
组(院系,专业,学习方式)内:有"专业"行取 MAX,否则 SUM"研究方向"行;再跨组求和。
返回 dict: dwdm -> sum_enroll(校级,全日制+非全合计)
"""
sql = """SELECT dwdm, COALESCE(SUM(gval),0) total FROM (
SELECT dwdm, 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]*' {cond}
GROUP BY 1,2,3,4) GROUP BY dwdm"""
if dwdm:
rows = con.execute(sql.format(cond='AND dwdm=?'), (int(dwdm),)).fetchall()
else:
rows = con.execute(sql.format(cond='')).fetchall()
return {r[0]: r[1] for r in rows}
def parse_subjects(s):
"""exam_subjects JSON → '101思想政治理论 / 201英语一' 文本"""
if not s:
return ''
try:
arr = json.loads(s)
return ' / '.join('%s%s' % (x.get('code', ''), x.get('name', '')) for x in arr)
except Exception:
return str(s)[:80]
def main():
os.makedirs(os.path.join(OUT, 'detail'), exist_ok=True)
os.makedirs(os.path.join(OUT, 'major'), exist_ok=True)
con = sqlite3.connect('file:%s?mode=ro' % DB, uri=True)
con.row_factory = sqlite3.Row
# ---------- 1. stats ----------
stats = {}
stats['dir27_rows'] = con.execute('SELECT COUNT(*) FROM chsi_2027_dir').fetchone()[0]
stats['dir27_schools'] = con.execute('SELECT COUNT(DISTINCT dwmc) FROM chsi_2027_dir').fetchone()[0]
stats['dir27_depts'] = con.execute('SELECT COUNT(DISTINCT dwmc||yxsmc) FROM chsi_2027_dir').fetchone()[0]
stats['dir27_majors'] = con.execute('SELECT COUNT(DISTINCT zydm) FROM chsi_2027_dir').fetchone()[0]
stats['dir27_dirs'] = stats['dir27_rows']
stats['dir_hist'] = con.execute('SELECT COUNT(*) FROM dir_hist').fetchone()[0]
stats['dir_hist_schools'] = con.execute('SELECT COUNT(DISTINCT 院校) FROM dir_hist').fetchone()[0]
stats['reverse'] = con.execute('SELECT COUNT(*) FROM ggyx_dir_hist_reverse').fetchone()[0]
stats['triples'] = con.execute('SELECT COUNT(*) FROM ggyx_hot_triple').fetchone()[0]
stats['schools_dim'] = con.execute('SELECT COUNT(*) FROM ggyx_dim_school').fetchone()[0]
lv = {}
for r in con.execute("SELECT CASE WHEN is_985=1 THEN '985' WHEN is_211=1 THEN '211' ELSE '双非' END lv, COUNT(*) n FROM ggyx_dim_school GROUP BY lv"):
lv[r['lv']] = r['n']
stats['levels'] = lv
src = {}
for r in con.execute('SELECT 来源, COUNT(*) n FROM dir_hist GROUP BY 来源 ORDER BY n DESC'):
src[r['来源']] = r['n']
stats['dir_hist_src'] = src
yr = {}
for r in con.execute('SELECT year, COUNT(*) n FROM ggyx_dir_hist_reverse GROUP BY year ORDER BY year'):
yr[str(r['year'])] = r['n']
stats['reverse_year'] = yr
with open(os.path.join(OUT, 'stats.json'), 'w', encoding='utf-8') as f:
json.dump(stats, f, ensure_ascii=False, indent=1)
log('stats:', stats)
# ---------- 2. schools.json ----------
# 院校维度:dim_school 信息 + 2027目录统计 + 历史覆盖
# sum_enroll 用正确口径(规范 §1.2a):专业行 MAX / 方向行 SUM,禁止盲目 SUM(enroll_num)
enroll27 = enroll_group(con)
d27 = {}
for r in con.execute("""SELECT dwdm, dwmc, COUNT(*) n_dir, COUNT(DISTINCT zydm) n_major,
COUNT(DISTINCT yxsmc) n_dept
FROM chsi_2027_dir GROUP BY dwdm, dwmc"""):
d27[r['dwmc']] = {'code': r['dwdm'], 'n_dir': r['n_dir'], 'n_major': r['n_major'],
'n_dept': r['n_dept'],
'sum_enroll': enroll27.get(r['dwdm'], 0)}
hist = {}
for r in con.execute('SELECT 院校, COUNT(*) n FROM dir_hist GROUP BY 院校'):
hist[r['院校']] = r['n']
schools = []
known_names = set()
# 旧称 → 新称映射(避免"安庆师范学院"这类陈旧条目显示为空数据)
rename = {}
try:
for r in con.execute('SELECT old_name, new_name FROM dim_school_rename'):
rename[r['old_name']] = r['new_name']
except Exception:
pass
for r in con.execute("""SELECT school_id, sch_name, province, city, is_985, is_211, is_syl, hot_sum, n_triple
FROM ggyx_dim_school ORDER BY hot_sum DESC"""):
nm = r['sch_name']
known_names.add(nm)
new_nm = rename.get(nm)
# 若该名是旧称,取新称的数据
d = d27.get(nm) or (d27.get(new_nm) if new_nm else None) or {}
lv = '985' if r['is_985'] else ('211' if r['is_211'] else ('双一流' if r['is_syl'] else '双非'))
item = {
'id': r['school_id'], 'name': nm, 'prov': r['province'] or '', 'city': r['city'] or '',
'level': lv, 'hot': r['hot_sum'] or 0, 'n_triple': r['n_triple'] or 0,
'd27': d.get('n_dir', 0), 'm27': d.get('n_major', 0), 'dept27': d.get('n_dept', 0),
'enroll27': d.get('sum_enroll', 0), 'hist': hist.get(nm, 0) or (hist.get(new_nm, 0) if new_nm else 0),
'code27': d.get('code', ''),
}
if new_nm:
item['renamed_to'] = new_nm
schools.append(item)
# 补充 chsi 有但 dim_school 没有的院校(半角括号校名/独立研究院等)
extra = 0
for nm, d in d27.items():
if nm in known_names:
continue
schools.append({
'id': 0, 'name': nm, 'prov': '', 'city': '', 'level': '双非', 'hot': 0, 'n_triple': 0,
'd27': d['n_dir'], 'm27': d['n_major'], 'dept27': d['n_dept'],
'enroll27': d['sum_enroll'], 'hist': hist.get(nm, 0), 'code27': d['code'],
})
extra += 1
schools.sort(key=lambda s: -((s['d27'] or 0) * 1000 + (s['hot'] or 0)))
with open(os.path.join(OUT, 'schools.json'), 'w', encoding='utf-8') as f:
json.dump(schools, f, ensure_ascii=False, separators=(',', ':'))
log('schools.json: %d 校(有2027目录 %d,补充 chsi 独有 %d)' % (
len(schools), sum(1 for s in schools if s['d27']), extra))
# ---------- 3. detail/{dwdm}.json 每校全量目录 ----------
n_file = 0
for dwdm, dwmc in con.execute('SELECT DISTINCT dwdm, dwmc FROM chsi_2027_dir').fetchall():
# 院系 → 专业 → 方向
depts = {}
for r in con.execute("""SELECT yxsmc, zydm, zymc, yjfxmc, enroll_num, enroll_str,
exam_subjects, duration_years, zybz, xxfsmc, xwlxmc, yjxkmc, mlmc,
tuition_fee
FROM chsi_2027_dir WHERE dwdm=? ORDER BY yxsmc, zydm, yjfxmc""", (dwdm,)):
dept = r['yxsmc'] or '(未分院系)'
key = r['zydm']
depts.setdefault(dept, {})
if key not in depts[dept]:
depts[dept][key] = {
'code': r['zydm'], 'name': r['zymc'], 'xk': r['yjxkmc'] or '',
'ml': r['mlmc'] or '', 'type': r['xwlxmc'] or '', 'fs': r['xxfsmc'] or '',
'years': r['duration_years'], 'fee': r['tuition_fee'] or 0,
'bz': r['zybz'] or '', 'dirs': [],
}
num, kind = parse_enroll(r['enroll_str'])
if num is None:
num = r['enroll_num'] if r['enroll_num'] else None
if kind == 'major':
# "专业:N" 是专业级数字(方向行重复),作为专业小计,不进方向列表
if num is not None:
prev = depts[dept][key].get('total')
depts[dept][key]['total'] = max(prev or 0, num)
continue
depts[dept][key]['dirs'].append({
'name': r['yjfxmc'] or '不区分研究方向', 'num': num, 'raw': r['enroll_str'] or '',
'subj': parse_subjects(r['exam_subjects']),
})
# 转 list
out_depts = []
for dept, majors in depts.items():
out_depts.append({'dept': dept, 'majors': list(majors.values())})
detail = {'code': dwdm, 'name': dwmc, 'depts': out_depts}
with open(os.path.join(OUT, 'detail', '%s.json' % dwdm), 'w', encoding='utf-8') as f:
json.dump(detail, f, ensure_ascii=False, separators=(',', ':'))
n_file += 1
log('detail: %d 校文件' % n_file)
# ---------- 4. majors.json 专业反查索引 ----------
mrows = con.execute("""SELECT zydm, MAX(zymc) nm, MAX(mlmc) ml, MAX(yjxkmc) xk,
COUNT(DISTINCT dwmc) n_school, COUNT(*) n_dir
FROM chsi_2027_dir GROUP BY zydm ORDER BY n_school DESC""").fetchall()
majors = [{'code': r['zydm'], 'name': r['nm'], 'ml': r['ml'] or '', 'xk': r['xk'] or '',
'n_school': r['n_school'], 'n_dir': r['n_dir']} for r in mrows]
with open(os.path.join(OUT, 'majors.json'), 'w', encoding='utf-8') as f:
json.dump(majors, f, ensure_ascii=False, separators=(',', ':'))
log('majors.json: %d 专业' % len(majors))
# ---------- 5. major/{code}.json 专业→院校列表 ----------
for r in mrows[:400]: # TOP400 专业(覆盖主流)
code = r['zydm']
rows = con.execute("""SELECT dwmc, yxsmc, yjfxmc, enroll_str, xxfsmc
FROM chsi_2027_dir WHERE zydm=? ORDER BY dwmc LIMIT 200""", (code,)).fetchall()
sch = {}
pro_max = {} # (dwmc, dept) -> 专业级 MAX
for x in rows:
num, kind = parse_enroll(x['enroll_str'])
if kind == 'major' and num:
k2 = (x['dwmc'], x['yxsmc'])
pro_max[k2] = max(pro_max.get(k2, 0), num)
sch.setdefault(x['dwmc'], []).append({
'dept': x['yxsmc'], 'dir': x['yjfxmc'],
'num': num if kind == 'dir' else None, 'kind': kind,
'fs': x['xxfsmc']})
totals = {}
for (nm, _dept), v in pro_max.items():
totals[nm] = totals.get(nm, 0) + v
with open(os.path.join(OUT, 'major', '%s.json' % code), 'w', encoding='utf-8') as f:
json.dump({'code': code, 'name': r['nm'], 'schools': sch, 'totals': totals},
f, ensure_ascii=False, separators=(',', ':'))
log('major: %d 专业文件' % min(400, len(mrows)))
con.close()
log('导出完成 →', OUT)
if __name__ == '__main__':
main()