数据采集作业3

数据采集第三次作业

学号:102402135

姓名:王伟浩

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

作业链接:第三次作业

本次作业完成三个任务:中国气象网图片爬虫、东方财富股票行情采集、中国银行外汇牌价采集。前两个任务分别练习单线程/多线程调度和 Scrapy + Pipeline + MySQL,第三个任务练习静态表格的 XPath 解析与 MySQL 入库。

实验环境

项目 配置
编程语言 Python 3.12
主要依赖 requests、BeautifulSoup、Scrapy、PyMySQL
数据库 MySQL 8.4(本地测试也使用 MariaDB 10.4,MySQL 协议兼容)
代码仓库 Gitee 第三次作业

按作业分开:

作业1 文件夹 | 作业2 文件夹 | 作业3 文件夹

一、实验代码与运行结果

1.1 作业①:中国气象网图片爬虫

要求:指定一个网站,爬取网站中的图片,实现单线程和多线程两种方式;总页数取学号后 2 位,图片总数取学号后 3 位。

学号 102402135,因此:

PYTHON 关键代码
STUDENT_ID = "102402135"
MAX_PAGES = int(STUDENT_ID[-2:]) # 35 页
MAX_IMAGES = int(STUDENT_ID[-3:]) # 135 张

1.1.1 代码设计思路

程序从中国气象网首页、图片频道、资讯频道三个入口开始,使用广度优先搜索(BFS)在站内翻页。每抓到一个页面,就提取页面中的图片 URL 和站内链接:

  1. 页面链接放进队列,最多访问 35 页;
  2. 图片 URL 做全局去重,最多成功保存 135 张;
  3. 图片以 URL 的 MD5 前 16 位命名,避免不同目录下的同名图片互相覆盖;
  4. 遇到 404、403 等失效图片时跳过,并继续从后续候选图片中补足,保证最终仍然是 135 张;
  5. 单线程版本“抓一页、下这一页的图”,多线程版本先把页面并发抓完、收集 URL,再用线程池并发下载。

单线程和多线程共用 common.py,只把“任务如何调度”分开写,这样两版的耗时才有可比性。

1.1.2 关键代码分析

(1)控制页数和图片数

PYTHON 关键代码
while frontier and pages < c.MAX_PAGES:
url = frontier.popleft()
...
for image_url in c.extract_images(html, final_url):
if downloaded >= c.MAX_IMAGES:
break
...

外层循环只受页数限制;图片达到 135 张后只停止下载,页面仍然继续翻到 35 页,两个限制条件互不干扰。

(2)提取懒加载图片

PYTHON 关键代码
for tag in soup.find_all(["img", "source"]):
raw = (tag.get("src") or tag.get("data-src")
or tag.get("data-original") or "").strip()
absolute = urllib.parse.urljoin(base_url, raw)

部分 <img> 的 src 为空,真实地址在 data-src、data-original 或 srcset 中,因此按顺序回退读取。

(3)多线程补足 135 张

PYTHON 关键代码
while downloaded < c.MAX_IMAGES:
while len(pending) < WORKERS:
url = next(candidates)
pending[pool.submit(grab_image, url)] = url
done, _ = wait(pending, return_when=FIRST_COMPLETED)
for future in done:
_, content = future.result()
if content is None:
failed += 1
continue
c.save_image(image_url, content)
downloaded += 1

页面中混有历史产品图,URL 已经 404。程序不能只取前 135 个 URL,否则遇到失效链接就会少于 135 张;这里持续补充候选 URL,跳过失效项,直到成功保存 135 张。

1.1.3 完整代码

common.py

PYTHON 完整代码 · 可上下滚动
# -*- coding: utf-8 -*-
"""作业①公共模块:页面抓取、链接/图片提取、图片落盘与统计。

单线程版 ``1.py`` 和多线程版 ``1.1.py`` 共用本模块,两个入口只在
“任务如何调度”上不同,爬取上限和解析规则保持一致,便于比较耗时。

爬取上限按作业要求取自学号:
总页数 = 学号后 2 位(35)
图片总数 = 学号后 3 位(135)
"""

from __future__ import annotations

import hashlib
import os
import time
import urllib.parse
from collections import deque

import requests
from bs4 import BeautifulSoup

# ---------------------------------------------------------------------------
# 爬取上限与入口
# ---------------------------------------------------------------------------
STUDENT_ID = "102402135"
MAX_PAGES = int(STUDENT_ID[-2:])
MAX_IMAGES = int(STUDENT_ID[-3:])

START_URL = "http://www.weather.com.cn/"
# 首页图片密度有限,图片频道和资讯频道作为补充种子,保证 135 张的配额。
SEED_URLS = [
START_URL,
"http://www.weather.com.cn/photo/",
"http://www.weather.com.cn/news/",
]
ALLOWED_HOSTS = ("www.weather.com.cn", "weather.com.cn", "p.weather.com.cn", "news.weather.com.cn")

HEADERS = {
"User-Agent": (
"Mozilla/5.0 (Windows NT 10.0; Win64; x64) "
"AppleWebKit/537.36 (KHTML, like Gecko) "
"Chrome/131.0.0.0 Safari/537.36"
),
"Accept-Language": "zh-CN,zh;q=0.9",
}
TIMEOUT = 15
IMAGE_DIR = os.path.join(os.path.dirname(os.path.abspath(__file__)), "images")
IMAGE_EXT = (".jpg", ".jpeg", ".png", ".gif", ".bmp", ".webp", ".svg")


def decode_html(response: requests.Response) -> str:
"""按响应头、UTF-8、GBK 的顺序解码,兼容气象网新旧页面。"""

if response.encoding and response.encoding.lower() not in ("iso-8859-1", "latin-1"):
return response.content.decode(response.encoding, "replace")
for encoding in ("utf-8", "gbk"):
try:
return response.content.decode(encoding)
except UnicodeDecodeError:
continue
return response.content.decode("utf-8", "replace")


def fetch_page(url: str):
"""抓取一个页面,返回 ``(html, final_url)``;失败返回 ``(None, url)``。"""

try:
response = requests.get(url, headers=HEADERS, timeout=TIMEOUT)
response.raise_for_status()
return decode_html(response), response.url
except requests.RequestException as exc:
print(f" 跳过页面 {url}({type(exc).__name__})")
return None, url


def fetch_image(url: str):
"""下载图片,返回 ``(bytes, content_type)``;失败或非图片返回 ``(None, None)``。"""

try:
response = requests.get(url, headers=HEADERS, timeout=TIMEOUT)
response.raise_for_status()
content_type = response.headers.get("Content-Type", "").lower()
if not content_type.startswith("image/"):
print(f" 跳过非图片资源 {url}({content_type or 'unknown'})")
return None, None
if not response.content:
return None, None
return response.content, content_type
except requests.RequestException:
return None, None


def is_page_link(url: str) -> bool:
"""判断是否为站内可继续抓取的页面。"""

parsed = urllib.parse.urlparse(url)
if parsed.scheme not in ("http", "https"):
return False
if parsed.netloc not in ALLOWED_HOSTS:
return False
path = parsed.path.lower()
return path.endswith((".shtml", ".html", "/")) or path == ""


def extract_links(html: str, base_url: str) -> list[str]:
"""提取页面中的站内链接,转成绝对地址并保序去重。"""

soup = BeautifulSoup(html, "html.parser")
links, seen = [], set()
for anchor in soup.find_all("a", href=True):
href = anchor["href"].strip()
if not href or href.startswith(("javascript:", "mailto:", "#")):
continue
absolute = urllib.parse.urljoin(base_url, href).split("#")[0]
if is_page_link(absolute) and absolute not in seen:
seen.add(absolute)
links.append(absolute)
return links


def _image_source(tag) -> str:
"""读取图片真实地址,兼容懒加载的 data-src / data-original / srcset。"""

for attr in ("src", "data-src", "data-original", "data-lazy-src"):
value = (tag.get(attr) or "").strip()
if value and not value.startswith("data:"):
return value
srcset = (tag.get("srcset") or "").strip()
if srcset:
return srcset.split(",")[0].strip().split(" ")[0]
return ""


def extract_images(html: str, base_url: str) -> list[str]:
"""提取页面中的所有图片地址,转为绝对地址并保序去重。"""

soup = BeautifulSoup(html, "html.parser")
images, seen = [], set()
for tag in soup.find_all(["img", "source"]):
raw = _image_source(tag)
if not raw:
continue
absolute = urllib.parse.urljoin(base_url, raw)
if absolute.startswith("//"):
absolute = urllib.parse.urlparse(base_url).scheme + ":" + absolute
path = urllib.parse.urlparse(absolute).path.lower()
if not path.endswith(IMAGE_EXT):
continue
if absolute not in seen:
seen.add(absolute)
images.append(absolute)
return images


def image_filename(url: str) -> str:
"""用 URL 哈希生成文件名,避免不同目录下的同名图片互相覆盖。"""

path = urllib.parse.urlparse(url).path
extension = os.path.splitext(path)[1].lower() or ".jpg"
digest = hashlib.md5(url.encode("utf-8")).hexdigest()[:16]
return f"{digest}{extension}"


def save_image(url: str, content: bytes) -> str:
"""保存图片到 images/,返回相对项目目录的路径。"""

os.makedirs(IMAGE_DIR, exist_ok=True)
path = os.path.join(IMAGE_DIR, image_filename(url))
with open(path, "wb") as fp:
fp.write(content)
return path


def human_size(num_bytes: int) -> str:
"""把字节数格式化为 KB/MB,便于控制台阅读。"""

if num_bytes >= 1024 * 1024:
return f"{num_bytes / 1024 / 1024:.1f} MB"
return f"{num_bytes / 1024:.1f} KB"


def banner(mode: str) -> None:
"""打印本次运行参数,截图时能直接看出单线程/多线程和上限。"""

print("=" * 70)
print(f"作业①|中国气象网图片爬虫({mode})")
print(f"学号 {STUDENT_ID} → 最多 {MAX_PAGES} 个页面、{MAX_IMAGES} 张图片")
print("入口:" + "、".join(SEED_URLS))
print("=" * 70)


