115 lines
4.9 KiB
Python
115 lines
4.9 KiB
Python
# -*- coding: utf-8 -*-
|
||
"""网站数据导出:从 zexiao_2027.db 导出静态网站所需的 JSON。
|
||
|
||
导出到 D:/择校/website/data/:
|
||
stats.json 全局统计
|
||
schools.json 1038 校列表(层次/省份/专业数/热度)
|
||
detail/{id}.json 热度 TOP150 校详情(专业+历年趋势+分数线)
|
||
"""
|
||
import json
|
||
import os
|
||
import sqlite3
|
||
import time
|
||
|
||
DB = r'D:\择校\数据库\zexiao_2027.db'
|
||
OUT = r'D:\择校\website\data'
|
||
DETAIL_N = 150
|
||
|
||
|
||
def log(*a):
|
||
print('[%s] %s' % (time.strftime('%H:%M:%S'), ' '.join(str(x) for x in a)), flush=True)
|
||
|
||
|
||
def q(con, sql, args=()):
|
||
return con.execute(sql, args).fetchall()
|
||
|
||
|
||
def main():
|
||
os.makedirs(os.path.join(OUT, 'detail'), exist_ok=True)
|
||
con = sqlite3.connect('file:%s?mode=ro' % DB, uri=True)
|
||
con.row_factory = sqlite3.Row
|
||
|
||
# 1. stats
|
||
stats = {}
|
||
stats['schools'] = q(con, 'SELECT COUNT(*) n FROM ggyx_dim_school')[0]['n']
|
||
stats['majors'] = q(con, 'SELECT COUNT(*) n FROM ggyx_dim_major')[0]['n']
|
||
stats['triples'] = q(con, 'SELECT COUNT(*) n FROM ggyx_hot_triple')[0]['n']
|
||
stats['dir_hist'] = q(con, 'SELECT COUNT(*) n FROM dir_hist')[0]['n']
|
||
stats['dir_hist_schools'] = q(con, 'SELECT COUNT(DISTINCT 院校) n FROM dir_hist')[0]['n']
|
||
stats['reverse'] = q(con, 'SELECT COUNT(*) n FROM ggyx_dir_hist_reverse')[0]['n']
|
||
stats['chsi_2027'] = q(con, 'SELECT COUNT(*) n FROM chsi_2027_dir')[0]['n']
|
||
stats['score_lines'] = q(con, 'SELECT COUNT(*) n FROM ggyx_exam_score')[0]['n']
|
||
# 层次分布
|
||
lv = {}
|
||
for r in q(con, "SELECT 层次, COUNT(*) n FROM (SELECT CASE WHEN is_985=1 THEN '985' WHEN is_211=1 THEN '211' ELSE '双非' END 层次 FROM ggyx_dim_school) GROUP BY 层次"):
|
||
lv[r['层次']] = r['n']
|
||
stats['levels'] = lv
|
||
# dir_hist 来源分布
|
||
src = {}
|
||
for r in q(con, '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 q(con, '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.json:', stats)
|
||
|
||
# 2. schools 列表
|
||
schools = []
|
||
rows = q(con, """
|
||
SELECT s.school_id, s.sch_name, s.province, s.city, s.is_985, s.is_211, s.is_syl,
|
||
s.hot_sum, s.n_triple,
|
||
(SELECT COUNT(*) FROM dir_hist d WHERE d.院校 = s.sch_name) dh,
|
||
(SELECT COUNT(*) FROM ggyx_dir_hist_reverse r WHERE r.school_id = s.school_id) rev
|
||
FROM ggyx_dim_school s ORDER BY s.hot_sum DESC
|
||
""")
|
||
for r in rows:
|
||
lv = '985' if r['is_985'] else ('211' if r['is_211'] else ('双一流' if r['is_syl'] else '双非'))
|
||
schools.append({
|
||
'id': r['school_id'], 'name': r['sch_name'], 'prov': r['province'] or '',
|
||
'city': r['city'] or '', 'level': lv, 'hot': r['hot_sum'] or 0,
|
||
'n_triple': r['n_triple'] or 0, 'dir_hist': r['dh'], 'reverse': r['rev'],
|
||
})
|
||
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 校' % len(schools))
|
||
|
||
# 3. 热度 TOP150 校详情
|
||
top = schools[:DETAIL_N]
|
||
for sc in top:
|
||
sid = sc['id']
|
||
name = sc['name']
|
||
# 该校专业(三元组聚合)
|
||
majors = q(con, """
|
||
SELECT maj_name, maj_code, xy_name, COUNT(*) n_dir
|
||
FROM ggyx_hot_triple WHERE school_id=? GROUP BY maj_name, maj_code, xy_name
|
||
ORDER BY n_dir DESC LIMIT 300
|
||
""", (sid,))
|
||
mlist = [{'name': m['maj_name'], 'code': m['maj_code'], 'xy': m['xy_name'] or ''} for m in majors]
|
||
# 历年趋势(倒推表)
|
||
rev = q(con, """
|
||
SELECT year, COUNT(*) n_major, AVG(nlq_total) avg_nlq
|
||
FROM ggyx_dir_hist_reverse WHERE school_id=? GROUP BY year ORDER BY year
|
||
""", (sid,))
|
||
trend = [{'year': r['year'], 'n_major': r['n_major'],
|
||
'avg_nlq': round(r['avg_nlq'], 1) if r['avg_nlq'] else None} for r in rev]
|
||
# dir_hist 按年份
|
||
dh = q(con, "SELECT 年份, COUNT(*) n FROM dir_hist WHERE 院校=? GROUP BY 年份 ORDER BY 年份", (name,))
|
||
dh_map = {r['年份']: r['n'] for r in dh}
|
||
detail = {'id': sid, 'name': name, 'prov': sc['prov'], 'level': sc['level'],
|
||
'hot': sc['hot'], 'majors': mlist, 'trend': trend,
|
||
'dir_hist': dh_map}
|
||
with open(os.path.join(OUT, 'detail', '%d.json' % sid), 'w', encoding='utf-8') as f:
|
||
json.dump(detail, f, ensure_ascii=False, separators=(',', ':'))
|
||
log('详情导出 %d 校' % len(top))
|
||
con.close()
|
||
log('导出完成 →', OUT)
|
||
|
||
|
||
if __name__ == '__main__':
|
||
main()
|