数据采集作业三

第三次作业:图片爬虫与 Scrapy 数据采集

课程:数据采集与融合技术

Gitee 仓库:https://gitee.com/cf919/shujucaijiyushujuronghejishu/tree/master/作业三

一、作业①:网站图片爬虫(单线程 + 多线程)

1.1 代码与运行结果


from __future__ import annotations

import argparse
import csv
import hashlib
import re
import threading
import time
from collections import deque
from concurrent.futures import ThreadPoolExecutor, as_completed
from pathlib import Path
from threading import Lock
from typing import Any
from urllib.parse import urljoin, urlparse, urlunparse

import requests
from bs4 import BeautifulSoup

MAX_PAGES = 19
MAX_IMAGES = 919

SEED_URL = "https://www.weather.com.cn/"
BASE_DIR = Path(__file__).resolve().parent
IMAGE_DIR = BASE_DIR / "images"
HEADERS = {
    "User-Agent": (
        "Mozilla/5.0 (Windows NT 10.0; Win64; x64) "
        "AppleWebKit/537.36 (KHTML, like Gecko) "
        "Chrome/124.0.0.0 Safari/537.36"
    ),
    "Accept": "text/html,application/xhtml+xml,image/avif,image/webp,*/*;q=0.8",
    "Accept-Language": "zh-CN,zh;q=0.9",
}

# 多线程写文件/打印时避免交错。
PRINT_LOCK = Lock()
_thread_local = threading.local()


def get_session() -> requests.Session:
    """每个线程复用一个 Session;Session 不保证跨线程安全。"""
    if not hasattr(_thread_local, "session"):
        session = requests.Session()
        session.headers.update(HEADERS)
        _thread_local.session = session
    return _thread_local.session


def normalize_page_url(url: str) -> str:
    """统一协议、域名和路径,便于页面去重。"""
    parsed = urlparse(url)
    path = re.sub(r"/+", "/", parsed.path or "/")
    netloc = parsed.netloc.lower().replace(":80", "").replace(":443", "")
    # http 与 https 指向同一页面时只保留一份。
    return urlunparse(("https", netloc, path, "", parsed.query, ""))


def is_page_link(url: str) -> bool:
    """判断链接是否适合作为待爬页面,过滤图片、压缩包和明显的文件链接。"""
    parsed = urlparse(url)
    if parsed.scheme not in ("http", "https"):
        return False
    if parsed.netloc not in ("www.weather.com.cn", "weather.com.cn"):
        return False
    if re.search(
        r"\.(?:jpg|jpeg|png|gif|webp|svg|ico|css|js|zip|rar|pdf|docx?|xlsx?)$",
        parsed.path,
        re.IGNORECASE,
    ):
        return False
    return True


def extract_image_urls(page_url: str, soup: BeautifulSoup) -> list[str]:
    """从 img、source、srcset 和内联 style 中提取图片地址。"""
    urls: list[str] = []

    for tag in soup.find_all("img"):
        for attr in ("src", "data-src", "data-original", "data-lazy-src"):
            value = tag.get(attr)
            if value:
                urls.append(urljoin(page_url, value.strip()))

    for tag in soup.find_all("source"):
        srcset = tag.get("srcset") or tag.get("data-srcset")
        if srcset:
            urls.append(urljoin(page_url, srcset.split(",")[0].strip().split(" ")[0]))

    # 有些天气图标写在 style="background-image:url(...)" 中。
    for tag in soup.find_all(style=True):
        for match in re.findall(r"url\((['\"]?)(.*?)\1\)", tag["style"]):
            urls.append(urljoin(page_url, match[1].strip()))

    clean_urls: list[str] = []
    seen: set[str] = set()
    for url in urls:
        if not url.startswith(("http://", "https://")):
            continue
        if url.startswith("data:"):
            continue
        if url not in seen:
            seen.add(url)
            clean_urls.append(url)
    return clean_urls


def crawl_pages(
    session: requests.Session,
    seed_url: str,
    max_pages: int,
    timeout: int = 20,
) -> tuple[list[tuple[str, list[str]]], list[str]]:
    """广度优先抓取页面,返回(页面及图片列表,失败页面列表)。"""
    queue: deque[str] = deque([seed_url])
    visited: set[str] = set()
    pages: list[tuple[str, list[str]]] = []
    failures: list[str] = []

    while queue and len(pages) < max_pages:
        page_url = queue.popleft()
        normalized = normalize_page_url(page_url)
        if normalized in visited:
            continue
        visited.add(normalized)

        try:
            response = session.get(page_url, timeout=timeout)
            response.raise_for_status()
            response.encoding = response.apparent_encoding or "utf-8"
        except requests.RequestException as exc:
            failures.append(page_url)
            print(f"[页面失败] {page_url} -> {exc}")
            continue

        soup = BeautifulSoup(response.text, "html.parser")
        final_normalized = normalize_page_url(response.url)
        if final_normalized != normalized and final_normalized in visited:
            continue
        visited.add(final_normalized)
        image_urls = extract_image_urls(response.url, soup)
        pages.append((response.url, image_urls))
        print(f"[页面 {len(pages):02d}/{max_pages}] {response.url} 发现 {len(image_urls)} 张图片")

        for anchor in soup.find_all("a", href=True):
            link = urljoin(response.url, anchor["href"].strip())
            if is_page_link(link):
                normalized_link = normalize_page_url(link)
                if normalized_link not in visited:
                    queue.append(link)

        time.sleep(0.2)

    return pages, failures