def report(mode: str, pages: int, images: int, elapsed: float, failed: int = 0) -> None:
"""输出统一格式的结束统计,方便对比单线程和多线程。"""

print("-" * 70)
print(f"运行方式:{mode}")
print(f"抓取页面:{pages} 个")
print(f"下载图片:{images} 张(失败 {failed} 次)")
print(f"存放目录:{IMAGE_DIR}")
print(f"耗时:{elapsed:.2f} 秒")
print("-" * 70)


def initial_frontier() -> deque[str]:
"""返回去重后的种子页面队列。"""

return deque(dict.fromkeys(SEED_URLS))


if __name__ == "__main__":
# 直接运行本模块时,快速探测三个种子的图片/链接数量。
for seed in SEED_URLS:
html, final_url = fetch_page(seed)
if html:
print(
f"{seed:<35} 图片 {len(extract_images(html, final_url)):>3} 张,"
f"站内链接 {len(extract_links(html, final_url)):>3} 个"
)
time.sleep(0.2)

1.py(单线程)

PYTHON 完整代码 · 可上下滚动
#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""作业① 单线程版:广度优先翻页,抓到图片立即下载。

学号 102402135 → 最多 35 个页面、135 张图片。
"""

from __future__ import annotations

import time

import common as c


def main() -> None:
"""单线程 BFS:逐页解析,逐张下载,严格守住页数和图片数上限。"""

c.banner("单线程")
started = time.time()

frontier = c.initial_frontier() # 待抓页面队列
seen_pages: set[str] = set() # 已抓页面
seen_images: set[str] = set() # 已下载/尝试下载的图片
pages = 0
downloaded = 0
failed = 0

# 页数达到上限才结束;图片达到上限只停止下载,仍然继续翻页,
# 这样两个限制条件都能独立满足。
while frontier and pages < c.MAX_PAGES:
url = frontier.popleft()
if url in seen_pages:
continue
seen_pages.add(url)

html, final_url = c.fetch_page(url)
if html is None:
continue
pages += 1
print(f"[页面 {pages:02d}/{c.MAX_PAGES}] {url}")

for link in c.extract_links(html, final_url):
if link not in seen_pages:
frontier.append(link)

for image_url in c.extract_images(html, final_url):
if downloaded >= c.MAX_IMAGES:
break
if image_url in seen_images:
continue
seen_images.add(image_url)

content, _ = c.fetch_image(image_url)
if content is None:
failed += 1
print(f" 下载失败 {image_url}")
continue

path = c.save_image(image_url, content)
downloaded += 1
print(
f"[图片 {downloaded:03d}/{c.MAX_IMAGES}] {image_url} "
f"{c.human_size(len(content))} -> {path}"
)

time.sleep(0.2) # 翻页间隔,降低目标站点压力

c.report("单线程", pages, downloaded, time.time() - started, failed)


if __name__ == "__main__":
main()

1.1.py(多线程)

PYTHON 完整代码 · 可上下滚动
#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""作业① 多线程版:分两阶段并发抓页面、并发下载图片。

与单线程版使用同一套上限和解析函数:
阶段一:线程池并发抓取页面,只收集图片地址,抓满 MAX_PAGES 页就停;
阶段二:线程池并发下载,自动跳过失效链接,直到成功保存 MAX_IMAGES 张。

学号 102402135 → 最多 35 个页面、135 张图片。
"""

from __future__ import annotations

import time
from concurrent.futures import FIRST_COMPLETED, ThreadPoolExecutor, as_completed, wait

import common as c

WORKERS = 8 # 页面和图片共用 8 个线程;再大容易被站点限流


def grab_page(url: str):
"""线程任务:抓一个页面,返回 ``(url, final_url, html)``。"""

html, final_url = c.fetch_page(url)
return url, final_url, html


def grab_image(url: str):
"""线程任务:下载一张图片,返回 ``(url, content)``。"""

content, _ = c.fetch_image(url)
return url, content


def collect_pages(pool: ThreadPoolExecutor):
"""阶段一:并发抓页面,返回 ``(pages, image_urls, seen_images, failed)``。"""

frontier = list(c.initial_frontier())
seen_pages: set[str] = set()
image_urls: list[str] = []
seen_images: set[str] = set()
pages = 0
failed = 0

while frontier and pages < c.MAX_PAGES:
# 只提交“还允许抓”的页数,避免多抓好几页。
batch = []
while frontier and len(batch) < WORKERS and pages + len(batch) < c.MAX_PAGES:
url = frontier.pop(0)
if url in seen_pages:
continue
seen_pages.add(url)
batch.append(url)
if not batch:
break

futures = [pool.submit(grab_page, url) for url in batch]
for future in as_completed(futures):
url, final_url, html = future.result()
if html is None:
failed += 1
continue

pages += 1
print(f"[页面 {pages:02d}/{c.MAX_PAGES}] {url}")

for link in c.extract_links(html, final_url):
if link not in seen_pages and link not in frontier:
frontier.append(link)

for image_url in c.extract_images(html, final_url):
if image_url not in seen_images:
seen_images.add(image_url)
image_urls.append(image_url)

time.sleep(0.15)

return pages, image_urls, seen_images, failed


def download_images(pool: ThreadPoolExecutor, image_urls: list[str]):
"""阶段二:并发下载,直到成功落盘 MAX_IMAGES 张。

页面中可能混有已经 404 的历史产品图;不能只取前 135 个 URL,
否则遇到失效链接就会少于 135 张。这里持续补充候选 URL,
跳过失效链接,直到成功下载数达到上限。
"""

print(f"\n共收集到 {len(image_urls)} 个图片地址,最多成功下载 {c.MAX_IMAGES} 张\n")

pending = {}
candidates = iter(image_urls)
downloaded = 0
failed = 0

while downloaded < c.MAX_IMAGES:
# 维持最多 WORKERS 个下载任务;失败一个就补一个候选。
while len(pending) < WORKERS:
try:
url = next(candidates)
except StopIteration:
break
pending[pool.submit(grab_image, url)] = url
if not pending:
break

done, _ = wait(pending, return_when=FIRST_COMPLETED)
for future in done:
image_url = pending.pop(future)
_, content = future.result()
if content is None:
failed += 1
print(f" 跳过失效图片 {image_url}")
continue

path = c.save_image(image_url, content)
downloaded += 1
print(
f"[图片 {downloaded:03d}/{c.MAX_IMAGES}] {image_url} "
f"{c.human_size(len(content))} -> {path}"
)
if downloaded >= c.MAX_IMAGES:
break

return downloaded, failed


def main() -> None:
"""多线程入口:先并发抓页,再并发下载,最后统一输出统计。"""

c.banner(f"多线程 / {WORKERS} 线程")
started = time.time()

with ThreadPoolExecutor(max_workers=WORKERS) as pool:
pages, image_urls, _seen, page_failed = collect_pages(pool)
downloaded, image_failed = download_images(pool, image_urls)

c.report(
f"多线程 / {WORKERS} 线程",
pages,
downloaded,
time.time() - started,
page_failed + image_failed,
)


if __name__ == "__main__":
main()

1.1.4 运行结果

单线程运行结果:
9ac39f6a86c722bddf3cc32e9a876784

多线程运行结果:
d0688c7170239b6eddc10bb6ad3dfe96

实际结果:

方式 页数 成功图片 跳过失效链接 耗时
单线程 35 135 2 136.31 秒
多线程(8 线程) 35 135 2 35.31 秒

两个版本都成功保存 135 张,多线程约快 3.9 倍。images/ 下就是这 135 张图片,完整文件名、大小和原始 URL 见 图片清单.csv。

Gitee 文件夹:作业1

1.2 作业②③:Scrapy + XPath + MySQL

1.2.1 作业②:东方财富股票行情

要求:使用 Scrapy 框架 + XPath + MySQL 爬取股票相关信息,掌握 Item 和 Pipeline 的序列化输出。

东方财富的行情列表页 quote.eastmoney.com/center/gridlist.html 是 JavaScript 动态渲染的,直接请求 HTML 后用 //table//tr 取不到股票表格。程序先用 XPath 解析页面标题和 keywords 确认页面结构,再请求页面背后的行情接口:

PYTHON 关键代码
def parse_entry(self, response):
title = response.xpath("//title/text()").get()
keywords = response.xpath("//meta[@name='keywords']/@content").get()
yield scrapy.Request(self.eastmoney_url(1), callback=self.parse_eastmoney)

这里需要说明:XPath 能解析服务端返回的文档结构,但解析不了浏览器执行 JavaScript 后才生成的 DOM。因此作业②采用“XPath 页面确认 + 行情接口解析”的方式;如果必须完全用 XPath 取表格,需要引入 Playwright 或 Selenium 先渲染页面。

东方财富接口在当前网络不可用时,程序自动切换到新浪财经备用接口,两个数据源的字段口径在 Spider 中统一:

Item 字段 含义 东方财富 新浪备用
bStockNo 股票代码 f12 code
bStockName 股票名称 f14 name
bLatestPrice 最新报价 f2 trade
bChangePercent 涨跌幅 f3 changepercent
bChangeAmount 涨跌额 f4 pricechange
bVolume 成交量(手) f5 volume / 100
bTurnover 成交额(元) f6 amount
bAmplitude 振幅 f7 (high-low)/prev_close*100
bHigh / bLow 最高 / 最低 f15 / f16 high / low
bOpen / bPrevClose 今开 / 昨收 f17 / f18 open / settlement

MySQL 表以 bStockNo 为唯一键,Pipeline 使用 INSERT ... ON DUPLICATE KEY UPDATE 更新行情,重复运行不会堆叠重复股票:

SQL 关键代码
INSERT INTO `eastmoney_stock`
(`bStockNo`, `bStockName`, `bLatestPrice`, `bChangePercent`, ...)
VALUES (%s, %s, %s, %s, ...)
ON DUPLICATE KEY UPDATE
`bStockName` = VALUES(`bStockName`),
`bLatestPrice` = VALUES(`bLatestPrice`),
...

实际运行抓取 3 页、每页 80 条,过滤北交所后共 240 条。控制台显示 eastmoney_stock 共 240 条,错误日志为 0。

