数据采集作业三
第三次作业:图片爬虫与 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
单线程运行结果:

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

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

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 查询结果如下:

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 查询结果如下:

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:作业三
浙公网安备 33010602011771号