def collect_tasks(
    pages: list[tuple[str, list[str]]],
    max_images: int,
) -> list[dict[str, Any]]:
    """汇总去重后的图片任务,并截断到最大图片数量。"""
    tasks: list[dict[str, Any]] = []
    seen: set[str] = set()
    for page_url, image_urls in pages:
        for image_url in image_urls:
            if image_url in seen:
                continue
            seen.add(image_url)
            if len(tasks) >= max_images:
                return tasks
            tasks.append(
                {
                    "index": len(tasks) + 1,
                    "page_url": page_url,
                    "image_url": image_url,
                }
            )
    return tasks


def image_extension(content_type: str, image_url: str) -> str:
    """优先根据 Content-Type 推断扩展名,取不到再看 URL。"""
    content_type = content_type.lower().split(";")[0].strip()
    mapping = {
        "image/jpeg": ".jpg",
        "image/jpg": ".jpg",
        "image/png": ".png",
        "image/gif": ".gif",
        "image/webp": ".webp",
        "image/svg+xml": ".svg",
        "image/x-icon": ".ico",
        "image/vnd.microsoft.icon": ".ico",
    }
    if content_type in mapping:
        return mapping[content_type]
    suffix = Path(urlparse(image_url).path).suffix.lower()
    return suffix if suffix in {".jpg", ".jpeg", ".png", ".gif", ".webp", ".svg", ".ico"} else ".jpg"


def download_one(task: dict[str, Any], output_dir: Path, timeout: int = 25) -> dict[str, Any]:
    """下载单张图片;失败时返回带 error 的记录,不中断整体任务。"""
    result: dict[str, Any] = {
        **task,
        "filename": "",
        "bytes": 0,
        "status": "failed",
        "error": "",
    }
    try:
        session = get_session()
        response = session.get(task["image_url"], timeout=timeout, stream=True)
        response.raise_for_status()
        content_type = response.headers.get("Content-Type", "")
        if content_type and not content_type.lower().startswith("image/"):
            result["error"] = f"非图片 Content-Type: {content_type}"
            return result

        content = response.content
        if len(content) < 512:
            result["error"] = f"文件过小({len(content)} 字节)"
            return result

        digest = hashlib.sha1(content).hexdigest()[:10]
        extension = image_extension(content_type, task["image_url"])
        filename = f"{task['index']:04d}_{digest}{extension}"
        path = output_dir / filename
        path.write_bytes(content)

        result.update(
            {
                "filename": filename,
                "bytes": len(content),
                "status": "ok",
            }
        )
        with PRINT_LOCK:
            print(f"[图片 {task['index']:04d}] {task['image_url']} -> images/{filename}")
    except requests.RequestException as exc:
        result["error"] = str(exc)
        with PRINT_LOCK:
            print(f"[图片失败 {task['index']:04d}] {task['image_url']} -> {exc}")
    return result


def download_single(tasks: list[dict[str, Any]], output_dir: Path, delay: float) -> list[dict[str, Any]]:
    """单线程顺序下载。"""
    results: list[dict[str, Any]] = []
    for task in tasks:
        results.append(download_one(task, output_dir))
        time.sleep(delay)
    return results


def download_multi(
    tasks: list[dict[str, Any]],
    output_dir: Path,
    workers: int,
) -> list[dict[str, Any]]:
    """多线程下载,使用 ThreadPoolExecutor 控制并发数。"""
    results: list[dict[str, Any]] = []
    with ThreadPoolExecutor(max_workers=workers) as executor:
        futures = [executor.submit(download_one, task, output_dir) for task in tasks]
        for future in as_completed(futures):
            results.append(future.result())
    return sorted(results, key=lambda item: item["index"])


def write_log(results: list[dict[str, Any]], mode: str, output_dir: Path) -> Path:
    """把下载结果写入 CSV,方便核对控制台输出。"""
    log_path = output_dir / f"download_log_{mode}.csv"
    with log_path.open("w", newline="", encoding="utf-8-sig") as file:
        writer = csv.DictWriter(
            file,
            fieldnames=["index", "page_url", "image_url", "filename", "bytes", "status", "error"],
        )
        writer.writeheader()
        writer.writerows(results)
    return log_path


