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()
|