作业2 运行结果
1dc124390a5974b903422a5d2420081f

作业2 MySQL 前20行
2c9be9cd70309f8b9d7aff1de8a18c75

完整 240 条数据见 作业2/output/eastmoney_stock_20261117_222803.csv 和对应 JSON 文件。

Gitee 文件夹:作业2

1.2.2 作业③:中国银行外汇牌价

要求:使用 Scrapy 框架 + XPath + MySQL 爬取外汇网站数据。

中国银行牌价页 https://www.boc.cn/sourcedb/whpj/ 是服务端直出的静态表格,正好适合 XPath。牌价全部在 id="priceTable" 的表格中,每行 8 个 td:

PYTHON 关键代码
rows = response.xpath('//table[@id="priceTable"]//tr[td]')
for row in rows:
currency = row.xpath("normalize-space(./td[1])").get()
tbp = row.xpath("normalize-space(./td[2])").get()
cbp = row.xpath("normalize-space(./td[3])").get()
tsp = row.xpath("normalize-space(./td[4])").get()
csp = row.xpath("normalize-space(./td[5])").get()
raw_date = row.xpath("normalize-space(./td[7])").get()
raw_time = row.xpath("normalize-space(./td[8])").get()

//tr[td] 会自动跳过使用 th 的表头;normalize-space() 去掉页面源码中的缩进和换行。日期列实际是 2026/11/17 22:10:39,时间列是 22:10:39,用正则分别提取后统一成 YYYY-MM-DD 和 HH:MM:SS。

Item 经 Pipeline 依次完成校验、去重、JSON/CSV 序列化和 MySQL 入库:

PYTHON 关键代码
ITEM_PIPELINES = {
"boc_fx.pipelines.BocFxValidationPipeline": 100,
"boc_fx.pipelines.BocFxDuplicatePipeline": 200,
"boc_fx.pipelines.BocFxJsonPipeline": 300,
"boc_fx.pipelines.BocFxCsvPipeline": 400,
"boc_fx.pipelines.BocFxMySQLPipeline": 500,
}

MySQL 表 boc_fx_rate 使用 DECIMAL(12, 4) 存价格,唯一键为 (currency, quote_date, quote_time)。页面中部分小币种的现汇/现钞买入价为空,程序存 NULL 而不是 0。

实际运行结果:45 条外汇牌价,MySQL 新增 45 条,错误日志 0 条。

作业3 运行结果
71a03f12d8712eebfc33be910f45fa64

作业3 MySQL 45条
e04b074997c8ce899d8b7a5127a2cead

完整数据见 作业3/output/boc_fx_rates_20261116_002358.csv 和对应 JSON 文件。

Gitee 文件夹:作业3

1.2.3 作业②完整代码

items.py

PYTHON 关键代码
# -*- coding: utf-8 -*-
"""作业② Item:东方财富沪深 A 股行情。"""

import scrapy


class StockItem(scrapy.Item):
"""一条股票行情记录。字段名与 MySQL 表列名一一对应。"""

serial_no = scrapy.Field() # 输出用序号(数据库 id 由自增主键生成)
bStockNo = scrapy.Field() # 股票代码
bStockName = scrapy.Field() # 股票名称
bLatestPrice = scrapy.Field() # 最新报价
bChangePercent = scrapy.Field() # 涨跌幅(%)
bChangeAmount = scrapy.Field() # 涨跌额
bVolume = scrapy.Field() # 成交量(手)
bTurnover = scrapy.Field() # 成交额(元)
bAmplitude = scrapy.Field() # 振幅(%)
bHigh = scrapy.Field() # 最高
bLow = scrapy.Field() # 最低
bOpen = scrapy.Field() # 今开
bPrevClose = scrapy.Field() # 昨收
bQuoteTime = scrapy.Field() # 行情抓取时间

spiders/stock.py

PYTHON 完整代码 · 可上下滚动
# -*- coding: utf-8 -*-
"""作业② Spider:东方财富沪深 A 股行情(Scrapy + XPath + JSON 接口)。

东方财富的行情列表页
``https://quote.eastmoney.com/center/gridlist.html`` 是 JavaScript 动态渲染的,
HTML 里没有股票表格,直接写 ``//table//tr`` 只能得到空列表。因此本爬虫:

1. 先请求列表页,用 XPath 解析标题和 keywords,确认页面可访问、结构正常;
2. 再请求页面背后真正的行情接口 ``push2.eastmoney.com/api/qt/clist/get``;
3. 接口不可用时自动切换到新浪财经的公开行情接口,保证实验可复现。

Excel/网页上的“最新价、涨跌幅、成交量”等字段都来自接口返回的 f 编码,
Item 字段与作业要求的表头一一对应。
"""

from __future__ import annotations

import math
from datetime import datetime
from urllib.parse import urlencode

import scrapy

from stock_scrapy.items import StockItem


class EastmoneyStockSpider(scrapy.Spider):
"""抓取沪深 A 股行情列表,按页产出 StockItem。"""

name = "eastmoney_stock"
allowed_domains = ["eastmoney.com", "sina.com.cn"]

entry_url = "https://quote.eastmoney.com/center/gridlist.html"
eastmoney_api = "https://push2.eastmoney.com/api/qt/clist/get"
sina_api = (
"https://vip.stock.finance.sina.com.cn/quotes_service/api/json_v2.php/"
"Market_Center.getHQNodeData"
)
page_size = 80
market_filter = "m:0+t:6,m:0+t:80,m:1+t:2,m:1+t:23"

def __init__(self, max_pages: str | None = None, *args, **kwargs):
"""max_pages 默认为 3 页;可用 ``-a max_pages=5`` 覆盖。"""

super().__init__(*args, **kwargs)
self.max_pages = int(max_pages) if max_pages else 3

def start_requests(self):
"""先请求列表页,在 parse_entry 中用 XPath 做结构确认。"""

yield scrapy.Request(self.entry_url, callback=self.parse_entry, dont_filter=True)

async def start(self):
"""Scrapy 2.13+ 的起始入口;与 start_requests 保持等价。"""

for request in self.start_requests():
yield request

def parse_entry(self, response):
"""XPath 解析页面标题/关键字,然后转入真正的行情接口。"""

title = (response.xpath("//title/text()").get() or "").strip()
keywords = (response.xpath("//meta[@name='keywords']/@content").get() or "").strip()
self.logger.info("东方财富行情页标题:%s", title[:80])
self.logger.info("页面 keywords 长度:%d", len(keywords))
self.logger.info("页面为 JS 动态渲染,转入行情接口抓取(最多 %d 页)", self.max_pages)

yield scrapy.Request(
self.eastmoney_url(1),
callback=self.parse_eastmoney,
errback=self.eastmoney_failed,
meta={"page": 1},
dont_filter=True,
)

def eastmoney_url(self, page: int) -> str:
"""拼装东方财富行情接口 URL。"""

params = {
"pn": page,
"pz": self.page_size,
"po": 1,
"np": 1,
"fltt": 2,
"invt": 2,
"fid": "f3",
"fs": self.market_filter,
"fields": "f2,f3,f4,f5,f6,f7,f12,f14,f15,f16,f17,f18",
}
return f"{self.eastmoney_api}?{urlencode(params, safe='+,:')}"

def sina_url(self, page: int) -> str:
"""拼装新浪财经备用接口 URL。"""

params = {
"page": page,
"num": self.page_size,
"sort": "changepercent",
"asc": 0,
"node": "hs_a",
"symbol": "",
"_s_r_a": "page",
}
return f"{self.sina_api}?{urlencode(params)}"

def parse_eastmoney(self, response):
"""解析东方财富 JSON,逐条产出 Item 并翻页。"""

payload = response.json()
data = payload.get("data") or {}
rows = data.get("diff") or []
if isinstance(rows, dict):
rows = list(rows.values())
total = data.get("total") or 0
page = response.meta["page"]

self.logger.info("东方财富第 %d 页:%d 条,total=%s", page, len(rows), total)
if not rows:
self.logger.warning("东方财富接口无数据,切换新浪备用接口")
yield scrapy.Request(
self.sina_url(1),
callback=self.parse_sina,
meta={"page": 1},
dont_filter=True,
)
return

for row in rows:
yield StockItem(
bStockNo=row.get("f12"),
bStockName=row.get("f14"),
bLatestPrice=self.to_number(row.get("f2")),
bChangePercent=self.to_number(row.get("f3")),
bChangeAmount=self.to_number(row.get("f4")),
bVolume=self.to_number(row.get("f5")),
bTurnover=self.to_number(row.get("f6")),
bAmplitude=self.to_number(row.get("f7")),
bHigh=self.to_number(row.get("f15")),
bLow=self.to_number(row.get("f16")),
bOpen=self.to_number(row.get("f17")),
bPrevClose=self.to_number(row.get("f18")),
bQuoteTime=self.quote_time(),
)

total_pages = min(math.ceil(total / self.page_size), self.max_pages) if total else self.max_pages
if page < total_pages:
yield scrapy.Request(
self.eastmoney_url(page + 1),
callback=self.parse_eastmoney,
errback=self.eastmoney_failed,
meta={"page": page + 1},
dont_filter=True,
)

def eastmoney_failed(self, failure):
"""东方财富接口失败时,自动切换到新浪备用源。"""

page = failure.request.meta.get("page", 1)
self.logger.warning("东方财富接口第 %d 页失败:%s,切换新浪备用源", page, failure.value)
yield scrapy.Request(
self.sina_url(page),
callback=self.parse_sina,
meta={"page": page},
dont_filter=True,
)

def parse_sina(self, response):
"""解析新浪备用 JSON,字段口径转换成与东方财富一致。"""

try:
rows = response.json()
except Exception:
rows = []
page = response.meta["page"]
self.logger.info("新浪备用源第 %d 页:%d 条", page, len(rows))

for row in rows:
code = str(row.get("code") or "").strip()
# 北交所代码以 4/8/9 开头;本作业的行情范围限定为沪深 A 股。
if code.startswith(("4", "8", "9")):
continue