def run_mode(mode: str, pages: list[tuple[str, list[str]]], tasks: list[dict[str, Any]],
             output_dir: Path, workers: int, delay: float) -> None:
    """执行一种下载模式并打印汇总。"""
    print(f"\n{'=' * 70}")
    print(f"[开始] 模式:{mode},页面 {len(pages)} 个,待下载图片 {len(tasks)} 张")
    print(f"{'=' * 70}")
    started = time.perf_counter()
    if mode == "single":
        results = download_single(tasks, output_dir, delay)
    else:
        results = download_multi(tasks, output_dir, workers)
    elapsed = time.perf_counter() - started

    ok_count = sum(1 for item in results if item["status"] == "ok")
    fail_count = len(results) - ok_count
    total_bytes = sum(item["bytes"] for item in results)
    log_path = write_log(results, mode, output_dir)

    print(f"\n[完成] 模式:{mode}")
    print(f"[完成] 成功 {ok_count} 张,失败 {fail_count} 张,"
          f"共 {total_bytes / 1024 / 1024:.2f} MB,耗时 {elapsed:.2f} 秒")
    print(f"[完成] 日志:{log_path}")
    print(f"[完成] 图片目录:{output_dir}")


def main(argv: list[str] | None = None) -> int:
    """程序入口:先抓页面,再按指定模式下载图片。"""
    parser = argparse.ArgumentParser(description="中国气象网图片爬虫(单线程/多线程)")
    parser.add_argument("--mode", choices=["single", "multi", "both"], default="single",
                        help="single=单线程,multi=多线程,both=两种都跑")
    parser.add_argument("--workers", type=int, default=8, help="多线程并发数,默认 8")
    parser.add_argument("--max-pages", type=int, default=MAX_PAGES, help=f"最大页数,默认 {MAX_PAGES}")
    parser.add_argument("--max-images", type=int, default=MAX_IMAGES, help=f"最大图片数,默认 {MAX_IMAGES}")
    parser.add_argument("--delay", type=float, default=0.05, help="单线程每张图片间隔秒数")
    parser.add_argument("--seed", default=SEED_URL, help="起始页面")
    parser.add_argument("--out", type=Path, default=IMAGE_DIR, help="图片保存目录")
    args = parser.parse_args(argv)

    if args.max_pages <= 0 or args.max_images <= 0:
        parser.error("--max-pages 和 --max-images 必须大于 0")
    if args.workers <= 0:
        parser.error("--workers 必须大于 0")

    args.out.mkdir(parents=True, exist_ok=True)
    print(f"[配置] 起始页面:{args.seed}")
    print(f"[配置] 页数上限:{args.max_pages},图片上限:{args.max_images},"
          f"线程数:{args.workers}")

    session = requests.Session()
    session.headers.update(HEADERS)
    pages, failures = crawl_pages(session, args.seed, args.max_pages)
    tasks = collect_tasks(pages, args.max_images)

    if not tasks:
        print("[错误] 没有发现可下载的图片。")
        return 1
    if failures:
        print(f"[提示] {len(failures)} 个页面请求失败,已跳过。")

    if args.mode in ("single", "both"):
        run_mode("single", pages, tasks, args.out, args.workers, args.delay)
    if args.mode in ("multi", "both"):
        # 多线程重跑时,图片已存在也会重新下载并覆盖同名文件,便于对比耗时。
        run_mode("multi", pages, tasks, args.out, args.workers, args.delay)

    print(f"\n[汇总] 页面 {len(pages)} 个,图片任务 {len(tasks)} 张,目录 {args.out}")
    return 0


if __name__ == "__main__":
    raise SystemExit(main())

运行方式:

python 1.py --mode both --workers 8

单线程运行结果:

01_single_thread

多线程运行结果(8 线程):

02_multi_thread

下载的图片保存在 images/ 子文件夹中,文件按“序号_内容哈希.扩展名”命名:

03_images_folder

1.2 心得体会

这次作业让我最直观地感受到单线程和多线程的差别。单线程按顺序一张一张下载,19 个页面、80 个图片任务用了 14.72 秒;改成 8 个线程后只用 1.53 秒。图片下载属于 I/O 密集型任务,等待网络响应的时间远大于 CPU 计算时间,所以用 ThreadPoolExecutor 把等待时间重叠起来,速度提升很明显。

不过多线程也不是“线程越多越好”。线程太多会增加连接数和对方服务器压力,还容易出现超时、连接被重置等问题。我最后只用了 8 个线程,并且保留了页数上限、图片数量上限和请求间隔。题目要求限制总页数和总图片数,我把它们做成 MAX_PAGES 和 MAX_IMAGES 两个常量,既方便按学号修改,也能避免程序无限爬下去。

