Files
yanxuan/脚本/website_export.py

115 lines
4.9 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 -*-
"""网站数据导出:从 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()