volume = self.to_number(row.get("volume"))
amount = self.to_number(row.get("amount"))
high = self.to_number(row.get("high"))
low = self.to_number(row.get("low"))
prev_close = self.to_number(row.get("settlement"))
amplitude = None
if high is not None and low is not None and prev_close:
amplitude = (high - low) / prev_close * 100

yield StockItem(
bStockNo=code,
bStockName=row.get("name"),
bLatestPrice=self.to_number(row.get("trade")),
bChangePercent=self.to_number(row.get("changepercent")),
bChangeAmount=self.to_number(row.get("pricechange")),
# 新浪 volume 单位是“股”,换算成“手”(1 手 = 100 股)。
bVolume=(volume / 100) if volume is not None else None,
bTurnover=amount,
bAmplitude=amplitude,
bHigh=high,
bLow=low,
bOpen=self.to_number(row.get("open")),
bPrevClose=prev_close,
bQuoteTime=self.quote_time(),
)

if rows and page < self.max_pages:
yield scrapy.Request(
self.sina_url(page + 1),
callback=self.parse_sina,
meta={"page": page + 1},
dont_filter=True,
)

@staticmethod
def to_number(value):
"""接口中的 '-'、空字符串、None 统一转成 None。"""

if value in (None, "", "-", "--"):
return None
try:
return float(value)
except (TypeError, ValueError):
return None

@staticmethod
def quote_time() -> str:
"""同一次抓取使用统一的时间戳。"""

return datetime.now().strftime("%Y-%m-%d %H:%M:%S")

pipelines.py

PYTHON 完整代码 · 可上下滚动
# -*- coding: utf-8 -*-
"""作业② Pipeline:校验、JSON/CSV 序列化、MySQL 入库。"""

from __future__ import annotations

import csv
import json
import logging
from datetime import datetime
from decimal import Decimal, InvalidOperation
from pathlib import Path
from typing import Any, Dict

import pymysql
from scrapy.exceptions import DropItem

from stock_scrapy.db import ensure_schema, mysql_config

logger = logging.getLogger(__name__)

EXPORT_FIELDS = (
"serial_no",
"bStockNo",
"bStockName",
"bLatestPrice",
"bChangePercent",
"bChangeAmount",
"bVolume",
"bTurnover",
"bAmplitude",
"bHigh",
"bLow",
"bOpen",
"bPrevClose",
"bQuoteTime",
)
NUMERIC_FIELDS = (
"bLatestPrice",
"bChangePercent",
"bChangeAmount",
"bVolume",
"bTurnover",
"bAmplitude",
"bHigh",
"bLow",
"bOpen",
"bPrevClose",
)


def to_decimal(value: Any) -> Decimal | None:
"""把接口的数值/字符串转为 Decimal,'-'、空值返回 None。"""

if value is None:
return None
text = str(value).strip().replace(",", "")
if text in {"", "-", "--", "—"}:
return None
try:
return Decimal(text)
except InvalidOperation:
return None


def clean(value: Any) -> str:
"""去除文本首尾空白。"""

return "" if value is None else str(value).replace("\xa0", " ").strip()


class StockValidationPipeline:
"""清洗字段并生成输出序号,缺少代码/名称的记录直接丢弃。"""

def open_spider(self, spider: Any) -> None:
self.serial_no = 0

def process_item(self, item: Any, spider: Any) -> Any:
item["bStockNo"] = clean(item.get("bStockNo"))
item["bStockName"] = clean(item.get("bStockName"))
item["bQuoteTime"] = clean(item.get("bQuoteTime")) or datetime.now().strftime(
"%Y-%m-%d %H:%M:%S"
)

if not item["bStockNo"] or not item["bStockName"]:
raise DropItem(f"缺少股票代码或名称:{dict(item)}")

for field in NUMERIC_FIELDS:
item[field] = to_decimal(item.get(field))

self.serial_no += 1
item["serial_no"] = self.serial_no
return item


class _BaseExportPipeline:
"""JSON/CSV 输出基类。"""

extension = ""

def __init__(self, settings: Any) -> None:
self.output_dir = Path(settings.get("OUTPUT_DIR", "output"))
self.run_id = settings.get("RUN_ID") or datetime.now().strftime("%Y%m%d_%H%M%S")
self.records: list[Dict[str, Any]] = []
self.path: Path | None = None

@classmethod
def from_crawler(cls, crawler: Any) -> "_BaseExportPipeline":
return cls(crawler.settings)

def open_spider(self, spider: Any) -> None:
self.output_dir.mkdir(parents=True, exist_ok=True)
self.path = self.output_dir / f"eastmoney_stock_{self.run_id}.{self.extension}"
self.records = []

def process_item(self, item: Any, spider: Any) -> Any:
record = {}
for field in EXPORT_FIELDS:
value = item.get(field)
record[field] = float(value) if isinstance(value, Decimal) else value
self.records.append(record)
return item

def close_spider(self, spider: Any) -> None:
self._write()
logger.info("已输出 %d 条股票记录到 %s", len(self.records), self.path)

def _write(self) -> None:
raise NotImplementedError


class StockJsonPipeline(_BaseExportPipeline):
"""序列化为 JSON 数组。"""

extension = "json"

def _write(self) -> None:
assert self.path is not None
with self.path.open("w", encoding="utf-8") as fp:
json.dump(self.records, fp, ensure_ascii=False, indent=2, default=str)


class StockCsvPipeline(_BaseExportPipeline):
"""序列化为 CSV,使用 utf-8-sig 方便 Excel 打开。"""

extension = "csv"

def _write(self) -> None:
assert self.path is not None
with self.path.open("w", encoding="utf-8-sig", newline="") as fp:
writer = csv.DictWriter(fp, fieldnames=EXPORT_FIELDS, extrasaction="ignore")
writer.writeheader()
for record in self.records:
row = {}
for key, value in record.items():
if isinstance(value, float):
row[key] = f"{value:.4f}".rstrip("0").rstrip(".")
else:
row[key] = "" if value is None else value
writer.writerow(row)


class StockMySQLPipeline:
"""把 Item 幂等写入 MySQL 表 eastmoney_stock(按股票代码更新)。"""

def __init__(self, settings: Any) -> None:
self.enabled = bool(settings.getbool("MYSQL_ENABLED", True))
self.config = mysql_config(settings)
self.conn: pymysql.connections.Connection | None = None
self.cursor: Any = None
self.inserted = 0
self.updated = 0

@classmethod
def from_crawler(cls, crawler: Any) -> "StockMySQLPipeline":
return cls(crawler.settings)

def open_spider(self, spider: Any) -> None:
if not self.enabled:
logger.warning("MYSQL_ENABLED=False,跳过 MySQL,仅输出 JSON/CSV")
return
ensure_schema(self.config)
self.conn = pymysql.connect(
host=self.config["host"],
port=self.config["port"],
user=self.config["user"],
password=self.config["password"],
database=self.config["database"],
charset="utf8mb4",
autocommit=False,
connect_timeout=10,
read_timeout=30,
write_timeout=30,
)
self.cursor = self.conn.cursor()
logger.info("MySQL 已连接:%s:%s/%s", self.config["host"], self.config["port"], self.config["database"])

def process_item(self, item: Any, spider: Any) -> Any:
if not self.enabled:
return item

sql = """
INSERT INTO `eastmoney_stock`
(`bStockNo`, `bStockName`, `bLatestPrice`, `bChangePercent`,
`bChangeAmount`, `bVolume`, `bTurnover`, `bAmplitude`,
`bHigh`, `bLow`, `bOpen`, `bPrevClose`, `bQuoteTime`)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
ON DUPLICATE KEY UPDATE
`bStockName` = VALUES(`bStockName`),
`bLatestPrice` = VALUES(`bLatestPrice`),
`bChangePercent` = VALUES(`bChangePercent`),
`bChangeAmount` = VALUES(`bChangeAmount`),
`bVolume` = VALUES(`bVolume`),
`bTurnover` = VALUES(`bTurnover`),
`bAmplitude` = VALUES(`bAmplitude`),
`bHigh` = VALUES(`bHigh`),
`bLow` = VALUES(`bLow`),
`bOpen` = VALUES(`bOpen`),
`bPrevClose` = VALUES(`bPrevClose`),
`bQuoteTime` = VALUES(`bQuoteTime`)
"""
values = (
item["bStockNo"],
item["bStockName"],
item["bLatestPrice"],
item["bChangePercent"],
item["bChangeAmount"],
item["bVolume"],
item["bTurnover"],
item["bAmplitude"],
item["bHigh"],
item["bLow"],
item["bOpen"],
item["bPrevClose"],
item["bQuoteTime"],
)
try:
self.cursor.execute(sql, values)
self.conn.commit()
except Exception:
self.conn.rollback()
logger.exception("写入 MySQL 失败:%r", dict(item))
raise

if self.cursor.rowcount == 1:
self.inserted += 1
else:
self.updated += 1
return item

def close_spider(self, spider: Any) -> None:
if self.conn is not None:
self.conn.close()
self.conn = None
if self.enabled:
logger.info("MySQL 入库完成:新增 %d 条,更新 %d 条", self.inserted, self.updated)

2.py

PYTHON 完整代码 · 可上下滚动
#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""作业② 一键运行入口。