写图片爬虫时还遇到两个小问题。第一,首页的 http:// 和 https:// 链接会跳到同一个页面,如果只按原始 URL 去重,会把首页统计两次;我在 normalize_page_url() 里统一了协议和域名,并在重定向后再次判断,才把重复页面去掉。第二,图片 URL 有相对路径、有 // 开头的协议相对路径,还有 srcset 和 style 里的背景图,所以不能只取 img[src],要用 urljoin 统一转成绝对地址。

另外,我把下载失败的图片单独记录到 CSV,而不是让整个程序中断。实际运行中有 2 张图返回 404,程序仍然把其余 78 张下载完,最后汇总成功和失败数量。文件名使用内容哈希,也能避免不同页面引用同一张图时重复保存。

二、作业②与作业③:Scrapy + Item/Pipeline + MySQL

2.1 代码与运行结果

2.1.1 作业②:股票数据(Scrapy + MySQL)


import scrapy


class StockItem(scrapy.Item):

    bStockNo = scrapy.Field()       # 股票代码
    bStockName = scrapy.Field()     # 股票名称
    latestPrice = scrapy.Field()    # 最新报价
    changePercent = scrapy.Field()  # 涨跌幅(%)
    changeAmount = scrapy.Field()   # 涨跌额
    volume = scrapy.Field()         # 成交量(手)
    turnover = scrapy.Field()       # 成交额(元)
    amplitude = scrapy.Field()      # 振幅(%)
    high = scrapy.Field()           # 最高价
    low = scrapy.Field()            # 最低价
    openPrice = scrapy.Field()      # 今开
    prevClose = scrapy.Field()      # 昨收
    crawlTime = scrapy.Field()      # 抓取时间

Pipeline:JSON Lines 序列化 + MySQL 入库(pipelines.py)


from __future__ import annotations

import json
import logging
import os
from datetime import datetime
from pathlib import Path
from typing import Any

import pymysql
from itemadapter import ItemAdapter
from scrapy.exceptions import DropItem

from stock_scraper.items import StockItem

logger = logging.getLogger(__name__)


class JsonLinesPipeline:
    """把每个 Item 追加写入 output/stock_items.jsonl,便于检查序列化结果。"""

    def open_spider(self) -> None:
        output_dir = Path(__file__).resolve().parent.parent / "output"
        output_dir.mkdir(parents=True, exist_ok=True)
        self.file = (output_dir / "stock_items.jsonl").open("w", encoding="utf-8")
        self.count = 0
        logger.info("JSONL 输出文件:%s", self.file.name)

    def process_item(self, item: StockItem) -> StockItem:
        line = json.dumps(dict(ItemAdapter(item)), ensure_ascii=False)
        self.file.write(line + "\n")
        self.count += 1
        return item

    def close_spider(self) -> None:
        self.file.close()
        logger.info("JSONL 共写入 %s 条", self.count)


