john
e76fa586f1
Ship dual-track learning (daily accumulation vs textbook),沪教/商务词书 APIs and UI, native iOS wrapper with bundled H5, and production book import on deploy. Co-authored-by: Cursor <cursoragent@cursor.com>
250 lines
8.5 KiB
Python
250 lines
8.5 KiB
Python
"""从 GitHub (lilinji/English) 下载沪版牛津与商务英语 xlsx，生成词书 JSON。
|
||
|
||
用法:
|
||
python -m scripts.fetch_hujiao_books
|
||
python -m scripts.fetch_hujiao_books --import # 生成后直接写入数据库
|
||
"""
|
||
|
||
from __future__ import annotations
|
||
|
||
import argparse
|
||
import json
|
||
import re
|
||
import sys
|
||
import urllib.parse
|
||
import urllib.request
|
||
from pathlib import Path
|
||
|
||
BACKEND_DIR = Path(__file__).resolve().parents[1]
|
||
OUT_DIR = BACKEND_DIR / "data" / "books"
|
||
|
||
GITHUB_RAW = "https://raw.githubusercontent.com/lilinji/English/main"
|
||
OXFORD_DIR = (
|
||
"1.全国各大教材版本中小学同步/牛津版"
|
||
)
|
||
BUSINESS_DIR = "8.商务英语"
|
||
|
||
PRIMARY_GRADES = [
|
||
(1, 11, "一年级", ["牛津上海版一年级上册.xlsx", "牛津上海版一年级下册.xlsx"]),
|
||
(2, 12, "二年级", ["牛津上海版二年级上册.xlsx", "牛津上海版二年级下册.xlsx"]),
|
||
(3, 13, "三年级", ["牛津上海版三年级上册.xlsx", "牛津上海版三年级下册.xlsx"]),
|
||
(4, 14, "四年级", ["牛津上海版四年级上册.xlsx", "牛津上海版四年级下册.xlsx"]),
|
||
(5, 15, "五年级", ["牛津上海版五年级上册.xlsx", "牛津上海版五年级下册.xlsx"]),
|
||
]
|
||
|
||
JUNIOR_GRADES = [
|
||
(6, 21, "六年级", ["牛津上海版六年级上册.xlsx", "牛津上海版六年级下册.xlsx"]),
|
||
(7, 22, "七年级", ["牛津上海版七年级上册.xlsx", "牛津上海版七年级下册.xlsx"]),
|
||
(8, 23, "八年级", ["牛津上海版八年级上册.xlsx", "牛津上海版八年级下册.xlsx"]),
|
||
(9, 24, "九年级", ["牛津上海版九年级上册.xlsx", "牛津上海版九年级下册.xlsx"]),
|
||
]
|
||
|
||
SENIOR_GRADES = [
|
||
(1, 31, "高一", ["牛津上海版高一上.xlsx", "牛津上海版高一下.xlsx"]),
|
||
(2, 32, "高二", ["牛津上海版高二上.xlsx", "牛津上海版高二下.xlsx"]),
|
||
(3, 33, "高三", ["牛津上海版高三上.xlsx", "牛津上海版高三下.xlsx"]),
|
||
]
|
||
|
||
|
||
BOOK_SPECS = [
|
||
*[
|
||
{
|
||
"slug": f"hujiao-primary-{grade}",
|
||
"title": f"沪版小学英语 · {label}",
|
||
"description": f"牛津上海版{label}词汇（上下册）",
|
||
"level": "primary",
|
||
"sort_order": order,
|
||
"daily_target": 10 + grade,
|
||
"master_required_count": 3,
|
||
"weak_wrong_threshold": 2,
|
||
"files": files,
|
||
}
|
||
for grade, order, label, files in PRIMARY_GRADES
|
||
],
|
||
*[
|
||
{
|
||
"slug": f"hujiao-junior-{grade}",
|
||
"title": f"沪版初中英语 · {label}",
|
||
"description": f"牛津上海版{label}词汇（上下册）",
|
||
"level": "junior",
|
||
"sort_order": order,
|
||
"daily_target": 11 + grade - 6,
|
||
"master_required_count": 4,
|
||
"weak_wrong_threshold": 2,
|
||
"files": files,
|
||
}
|
||
for grade, order, label, files in JUNIOR_GRADES
|
||
],
|
||
*[
|
||
{
|
||
"slug": f"hujiao-senior-{grade}",
|
||
"title": f"沪版高中英语 · {label}",
|
||
"description": f"牛津上海版{label}词汇（上下册）",
|
||
"level": "senior",
|
||
"sort_order": order,
|
||
"daily_target": 15 + grade,
|
||
"master_required_count": 4,
|
||
"weak_wrong_threshold": 2,
|
||
"files": files,
|
||
}
|
||
for grade, order, label, files in SENIOR_GRADES
|
||
],
|
||
{
|
||
"slug": "business-daily",
|
||
"title": "日常商务英语",
|
||
"description": "BEC 初/中级核心商务词汇",
|
||
"level": "business",
|
||
"sort_order": 40,
|
||
"daily_target": 15,
|
||
"master_required_count": 4,
|
||
"weak_wrong_threshold": 2,
|
||
"files": [
|
||
"BEC初级词汇精选.xlsx",
|
||
"BEC中级词汇精选.xlsx",
|
||
],
|
||
"base_dir": BUSINESS_DIR,
|
||
},
|
||
]
|
||
|
||
|
||
def download_file(rel_path: str, cache_dir: Path) -> Path:
|
||
cache_dir.mkdir(parents=True, exist_ok=True)
|
||
local = cache_dir / Path(rel_path).name
|
||
if local.exists() and local.stat().st_size > 0:
|
||
return local
|
||
url = f"{GITHUB_RAW}/{urllib.parse.quote(rel_path)}"
|
||
print(f" 下载 {Path(rel_path).name} ...")
|
||
urllib.request.urlretrieve(url, local)
|
||
return local
|
||
|
||
|
||
def parse_xlsx(path: Path) -> list[dict]:
|
||
import openpyxl
|
||
|
||
wb = openpyxl.load_workbook(path, read_only=True, data_only=True)
|
||
ws = wb.active
|
||
rows = list(ws.iter_rows(values_only=True))
|
||
if not rows:
|
||
return []
|
||
header = [str(c).strip() if c else "" for c in rows[0]]
|
||
col_word = next((i for i, h in enumerate(header) if h in ("单词", "word", "英文")), 0)
|
||
col_zh = next((i for i, h in enumerate(header) if h in ("释义", "中文", "意思")), -1)
|
||
col_phonetic = next((i for i, h in enumerate(header) if "英音" in h or h == "音标"), -1)
|
||
|
||
out: list[dict] = []
|
||
seen: set[str] = set()
|
||
for row in rows[1:]:
|
||
if not row or not row[col_word]:
|
||
continue
|
||
lemma = str(row[col_word]).strip().lower()
|
||
if not lemma or lemma in seen:
|
||
continue
|
||
seen.add(lemma)
|
||
zh_raw = str(row[col_zh]).strip() if col_zh >= 0 and row[col_zh] else lemma
|
||
zh = _simplify_zh(zh_raw)
|
||
phonetic = None
|
||
if col_phonetic >= 0 and row[col_phonetic]:
|
||
phonetic = str(row[col_phonetic]).strip()[:128] or None
|
||
out.append({"lemma_en": lemma[:128], "zh": zh[:256], "phonetic": phonetic})
|
||
wb.close()
|
||
return out
|
||
|
||
|
||
def _simplify_zh(raw: str) -> str:
|
||
text = raw.strip()
|
||
first = text.split("\n")[0].strip()
|
||
first = re.sub(r"^[a-z]+\.\s*", "", first, flags=re.I)
|
||
for sep in ("；", ";", "，", ","):
|
||
if sep in first:
|
||
first = first.split(sep)[0].strip()
|
||
return first or raw[:64]
|
||
|
||
|
||
def unit_title_from_filename(name: str) -> str:
|
||
base = name.replace(".xlsx", "").replace("牛津上海版", "").replace("BEC", "BEC ")
|
||
return base.strip()
|
||
|
||
|
||
def build_book(spec: dict, cache_dir: Path) -> dict:
|
||
base_dir = spec.get("base_dir", OXFORD_DIR)
|
||
units = []
|
||
global_seen: set[str] = set()
|
||
sort_index = 0
|
||
|
||
for unit_no, fname in enumerate(spec["files"], start=1):
|
||
rel = f"{base_dir}/{fname}"
|
||
path = download_file(rel, cache_dir)
|
||
words = parse_xlsx(path)
|
||
unit_words = []
|
||
for w in words:
|
||
key = w["lemma_en"]
|
||
if key in global_seen:
|
||
continue
|
||
global_seen.add(key)
|
||
sort_index += 1
|
||
unit_words.append({**w, "sort_index": sort_index})
|
||
if unit_words:
|
||
units.append(
|
||
{
|
||
"unit": unit_no,
|
||
"title": unit_title_from_filename(fname),
|
||
"words": unit_words,
|
||
}
|
||
)
|
||
|
||
return {
|
||
"slug": spec["slug"],
|
||
"title": spec["title"],
|
||
"description": spec["description"],
|
||
"level": spec["level"],
|
||
"sort_order": spec["sort_order"],
|
||
"daily_target": spec["daily_target"],
|
||
"master_required_count": spec["master_required_count"],
|
||
"weak_wrong_threshold": spec["weak_wrong_threshold"],
|
||
"learn_mode": "sequential",
|
||
"units": units,
|
||
}
|
||
|
||
|
||
def main() -> None:
|
||
parser = argparse.ArgumentParser(description="下载沪版词书并生成 JSON")
|
||
parser.add_argument("--import-db", action="store_true", help="生成后导入数据库")
|
||
args = parser.parse_args()
|
||
|
||
try:
|
||
import openpyxl # noqa: F401
|
||
except ImportError:
|
||
print("请先安装: pip install openpyxl", file=sys.stderr)
|
||
sys.exit(1)
|
||
|
||
OUT_DIR.mkdir(parents=True, exist_ok=True)
|
||
cache_dir = BACKEND_DIR / "data" / "cache" / "word_books"
|
||
books = []
|
||
for spec in BOOK_SPECS:
|
||
print(f"处理 {spec['title']} ...")
|
||
book = build_book(spec, cache_dir)
|
||
word_count = sum(len(u["words"]) for u in book["units"])
|
||
book["word_count"] = word_count
|
||
book["unit_count"] = len(book["units"])
|
||
out_path = OUT_DIR / f"{spec['slug']}.json"
|
||
out_path.write_text(json.dumps(book, ensure_ascii=False, indent=2), encoding="utf-8")
|
||
print(f" -> {out_path.name} ({word_count} 词, {book['unit_count']} 单元)")
|
||
books.append(book)
|
||
|
||
manifest = OUT_DIR / "manifest.json"
|
||
manifest.write_text(
|
||
json.dumps([{"slug": b["slug"], "title": b["title"], "word_count": b["word_count"]} for b in books],
|
||
ensure_ascii=False, indent=2),
|
||
encoding="utf-8",
|
||
)
|
||
print(f"已生成 {len(books)} 本词书 -> {OUT_DIR}")
|
||
|
||
if args.import_db:
|
||
from scripts.import_word_books import import_all
|
||
|
||
import_all(OUT_DIR)
|
||
|
||
|
||
if __name__ == "__main__":
|
||
main()
|