#!/usr/bin/env python3 """修复 yanshougs.com 的 null号 记录 — 批量重爬详情页并同步到服务器""" import asyncio, sys, os, subprocess from playwright.async_api import async_playwright SITE_NAME = "工程建设验收公示网" BASE_DIR = os.path.dirname(os.path.abspath(__file__)) sys.path.insert(0, BASE_DIR) from base_crawler import clean_noisy_html, extract_plain_text def sq(v): # SQL quote if v is None: return "''" return "'" + str(v).replace("'", "''") + "'" async def main(): bad_urls = [ "https://www.yanshougs.com/content/107327.html", "https://www.yanshougs.com/content/95561.html", "https://www.yanshougs.com/content/95564.html", "https://www.yanshougs.com/content/95572.html", "https://www.yanshougs.com/content/95634.html", "https://www.yanshougs.com/content/95641.html", "https://www.yanshougs.com/content/95652.html", "https://www.yanshougs.com/content/95744.html", "https://www.yanshougs.com/content/95745.html", "https://www.yanshougs.com/content/95807.html", "https://www.yanshougs.com/content/95811.html", "https://www.yanshougs.com/content/95813.html", "https://www.yanshougs.com/content/95828.html", "https://www.yanshougs.com/content/95846.html", "https://www.yanshougs.com/content/95872.html", "https://www.yanshougs.com/content/95873.html", "https://www.yanshougs.com/content/95879.html", "https://www.yanshougs.com/content/95889.html", "https://www.yanshougs.com/content/95891.html", "https://www.yanshougs.com/content/95916.html", "https://www.yanshougs.com/content/95979.html", "https://www.yanshougs.com/content/95981.html", "https://www.yanshougs.com/content/96001.html", "https://www.yanshougs.com/content/96017.html", "https://www.yanshougs.com/content/96064.html", "https://www.yanshougs.com/content/96309.html", ] print(f"📋 需修复 {len(bad_urls)} 条") async with async_playwright() as pw: browser = await pw.chromium.launch( headless=True, args=["--no-sandbox", "--disable-setuid-sandbox", "--disable-dev-shm-usage", "--disable-gpu"], ) fixed = 0 failed = 0 sql_lines = [] for url in bad_urls: page = await browser.new_page() try: # 加载详情页（3次重试） ok = False for attempt in range(3): try: await page.goto(url, timeout=30000, wait_until="networkidle") await page.wait_for_timeout(2000) ok = True break except Exception as e: if attempt < 2: await asyncio.sleep(3) else: print(f" ✗ {url[-20:]}: 3次加载失败: {str(e)[:60]}") if not ok: failed += 1 continue # 提取标题 title_el = await page.query_selector(".dir_c_title") title = (await title_el.inner_text()).strip() if title_el else "" if not title: print(f" ⚠️ 空标题: {url[-20:]}") failed += 1 continue # 提取时间 time_el = await page.query_selector(".dir_c_time") date = (await time_el.inner_text()).strip().replace("发布时间：", "") if time_el else "" # 提取正文 content_div = await page.query_selector(".dir_c_content") content = await content_div.inner_html() if content_div else "" content = clean_noisy_html(content) text = extract_plain_text(content) # 提取建设单位/地点 builder, location = "", "" for line in text.split("\n"): if line.startswith("建设单位："): builder = line.replace("建设单位：", "").strip() elif line.startswith("建设地点："): location = line.replace("建设地点：", "").strip() # 附件 attachments = [] for a in await page.query_selector_all(".attachment a, a[href*='.pdf']"): href = await a.get_attribute("href") name = (await a.inner_text()).strip() or (href.split("/")[-1] if href else "附件") if href: full = f"https://www.yanshougs.com{href}" if href.startswith("/") else href attachments.append(f"{name}: {full}") if not attachments: for line in text.split("\n"): if line.startswith("附件"): attachments.append(line) summary = text[:500] if attachments: summary += "\n" + "\n".join(attachments) # SQL sql_lines.append( f"UPDATE gov_raw SET title={sq(title)}, " f"content={sq(content[:8000])}, summary={sq(summary[:500])}, " f"publish_date={sq(date)} " f"WHERE page_url={sq(url)} AND site_name='{SITE_NAME}';\n" ) # FTS同步更新 sql_lines.append( f"UPDATE gov_search SET title={sq(title)}, summary={sq(summary[:500])} " f"WHERE rowid=(SELECT id FROM gov_raw WHERE page_url={sq(url)} AND site_name='{SITE_NAME}');\n" ) attach_info = f" +{len(attachments)}附件" if attachments else "" print(f" ✅ {title[:30]} | {len(content)}B{attach_info}") fixed += 1 finally: await page.close() await asyncio.sleep(1.0) await browser.close() print(f"\n📊 结果: {fixed} 条修复, {failed} 条失败") if not sql_lines: print("⚠️ 无修复内容") return sql = 'PRAGMA busy_timeout=30000;\n' + ''.join(sql_lines) tmpfile = '/tmp/fix_yanshougs_nullhao.sql' with open(tmpfile, 'w', encoding='utf-8') as f: f.write(sql) print(f"📝 SQL 生成 ({len(sql)} chars, {fixed} 条 UPDATE)") # 上传+执行 print("📤 上传到服务器...") r1 = subprocess.run( f'base64 -i {tmpfile} | ssh root@1.94.217.116 "base64 -d > /tmp/fix_yanshougs_nullhao.sql"', shell=True, capture_output=True, text=True, timeout=60 ) if r1.returncode != 0: print(f" ✗ 上传失败: {r1.stderr[:200]}") return print("⚡ 执行 SQL...") r2 = subprocess.run( 'ssh root@1.94.217.116 "sqlite3 /root/search.db < /tmp/fix_yanshougs_nullhao.sql && echo OK; rm -f /tmp/fix_yanshougs_nullhao.sql"', shell=True, capture_output=True, text=True, timeout=120 ) if "OK" in (r2.stdout or ""): print(" ✅ 服务器更新成功!") else: err = (r2.stderr or r2.stdout or "no output")[:300] print(f" ✗ 执行失败: {err}") # 验证（通过单独的 SSH 命令） print("\n🔍 验证: 用 `ssh root@1.94.217.116 \"sqlite3 /root/search.db 'SELECT COUNT(*),title FROM gov_raw WHERE title LIKE \\\"%null号%\\\" AND site_name=\\\"工程建设验收公示网\\\";'\"`") if __name__ == "__main__": asyncio.run(main())