class MySQLPipeline:

    def __init__(self) -> None:
        self.conn: pymysql.connections.Connection | None = None
        self.count = 0

    def open_spider(self) -> None:
        host = os.getenv("MYSQL_HOST", "127.0.0.1")
        port = int(os.getenv("MYSQL_PORT", "3306"))
        user = os.getenv("MYSQL_USER", "root")
        password = os.getenv("MYSQL_PASSWORD", "123456")
        database = os.getenv("MYSQL_DATABASE", "homework3")

        # 先连接服务器创建数据库,再切换到目标库。
        server_conn = pymysql.connect(
            host=host,
            port=port,
            user=user,
            password=password,
            charset="utf8mb4",
            autocommit=True,
        )
        try:
            with server_conn.cursor() as cursor:
                cursor.execute(
                    f"CREATE DATABASE IF NOT EXISTS `{database}` "
                    "DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"
                )
        finally:
            server_conn.close()

        self.conn = pymysql.connect(
            host=host,
            port=port,
            user=user,
            password=password,
            database=database,
            charset="utf8mb4",
            autocommit=False,
        )
        self._create_table()
        logger.info("MySQL 已连接:%s:%s/%s", host, port, database)

    def _create_table(self) -> None:
        """创建股票行情表;同一天同一代码只保留一条记录。"""
        assert self.conn is not None
        sql = """
            CREATE TABLE IF NOT EXISTS stock (
                id            INT AUTO_INCREMENT PRIMARY KEY,
                bStockNo      VARCHAR(10)  NOT NULL COMMENT '股票代码',
                bStockName    VARCHAR(50)  NOT NULL COMMENT '股票名称',
                latestPrice   DECIMAL(12,3)         COMMENT '最新报价',
                changePercent DECIMAL(10,2)         COMMENT '涨跌幅%',
                changeAmount  DECIMAL(12,3)         COMMENT '涨跌额',
                volume        BIGINT                COMMENT '成交量(手)',
                turnover      DECIMAL(20,2)         COMMENT '成交额(元)',
                amplitude     DECIMAL(10,2)         COMMENT '振幅%',
                high          DECIMAL(12,3)         COMMENT '最高',
                low           DECIMAL(12,3)         COMMENT '最低',
                openPrice     DECIMAL(12,3)         COMMENT '今开',
                prevClose     DECIMAL(12,3)         COMMENT '昨收',
                crawlDate     DATE         NOT NULL COMMENT '抓取日期',
                crawlTime     DATETIME     NOT NULL COMMENT '抓取时间',
                UNIQUE KEY uk_code_date (bStockNo, crawlDate),
                KEY idx_stock_name (bStockName)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='东方财富股票行情'
        """
        with self.conn.cursor() as cursor:
            cursor.execute(sql)
        self.conn.commit()

    def process_item(self, item: StockItem) -> StockItem:
        assert self.conn is not None
        data: dict[str, Any] = dict(ItemAdapter(item))
        crawl_time = data.get("crawlTime") or datetime.now()
        if isinstance(crawl_time, str):
            crawl_time = datetime.strptime(crawl_time, "%Y-%m-%d %H:%M:%S")

        sql = """
            INSERT INTO stock (
                bStockNo, bStockName, latestPrice, changePercent, changeAmount,
                volume, turnover, amplitude, high, low, openPrice, prevClose,
                crawlDate, crawlTime
            ) VALUES (
                %(bStockNo)s, %(bStockName)s, %(latestPrice)s, %(changePercent)s,
                %(changeAmount)s, %(volume)s, %(turnover)s, %(amplitude)s,
                %(high)s, %(low)s, %(openPrice)s, %(prevClose)s,
                %(crawlDate)s, %(crawlTime)s
            )
            ON DUPLICATE KEY UPDATE
                bStockName    = VALUES(bStockName),
                latestPrice   = VALUES(latestPrice),
                changePercent = VALUES(changePercent),
                changeAmount  = VALUES(changeAmount),
                volume        = VALUES(volume),
                turnover      = VALUES(turnover),
                amplitude     = VALUES(amplitude),
                high          = VALUES(high),
                low           = VALUES(low),
                openPrice     = VALUES(openPrice),
                prevClose     = VALUES(prevClose),
                crawlTime     = VALUES(crawlTime)
        """
        data["crawlDate"] = crawl_time.date()
        try:
            with self.conn.cursor() as cursor:
                cursor.execute(sql, data)
            self.conn.commit()
            self.count += 1
        except pymysql.MySQLError as exc:
            self.conn.rollback()
            logger.error("写入 MySQL 失败:%s", exc)
            raise DropItem(f"MySQL 写入失败:{exc}") from exc
        return item

    def close_spider(self) -> None:
        if self.conn is not None:
            self.conn.close()
            logger.info("MySQL 共写入/更新 %s 条", self.count)

Spider:分页请求与字段映射(spiders/sina.py)


from __future__ import annotations

from datetime import datetime
from typing import Any, AsyncIterator, Iterator
from urllib.parse import urlencode

import scrapy
from scrapy.http import Response

from stock_scraper.items import StockItem

API_URL = "https://vip.stock.finance.sina.com.cn/quotes_service/api/json_v2.php/Market_Center.getHQNodeData"
REFERER = "https://finance.sina.com.cn/stock/"


def to_float(value: Any) -> float | None:
    """把接口中的数值字符串转成 float/None。"""
    if value in (None, "", "-", "0.000"):
        return None
    try:
        return float(value)
    except (TypeError, ValueError):
        return None


def to_int(value: Any) -> int | None:
    """把接口中的数值转成 int/None。"""
    number = to_float(value)
    return int(number) if number is not None else None