用法:
python 2.py # 抓前 3 页(每页 80 条),写入 MySQL
python 2.py --pages 1 # 只抓 1 页,快速验证
python 2.py --no-mysql # 不连数据库,只导出 JSON/CSV
python 2.py --port 3307 --password root
"""

from __future__ import annotations

import argparse
import os
import sys
import warnings
from datetime import datetime
from pathlib import Path

PROJECT_DIR = Path(__file__).resolve().parent
WORKSPACE_DIR = PROJECT_DIR.parent
for path in (PROJECT_DIR, WORKSPACE_DIR / "vendor"):
if path.is_dir() and str(path) not in sys.path:
sys.path.insert(0, str(path))

os.environ.setdefault("SCRAPY_SETTINGS_MODULE", "stock_scrapy.settings")

from scrapy.crawler import CrawlerProcess # noqa: E402
from scrapy.exceptions import ScrapyDeprecationWarning # noqa: E402
from scrapy.utils.project import get_project_settings # noqa: E402

from stock_scrapy.db import ensure_schema, mysql_config # noqa: E402
from stock_scrapy.spiders.stock import EastmoneyStockSpider # noqa: E402

warnings.filterwarnings(
"ignore",
category=ScrapyDeprecationWarning,
message=r".*requires a spider argument.*",
)


def build_parser() -> argparse.ArgumentParser:
"""构造命令行参数。"""

parser = argparse.ArgumentParser(description="Scrapy 抓取东方财富沪深 A 股行情")
parser.add_argument("--pages", type=int, default=3, help="最多抓取页数,默认 3")
parser.add_argument("--no-mysql", action="store_true", help="跳过 MySQL,仅导出 JSON/CSV")
parser.add_argument("--output-dir", default=None, help="JSON/CSV 输出目录")
parser.add_argument("--host", default=None, help="MySQL 主机")
parser.add_argument("--port", type=int, default=None, help="MySQL 端口")
parser.add_argument("--user", default=None, help="MySQL 用户")
parser.add_argument("--password", default=None, help="MySQL 密码")
parser.add_argument("--database", default=None, help="MySQL 数据库名")
parser.add_argument("--log-level", default="INFO", choices=("DEBUG", "INFO", "WARNING", "ERROR"))
return parser


def apply_settings(settings, args: argparse.Namespace) -> None:
"""把命令行参数覆盖到 Scrapy settings。"""

settings.set("LOG_LEVEL", args.log_level, priority="cmdline")
settings.set("RUN_ID", datetime.now().strftime("%Y%m%d_%H%M%S"), priority="cmdline")
if args.no_mysql:
settings.set("MYSQL_ENABLED", False, priority="cmdline")
if args.output_dir:
settings.set("OUTPUT_DIR", str(Path(args.output_dir).resolve()), priority="cmdline")
for arg_name, setting_name in (
("host", "MYSQL_HOST"),
("port", "MYSQL_PORT"),
("user", "MYSQL_USER"),
("password", "MYSQL_PASSWORD"),
("database", "MYSQL_DATABASE"),
):
value = getattr(args, arg_name)
if value is not None:
settings.set(setting_name, value, priority="cmdline")


def print_db_summary(settings) -> None:
"""爬完后从数据库读一次,打印条数和涨幅前 5 名。"""

if not settings.getbool("MYSQL_ENABLED", True):
return
try:
import pymysql

config = mysql_config(settings)
conn = pymysql.connect(
host=config["host"], port=config["port"], user=config["user"],
password=config["password"], database=config["database"], charset="utf8mb4",
)
with conn.cursor() as cur:
cur.execute("SELECT COUNT(*) FROM eastmoney_stock")
total = cur.fetchone()[0]
cur.execute(
"SELECT bStockNo,bStockName,bLatestPrice,bChangePercent,bVolume "
"FROM eastmoney_stock ORDER BY bChangePercent DESC LIMIT 5"
)
rows = cur.fetchall()
conn.close()
print(f"[MySQL] eastmoney_stock 共 {total} 条;涨幅前 5:")
for row in rows:
print(" %s %s 最新=%s 涨跌幅=%s 成交量=%s" % row)
except Exception as exc: # noqa: BLE001
print(f"[MySQL] 查询失败:{exc}")


def main() -> int:
"""初始化数据库、启动爬虫、输出摘要。"""

args = build_parser().parse_args()
settings = get_project_settings()
apply_settings(settings, args)

if settings.getbool("MYSQL_ENABLED", True):
try:
ensure_schema(mysql_config(settings))
except Exception as exc: # noqa: BLE001
config = mysql_config(settings)
print(f"[错误] MySQL 初始化失败:{config['host']}:{config['port']}/{config['database']}")
print(f" {exc}")
print(" 如只需导出 JSON/CSV,可加 --no-mysql。")
return 1

process = CrawlerProcess(settings)
process.crawl(EastmoneyStockSpider, max_pages=args.pages)
# 在 start() 之前拿到 crawler,跑完后才能读取它的统计信息。
crawler = next(iter(process.crawlers))
process.start()

stats = crawler.stats.get_stats()
count = stats.get("item_scraped_count", 0)
errors = stats.get("log_count/ERROR", 0)
output_dir = Path(settings.get("OUTPUT_DIR"))

print("\n" + "=" * 64)
print(f"本次抓取完成:{count} 条股票行情")
print(f"输出目录:{output_dir}")
print(f" - eastmoney_stock_{settings.get('RUN_ID')}.json")
print(f" - eastmoney_stock_{settings.get('RUN_ID')}.csv")
print_db_summary(settings)
print(f"错误日志数:{errors}")
print("=" * 64 + "\n")
# 东方财富接口不可用时会切换到新浪备用源,日志里会留下连接错误;
# 只要最终抓到了数据,就不把这种“已降级处理”的情况判为失败。
return 1 if count == 0 else 0


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

settings.py

PYTHON 完整代码 · 可上下滚动
# -*- coding: utf-8 -*-
"""作业② Scrapy 配置:东方财富行情 → JSON/CSV/MySQL。"""

from pathlib import Path
import os

BASE_DIR = Path(__file__).resolve().parent.parent

BOT_NAME = "stock_scrapy"
SPIDER_MODULES = ["stock_scrapy.spiders"]
NEWSPIDER_MODULE = "stock_scrapy.spiders"

# 东方财富行情接口是公开 JSON 接口,按教学用途低频抓取。
ROBOTSTXT_OBEY = False
CONCURRENT_REQUESTS = 2
DOWNLOAD_DELAY = 0.8
DOWNLOAD_TIMEOUT = 20
RETRY_ENABLED = False
COOKIES_ENABLED = False
TELNETCONSOLE_ENABLED = False
REMOTE_CONTROL_ENABLED = False

USER_AGENT = (
"Mozilla/5.0 (Windows NT 10.0; Win64; x64) "
"AppleWebKit/537.36 (KHTML, like Gecko) "
"Chrome/131.0.0.0 Safari/537.36"
)
DEFAULT_REQUEST_HEADERS = {
"Accept": "application/json, text/plain, */*",
"Accept-Language": "zh-CN,zh;q=0.9",
"Referer": "https://quote.eastmoney.com/",
}

OUTPUT_DIR = os.getenv("OUTPUT_DIR", str(BASE_DIR / "output"))

MYSQL_ENABLED = os.getenv("MYSQL_ENABLED", "1").lower() not in {"0", "false", "no"}
MYSQL_HOST = os.getenv("MYSQL_HOST", "127.0.0.1")
MYSQL_PORT = int(os.getenv("MYSQL_PORT", "3306"))
MYSQL_USER = os.getenv("MYSQL_USER", "root")
MYSQL_PASSWORD = os.getenv("MYSQL_PASSWORD", "")
MYSQL_DATABASE = os.getenv("MYSQL_DATABASE", "spider_db")

ITEM_PIPELINES = {
"stock_scrapy.pipelines.StockValidationPipeline": 100,
"stock_scrapy.pipelines.StockJsonPipeline": 300,
"stock_scrapy.pipelines.StockCsvPipeline": 400,
"stock_scrapy.pipelines.StockMySQLPipeline": 500,
}

LOG_LEVEL = os.getenv("LOG_LEVEL", "INFO")
FEED_EXPORT_ENCODING = "utf-8"

db.py

PYTHON 完整代码 · 可上下滚动
# -*- coding: utf-8 -*-
"""作业② MySQL 建库建表与连接工具。"""

from __future__ import annotations

import re
from typing import Any, Dict

import pymysql

CREATE_TABLE_SQL = """
CREATE TABLE IF NOT EXISTS `eastmoney_stock` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '序号',
`bStockNo` VARCHAR(10) NOT NULL COMMENT '股票代码',
`bStockName` VARCHAR(50) NOT NULL COMMENT '股票名称',
`bLatestPrice` DECIMAL(12, 4) DEFAULT NULL COMMENT '最新报价',
`bChangePercent` DECIMAL(10, 4) DEFAULT NULL COMMENT '涨跌幅(%)',
`bChangeAmount` DECIMAL(12, 4) DEFAULT NULL COMMENT '涨跌额',
`bVolume` DECIMAL(20, 2) DEFAULT NULL COMMENT '成交量(手)',
`bTurnover` DECIMAL(20, 2) DEFAULT NULL COMMENT '成交额(元)',
`bAmplitude` DECIMAL(10, 4) DEFAULT NULL COMMENT '振幅(%)',
`bHigh` DECIMAL(12, 4) DEFAULT NULL COMMENT '最高',
`bLow` DECIMAL(12, 4) DEFAULT NULL COMMENT '最低',
`bOpen` DECIMAL(12, 4) DEFAULT NULL COMMENT '今开',
`bPrevClose` DECIMAL(12, 4) DEFAULT NULL COMMENT '昨收',
`bQuoteTime` DATETIME NOT NULL COMMENT '行情抓取时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_stock_no` (`bStockNo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='东方财富沪深A股行情';
"""


def mysql_config(settings: Any) -> Dict[str, Any]:
"""从 Scrapy settings 读取 MySQL 连接参数。"""

return {
"host": settings.get("MYSQL_HOST", "127.0.0.1"),
"port": int(settings.get("MYSQL_PORT", 3306)),
"user": settings.get("MYSQL_USER", "root"),
"password": settings.get("MYSQL_PASSWORD", ""),
"database": settings.get("MYSQL_DATABASE", "spider_db"),
"charset": "utf8mb4",
}


def _quote_identifier(name: str) -> str:
"""校验数据库名并加反引号,避免配置值注入 SQL。"""

if not re.fullmatch(r"[0-9A-Za-z_]+", name):
raise ValueError(f"非法的数据库名:{name!r}")
return f"`{name}`"


def connect(config: Dict[str, Any], database: str | None = None):
"""创建 PyMySQL 连接;database 为空表示只连接服务器。"""

kwargs = {
"host": config["host"],
"port": config["port"],
"user": config["user"],
"password": config["password"],
"charset": config.get("charset", "utf8mb4"),
"connect_timeout": 10,
"read_timeout": 30,
"write_timeout": 30,
"autocommit": False,
}
target = config["database"] if database is None else database
if target:
kwargs["database"] = target
return pymysql.connect(**kwargs)


def ensure_schema(config: Dict[str, Any]) -> None:
"""创建数据库和 eastmoney_stock 表(幂等)。"""

database = _quote_identifier(config["database"])
conn = connect(config, database="")
try:
with conn.cursor() as cursor:
cursor.execute(
f"CREATE DATABASE IF NOT EXISTS {database} "
"DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"
)
conn.commit()
finally:
conn.close()

conn = connect(config)
try:
with conn.cursor() as cursor:
cursor.execute(CREATE_TABLE_SQL)
conn.commit()
finally:
conn.close()

1.2.4 作业③完整代码

items.py

PYTHON 完整代码 · 可上下滚动
# -*- coding: utf-8 -*-
"""定义外汇牌价 Item,字段与作业要求的表头一一对应。"""

import scrapy


class BocFxItem(scrapy.Item):
"""中国银行外汇牌价数据项。

