238 lines
12 KiB
Python
238 lines
12 KiB
Python
# -*- 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()
|