class SinaStockSpider(scrapy.Spider):
    """新浪财经沪深 A 股行情爬虫。

    运行:
        scrapy crawl sina -a max_pages=2
    """

    name = "sina"
    allowed_domains = ["vip.stock.finance.sina.com.cn"]

    def __init__(self, max_pages: int = 2, page_size: int = 100, *args: Any, **kwargs: Any):
        super().__init__(*args, **kwargs)
        self.max_pages = max(1, int(max_pages))
        self.page_size = max(1, min(int(page_size), 100))
        self.crawl_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")

    async def start(self) -> AsyncIterator[scrapy.Request]:
        """Scrapy 2.13+ 入口:从第 1 页开始。"""
        yield self._make_request(1)

    def start_requests(self) -> Iterator[scrapy.Request]:
        """兼容旧版 Scrapy 的入口。"""
        yield self._make_request(1)

    def _make_request(self, page: int) -> scrapy.Request:
        """构造新浪行情列表接口请求。"""
        params = {
            "page": page,
            "num": self.page_size,
            "sort": "amount",
            "asc": "0",
            "node": "hs_a",
            "symbol": "",
            "_s_r_a": "page",
        }
        return scrapy.Request(
            f"{API_URL}?{urlencode(params)}",
            callback=self.parse,
            meta={"page": page},
            headers={"Referer": REFERER},
            dont_filter=True,
        )

    def parse(self, response: Response) -> Iterator[StockItem | scrapy.Request]:
        """解析 JSON 数组,生成 Item;空页或达到页数上限时停止。"""
        try:
            rows = response.json()
        except ValueError:
            self.logger.error("第 %s 页 JSON 解析失败", response.meta["page"])
            return

        if not isinstance(rows, list) or not rows:
            self.logger.info("第 %s 页为空,停止翻页", response.meta["page"])
            return

        self.logger.info("第 %s 页:%s 条", response.meta["page"], len(rows))
        for raw in rows:
            if not raw.get("code") or not raw.get("name"):
                continue
            # 只保留沪深 A 股:沪市 60/68,深市 00/30;排除北交所 8/4/92 开头。
            if not str(raw["code"]).startswith(("60", "68", "00", "30")):
                continue
            high = to_float(raw.get("high"))
            low = to_float(raw.get("low"))
            prev_close = to_float(raw.get("settlement"))
            amplitude = None
            if high is not None and low is not None and prev_close:
                amplitude = round((high - low) / prev_close * 100, 2)

            yield StockItem(
                bStockNo=str(raw["code"]).strip(),
                bStockName=str(raw["name"]).strip(),
                latestPrice=to_float(raw.get("trade")),
                changePercent=to_float(raw.get("changepercent")),
                changeAmount=to_float(raw.get("pricechange")),
                volume=to_int(raw.get("volume")) // 100 if raw.get("volume") else None,
                turnover=to_float(raw.get("amount")),
                amplitude=amplitude,
                high=high,
                low=low,
                openPrice=to_float(raw.get("open")),
                prevClose=prev_close,
                crawlTime=self.crawl_time,
            )

        current_page = response.meta["page"]
        if current_page < self.max_pages:
            yield self._make_request(current_page + 1)

Scrapy 配置(settings.py)


BOT_NAME = "stock_scraper"
SPIDER_MODULES = ["stock_scraper.spiders"]
NEWSPIDER_MODULE = "stock_scraper.spiders"
ROBOTSTXT_OBEY = False
DOWNLOAD_DELAY = 0.2
CONCURRENT_REQUESTS = 4
COOKIES_ENABLED = False
LOG_LEVEL = "INFO"
FEED_EXPORT_ENCODING = "utf-8"

DEFAULT_REQUEST_HEADERS = {
    "User-Agent": (
        "Mozilla/5.0 (Windows NT 10.0; Win64; x64) "
        "AppleWebKit/537.36 (KHTML, like Gecko) "
        "Chrome/124.0.0.0 Safari/537.36"
    ),
    "Accept": "application/json, text/plain, */*",
    "Referer": "https://quote.eastmoney.com/",
}

ITEM_PIPELINES = {
    "stock_scraper.pipelines.JsonLinesPipeline": 100,
    "stock_scraper.pipelines.MySQLPipeline": 300,
}

REQUEST_FINGERPRINTER_IMPLEMENTATION = "2.7"
TWISTED_REACTOR = "twisted.internet.asyncioreactor.AsyncioSelectorReactor"

运行命令:

scrapy crawl sina -a max_pages=2 -a page_size=100

运行结果:抓取 2 页共 200 条股票记录,JSON Lines 和 MySQL 各写入 200 条。MySQL 查询结果如下:

04_stock_scrapy_mysql

2.1.2 作业③:外汇数据(Scrapy + XPath + MySQL)

Item 定义(items.py)


import scrapy


class ForexItem(scrapy.Item):
    """中国银行外汇牌价 Item。"""

    currency = scrapy.Field()   # 货币名称
    tbp = scrapy.Field()        # 现汇买入价 TBP
    cbp = scrapy.Field()        # 现钞买入价 CBP
    tsp = scrapy.Field()        # 现汇卖出价 TSP
    csp = scrapy.Field()        # 现钞卖出价 CSP
    quoteDate = scrapy.Field()  # 牌价日期
    quoteTime = scrapy.Field()  # 牌价时间
    crawlTime = scrapy.Field()  # 抓取时间

Pipeline:JSON Lines 序列化 + MySQL 入库(pipelines.py)


from __future__ import annotations

import json
import logging
import os
from datetime import datetime
from pathlib import Path
from typing import Any

import pymysql
from itemadapter import ItemAdapter
from scrapy.exceptions import DropItem

from forex_scraper.items import ForexItem

logger = logging.getLogger(__name__)