字段说明:
serial_no: 序号(由去重 Pipeline 按抓取顺序生成)
currency: 货币名称(Currency)
tbp: 现汇买入价(TBP)
cbp: 现钞买入价(CBP)
tsp: 现汇卖出价(TSP)
csp: 现钞卖出价(CSP)
quote_date: 发布日期,格式 YYYY-MM-DD
quote_time: 发布时间,格式 HH:MM:SS
source_url: 数据来源页面
crawled_at: 抓取时间(ISO 8601)
"""

serial_no = scrapy.Field()
currency = scrapy.Field()
tbp = scrapy.Field()
cbp = scrapy.Field()
tsp = scrapy.Field()
csp = scrapy.Field()
quote_date = scrapy.Field()
quote_time = scrapy.Field()
source_url = scrapy.Field()
crawled_at = scrapy.Field()

spiders/boc_whpj.py

PYTHON 完整代码 · 可上下滚动
# -*- coding: utf-8 -*-
"""中国银行外汇牌价 Spider。

目标页面:https://www.boc.cn/sourcedb/whpj/
页面结构:id 为 priceTable 的表格,每个 tr 包含 8 个 td:
货币名称、现汇买入价、现钞买入价、现汇卖出价、现钞卖出价、
中行折算价、发布日期、发布时间。
"""

from __future__ import annotations

import re

import scrapy

from boc_fx.items import BocFxItem

_DATE_RE = re.compile(r"(\d{4})[/-](\d{1,2})[/-](\d{1,2})")
_TIME_RE = re.compile(r"(\d{1,2}):(\d{2}):(\d{2})")


class BocWhpjSpider(scrapy.Spider):
"""抓取中国银行“外汇牌价”列表页。"""

name = "boc_whpj"
allowed_domains = ["boc.cn"]
start_urls = ["https://www.boc.cn/sourcedb/whpj/"]

def parse(self, response: scrapy.http.Response):
"""解析表格中的每一行并生成 Item。

XPath 说明:
//table[@id="priceTable"] 精确定位牌价表,避免抓到导航/页脚表格
//tr[td] 只取含数据单元格的行,自动跳过表头
"""

rows = response.xpath('//table[@id="priceTable"]//tr[td]')
self.logger.info("页面 %s 共解析到 %d 行原始数据", response.url, len(rows))

for row in rows:
currency = self._cell(row, 1)
if not currency or currency == "货币名称":
continue

raw_date = self._cell(row, 7)
raw_time = self._cell(row, 8)
quote_date, quote_time = self.parse_quote_datetime(raw_date, raw_time)

yield BocFxItem(
currency=currency,
tbp=self._cell(row, 2),
cbp=self._cell(row, 3),
tsp=self._cell(row, 4),
csp=self._cell(row, 5),
quote_date=quote_date,
quote_time=quote_time,
source_url=response.url,
)

@staticmethod
def _cell(row: scrapy.selector.Selector, index: int) -> str:
"""读取第 index 个 td 的纯文本,并压缩连续空白。"""

# normalize-space() 会去掉首尾空白并把内部连续空白压成一个空格。
return (row.xpath(f"normalize-space(./td[{index}])").get() or "").replace("\xa0", " ").strip()

@staticmethod
def parse_quote_datetime(raw_date: str, raw_time: str) -> tuple[str, str]:
"""拆分“发布日期”列中的日期与时间。

实际页面第 7 列形如 ``2026/11/16 00:00:05``,第 8 列只有
``00:00:05``。这里优先使用第 8 列的时间,缺失时回退到第 7 列。
"""

date_match = _DATE_RE.search(raw_date or "")
time_match = _TIME_RE.search(raw_time or "") or _TIME_RE.search(raw_date or "")
if not date_match or not time_match:
return "", ""

year, month, day = (int(part) for part in date_match.groups())
hour, minute, second = (int(part) for part in time_match.groups())
return (
f"{year:04d}-{month:02d}-{day:02d}",
f"{hour:02d}:{minute:02d}:{second:02d}",
)

def closed(self, reason: str) -> None:
self.logger.info("Spider 结束,原因:%s", reason)

pipelines.py

PYTHON 完整代码 · 可上下滚动
# -*- coding: utf-8 -*-
"""Item Pipeline:清洗校验、去重、JSON/CSV 序列化以及 MySQL 入库。"""

from __future__ import annotations

import csv
import json
import logging
import re
from datetime import datetime
from decimal import Decimal, InvalidOperation
from pathlib import Path
from typing import Any, Dict

import pymysql
from scrapy.exceptions import DropItem

from boc_fx.db import ensure_schema, mysql_config

logger = logging.getLogger(__name__)

# 输出字段顺序与作业要求的表头一致。
EXPORT_FIELDS = (
"serial_no",
"currency",
"tbp",
"cbp",
"tsp",
"csp",
"quote_date",
"quote_time",
"source_url",
"crawled_at",
)

PRICE_FIELDS = ("tbp", "cbp", "tsp", "csp")
_PRICE_RE = re.compile(r"^[+-]?\d+(?:\.\d+)?$")


def clean_text(value: Any) -> str:
"""去除首尾空白,并把不换行空格统一成普通空格。"""

if value is None:
return ""
return str(value).replace("\xa0", " ").strip()


def parse_price(value: Any) -> Decimal | None:
"""把页面上的价格文本转成 Decimal;空值或非法值返回 None。"""

text = clean_text(value).replace(",", "").replace(",", "")
if not text or text in {"-", "--", "—", "/"}:
return None
if not _PRICE_RE.fullmatch(text):
logger.warning("价格字段格式异常,已置空:%r", value)
return None
try:
# 保留 4 位小数,与 MySQL DECIMAL(12, 4) 的精度保持一致。
return Decimal(text).quantize(Decimal("0.0001"))
except InvalidOperation:
logger.warning("价格字段无法转换为 Decimal,已置空:%r", value)
return None


def item_to_record(item: Any) -> Dict[str, Any]:
"""把 Item 转成可 JSON 序列化的字典(Decimal 转 float)。"""

record: Dict[str, Any] = {}
for field in EXPORT_FIELDS:
value = item.get(field)
record[field] = float(value) if isinstance(value, Decimal) else value
return record


def _csv_value(value: Any) -> Any:
"""CSV 中价格去掉无意义的尾随 0,例如 198.5800 -> 198.58。"""

if isinstance(value, Decimal):
return format(value.normalize(), "f")
return "" if value is None else value


class BocFxValidationPipeline:
"""字段清洗与合法性校验。

页面中部分货币的“现钞买入价/现钞卖出价”为空,属于正常业务情况,
因此只要求货币名称、日期、时间存在,且四个价格至少有一个非空。
"""

def process_item(self, item: Any, spider: Any) -> Any:
for field in ("currency", "quote_date", "quote_time", "source_url"):
item[field] = clean_text(item.get(field))

if not item["currency"] or item["currency"] == "货币名称":
raise DropItem(f"货币名称为空或为表头:{item!r}")

if not re.fullmatch(r"\d{4}-\d{2}-\d{2}", item["quote_date"]):
raise DropItem(f"发布日期格式不正确:{item['quote_date']!r}")
if not re.fullmatch(r"\d{2}:\d{2}:\d{2}", item["quote_time"]):
raise DropItem(f"发布时间格式不正确:{item['quote_time']!r}")

for field in PRICE_FIELDS:
item[field] = parse_price(item.get(field))

if all(item[field] is None for field in PRICE_FIELDS):
raise DropItem(f"四个价格字段全为空:{item['currency']!r}")

item["crawled_at"] = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
return item


class BocFxDuplicatePipeline:
"""按“货币 + 日期 + 时间”去重,并生成输出用的序号。

