ChraKIs/YouTube-research
0
1"""2YouTube Comment Research Tool — by Fynn3"""4import os5import csv6import io7import json8import uuid9import threading10import time11from datetime import datetime, timedelta12 13import requests14import xml.etree.ElementTree as ET15import glob as _glob16 17import logging18logging.basicConfig(level=logging.INFO, format='[%(levelname)s] %(message)s')19logger = logging.getLogger(__name__)20 21from flask import Flask, render_template, request, jsonify, send_file, redirect22 23# ── 可选依赖（不阻塞启动）──24try:25 import yt_dlp26 _HAS_YTDLP = True27except ImportError:28 _HAS_YTDLP = False29 logger.warning("yt-dlp 未安装，字幕方案 B 不可用")30 31try:32 from deep_translator import GoogleTranslator33 _HAS_TRANSLATOR = True34except ImportError:35 _HAS_TRANSLATOR = False36 logger.warning("deep-translator 未安装，翻译功能不可用")37 38app = Flask(__name__)39app.config['MAX_CONTENT_LENGTH'] = 16 * 1024 * 1024 # 16MB max upload40 41# ── 请求耗时日志 ──42@app.before_request43def _log_request_start():44 request._start_time = time.time()45 46@app.after_request47def _log_request_end(response):48 if hasattr(request, '_start_time'):49 elapsed = time.time() - request._start_time50 if elapsed > 3.0:51 logger.warning(f"slow request: {request.method} {request.path} took {elapsed:.1f}s")52 return response53 54# ── 统一错误响应 ──55def _err(msg: str, code: int = 400):56 return jsonify({"ok": False, "error": msg}), code57 58# ── 配置（仅从环境变量读取，无默认值暴露）──59API_KEY = os.getenv("YOUTUBE_API_KEY")60BASE_URL = os.getenv("YOUTUBE_BASE_URL", "https://www.googleapis.com/youtube/v3")61CAPTION_PROXY = os.getenv("CAPTION_PROXY", "")62ADMIN_PASSWORD = os.getenv("ADMIN_PASSWORD", "admin123")63 64# ── 激活码存储 ──65CODES_FILE = os.path.join(os.path.dirname(os.path.abspath(__file__)), "codes.json")66_codes_lock = threading.Lock()67 68def _load_codes():69 try:70 with open(CODES_FILE, "r") as f:71 return json.load(f)72 except (FileNotFoundError, json.JSONDecodeError):73 return {}74 75def _save_codes(codes):76 with open(CODES_FILE, "w") as f:77 json.dump(codes, f, ensure_ascii=False, indent=2)78 79def _get_codes():80 with _codes_lock:81 return _load_codes()82 83def _update_codes(fn):84 with _codes_lock:85 codes = _load_codes()86 codes = fn(codes)87 _save_codes(codes)88 return codes89 90# 确保 /data 目录存在91# 启动时初始化92if not os.path.exists(CODES_FILE):93 _save_codes({})94 95 96# ── 配额保护：检测到 quotaExceeded 后全局标记，避免浪费后续请求 ──97_quota_exhausted = False98 99def _youtube_get(path: str, key_override: str = None, retries: int = 3) -> dict:100 global _quota_exhausted101 if _quota_exhausted:102 raise RuntimeError("YouTube API 配额已用完（本次运行期间已检测到）")103 104 key = key_override or API_KEY105 if not key:106 raise RuntimeError("YOUTUBE_API_KEY 未配置")107 url = f"{BASE_URL}/{path}&key={key}"108 last_err = None109 for attempt in range(retries):110 try:111 resp = requests.get(url, timeout=20)112 if resp.status_code == 403 and "quotaExceeded" in resp.text:113 _quota_exhausted = True114 raise RuntimeError("YouTube API 配额已用完，请明天再试或更换 API Key")115 resp.raise_for_status()116 return resp.json()117 except requests.exceptions.Timeout as e:118 last_err = e119 if attempt < retries - 1:120 time.sleep(1.5 * (attempt + 1))121 except requests.exceptions.HTTPError as e:122 if resp.status_code == 429 and attempt < retries - 1:123 time.sleep(3 * (attempt + 1))124 continue125 raise126 except requests.exceptions.RequestException as e:127 last_err = e128 if attempt < retries - 1:129 time.sleep(1 * (attempt + 1))130 raise RuntimeError(f"YouTube API 请求失败（重试{retries}次）: {last_err}")131 132 133def _format_duration(iso: str) -> str:134 """PT1H23M45S → 1:23:45"""135 import re136 if not iso:137 return ""138 m = re.match(r'PT(?:(\d+)H)?(?:(\d+)M)?(?:(\d+)S)?', iso)139 if not m:140 return ""141 h, mm, s = m.groups()142 parts = []143 if h: parts.append(h)144 parts.append(mm or "0")145 sec = s if s else "0"146 parts.append(sec.zfill(2))147 if len(parts) == 3:148 return f"{parts[0]}:{parts[1]}:{parts[2]}"149 return f"{parts[0]}:{parts[1]}"150 151 152# ═══════════════════════════════════════153# 激活码 API154# ═══════════════════════════════════════155 156@app.route("/api/verify-code", methods=["POST"])157def api_verify_code():158 data = request.get_json()159 code = (data.get("code") or "").strip().upper()160 if not code:161 return jsonify({"ok": False, "error": "请输入激活码"}), 400162 163 codes = _get_codes()164 entry = codes.get(code)165 166 if not entry:167 return jsonify({"ok": False, "error": "激活码无效"}), 403168 169 if not entry.get("active", True):170 return jsonify({"ok": False, "error": "激活码已禁用"}), 403171 172 if entry.get("used", False):173 return jsonify({"ok": False, "error": "激活码已被使用"}), 403174 175 expires = entry.get("expires_at")176 if expires:177 try:178 exp = datetime.fromisoformat(expires)179 if datetime.now() > exp:180 return jsonify({"ok": False, "error": "激活码已过期"}), 403181 except ValueError:182 pass183 184 # 标记为已使用185 def _mark_used(codes):186 if code in codes:187 codes[code]["used"] = True188 return codes189 _update_codes(_mark_used)190 191 return jsonify({"ok": True, "expires_at": expires})192 193 194# ═══════════════════════════════════════195# 管理面板196# ═══════════════════════════════════════197 198@app.route("/admin")199def admin_panel():200 return render_template("admin.html")201 202 203@app.route("/api/admin/login", methods=["POST"])204def admin_login():205 data = request.get_json()206 pw = data.get("password", "")207 if pw == ADMIN_PASSWORD:208 return jsonify({"ok": True, "token": "admin_session"})209 return jsonify({"ok": False, "error": "密码错误"}), 403210 211 212@app.route("/api/admin/codes", methods=["GET"])213def admin_list_codes():214 codes = _get_codes()215 items = []216 for code, entry in codes.items():217 items.append({218 "code": code,219 "active": entry.get("active", True),220 "used": entry.get("used", False),221 "created_at": entry.get("created_at", ""),222 "expires_at": entry.get("expires_at", ""),223 "note": entry.get("note", ""),224 })225 items.sort(key=lambda x: x["created_at"], reverse=True)226 return jsonify({"codes": items})227 228 229@app.route("/api/admin/codes/create", methods=["POST"])230def admin_create_code():231 data = request.get_json()232 note = data.get("note", "").strip()233 days = int(data.get("days", 30))234 235 # 生成 8 位大写激活码236 code = uuid.uuid4().hex[:8].upper()237 now = datetime.now()238 expires = now + timedelta(days=days)239 240 def _add(codes):241 codes[code] = {242 "active": True,243 "used": False,244 "created_at": now.isoformat(),245 "expires_at": expires.isoformat(),246 "note": note,247 }248 return codes249 250 _update_codes(_add)251 return jsonify({"ok": True, "code": code, "expires_at": expires.isoformat()})252 253 254@app.route("/api/admin/codes/toggle", methods=["POST"])255def admin_toggle_code():256 data = request.get_json()257 code = (data.get("code") or "").strip().upper()258 259 def _toggle(codes):260 if code in codes:261 codes[code]["active"] = not codes[code].get("active", True)262 return codes263 264 _update_codes(_toggle)265 codes = _get_codes()266 entry = codes.get(code, {})267 return jsonify({"ok": True, "active": entry.get("active", True)})268 269 270@app.route("/api/admin/codes/delete", methods=["POST"])271def admin_delete_code():272 data = request.get_json()273 code = (data.get("code") or "").strip().upper()274 275 def _del(codes):276 codes.pop(code, None)277 return codes278 279 _update_codes(_del)280 return jsonify({"ok": True})281 282 283@app.route("/api/admin/codes/update", methods=["POST"])284def admin_update_code():285 data = request.get_json()286 code = (data.get("code") or "").strip().upper()287 days = data.get("days")288 289 def _upd(codes):290 if code in codes and days:291 now = datetime.now()292 codes[code]["expires_at"] = (now + timedelta(days=int(days))).isoformat()293 return codes294 295 _update_codes(_upd)296 return jsonify({"ok": True})297 298 299 300@app.route("/api/admin/codes/reset", methods=["POST"])301def admin_reset_code():302 data = request.get_json()303 code = (data.get("code") or "").strip().upper()304 305 def _reset(codes):306 if code in codes:307 codes[code]["used"] = False308 return codes309 310 _update_codes(_reset)311 return jsonify({"ok": True})312 313 314 315# ═══════════════════════════════════════316# 字幕提取317# ═══════════════════════════════════════318 319def _extract_captions(video_id, timeout_sec: int = 30):320 """字幕提取：youtube_transcript_api 优先 → yt-dlp → timedtext API（总超时{timeout_sec}s）"""321 import concurrent.futures322 import html as html_mod323 324 def _do_extract():325 return _extract_captions_inner(video_id, html_mod)326 327 try:328 with concurrent.futures.ThreadPoolExecutor(max_workers=1) as executor:329 future = executor.submit(_do_extract)330 return future.result(timeout=timeout_sec)331 except concurrent.futures.TimeoutError:332 logger.warning(f"captions: timed out after {timeout_sec}s for {video_id}")333 return None334 except Exception:335 return None336 337 338def _extract_captions_inner(video_id, html_mod):339 340 segments = None341 lang = "en"342 343 # === 方案 A: youtube_transcript_api（最可靠，无需 PO token）===344 try:345 from youtube_transcript_api import YouTubeTranscriptApi346 api = YouTubeTranscriptApi()347 transcripts = api.list(video_id)348 # 优先英文手动字幕 → 英文自动字幕 → 其他349 target = None350 for t in transcripts:351 if t.language_code.startswith("en") and not t.is_generated:352 target = t353 break354 if not target:355 for t in transcripts:356 if t.language_code.startswith("en") and t.is_generated:357 target = t358 break359 if not target:360 for t in transcripts:361 target = t362 break363 if target:364 fetched = target.fetch()365 lang = target.language_code366 segments = []367 for s in fetched:368 segments.append({369 "text": html_mod.unescape(s.text),370 "start": round(s.start, 1),371 "duration": round(s.duration, 1),372 })373 except Exception as e:374 logger.info(f"captions: transcript_api failed for {video_id}: {e}")375 376 # === 方案 B: yt-dlp 下载字幕 ===377 if not segments and _HAS_YTDLP:378 ydl_opts = {379 'skip_download': True,380 'writesubtitles': True,381 'writeautomaticsub': True,382 'subtitleslangs': ['en'],383 'subtitlesformat': 'ttml',384 'outtmpl': {'default': f'/tmp/ytdl_{video_id}.%(ext)s'},385 'quiet': True,386 'no_warnings': True,387 'socket_timeout': 20,388 'retries': 2,389 }390 if CAPTION_PROXY:391 ydl_opts['proxy'] = CAPTION_PROXY392 393 try:394 with yt_dlp.YoutubeDL(ydl_opts) as ydl:395 ydl.extract_info(f'https://www.youtube.com/watch?v={video_id}', download=True)396 for path in sorted(_glob.glob(f'/tmp/ytdl_{video_id}*.ttml')):397 try:398 with open(path, 'r') as f:399 xml_text = f.read()400 os.remove(path)401 if len(xml_text) > 50:402 segs = _parse_ttml(xml_text, html_mod)403 if segs:404 segments = segs405 break406 except Exception:407 try: os.remove(path)408 except: pass409 except Exception as e:410 logger.info(f"captions: yt-dlp failed for {video_id}: {e}")411 412 # === 方案 C: timedtext API 直连 ===413 if not segments:414 UA = "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36"415 for ln in ["en", "en-US", "en-GB"]:416 try:417 proxies = {"http": CAPTION_PROXY, "https": CAPTION_PROXY} if CAPTION_PROXY else None418 r = requests.get(419 f"https://www.youtube.com/api/timedtext?v={video_id}&lang={ln}",420 headers={"User-Agent": UA}, timeout=10, proxies=proxies421 )422 if r.status_code == 200 and len(r.text) > 50:423 segments = _parse_ttml(r.text, html_mod)424 lang = ln425 break426 except Exception as e:427 logger.info(f"captions: timedtext failed for {video_id} ({ln}): {e}")428 429 if not segments:430 logger.warning(f"captions: all 3 methods failed for {video_id}")431 return None432 433 return {434 "video_id": video_id,435 "language": lang,436 "segments": segments,437 "total": len(segments),438 }439 440 441def _parse_ttml(xml_text, html_mod):442 try:443 root = ET.fromstring(xml_text)444 segments = []445 for p in root.iter("{http://www.w3.org/ns/ttml}p"):446 begin = p.attrib.get("begin", "0")447 dur = p.attrib.get("dur", "1")448 text = "".join(p.itertext()).strip()449 if text:450 s = _parse_ttml_time(begin)451 d = _parse_ttml_time(dur)452 segments.append({"text": html_mod.unescape(text), "start": round(s, 1), "duration": round(d, 1)})453 return segments454 except ET.ParseError as e:455 logger.info(f"ttml parse error: {e}")456 return []457 except Exception as e:458 logger.error(f"ttml parse unexpected error: {e}")459 return []460 461 462def _parse_ttml_time(t):463 t = t.strip()464 if ":" in t:465 parts = t.split(":")466 if len(parts) == 3:467 return int(parts[0]) * 3600 + int(parts[1]) * 60 + float(parts[2])468 elif len(parts) == 2:469 return int(parts[0]) * 60 + float(parts[1])470 return float(t) if t else 0471 472 473@app.route("/api/captions")474def api_captions():475 video_id = request.args.get("video_id", "").strip()476 if not video_id:477 return jsonify({"error": "缺少 video_id"}), 400478 result = _extract_captions(video_id)479 if result is None:480 return jsonify({"error": "字幕获取失败（视频可能无字幕或被封锁）"}), 404481 return jsonify({482 "video_id": video_id,483 "language": result["language"],484 "segments": result["segments"],485 "total_segments": result["total"],486 })487 488 489@app.route("/api/export/transcript", methods=["POST"])490def api_export_transcript():491 data = request.get_json()492 video_ids = data.get("video_ids", [])493 fmt = data.get("format", "csv")494 structure = data.get("structure", "flat")495 if not video_ids:496 return jsonify({"error": "未选择视频"}), 400497 498 videos = []499 for vid in video_ids:500 try:501 vdata = _youtube_get(f"videos?part=snippet&id={vid}")502 except Exception:503 continue504 items = vdata.get("items", [])505 if not items:506 continue507 title = items[0]["snippet"]["title"]508 result = _extract_captions(vid)509 if not result:510 continue511 segs = []512 for seg in result["segments"]:513 ss = int(seg["start"])514 mm = ss // 60; ss2 = ss % 60515 ts = f"{mm:02d}:{ss2:02d}"516 link = f"https://www.youtube.com/watch?v={vid}&t={ss}"517 segs.append({"timestamp": ts, "duration": round(seg["duration"], 1), "text": seg["text"], "link": link})518 videos.append({"video_id": vid, "title": title, "segments": segs})519 520 if not videos:521 return jsonify({"error": "所选视频无字幕"}), 404522 523 date_str = datetime.now().strftime("%Y-%m-%d")524 filename = f"transcript_{date_str}"525 526 if fmt == "xlsx":527 try:528 from openpyxl import Workbook; from openpyxl.styles import Font, PatternFill529 except ImportError:530 return jsonify({"error": "需要openpyxl"}), 500531 wb = Workbook(); hf = Font(bold=True, color="FFFFFF"); hfill = PatternFill(start_color="1a1a1a", end_color="1a1a1a", fill_type="solid")532 if structure == "per_video":533 first = True534 for v in videos:535 name = v["title"][:31].replace("/","-")536 ws = wb.active if first else wb.create_sheet(name); first = False537 ws.title = name538 for col, h in enumerate(["Timestamp", "Duration", "Transcript", "Video Link"], 1):539 cell = ws.cell(row=1, column=col, value=h); cell.font = hf; cell.fill = hfill540 for r, s in enumerate(v["segments"], 2):541 for ci, val in enumerate([s["timestamp"], s["duration"], s["text"], s["link"]], 1):542 ws.cell(row=r, column=ci, value=val)543 ws.column_dimensions["A"].width = 10; ws.column_dimensions["B"].width = 8544 ws.column_dimensions["C"].width = 60; ws.column_dimensions["D"].width = 40545 else:546 ws = wb.active; ws.title = "字幕"547 for col, h in enumerate(["Video Title", "Video ID", "Timestamp", "Duration", "Transcript", "Video Link"], 1):548 cell = ws.cell(row=1, column=col, value=h); cell.font = hf; cell.fill = hfill549 r = 2550 for v in videos:551 for s in v["segments"]:552 for ci, val in enumerate([v["title"], v["video_id"], s["timestamp"], s["duration"], s["text"], s["link"]], 1):553 ws.cell(row=r, column=ci, value=val)554 r += 1555 for col, w in enumerate([40, 14, 10, 8, 60, 40], 1):556 ws.column_dimensions[chr(64+col)].width = w557 buf = io.BytesIO(); wb.save(buf); buf.seek(0)558 return send_file(buf, mimetype="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", as_attachment=True, download_name=f"{filename}.xlsx")559 elif fmt == "json":560 if structure == "per_video":561 result = {"exported_at": datetime.now().isoformat(), "videos": []}562 for v in videos:563 result["videos"].append({"title": v["title"], "video_id": v["video_id"], "segments": v["segments"]})564 else:565 result = {"exported_at": datetime.now().isoformat(), "transcripts": []}566 for v in videos:567 for s in v["segments"]:568 result["transcripts"].append({"video_title": v["title"], "video_id": v["video_id"],569 "timestamp": s["timestamp"], "duration": s["duration"], "text": s["text"], "link": s["link"]})570 buf = io.BytesIO(); buf.write(json.dumps(result, ensure_ascii=False, indent=2).encode()); buf.seek(0)571 return send_file(buf, mimetype="application/json", as_attachment=True, download_name=f"{filename}.json")572 else:573 buf = io.StringIO(); buf.write("\ufeff")574 writer = csv.writer(buf)575 if structure == "per_video":576 for v in videos:577 writer.writerow([f"=== {v['title']} | https://www.youtube.com/watch?v={v['video_id']} ==="])578 writer.writerow(["Timestamp", "Duration", "Transcript", "Video Link"])579 for s in v["segments"]:580 writer.writerow([s["timestamp"], s["duration"], s["text"], s["link"]])581 writer.writerow([])582 else:583 writer.writerow(["Video Title", "Video ID", "Timestamp", "Duration", "Transcript", "Video Link"])584 for v in videos:585 for s in v["segments"]:586 writer.writerow([v["title"], v["video_id"], s["timestamp"], s["duration"], s["text"], s["link"]])587 buf.seek(0)588 return send_file(io.BytesIO(buf.getvalue().encode("utf-8")), mimetype="text/csv", as_attachment=True, download_name=f"{filename}.csv")589 590 591 592# ═══════════════════════════════════════593# 翻译 API594# ═══════════════════════════════════════595 596@app.route("/api/translate", methods=["POST"])597def api_translate():598 """批量翻译文本"""599 data = request.get_json()600 texts = data.get("texts", [])601 target = data.get("target", "zh-CN")602 if not texts:603 return jsonify({"error": "没有待翻译文本"}), 400604 try:605 translator = GoogleTranslator(source="auto", target=target)606 results = []607 for text in texts:608 if not text or not text.strip():609 results.append("")610 continue611 try:612 # 限制单条长度避免超时613 chunk = text[:1500]614 results.append(translator.translate(chunk))615 except Exception:616 results.append(text) # 翻译失败保留原文617 return jsonify({"translations": results, "target": target})618 except Exception as e:619 return jsonify({"error": f"翻译服务不可用: {str(e)}"}), 500620 621 622# ═══════════════════════════════════════623# 主页面624# ═══════════════════════════════════════625 626@app.route("/")627def index():628 return render_template("index.html")629 630 631# ── 共享：拉取单个视频元信息 ──632def _fetch_video_info(vid: str, user_key: str = None) -> dict | None:633 """拉取视频元信息，失败返回 None"""634 try:635 vdata = _youtube_get(f"videos?part=snippet,statistics,contentDetails&id={vid}",636 key_override=user_key, retries=2)637 except Exception as e:638 logger.warning(f"video_info: failed for {vid}: {e}")639 return None640 items = vdata.get("items", [])641 if not items:642 return None643 info = items[0]644 snippet = info["snippet"]645 stats = info.get("statistics", {})646 cd = info.get("contentDetails", {})647 return {648 "video_id": vid,649 "title": snippet["title"],650 "channel": snippet["channelTitle"],651 "published_at": snippet["publishedAt"],652 "views": int(stats.get("viewCount", 0)),653 "url": f"https://www.youtube.com/watch?v={vid}",654 "duration": _format_duration(cd.get("duration", "")),655 "has_captions": cd.get("caption", "false") == "true",656 "comment_count": int(stats.get("commentCount", 0)),657 }658 659# ── 共享：拉取视频评论 ──660def _fetch_video_comments(vid: str, videos: list, min_likes: int = 0,661 user_key: str = None, fetch_transcripts: bool = False) -> int:662 """拉取单个视频的评论+可选字幕，追加到 videos 列表。返回实际添加的视频数(0或1)"""663 info = _fetch_video_info(vid, user_key)664 if not info:665 return 0666 667 comments = _fetch_comments(vid, min_likes=min_likes, key_override=user_key)668 if not comments and not fetch_transcripts:669 return 0670 671 video = {**info, "comments": comments, "transcripts": []}672 673 if fetch_transcripts:674 captions = _extract_captions(vid)675 if captions:676 for seg in captions["segments"]:677 ss = int(seg["start"])678 mm, ss2 = divmod(ss, 60)679 video["transcripts"].append({680 "timestamp": f"{mm:02d}:{ss2:02d}",681 "duration": round(seg["duration"], 1),682 "text": seg["text"],683 "link": f"https://www.youtube.com/watch?v={vid}&t={ss}",684 })685 686 if video["comments"] or video["transcripts"]:687 videos.append(video)688 return 1689 return 0690 691@app.route("/api/search")692def api_search():693 keyword = request.args.get("q", "").strip()694 page_token = request.args.get("pageToken", "")695 user_key = request.args.get("key", "").strip()696 if not keyword:697 return jsonify({"error": "关键词不能为空"}), 400698 699 path = f"search?part=snippet&order=date&q={keyword}&type=video&maxResults=50"700 if page_token:701 path += f"&pageToken={page_token}"702 703 key_override = user_key or None704 try:705 data = _youtube_get(path, key_override=key_override)706 except RuntimeError as e:707 err_msg = str(e)708 is_quota = "配额" in err_msg or "quota" in err_msg.lower()709 return jsonify({710 "error": err_msg,711 "quota_exhausted": is_quota,712 }), (429 if is_quota else 500)713 except Exception as e:714 return jsonify({"error": f"搜索失败: {str(e)}"}), 500715 716 videos = []717 video_ids = []718 for item in data.get("items", []):719 if item.get("id", {}).get("kind") != "youtube#video":720 continue721 s = item["snippet"]722 vid = item["id"]["videoId"]723 video_ids.append(vid)724 videos.append({725 "video_id": vid,726 "title": s["title"],727 "channel": s["channelTitle"],728 "published_at": s["publishedAt"],729 "thumbnail": s["thumbnails"]["default"]["url"],730 })731 732 if not video_ids:733 return jsonify({"videos": []})734 735 try:736 stats_data = _youtube_get(737 f"videos?part=statistics,contentDetails&id={','.join(video_ids)}",738 key_override=user_key or None739 )740 except Exception as e:741 return jsonify({"error": f"统计失败: {str(e)}"}), 500742 743 stats_map = {}744 for item in stats_data.get("items", []):745 s = item.get("statistics", {})746 cd = item.get("contentDetails", {})747 stats_map[item["id"]] = {748 "view_count": int(s.get("viewCount", 0)),749 "comment_count": int(s.get("commentCount", 0)),750 "duration": _format_duration(cd.get("duration", "")),751 "has_captions": cd.get("caption", "false") == "true",752 }753 754 for v in videos:755 st = stats_map.get(v["video_id"], {})756 v["view_count"] = st.get("view_count", 0)757 v["comment_count"] = st.get("comment_count", 0)758 v["duration"] = st.get("duration", "")759 v["has_captions"] = st.get("has_captions", False)760 761 videos.sort(key=lambda v: v["view_count"], reverse=True)762 763 return jsonify({764 "videos": videos,765 "nextPageToken": data.get("nextPageToken", ""),766 })767 768 769def _fetch_comments(vid: str, min_likes: int = 0, max_pages: int = 5, key_override: str = None) -> list[dict]:770 """拉取评论，支持翻页（每页100条，最多5页=500条），含重试"""771 comments = []772 page_token = ""773 for page_num in range(max_pages):774 try:775 path = f"commentThreads?part=snippet&videoId={vid}&maxResults=100&order=time"776 if page_token:777 path += f"&pageToken={page_token}"778 cdata = _youtube_get(path, key_override=key_override, retries=2)779 except Exception:780 if page_num == 0:781 raise # 第一页失败就抛出去，别静默吞掉782 break # 后续页失败则停止翻页，保留已有数据783 for item in cdata.get("items", []):784 top = item["snippet"]["topLevelComment"]["snippet"]785 likes = top.get("likeCount", 0)786 if likes < min_likes:787 continue788 comments.append({789 "author": top["authorDisplayName"],790 "text": top["textDisplay"],791 "likes": likes,792 "published_at": top["publishedAt"],793 })794 page_token = cdata.get("nextPageToken", "")795 if not page_token:796 break797 return comments798 799 800@app.route("/api/preview", methods=["POST"])801def api_preview():802 data = request.get_json()803 video_ids = data.get("video_ids", [])804 if not video_ids:805 return jsonify({"error": "参数错误"}), 400806 comments = _fetch_comments(video_ids[0])807 return jsonify({"comments": comments, "total": len(comments)})808 809 810@app.route("/api/export", methods=["POST"])811def api_export():812 data = request.get_json()813 video_ids = data.get("video_ids", [])814 fmt = data.get("format", "csv")815 min_likes = int(data.get("min_likes", 0))816 structure = data.get("structure", "flat")817 user_key = data.get("api_key", "").strip() or None818 819 if not video_ids:820 return _err("请选择至少一个视频")821 822 # ── 按视频拉取 ──823 videos = []824 stats = {"total_comments": 0, "total_likes": 0, "video_count": 0}825 826 for vid in video_ids:827 try:828 vdata = _youtube_get(f"videos?part=snippet,statistics&id={vid}", key_override=user_key, retries=2)829 except Exception as e:830 print(f"[export] video info failed for {vid}: {e}")831 continue832 items = vdata.get("items", [])833 if not items:834 continue835 info = items[0]836 title = info["snippet"]["title"]837 channel = info["snippet"]["channelTitle"]838 pub = info["snippet"]["publishedAt"]839 views = info["statistics"].get("viewCount", 0)840 url = f"https://www.youtube.com/watch?v={vid}"841 842 comments = _fetch_comments(vid, min_likes=min_likes, key_override=user_key)843 if not comments:844 continue845 846 stats["video_count"] += 1847 video = {848 "video_id": vid, "title": title, "channel": channel,849 "published_at": pub, "views": views, "url": url,850 "comments": comments,851 }852 videos.append(video)853 for c in comments:854 stats["total_comments"] += 1855 stats["total_likes"] += c["likes"]856 857 if not videos:858 is_quota = _quota_exhausted859 return jsonify({860 "error": "所选视频暂无数据",861 "detail": "可能原因：API 配额用完、视频评论已关闭、或网络异常，请稍后重试。",862 "quota_exhausted": is_quota,863 }), (429 if is_quota else 404)864 865 date_str = datetime.now().strftime("%Y-%m-%d")866 safe_title = "".join(c if c.isalnum() or c in "-_" else "" for c in videos[0]["title"][:30]).strip()[:30]867 filename = f"{safe_title}-Comments-{date_str}" if safe_title else f"comments_{date_str}"868 869 if fmt == "xlsx":870 return _export_comments_xlsx(videos, filename, structure)871 elif fmt == "json":872 return _export_comments_json(videos, filename, structure)873 else:874 return _export_comments_csv(videos, filename, structure)875 876 877def _export_comments_csv(videos, filename, structure):878 buf = io.StringIO()879 buf.write("\ufeff")880 writer = csv.writer(buf)881 882 if structure == "per_video":883 for v in videos:884 writer.writerow([f"=== {v['title']} | {v['url']} | {v['channel']} ==="])885 writer.writerow(["评论作者", "评论内容", "点赞数", "评论时间"])886 for c in v["comments"]:887 writer.writerow([c["author"], c["text"], c["likes"], c["published_at"]])888 writer.writerow([])889 else:890 writer.writerow(["评论作者", "评论内容", "点赞数", "评论时间",891 "视频标题", "视频链接", "频道", "视频播放量", "视频发布时间"])892 for v in videos:893 for c in v["comments"]:894 writer.writerow([c["author"], c["text"], c["likes"], c["published_at"],895 v["title"], v["url"], v["channel"], v["views"], v["published_at"]])896 897 buf.seek(0)898 return send_file(io.BytesIO(buf.getvalue().encode("utf-8")), mimetype="text/csv",899 as_attachment=True, download_name=f"{filename}.csv")900 901 902def _export_comments_xlsx(videos, filename, structure):903 try:904 from openpyxl import Workbook905 from openpyxl.styles import Font, PatternFill906 except ImportError:907 return jsonify({"error": "Excel 导出需要 openpyxl 库"}), 500908 909 wb = Workbook()910 hf = Font(bold=True, color="FFFFFF", size=11)911 hfill = PatternFill(start_color="1a1a1a", end_color="1a1a1a", fill_type="solid")912 913 if structure == "per_video":914 # 每个视频一个 Sheet915 first = True916 for v in videos:917 name = v["title"][:31].replace("/","-").replace("\\","-")918 if first:919 ws = wb.active; ws.title = name; first = False920 else:921 ws = wb.create_sheet(name)922 headers = ["评论作者", "评论内容", "点赞数", "评论时间"]923 for col, h in enumerate(headers, 1):924 cell = ws.cell(row=1, column=col, value=h)925 cell.font = hf; cell.fill = hfill926 for r, c in enumerate(v["comments"], 2):927 for ci, val in enumerate([c["author"], c["text"], c["likes"], c["published_at"]], 1):928 ws.cell(row=r, column=ci, value=val)929 ws.column_dimensions["A"].width = 18930 ws.column_dimensions["B"].width = 60931 ws.column_dimensions["C"].width = 10932 ws.column_dimensions["D"].width = 18933 else:934 # 统一汇总935 ws = wb.active; ws.title = "评论数据"936 headers = ["评论作者", "评论内容", "点赞数", "评论时间",937 "视频标题", "视频链接", "频道", "视频播放量", "视频发布时间"]938 for col, h in enumerate(headers, 1):939 cell = ws.cell(row=1, column=col, value=h)940 cell.font = hf; cell.fill = hfill941 r = 2942 for v in videos:943 for c in v["comments"]:944 for ci, val in enumerate([c["author"], c["text"], c["likes"], c["published_at"],945 v["title"], v["url"], v["channel"], v["views"], v["published_at"]], 1):946 ws.cell(row=r, column=ci, value=val)947 r += 1948 ws.column_dimensions["A"].width = 18949 ws.column_dimensions["B"].width = 60950 ws.column_dimensions["C"].width = 10951 ws.column_dimensions["D"].width = 18952 ws.column_dimensions["E"].width = 40953 ws.column_dimensions["F"].width = 40954 ws.column_dimensions["G"].width = 20955 ws.column_dimensions["H"].width = 14956 ws.column_dimensions["I"].width = 18957 958 buf = io.BytesIO(); wb.save(buf); buf.seek(0)959 return send_file(buf, mimetype="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",960 as_attachment=True, download_name=f"{filename}.xlsx")961 962 963def _export_comments_json(videos, filename, structure):964 if structure == "per_video":965 result = {"exported_at": datetime.now().isoformat(), "videos": []}966 for v in videos:967 result["videos"].append({968 "title": v["title"], "url": v["url"], "channel": v["channel"],969 "views": v["views"], "published_at": v["published_at"],970 "comment_count": len(v["comments"]),971 "comments": [{"author": c["author"], "text": c["text"],972 "likes": c["likes"], "published_at": c["published_at"]} for c in v["comments"]],973 })974 else:975 result = {"exported_at": datetime.now().isoformat(), "comments": []}976 for v in videos:977 for c in v["comments"]:978 result["comments"].append({979 "author": c["author"], "text": c["text"], "likes": c["likes"],980 "published_at": c["published_at"], "video_title": v["title"],981 "video_url": v["url"], "channel": v["channel"],982 "video_views": v["views"], "video_published": v["published_at"],983 })984 buf = io.BytesIO()985 buf.write(json.dumps(result, ensure_ascii=False, indent=2).encode("utf-8"))986 buf.seek(0)987 return send_file(buf, mimetype="application/json", as_attachment=True, download_name=f"{filename}.json")988 989 990@app.route("/api/export/stats", methods=["POST"])991def api_export_stats():992 data = request.get_json()993 video_ids = data.get("video_ids", [])994 min_likes = int(data.get("min_likes", 0))995 total_comments = 0996 total_likes = 0997 video_count = 0998 for vid in video_ids:999 comments = _fetch_comments(vid, min_likes=min_likes)1000 if comments:1001 video_count += 11002 total_comments += len(comments)1003 total_likes += sum(c["likes"] for c in comments)1004 return jsonify({1005 "video_count": video_count,1006 "total_comments": total_comments,1007 "total_likes": total_likes,1008 })1009 1010@app.route("/api/export/combined", methods=["POST"])1011def api_export_combined():1012 """合并导出：评论 + 字幕，按视频分组，可选翻译"""1013 data = request.get_json()1014 video_ids = data.get("video_ids", [])1015 fmt = data.get("format", "csv")1016 min_likes = int(data.get("min_likes", 0))1017 translate_to = data.get("translate_to", "")1018 user_key = data.get("api_key", "").strip() or None1019 1020 if not video_ids:1021 return _err("请选择至少一个视频")1022 1023 # ── 按视频拉取评论 + 字幕 ──1024 videos = []1025 for vid in video_ids:1026 try:1027 vdata = _youtube_get(f"videos?part=snippet,statistics&id={vid}", key_override=user_key, retries=2)1028 except Exception as e:1029 print(f"[export] video info failed for {vid}: {e}")1030 continue1031 items = vdata.get("items", [])1032 if not items:1033 continue1034 info = items[0]1035 title = info["snippet"]["title"]1036 channel = info["snippet"]["channelTitle"]1037 pub = info["snippet"]["publishedAt"]1038 views = info["statistics"].get("viewCount", 0)1039 url = f"https://www.youtube.com/watch?v={vid}"1040 1041 video = {1042 "video_id": vid,1043 "title": title,1044 "channel": channel,1045 "published_at": pub,1046 "views": views,1047 "url": url,1048 "comments": [],1049 "transcripts": [],1050 }1051 1052 # 评论1053 for c in _fetch_comments(vid, min_likes=min_likes):1054 video["comments"].append(c)1055 1056 # 字幕1057 result = _extract_captions(vid)1058 if result:1059 for seg in result["segments"]:1060 ss = int(seg["start"])1061 mm = ss // 601062 ss2 = ss % 601063 ts = f"{mm:02d}:{ss2:02d}"1064 link = f"https://www.youtube.com/watch?v={vid}&t={ss}"1065 video["transcripts"].append({1066 "timestamp": ts,1067 "duration": round(seg["duration"], 1),1068 "text": seg["text"],1069 "link": link,1070 })1071 1072 if video["comments"] or video["transcripts"]:1073 videos.append(video)1074 1075 # ── 翻译 ──1076 if translate_to and videos and _HAS_TRANSLATOR:1077 try:1078 translator = GoogleTranslator(source="auto", target=translate_to)1079 for v in videos:1080 for c in v.get("comments", []):1081 try:1082 c["text_translated"] = translator.translate(c["text"][:1500])1083 except Exception:1084 c["text_translated"] = c["text"]1085 for t in v.get("transcripts", []):1086 try:1087 t["text_translated"] = translator.translate(t["text"][:1500])1088 except Exception:1089 t["text_translated"] = t["text"]1090 except Exception:1091 pass # 翻译失败不影响导出1092 1093 if not videos:1094 return jsonify({"error": "所选视频暂无数据"}), 4041095 1096 date_str = datetime.now().strftime("%Y-%m-%d")1097 filename = f"youtube_data_{date_str}"1098 1099 structure = data.get("structure", "flat")1100 if fmt == "json":1101 return _export_combined_json(videos, filename)1102 elif fmt == "xlsx":1103 return _export_combined_xlsx(videos, filename, structure)1104 else:1105 return _export_combined_csv(videos, filename, structure)1106 1107 1108def _export_combined_csv(videos, filename, structure="flat"):1109 buf = io.StringIO()1110 buf.write("\ufeff")1111 writer = csv.writer(buf)1112 1113 if structure == "per_video":1114 for v in videos:1115 views_str = str(v['views']); writer.writerow([f"=== {v['title']} | {v['url']} | {v['channel']} | {views_str} 次播放 ==="])1116 if v["comments"]:1117 writer.writerow(["--- 评论 ({0} 条) ---".format(len(v["comments"]))])1118 writer.writerow(["评论作者", "评论内容", "点赞数", "评论时间"])1119 for c in v["comments"]:1120 writer.writerow([c["author"], c["text"], c["likes"], c["published_at"]])1121 if v["transcripts"]:1122 writer.writerow(["--- 字幕 ({0} 段) ---".format(len(v["transcripts"]))])1123 writer.writerow(["时间戳", "时长", "字幕内容"])1124 for t in v["transcripts"]:1125 writer.writerow([t["timestamp"], t["duration"], t["text"]])1126 writer.writerow([])1127 else:1128 has_trans = any(c.get("text_translated") for v in videos for c in v.get("comments",[])) or any(t.get("text_translated") for v in videos for t in v.get("transcripts",[]))1129 headers = ["视频标题", "视频链接", "频道", "视频播放量", "视频发布时间",1130 "类型", "作者/时间戳", "内容", "点赞/时长", "发布时间"]1131 if has_trans:1132 headers.insert(8, "翻译")1133 writer.writerow(headers)1134 for v in videos:1135 for c in v["comments"]:1136 row = [v["title"], v["url"], v["channel"], v["views"], v["published_at"],1137 "评论", c["author"], c["text"], c["likes"], c["published_at"]]1138 if has_trans:1139 row.insert(8, c.get("text_translated", ""))1140 writer.writerow(row)1141 for t in v["transcripts"]:1142 row = [v["title"], v["url"], v["channel"], v["views"], v["published_at"],1143 "字幕", t["timestamp"], t["text"], t["duration"], ""]1144 if has_trans:1145 row.insert(8, t.get("text_translated", ""))1146 writer.writerow(row)1147 1148 buf.seek(0)1149 return send_file(io.BytesIO(buf.getvalue().encode("utf-8")), mimetype="text/csv",1150 as_attachment=True, download_name=f"{filename}.csv")1151 1152 1153def _export_combined_xlsx(videos, filename, structure="flat"):1154 try:1155 from openpyxl import Workbook1156 from openpyxl.styles import Font, PatternFill1157 except ImportError:1158 return jsonify({"error": "Excel 导出需要 openpyxl 库"}), 5001159 1160 wb = Workbook()1161 hf = Font(bold=True, color="FFFFFF", size=11)1162 hfill = PatternFill(start_color="1a1a1a", end_color="1a1a1a", fill_type="solid")1163 1164 if structure == "per_video":1165 # 每个视频一个 Sheet，内含评论+字幕两个区域1166 first = True1167 for vi, v in enumerate(videos):1168 name = v["title"][:28].replace("/","-").replace("\\","-")1169 ws = wb.active if first else wb.create_sheet(name)1170 if first: ws.title = name; first = False1171 # 评论区域1172 r = 11173 if v["comments"]:1174 for col, h in enumerate(["评论作者", "评论内容", "点赞数", "评论时间"], 1):1175 cell = ws.cell(row=r, column=col, value=h); cell.font = hf; cell.fill = hfill1176 r += 11177 for c in v["comments"]:1178 for ci, val in enumerate([c["author"], c["text"], c["likes"], c["published_at"]], 1):1179 ws.cell(row=r, column=ci, value=val)1180 r += 11181 r += 1 # spacer1182 # 字幕区域1183 if v["transcripts"]:1184 for col, h in enumerate(["时间戳", "时长", "字幕内容", "链接"], 1):1185 cell = ws.cell(row=r, column=col, value=h); cell.font = hf; cell.fill = hfill1186 r += 11187 for t in v["transcripts"]:1188 for ci, val in enumerate([t["timestamp"], t["duration"], t["text"], t["link"]], 1):1189 ws.cell(row=r, column=ci, value=val)1190 r += 11191 ws.column_dimensions["A"].width = 181192 ws.column_dimensions["B"].width = 601193 ws.column_dimensions["C"].width = 101194 ws.column_dimensions["D"].width = 181195 buf = io.BytesIO(); wb.save(buf); buf.seek(0)1196 return send_file(buf, mimetype="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",1197 as_attachment=True, download_name=f"{filename}.xlsx")1198 1199 # ── 汇总 Sheet（统一扁平）──1200 ws0 = wb.active