class JsonLinesPipeline:
    """把 Item 写入 output/forex_items.jsonl。"""

    def open_spider(self) -> None:
        output_dir = Path(__file__).resolve().parent.parent / "output"
        output_dir.mkdir(parents=True, exist_ok=True)
        self.file = (output_dir / "forex_items.jsonl").open("w", encoding="utf-8")
        self.count = 0
        logger.info("JSONL 输出文件:%s", self.file.name)

    def process_item(self, item: ForexItem) -> ForexItem:
        self.file.write(json.dumps(dict(ItemAdapter(item)), ensure_ascii=False) + "\n")
        self.count += 1
        return item

    def close_spider(self) -> None:
        self.file.close()
        logger.info("JSONL 共写入 %s 条", self.count)


class MySQLPipeline:
    """把外汇 Item 写入 MySQL 的 forex_rate 表。"""

    def __init__(self) -> None:
        self.conn: pymysql.connections.Connection | None = None
        self.count = 0

    def open_spider(self) -> None:
        host = os.getenv("MYSQL_HOST", "127.0.0.1")
        port = int(os.getenv("MYSQL_PORT", "3306"))
        user = os.getenv("MYSQL_USER", "root")
        password = os.getenv("MYSQL_PASSWORD", "123456")
        database = os.getenv("MYSQL_DATABASE", "homework3")

        server_conn = pymysql.connect(
            host=host, port=port, user=user, password=password,
            charset="utf8mb4", autocommit=True,
        )
        try:
            with server_conn.cursor() as cursor:
                cursor.execute(
                    f"CREATE DATABASE IF NOT EXISTS `{database}` "
                    "DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"
                )
        finally:
            server_conn.close()

        self.conn = pymysql.connect(
            host=host, port=port, user=user, password=password,
            database=database, charset="utf8mb4", autocommit=False,
        )
        self._create_table()
        logger.info("MySQL 已连接:%s:%s/%s", host, port, database)

    def _create_table(self) -> None:
        assert self.conn is not None
        sql = """
            CREATE TABLE IF NOT EXISTS forex_rate (
                id         INT AUTO_INCREMENT PRIMARY KEY,
                currency   VARCHAR(30) NOT NULL COMMENT '货币名称',
                tbp        DECIMAL(10,3) COMMENT '现汇买入价',
                cbp        DECIMAL(10,3) COMMENT '现钞买入价',
                tsp        DECIMAL(10,3) COMMENT '现汇卖出价',
                csp        DECIMAL(10,3) COMMENT '现钞卖出价',
                quoteDate  DATE NOT NULL COMMENT '牌价日期',
                quoteTime  TIME NOT NULL COMMENT '牌价时间',
                crawlTime  DATETIME NOT NULL COMMENT '抓取时间',
                UNIQUE KEY uk_currency_time (currency, quoteDate, quoteTime)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='中国银行外汇牌价'
        """
        with self.conn.cursor() as cursor:
            cursor.execute(sql)
        self.conn.commit()

    def process_item(self, item: ForexItem) -> ForexItem:
        assert self.conn is not None
        data: dict[str, Any] = dict(ItemAdapter(item))
        sql = """
            INSERT INTO forex_rate (
                currency, tbp, cbp, tsp, csp, quoteDate, quoteTime, crawlTime
            ) VALUES (
                %(currency)s, %(tbp)s, %(cbp)s, %(tsp)s, %(csp)s,
                %(quoteDate)s, %(quoteTime)s, %(crawlTime)s
            )
            ON DUPLICATE KEY UPDATE
                tbp = VALUES(tbp),
                cbp = VALUES(cbp),
                tsp = VALUES(tsp),
                csp = VALUES(csp),
                crawlTime = VALUES(crawlTime)
        """
        try:
            with self.conn.cursor() as cursor:
                cursor.execute(sql, data)
            self.conn.commit()
            self.count += 1
        except pymysql.MySQLError as exc:
            self.conn.rollback()
            logger.error("写入 MySQL 失败:%s", exc)
            raise DropItem(f"MySQL 写入失败:{exc}") from exc
        return item

    def close_spider(self) -> None:
        if self.conn is not None:
            self.conn.close()
            logger.info("MySQL 共写入/更新 %s 条", self.count)

Spider:XPath 解析中国银行外汇牌价表(spiders/boc.py)


from __future__ import annotations

from datetime import datetime
from typing import Any, AsyncIterator, Iterator

import scrapy
from scrapy.http import Response

from forex_scraper.items import ForexItem

PAGE_URL = "https://www.boc.cn/sourcedb/whpj/"


def to_float(value: str | None) -> float | None:
    """把 '198.58' 或空字符串转换为 float/None。"""
    if not value:
        return None
    try:
        return float(value.replace(",", "").strip())
    except ValueError:
        return None