MySQL 侧还有唯一索引兜底;这里的去重主要避免同一次抓取中
重复行被重复写入,同时保证 JSON/CSV 的序号连续。
"""

def open_spider(self, spider: Any) -> None:
self.seen: set[tuple[str, str, str]] = set()
self.serial_no = 0

def process_item(self, item: Any, spider: Any) -> Any:
key = (item["currency"], item["quote_date"], item["quote_time"])
if key in self.seen:
raise DropItem(f"重复记录:{key}")
self.seen.add(key)
self.serial_no += 1
item["serial_no"] = self.serial_no
return item

def close_spider(self, spider: Any) -> None:
logger.info("去重后共 %d 条外汇牌价记录", len(self.seen))


class _BaseExportPipeline:
"""JSON/CSV 输出 Pipeline 的公共逻辑。"""

extension = ""

def __init__(self, settings: Any) -> None:
self.output_dir = Path(settings.get("OUTPUT_DIR", "output"))
# RUN_ID 由 3.py 统一写入;直接执行 scrapy crawl 时按启动时间生成。
self.run_id = settings.get("RUN_ID") or datetime.now().strftime("%Y%m%d_%H%M%S")
self.records: list[Dict[str, Any]] = []
self.path: Path | None = None

@classmethod
def from_crawler(cls, crawler: Any) -> "_BaseExportPipeline":
return cls(crawler.settings)

def open_spider(self, spider: Any) -> None:
self.output_dir.mkdir(parents=True, exist_ok=True)
self.path = self.output_dir / f"boc_fx_rates_{self.run_id}.{self.extension}"
self.records = []

def process_item(self, item: Any, spider: Any) -> Any:
self.records.append(item_to_record(item))
return item

def close_spider(self, spider: Any) -> None:
self._write()
logger.info("已输出 %d 条记录到 %s", len(self.records), self.path)

def _write(self) -> None:
raise NotImplementedError


class BocFxJsonPipeline(_BaseExportPipeline):
"""把 Item 序列化为 UTF-8 JSON 数组,便于阅读和后续分析。"""

extension = "json"

def _write(self) -> None:
assert self.path is not None
with self.path.open("w", encoding="utf-8") as fp:
json.dump(self.records, fp, ensure_ascii=False, indent=2, default=str)


class BocFxCsvPipeline(_BaseExportPipeline):
"""把 Item 序列化为 CSV,使用 utf-8-sig 方便 Excel 直接打开。"""

extension = "csv"

def _write(self) -> None:
assert self.path is not None
with self.path.open("w", encoding="utf-8-sig", newline="") as fp:
writer = csv.DictWriter(fp, fieldnames=EXPORT_FIELDS, extrasaction="ignore")
writer.writeheader()
for record in self.records:
writer.writerow({key: _csv_value(value) for key, value in record.items()})


class BocFxMySQLPipeline:
"""使用 PyMySQL 把 Item 写入 MySQL。

使用 ``INSERT ... ON DUPLICATE KEY UPDATE``,配合唯一索引实现幂等入库:
同一货币在同一发布日期、发布时间下重复抓取时只更新价格,不产生重复行。
"""

def __init__(self, settings: Any) -> None:
self.enabled = bool(settings.getbool("MYSQL_ENABLED", True))
self.config = mysql_config(settings)
self.conn: pymysql.connections.Connection | None = None
self.cursor: Any = None
self.inserted = 0
self.updated = 0

@classmethod
def from_crawler(cls, crawler: Any) -> "BocFxMySQLPipeline":
return cls(crawler.settings)

def open_spider(self, spider: Any) -> None:
if not self.enabled:
logger.warning("MYSQL_ENABLED=False,跳过 MySQL 入库,仅输出 JSON/CSV")
return

ensure_schema(self.config)
self.conn = pymysql.connect(
host=self.config["host"],
port=self.config["port"],
user=self.config["user"],
password=self.config["password"],
database=self.config["database"],
charset="utf8mb4",
autocommit=False,
connect_timeout=10,
read_timeout=30,
write_timeout=30,
)
self.cursor = self.conn.cursor()
logger.info(
"MySQL 已连接:%s:%s/%s",
self.config["host"],
self.config["port"],
self.config["database"],
)

def process_item(self, item: Any, spider: Any) -> Any:
if not self.enabled:
return item

sql = """
INSERT INTO `boc_fx_rate`
(`currency`, `tbp`, `cbp`, `tsp`, `csp`,
`quote_date`, `quote_time`, `source_url`, `crawled_at`)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
ON DUPLICATE KEY UPDATE
`tbp` = VALUES(`tbp`),
`cbp` = VALUES(`cbp`),
`tsp` = VALUES(`tsp`),
`csp` = VALUES(`csp`),
`source_url` = VALUES(`source_url`),
`crawled_at` = VALUES(`crawled_at`)
"""
values = (
item["currency"],
item["tbp"],
item["cbp"],
item["tsp"],
item["csp"],
item["quote_date"],
item["quote_time"],
item["source_url"],
item["crawled_at"],
)
try:
self.cursor.execute(sql, values)
self.conn.commit()
except Exception:
self.conn.rollback()
logger.exception("写入 MySQL 失败:%r", dict(item))
raise

# rowcount:1 表示新插入,2 表示命中唯一键后更新。
if self.cursor.rowcount == 1:
self.inserted += 1
else:
self.updated += 1
return item

def close_spider(self, spider: Any) -> None:
if self.conn is not None:
self.conn.close()
self.conn = None
if self.enabled:
logger.info("MySQL 入库完成:新增 %d 条,更新 %d 条", self.inserted, self.updated)

3.py

PYTHON 完整代码 · 可上下滚动
#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""作业③ 一键运行入口。

用法:
python 3.py # 抓取并写入 MySQL,同时导出 JSON/CSV
python 3.py --no-mysql # 只抓取并导出 JSON/CSV(调试用)
python 3.py --host 127.0.0.1 --port 3307 --password 123456
"""

from __future__ import annotations

import argparse
import os
import sys
import warnings
from datetime import datetime
from pathlib import Path

# 把项目目录加入模块搜索路径,保证从任意工作目录执行都能找到 boc_fx。
PROJECT_DIR = Path(__file__).resolve().parent
WORKSPACE_DIR = PROJECT_DIR.parent
for path in (PROJECT_DIR, WORKSPACE_DIR / "vendor"):
if path.is_dir() and str(path) not in sys.path:
sys.path.insert(0, str(path))

# 必须在导入 Scrapy 项目设置前指定 settings 模块。
os.environ.setdefault("SCRAPY_SETTINGS_MODULE", "boc_fx.settings")

from scrapy.crawler import CrawlerProcess # noqa: E402
from scrapy.exceptions import ScrapyDeprecationWarning # noqa: E402
from scrapy.utils.project import get_project_settings # noqa: E402

from boc_fx.db import ensure_schema, mysql_config # noqa: E402
from boc_fx.spiders.boc_whpj import BocWhpjSpider # noqa: E402

# Scrapy 2.19 起提示 Pipeline 的 spider 参数将在未来移除。为了兼容课程环境
# 中可能使用的 Scrapy 2.11,本项目仍保留 spider 参数,只屏蔽这一条提示。
warnings.filterwarnings(
"ignore",
category=ScrapyDeprecationWarning,
message=r".*requires a spider argument.*",
)


def build_parser() -> argparse.ArgumentParser:
"""构造命令行参数解析器。"""

parser = argparse.ArgumentParser(description="Scrapy 抓取中国银行外汇牌价并写入 MySQL")
parser.add_argument("--no-mysql", action="store_true", help="跳过 MySQL,仅导出 JSON/CSV")
parser.add_argument("--output-dir", default=None, help="JSON/CSV 输出目录,默认 作业3/output")
parser.add_argument("--host", default=None, help="MySQL 主机,默认 127.0.0.1")
parser.add_argument("--port", type=int, default=None, help="MySQL 端口,默认 3306")
parser.add_argument("--user", default=None, help="MySQL 用户名,默认 root")
parser.add_argument("--password", default=None, help="MySQL 密码,默认空")
parser.add_argument("--database", default=None, help="MySQL 数据库名,默认 spider_db")
parser.add_argument(
"--log-level",
default="INFO",
choices=("DEBUG", "INFO", "WARNING", "ERROR"),
help="Scrapy 日志级别",
)
return parser


def apply_cli_settings(settings, args: argparse.Namespace) -> None:
"""把命令行参数覆盖到 Scrapy settings(优先级高于 settings.py)。"""

settings.set("LOG_LEVEL", args.log_level, priority="cmdline")
if args.no_mysql:
settings.set("MYSQL_ENABLED", False, priority="cmdline")
if args.output_dir:
settings.set("OUTPUT_DIR", str(Path(args.output_dir).resolve()), priority="cmdline")
for arg_name, setting_name in (
("host", "MYSQL_HOST"),
("port", "MYSQL_PORT"),
("user", "MYSQL_USER"),
("password", "MYSQL_PASSWORD"),
("database", "MYSQL_DATABASE"),
):
value = getattr(args, arg_name)
if value is not None:
settings.set(setting_name, value, priority="cmdline")

# JSON 与 CSV 共用同一个 run_id,保证两份文件名一致。
settings.set("RUN_ID", datetime.now().strftime("%Y%m%d_%H%M%S"), priority="cmdline")


def main() -> int:
"""初始化数据库、启动爬虫,并打印本次运行摘要。"""

args = build_parser().parse_args()
settings = get_project_settings()
apply_cli_settings(settings, args)

# 先建库建表,配置错误可以在爬虫启动前快速失败并给出明确提示。
if settings.getbool("MYSQL_ENABLED", True):
config = mysql_config(settings)
try:
ensure_schema(config)
except Exception as exc: # noqa: BLE001 - 入口需要把连接问题完整反馈给使用者
print("\n[错误] 无法连接或初始化 MySQL,请检查服务与参数。")
print(f" 地址:{config['host']}:{config['port']},数据库:{config['database']}")
print(f" 原因:{exc}")
print(" 如只需导出 JSON/CSV,可加 --no-mysql 参数。\n")
return 1
print(
f"[MySQL] 已就绪:{config['host']}:{config['port']}/"
f"{config['database']},表 boc_fx_rate"
)

process = CrawlerProcess(settings)
crawler = process.create_crawler(BocWhpjSpider)
process.crawl(crawler)
process.start()

stats = crawler.stats.get_stats()
item_count = stats.get("item_scraped_count", 0)
error_count = stats.get("log_count/ERROR", 0)
output_dir = Path(settings.get("OUTPUT_DIR"))

print("\n" + "=" * 64)
print(f"本次抓取完成:成功入库/输出 {item_count} 条记录")
print(f"输出目录:{output_dir}")
for suffix in ("json", "csv"):
print(f" - boc_fx_rates_{settings.get('RUN_ID')}.{suffix}")
print(f"错误日志数:{error_count}")
print("=" * 64 + "\n")

return 1 if error_count or item_count == 0 else 0


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

settings.py

PYTHON 完整代码 · 可上下滚动
# -*- coding: utf-8 -*-
"""Scrapy 全局配置。

数据库连接参数优先从环境变量读取,未设置时使用本机开发默认值:
MYSQL_HOST、MYSQL_PORT、MYSQL_USER、MYSQL_PASSWORD、MYSQL_DATABASE
"""

from pathlib import Path
import os

# 项目根目录(作业3/)
BASE_DIR = Path(__file__).resolve().parent.parent


