Files
yanxuan/数据库/batch_fetch_lines.py

616 lines
22 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.
#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
按台账批量抓官方复试线,只补缺口,不重复抓取。
三层去重(任一命中即跳过,绝不重复下载):
L1 台账层 gap_ledger.py 产出的台账里没有的组合,不抓
L2 库表层 src_official_lines 中该 (院校, 年份) 已有行,跳过
L3 文件层 本地 CSV 已存在且行数 > 0,跳过
另有状态层 状态表里已 ok 的组合,跳过(--force 可强制重试)
定位流程(解决「栏目页只有最新几条」的问题):
360 站内检索 site:{域名} {年份} 复试分数线
→ 取 data-mdurl 真实链接(非跳转链接)
→ 按标题打分(含年份 + 复试/分数线,排除名单/细则/培训)
→ 逐个试解析,行数 ≥ 5 即接受
用法:
python -B batch_fetch_lines.py --priority P1 --limit 10
python -B batch_fetch_lines.py --priority P1 --year 2023
python -B batch_fetch_lines.py --priority P1 --dry-run
"""
import argparse
import csv
import json
import os
import re
import sqlite3
import subprocess
import sys
import tempfile
import threading
import time
import urllib.parse
import urllib.request
from concurrent.futures import ThreadPoolExecutor, as_completed
sys.path.insert(0, os.path.dirname(os.path.abspath(__file__)))
from fetch_official_lines import map_columns, normalize_num, table_rows # noqa: E402
import polite_fetch as polite # noqa: E402
DB = r"D:/择校/数据库/zexiao_2027.db"
OUT_DIR = r"D:/择校/原始来源/官方资料"
STATUS = r"D:/择校/原始来源/官方资料/_批量抓取状态.csv"
LEDGER = r"D:/择校/审计报告/缺口台账_待抓.csv"
LOCATED = r"D:/择校/审计报告/已定位页面.csv"
PY_PDF = r"C:/Users/12742/AppData/Local/Programs/Python/Python312/python.exe"
# 单并发:礼貌抓取层已做同域名串行 + 最小间隔,不再多线程抢跑
WORKERS = 1
_tlock = threading.Lock()
BAD_HOST = ("baidu.com", "zhihu.com", "sohu.com", "163.com", "toutiao.com",
"sina.com", "qq.com", "eol.cn", "kaoyan", "iqihang", "kuakao",
"bkw8", "wendu", "xdf.cn", "gaodun", "offcn", "zhonggong", "doxue",
"koolearn", "21cnjy", "douban", "weibo", "bilibili", "csdn",
"docin", "doc88", "wenku", "youdao", "hujiang", "chinakaoyan")
def log(msg):
with _tlock:
print(msg, flush=True)
def fetch(url, timeout=25, referer=None):
"""统一走礼貌抓取层:robots.txt + 同域名限速 + 落盘缓存 + 熔断。"""
return polite.get_text(url, timeout=timeout)
def resolve_sogou(href, base="https://www.sogou.com"):
"""搜狗结果链接是 /link?url=...,需要读出跳转脚本里的真实地址。"""
if href.lower().startswith("http"):
return href.replace("&", "&")
try:
html = fetch(base + href, referer="https://www.sogou.com/web")
except Exception:
return ""
m = re.search(r'location\.replace\("([^"]+)"', html) or \
re.search(r'(?:URL|url)\s*=\s*["\'](https?://[^"\']+)', html)
return m.group(1).replace("&", "&") if m else ""
_S = {"lock": threading.Lock(), "last": 0.0, "cool": {}, "turn": 0}
MIN_GAP = 3.2 # 两次检索最小间隔(秒)
COOL = 90 # 引擎被限流后的冷却时长(秒)
def _throttle():
"""全局限速:同一时刻只有一个检索请求,且间隔不小于 MIN_GAP。"""
with _S["lock"]:
wait = MIN_GAP - (time.time() - _S["last"])
if wait > 0:
time.sleep(wait)
_S["last"] = time.time()
def _blocked(html):
head = html[:3000]
return ("访问异常" in head or "请输入验证码" in head or
len(html) < 8000 or "<h3" not in html)
def _sogou(query):
html = fetch("https://www.sogou.com/web?query=" + urllib.parse.quote(query))
if _blocked(html):
return [], "blocked"
items = []
for m in re.finditer(r'<h3[^>]*>(.*?)</h3>', html, re.S):
a = re.search(r'href=["\']([^"\']+)["\']', m.group(1))
if not a:
continue
title = re.sub(r"\s+", " ", re.sub(r"<[^>]+>", "", m.group(1))).strip()
items.append((a.group(1).replace("&amp;", "&"), title))
out, resolved = [], 0
for href, title in items:
if href.lower().startswith("http"):
out.append((href, title))
continue
if resolved >= 4:
continue
resolved += 1
real = resolve_sogou(href)
if real:
out.append((real, title))
time.sleep(0.15)
return out, "ok"
def _so360(query):
html = fetch("https://www.so.com/s?q=" + urllib.parse.quote(query))
if "访问异常" in html[:3000] or 'data-mdurl' not in html:
return [], "blocked"
out = []
for block in re.findall(r'<li class="res-list.*?</li>', html, re.S):
m = re.search(r'data-mdurl="([^"]+)"', block)
t = re.search(r'<h3[^>]*>(.*?)</h3>', block, re.S)
if not m:
continue
title = re.sub(r"\s+", " ", re.sub(r"<[^>]+>", "", t.group(1))).strip() if t else ""
out.append((m.group(1).replace("&amp;", "&"), title))
return out, "ok"
ENGINES = [("sogou", _sogou), ("360", _so360)]
def web_search(query):
"""串行检索,引擎轮换 + 冷却;返回 [(真实URL, 标题)]。"""
for _ in range(len(ENGINES)):
name, fn = ENGINES[_S["turn"] % len(ENGINES)]
_S["turn"] += 1
if _S["cool"].get(name, 0) > time.time():
continue
_throttle()
try:
res, why = fn(query)
except Exception:
res, why = [], "err"
if why == "blocked":
_S["cool"][name] = time.time() + COOL
log(f" [{name}] 被限流,冷却 {COOL}s")
continue
if res:
return res
return []
def score_candidate(url, title, year, domain):
s = 0
if year in title:
s += 4
if "分数线" in title or "基本线" in title or "基本要求" in title:
s += 3
if "复试" in title:
s += 2
if re.search(r"名单|细则|调剂|招生简章|专业目录|培训|课程|辅导|真题", title):
s -= 4
hu = urllib.parse.urlparse(url).netloc.lower()
if domain and hu.endswith(domain.lower()):
s += 4
elif hu.endswith(".edu.cn") or hu.endswith(".ac.cn"):
s += 5
elif "chsi.com.cn" in hu:
s += 2
if any(b in hu for b in BAD_HOST):
s -= 6
if len(title) > 70:
s -= 1
return s
def locate(school, year, domain, entry_urls):
"""返回按分数排序的候选 URL 列表。"""
cands = []
hosts = []
if domain and "." in domain and "*" not in domain:
hosts.append(domain)
for u in entry_urls:
h = urllib.parse.urlparse(u).netloc.lower()
if h and h not in hosts:
hosts.append(h)
queries = [f"{school} {year} 硕士研究生 复试分数线"]
for h in hosts[:2]:
queries.append(f"site:{h} {year} 复试分数线")
queries.append(f"{school} {year}年 硕士 复试基本分数线 研究生院")
seen = set()
for q in queries:
if len(cands) >= 8:
break
for url, title in web_search(q):
if url in seen:
continue
seen.add(url)
sc = score_candidate(url, title, year, domain)
if sc <= 0:
continue
cands.append((sc, url, title))
time.sleep(0.6)
cands.sort(key=lambda x: -x[0])
return [(u, t) for _, u, t in cands]
SUBHDR = re.compile(r"总分|政治|外语|业务课|科目|管理类|英语|数学|专业课")
HEAD_KEYS = [
("code", ("专业代码", "学科代码", "专业(领域)代码")),
("name", ("专业名称", "学科专业", "专业(领域)名称")),
("college", ("院系", "学院", "招生单位", "培养单位")),
("direction", ("研究方向", "方向")),
("mode", ("学习方式", "培养方式")),
("total", ("总分", "复试分数线", "复试线", "分数")),
("s1", ("政治", "科目一", "管理类联考")),
("s2", ("外语", "英语", "科目二")),
("s3", ("业务课1", "业务课一", "科目三")),
("s4", ("业务课2", "业务课二", "科目四")),
]
def head_map(names):
m = {}
for i, c in enumerate(names):
c2 = re.sub(r"\s|/", "", c or "")
for key, kws in HEAD_KEYS:
if key in m:
continue
if any(k.replace("/", "") in c2 for k in kws):
m[key] = i
return m
def parse_page(html):
"""支持单行表头、双行表头(复试分数线下挂 总分/政治/外语/业务课)、
以及首列 rowspan 合并(第二行起缺院系)的表格。
仅公布学科门类/类别线(无专业代码)的页面单独识别为 category_only。"""
rows = table_rows(html)
if not rows:
return None, "no_table"
for i, r in enumerate(rows[:15]):
flat = "".join(r)
if ("专业代码" not in flat and "学科门类" in flat and "总分" in flat) or \
("专业代码" not in flat and "专业学位类别" in flat):
return None, "category_only"
if not any("专业代码" in c for c in r):
continue
nxt = rows[i + 1] if i + 1 < len(rows) else []
merged = (nxt and len(nxt) >= 3 and
all(SUBHDR.search(c) for c in nxt if c.strip()) and
sum(1 for c in nxt if c.strip()) >= 3)
if merged:
names = list(r[:-1]) + list(nxt)
hidx = i + 1
else:
names, hidx = list(r), i
hmap = head_map(names)
if "code" not in hmap or "total" not in hmap:
continue
width = len(names)
out, college = [], ""
for row in rows[hidx + 1:]:
cells = [c.strip() for c in row]
if len(cells) == width - 1 and hmap.get("college") == 0:
cells = [""] + cells
if len(cells) < width:
cells += [""] * (width - len(cells))
def g(k):
j = hmap.get(k)
return cells[j] if j is not None and j < len(cells) else ""
code = g("code")
total = normalize_num(g("total"))
if not re.fullmatch(r"\d{4,6}[A-Za-z]?\d?", code) or total is None:
if g("college") and not code:
college = g("college")
continue
if g("college"):
college = g("college")
out.append([college, code, g("name"), g("direction"),
normalize_num(g("s1")), normalize_num(g("s2")),
normalize_num(g("s3")), normalize_num(g("s4")), total])
if len(out) >= 5:
return (out, hmap), "ok"
return None, "no_header"
def pdf_rows(page_url):
"""页面无表格时,交给系统 Python(带 PyMuPDF)解析 PDF 附件。"""
helper = os.path.join(os.path.dirname(os.path.abspath(__file__)), "pdf_extract.py")
fd, out = tempfile.mkstemp(suffix=".json")
os.close(fd)
try:
subprocess.run([PY_PDF, "-B", helper, page_url, out],
capture_output=True, timeout=150)
with open(out, encoding="utf-8") as f:
data = json.load(f)
return data.get("rows") or [], data.get("used", ""), data.get("note", "")
except Exception as e:
return [], "", f"pdf_err:{type(e).__name__}"
finally:
try:
os.remove(out)
except OSError:
pass
def collect_links(html, base, year):
"""从栏目页里挑出「年份 + 复试/分数线」的公告链接。"""
kw = re.compile(r"复试|分数线|基本线|基本要求|进入复试")
out = []
for m in re.finditer(r'<a[^>]+href=["\']([^"\']+)["\'][^>]*>(.*?)</a>', html, re.S | re.I):
href = urllib.parse.urljoin(base, m.group(1)).replace("&amp;", "&")
text = re.sub(r"<[^>]+>", "", m.group(2))
text = re.sub(r"\s+", " ", text.replace("&nbsp;", " ")).strip()
if not text or year not in text or not kw.search(text):
continue
if not href.lower().startswith("http"):
continue
sc = 3 if ("分数线" in text or "基本线" in text or "基本要求" in text) else 1
if re.search(r"名单|细则|调剂|办法|目录|简章", text):
sc -= 2
out.append((sc, href, text))
out.sort(key=lambda x: -x[0])
seen, res = set(), []
for sc, href, text in out:
if href in seen:
continue
seen.add(href)
res.append((href, text))
return res[:6]
def try_parse(url):
"""单页取数:HTML 表格 → PDF 附件。"""
try:
html = fetch(url)
except Exception as e:
return [], f"fetch:{type(e).__name__}"
got, why = parse_page(html)
if got and len(got[0]) >= 5:
return got[0], f"html:{len(got[1])}cols"
if why == "category_only":
return [], "category_only"
if why == "no_table" and html.count("<img") >= 2:
return [], "image_only"
rows, used, note = pdf_rows(url)
if len(rows) >= 5:
return rows, f"pdf:{note}"
return [], (note or why)
def handle_direct(school, year, domain, urls):
"""不检索:直接解析入口页本身,再扫同页链接。"""
urls = [u for u in urls if u][:6]
if not urls:
return school, year, "no_url", 0, "", "台账无入口URL"
reasons = []
for u in urls:
rows, why = try_parse(u)
if len(rows) >= 5:
return save_rows(school, year, rows, u, why)
reasons.append(why)
# 入口本身不是分数线页时,扫它的公告链接
try:
html = fetch(u)
except Exception:
continue
for href, text in collect_links(html, u, year)[:4]:
rows, why = try_parse(href)
if len(rows) >= 5:
return save_rows(school, year, rows, href, why)
reasons.append(why)
time.sleep(0.2)
tag = "category_only" if reasons and all(r == "category_only" for r in reasons) else \
("image_only" if "image_only" in reasons else "parse_fail")
return school, year, tag, 0, "", "/".join(sorted(set(reasons))[:4])
def save_rows(school, year, rows, url, note):
d = os.path.join(OUT_DIR, f"{year}复试线")
os.makedirs(d, exist_ok=True)
path = os.path.join(d, f"{school}_{year}.csv")
with open(path, "w", newline="", encoding="utf-8-sig") as f:
w = csv.writer(f)
w.writerow(["院校", "年份", "院系", "专业代码", "专业名称", "研究方向",
"科目一", "科目二", "科目三", "科目四", "总分"])
for r in rows:
w.writerow([school, year] + list(r))
return school, year, "ok", len(rows), url, note
def handle(school, year, domain, entry_urls, direct=None):
if direct:
cands = [(direct, "已定位")]
else:
cands = locate(school, year, domain, entry_urls)
if not cands:
return school, year, "no_link", 0, "", "检索无命中"
tried, last = 0, ""
for url, title in cands[:5]:
tried += 1
got, why = None, "no_table"
html = ""
try:
html = fetch(url)
got, why = parse_page(html)
except Exception as e:
why = f"fetch:{type(e).__name__}"
if got is None and why == "no_table" and html.count("<img") >= 2:
why = "image_only"
rows, used, pdf_note = None, url, ""
if got and len(got[0]) >= 5:
rows, colmap = got[0], got[1]
pdf_note = f"html_cols={len(colmap)}"
else:
rows, used, pdf_note = pdf_rows(url)
if len(rows) < 5:
last = f"{why}/{pdf_note}"
time.sleep(0.3)
continue
rows = [r for r in rows]
d = os.path.join(OUT_DIR, f"{year}复试线")
os.makedirs(d, exist_ok=True)
path = os.path.join(d, f"{school}_{year}.csv")
with open(path, "w", newline="", encoding="utf-8-sig") as f:
w = csv.writer(f)
w.writerow(["院校", "年份", "院系", "专业代码", "专业名称", "研究方向",
"科目一", "科目二", "科目三", "科目四", "总分"])
for r in rows:
w.writerow([school, year] + list(r))
return school, year, "ok", len(rows), used, pdf_note
return school, year, ("no_link" if tried == 0 else "parse_fail"), 0, "", f"tried={tried} {last}"
def load_done():
"""已完成组合:库表 + 本地 CSV。"""
done = {}
try:
con = sqlite3.connect(f"file:{DB}?mode=ro", uri=True)
for s, y, n in con.execute(
"select school, year, count(*) from src_official_lines group by 1,2"):
done[(s, str(y))] = n
con.close()
except sqlite3.OperationalError:
pass
for f in os.path.join(OUT_DIR), os.path.join(OUT_DIR, "2023复试线"), os.path.join(OUT_DIR, "2026复试线"):
if not os.path.isdir(f):
continue
for name in os.listdir(f):
if not name.endswith(".csv") or name.startswith("_"):
continue
flag = os.path.join(f, name)
try:
with open(flag, encoding="utf-8-sig") as fh:
rows = list(csv.DictReader(fh))
if rows:
s = (rows[0].get("院校") or "").strip()
y = str(rows[0].get("年份") or "").strip()
if s and y:
done[(s, y)] = max(done.get((s, y), 0), len(rows))
except Exception:
continue
return done
def load_status():
st = {}
if os.path.exists(STATUS):
with open(STATUS, encoding="utf-8-sig") as f:
for r in csv.DictReader(f):
st[(r.get("院校", ""), str(r.get("年份", "")))] = r
return st
def load_ledger(priority, year):
if not os.path.exists(LEDGER):
sys.exit(f"台账不存在,先跑 gap_ledger.py:{LEDGER}")
out = []
with open(LEDGER, encoding="utf-8-sig") as f:
for r in csv.DictReader(f):
if priority and r["优先级"] != priority:
continue
if year and r["年份"] != year:
continue
out.append(r)
return out
def load_located():
"""已人工/工具定位好的页面:(院校, 年份) -> URL。"""
if not os.path.exists(LOCATED):
return {}
out = {}
with open(LOCATED, encoding="utf-8-sig") as f:
for r in csv.DictReader(f):
if r.get("院校") and r.get("年份") and r.get("URL"):
out[(r["院校"], str(r["年份"]))] = r["URL"].strip()
return out
def main():
ap = argparse.ArgumentParser()
ap.add_argument("--priority", default="P1")
ap.add_argument("--year", default="")
ap.add_argument("--limit", type=int, default=0)
ap.add_argument("--dry-run", action="store_true")
ap.add_argument("--force", action="store_true", help="忽略状态表里的 ok,强制重试")
ap.add_argument("--urls", action="store_true",
help="只处理已定位清单里的 URL,不做检索")
ap.add_argument("--direct", action="store_true",
help="直接解析台账里的入口/栏目页,不检索")
a = ap.parse_args()
ledger = load_ledger(a.priority, a.year)
done = load_done()
status = load_status()
located = load_located() if a.urls else {}
todo, skip = [], {"in_db": 0, "status_ok": 0, "no_url": 0}
for r in ledger:
k = (r["院校"], r["年份"])
if k in status and status[k].get("状态") == "ok" and not a.force:
skip["status_ok"] += 1
continue
if done.get(k, 0) > 0:
skip["in_db"] += 1
continue
urls = []
if a.urls:
if k not in located:
skip["no_url"] += 1
continue
urls = [located[k]]
else:
if r.get("入口URL"):
urls.append(r["入口URL"])
if r.get("栏目URL"):
try:
urls += [u for u in json.loads(r["栏目URL"]) if isinstance(u, str)]
except Exception:
pass
todo.append((r["院校"], r["年份"], r.get("官网域名", ""), urls))
if a.limit:
todo = todo[:a.limit]
log(f"台账 {len(ledger)} 条 → 跳过 {sum(skip.values())}(状态ok {skip['status_ok']} / "
f"已入库 {skip['in_db']} / 未定位 {skip['no_url']})→ 本次待抓 {len(todo)},并发 {WORKERS}")
if a.dry_run:
for s, y, d, _ in todo:
log(f" [待抓] {y} {s:16s} {(u[0] if u else '(无URL)')}")
return
if not todo:
log("没有待抓项,无需运行")
return
t0 = time.time()
results = []
with ThreadPoolExecutor(max_workers=WORKERS) as ex:
if a.urls:
futs = {ex.submit(handle, s, y, d, [], u[0]): (s, y) for s, y, d, u in todo}
elif a.direct:
futs = {ex.submit(handle_direct, s, y, d, u): (s, y) for s, y, d, u in todo}
else:
futs = {ex.submit(handle, s, y, d, u): (s, y) for s, y, d, u in todo}
for i, fu in enumerate(as_completed(futs), 1):
s, y, status_code, n, url, note = fu.result()
results.append([s, y, status_code, n, url, note])
log(f"[{i}/{len(todo)}] {y} {s:16s} {status_code:10s} {n:4d} 行 {note}")
# 状态表合并:保留历史 ok 记录,按 (院校,年份) 覆盖
for r in results:
status[(r[0], r[1])] = {"院校": r[0], "年份": r[1], "状态": r[2],
"行数": r[3], "采用URL": r[4], "备注": r[5]}
rows_out = sorted(status.values(), key=lambda r: (str(r["年份"]), r["院校"]))
with open(STATUS, "w", newline="", encoding="utf-8-sig") as f:
w = csv.DictWriter(f, fieldnames=["院校", "年份", "状态", "行数", "采用URL", "备注"])
w.writeheader()
for r in rows_out:
w.writerow({k: r.get(k, "") for k in w.fieldnames})
ok = [r for r in results if r[2] == "ok"]
log(f"\n成功 {len(ok)}/{len(todo)} 校,新增 {sum(r[3] for r in ok)} 行,"
f"耗时 {time.time() - t0:.0f}s")
log(f"状态表 -> {STATUS}")
fail = [r for r in results if r[2] != "ok"]
if fail:
log("未成功:" + ", ".join(f"{r[1]} {r[0]}({r[2]})" for r in fail[:12]))
if __name__ == "__main__":
main()