class BocForexSpider(scrapy.Spider):
    """中国银行外汇牌价爬虫。

    运行:
        scrapy crawl boc
    """

    name = "boc"
    allowed_domains = ["boc.cn"]
    start_urls = [PAGE_URL]

    async def start(self) -> AsyncIterator[scrapy.Request]:
        """Scrapy 2.13+ 入口。"""
        yield scrapy.Request(PAGE_URL, callback=self.parse, dont_filter=True)

    def start_requests(self) -> Iterator[scrapy.Request]:
        """兼容旧版 Scrapy 的入口。"""
        yield scrapy.Request(PAGE_URL, callback=self.parse, dont_filter=True)

    def parse(self, response: Response) -> Iterator[ForexItem]:
        """用 XPath 定位表头包含“货币名称”的表格,再逐行解析。"""
        # 只取目标表格:表头里含“货币名称”,避免抓到页脚的其他 table。
        rows = response.xpath(
            '//table[.//th[contains(normalize-space(.), "货币名称")]]//tr[td]'
        )
        self.logger.info("目标表格数据行:%s", len(rows))
        crawl_time = datetime.now()

        for row in rows:
            cells = row.xpath("./td")
            if len(cells) < 8:
                continue

            currency = cells[0].xpath("normalize-space(.)").get()
            if not currency or currency == "货币名称":
                continue

            # 第 7 列是“2026/11/19 23:31:45”,第 8 列是“23:31:45”。
            datetime_text = cells[6].xpath("normalize-space(.)").get("")
            time_text = cells[7].xpath("normalize-space(.)").get("")
            quote_date = datetime.now().date()
            quote_time = time_text or None
            if datetime_text:
                try:
                    parsed = datetime.strptime(datetime_text, "%Y/%m/%d %H:%M:%S")
                    quote_date = parsed.date()
                    quote_time = parsed.strftime("%H:%M:%S")
                except ValueError:
                    pass

            tbp = to_float(cells[1].xpath("normalize-space(.)").get())
            cbp = to_float(cells[2].xpath("normalize-space(.)").get())
            tsp = to_float(cells[3].xpath("normalize-space(.)").get())
            csp = to_float(cells[4].xpath("normalize-space(.)").get())

            # 四个价格都为空的行通常是说明文字,跳过。
            if all(value is None for value in (tbp, cbp, tsp, csp)):
                continue

            yield ForexItem(
                currency=currency,
                tbp=tbp,
                cbp=cbp,
                tsp=tsp,
                csp=csp,
                quoteDate=quote_date.isoformat(),
                quoteTime=quote_time or "00:00:00",
                crawlTime=crawl_time.strftime("%Y-%m-%d %H:%M:%S"),
            )

运行命令:

scrapy crawl boc

运行结果:XPath 定位到 45 行外汇牌价,MySQL 写入 45 条,JSON Lines 也写入 45 条。MySQL 查询结果如下:

05_forex_scrapy_mysql

2.2 心得体会

这次作业让我把 Scrapy 的“Spider → Item → Pipeline”串起来了。以前写 requests 爬虫,解析、清洗、保存都挤在一个函数里;用 Scrapy 之后,Spider 只负责发请求和提取字段,Item 负责定义数据结构,Pipeline 负责序列化和入库,职责清楚很多。JSON Lines 和 MySQL 两个 Pipeline 可以同时启用,一个方便本地检查,一个负责持久化。

股票部分的主要工作是翻页和字段映射。新浪接口每页最多 100 条,Spider 从第 1 页开始,解析完当前页再请求下一页,达到 max_pages 就停止。接口里的 trade、changepercent、pricechange、volume、amount、high、low、open、settlement 分别对应最新价、涨跌幅、涨跌额、成交量、成交额、最高、最低、今开、昨收;振幅接口没有直接给,我用 (最高 - 最低) / 昨收 × 100 算出来,没有引入额外的库。

外汇部分练的是 XPath。中国银行页面是一个传统 HTML 表格,表头里有“货币名称”,所以先用 //table[.//th[contains(normalize-space(.), "货币名称")]] 定位目标表格,再取 //tr[td] 逐行解析。每行有 8 列,前 5 列是货币名称和四个价格,第 7 列是日期时间,第 8 列是时间。空价格统一转成 None,避免 float("") 报错。

MySQL 这部分我主要注意了重复运行的问题。股票表用“股票代码 + 抓取日期”做唯一键,外汇表用“货币 + 日期 + 时间”做唯一键,插入时用 ON DUPLICATE KEY UPDATE,这样同一天重复跑不会产生重复记录。Pipeline 里每次写入后提交事务,出错就回滚,避免只写进去一半。

最后是“不要超纲”。图片爬虫只用了 BFS 队列、URL 去重和线程池;Scrapy 部分只用了 Item、Pipeline、XPath 和原生 SQL,没有引入分布式、代理池、验证码识别这些复杂东西。先把请求、解析、存储这条链路跑通,比堆很多花哨的组件更有用。


完整代码、数据库建表脚本和运行截图见 Gitee:作业三

posted @ 2026-11-19 23:59  罗cf  阅读(5)  评论(0)    收藏  举报