def _env_bool(name: str, default: bool = False) -> bool:
"""把环境变量解析为布尔值,便于用 MYSQL_ENABLED=0 临时关闭入库。"""

value = os.getenv(name)
if value is None:
return default
return value.strip().lower() in {"1", "true", "yes", "on"}


BOT_NAME = "boc_fx"
SPIDER_MODULES = ["boc_fx.spiders"]
NEWSPIDER_MODULE = "boc_fx.spiders"

# 遵守 robots.txt;中国银行页面本身只有一个列表页,抓取压力很小。
ROBOTSTXT_OBEY = True
USER_AGENT = (
"Mozilla/5.0 (Windows NT 10.0; Win64; x64) "
"AppleWebKit/537.36 (KHTML, like Gecko) "
"Chrome/131.0.0.0 Safari/537.36"
)
DEFAULT_REQUEST_HEADERS = {
"Accept": "text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8",
"Accept-Language": "zh-CN,zh;q=0.9,en;q=0.8",
"Referer": "https://www.boc.cn/sourcedb/whpj/",
}

# 单页面、低频抓取,避免给目标网站造成压力。
CONCURRENT_REQUESTS = 1
DOWNLOAD_DELAY = float(os.getenv("DOWNLOAD_DELAY", "1.0"))
DOWNLOAD_TIMEOUT = 30
RETRY_TIMES = 3
COOKIES_ENABLED = False
TELNETCONSOLE_ENABLED = False
# Scrapy 2.19 默认启用远程控制 HTTP 服务;课程实验不需要,且会在用户目录写 job 文件。
REMOTE_CONTROL_ENABLED = False

# JSON/CSV/MySQL 三个 Pipeline 的公共输出目录。
OUTPUT_DIR = os.getenv("OUTPUT_DIR", str(BASE_DIR / "output"))

# MySQL 配置
MYSQL_ENABLED = _env_bool("MYSQL_ENABLED", True)
MYSQL_HOST = os.getenv("MYSQL_HOST", "127.0.0.1")
MYSQL_PORT = int(os.getenv("MYSQL_PORT", "3306"))
MYSQL_USER = os.getenv("MYSQL_USER", "root")
MYSQL_PASSWORD = os.getenv("MYSQL_PASSWORD", "")
MYSQL_DATABASE = os.getenv("MYSQL_DATABASE", "spider_db")

ITEM_PIPELINES = {
# 先清洗校验,再去重,最后依次输出 JSON、CSV 和写入 MySQL。
"boc_fx.pipelines.BocFxValidationPipeline": 100,
"boc_fx.pipelines.BocFxDuplicatePipeline": 200,
"boc_fx.pipelines.BocFxJsonPipeline": 300,
"boc_fx.pipelines.BocFxCsvPipeline": 400,
"boc_fx.pipelines.BocFxMySQLPipeline": 500,
}

LOG_LEVEL = os.getenv("LOG_LEVEL", "INFO")
FEED_EXPORT_ENCODING = "utf-8"

db.py

PYTHON 完整代码 · 可上下滚动
# -*- coding: utf-8 -*-
"""MySQL 连接与建库建表工具。

将数据库操作集中在本模块,Pipeline 和入口脚本共用同一套配置与表结构,
避免出现“入口建的表”和“Pipeline 写入的表”不一致的问题。
"""

from __future__ import annotations

import re
from typing import Any, Dict

import pymysql

# 表结构说明:
# id 序号,自增主键
# currency 货币名称
# tbp/cbp 现汇买入价 / 现钞买入价
# tsp/csp 现汇卖出价 / 现钞卖出价
# quote_date 发布日期
# quote_time 发布时间
# 同一货币在同一发布日期、发布时间下只保留一条记录,重复抓取时执行更新。
CREATE_TABLE_SQL = """
CREATE TABLE IF NOT EXISTS `boc_fx_rate` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '序号',
`currency` VARCHAR(50) NOT NULL COMMENT '货币名称',
`tbp` DECIMAL(12, 4) DEFAULT NULL COMMENT '现汇买入价 TBP',
`cbp` DECIMAL(12, 4) DEFAULT NULL COMMENT '现钞买入价 CBP',
`tsp` DECIMAL(12, 4) DEFAULT NULL COMMENT '现汇卖出价 TSP',
`csp` DECIMAL(12, 4) DEFAULT NULL COMMENT '现钞卖出价 CSP',
`quote_date` DATE NOT NULL COMMENT '发布日期',
`quote_time` TIME NOT NULL COMMENT '发布时间',
`source_url` VARCHAR(255) NOT NULL COMMENT '数据来源',
`crawled_at` DATETIME NOT NULL COMMENT '抓取时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_currency_datetime` (`currency`, `quote_date`, `quote_time`),
KEY `idx_quote_date` (`quote_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='中国银行外汇牌价';
"""


def mysql_config(settings: Any) -> Dict[str, Any]:
"""从 Scrapy settings 中提取 PyMySQL 连接参数。"""

return {
"host": settings.get("MYSQL_HOST", "127.0.0.1"),
"port": int(settings.get("MYSQL_PORT", 3306)),
"user": settings.get("MYSQL_USER", "root"),
"password": settings.get("MYSQL_PASSWORD", ""),
"database": settings.get("MYSQL_DATABASE", "spider_db"),
"charset": "utf8mb4",
}


def _quote_identifier(name: str) -> str:
"""校验并引用数据库名,防止配置值被当作 SQL 片段执行。"""

if not re.fullmatch(r"[0-9A-Za-z_]+", name):
raise ValueError(f"非法的数据库名:{name!r}")
return f"`{name}`"


def connect_mysql(config: Dict[str, Any], database: str | None = None) -> pymysql.connections.Connection:
"""创建 MySQL 连接。

Args:
config: 包含 host/port/user/password/database/charset 的字典。
database: 指定要连接的库;传 None 表示连接服务器但不选库,
用于执行 CREATE DATABASE。
"""

kwargs = {
"host": config["host"],
"port": config["port"],
"user": config["user"],
"password": config["password"],
"charset": config.get("charset", "utf8mb4"),
"connect_timeout": 10,
"read_timeout": 30,
"write_timeout": 30,
"autocommit": False,
}
target_db = config["database"] if database is None else database
if target_db:
kwargs["database"] = target_db
return pymysql.connect(**kwargs)


def ensure_schema(config: Dict[str, Any]) -> None:
"""创建数据库和数据表(幂等操作)。

先以“不选库”的方式连接,确保数据库存在;再连接到目标库建表。
"""

database = _quote_identifier(config["database"])

server_conn = connect_mysql(config, database="")
try:
with server_conn.cursor() as cursor:
cursor.execute(
f"CREATE DATABASE IF NOT EXISTS {database} "
"DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"
)
server_conn.commit()
finally:
server_conn.close()

conn = connect_mysql(config)
try:
with conn.cursor() as cursor:
cursor.execute(CREATE_TABLE_SQL)
conn.commit()
finally:
conn.close()


def fetch_all(config: Dict[str, Any], sql: str) -> list[tuple]:
"""执行查询并返回全部结果,供导出脚本和截图脚本复用。"""

conn = connect_mysql(config)
try:
with conn.cursor() as cursor:
cursor.execute(sql)
return cursor.fetchall()
finally:
conn.close()

二、实验心得

2.1 心得体会
通过本次实验,我熟悉了 requests、BeautifulSoup、Scrapy 和 MySQL 的基本使用方法。三个任务的数据来源并不相同:气象网图片直接写在 HTML 页面中,需要翻页、去重和控制数量;股票行情和外汇牌价更适合用 Scrapy 的 Item、Pipeline 组织采集流程,再统一写入数据库。

在图片爬虫任务中,我使用广度优先搜索在站内翻页,并把页面链接和图片 URL 分别去重。单线程版本按页面顺序逐张下载,多线程版本把页面抓取和图片下载拆成两个阶段并发执行。通过两种方式的对比,我体会到图片下载属于 IO 密集型任务,合理使用线程池可以明显缩短等待时间;同时,文件名使用 URL 哈希生成,避免了不同目录下同名图片互相覆盖的问题。

在 Scrapy 任务中,我先分析页面结构,能用 XPath 直接定位的表格就逐行解析;对于动态渲染的页面,则通过浏览器开发者工具分析实际请求的数据接口,再把返回字段统一映射到 Item。Pipeline 依次完成字段校验、去重、JSON/CSV 序列化和 MySQL 入库,使采集、清洗和存储彼此独立,代码结构比单文件脚本更清晰。MySQL 表使用合适的主键和唯一键,价格字段使用 DECIMAL 类型,重复运行时通过更新已有记录保证结果稳定。

通过完成 240 条股票行情和 45 条外汇牌价的采集与入库,我进一步认识到数据采集不仅是“把数据抓下来”,还包括字段命名、空值处理、重复数据控制、数据库表结构设计和采集结果校验。只有把这几部分都做好,采集结果才便于后续查询和分析。

2.2 作业总结
本次作业完成了图片、股票行情和外汇牌价三类数据的采集,覆盖了从需求分析、页面/接口分析、代码实现、数据入库到结果展示的完整流程。三个任务分别对应不同的页面形态和处理方式,让我对数据采集的常用技术路线有了更具体的理解。

在实现过程中,我学习了如何根据页面特点选择解析方式,如何使用 Scrapy 的 Item 和 Pipeline 组织数据流,如何设计 MySQL 表结构并保证重复运行时的数据一致性,也学会了用截图、CSV/JSON 和数据库查询结果共同验证采集结果。

总体来说,这次实验提高了我分析网页结构、编写爬虫程序和设计数据存储方案的能力,也让我对数据采集项目的完整开发过程有了更清晰的认识。

最终结果:

  • 作业①:35 页、135 张图片;
  • 作业②:240 条股票行情,MySQL 表 eastmoney_stock;
  • 作业③:45 条外汇牌价,MySQL 表 boc_fx_rate。

完整代码、CSV/JSON 数据和运行截图均已放入 Gitee 对应作业文件夹。

posted @ 2026-11-17 23:47  好的id  阅读(2)  评论(0)    收藏  举报