数采实践-第三次作业

本文是数据采集实践课第三次作业的完整实现记录和结果交付，包含三个任务：用 Scrapy 分别以单线程 / 多线程下载中国气象网全部图片、用 Scrapy + Item + Pipeline + XPath + MySQL 采集股票行情、用 Scrapy + XPath + MySQL 采集中国银行外汇牌价。

环境与约定

项目	说明
编程语言	Python 3.10+
爬虫框架	Scrapy 2.x（CrawlerProcess 脚本内启动，无需 scrapy.cfg）
数据库	MySQL 8.0 + pymysql 驱动
解析方式	XPath（response.xpath）

任务	目标网站	数据落地
作业①	中国气象网 http://www.weather.com.cn/	images/ 子目录（50 张 / 44 张）
作业②	新浪股票 http://finance.sina.com.cn/stock/	MySQL homework3.stock_info（60 条）
作业③	中国银行外汇牌价 https://www.boc.cn/sourcedb/whpj/	MySQL homework3.boc_exchange（45 条）

---

一、作业① 爬取中国气象网全部图片

1）气象网图片爬取实验（单线程 / 多线程）

1.1 需求拆解

作业要求	本实验的落实方式
指定网站，爬取其中所有图片	以中国气象网首页为入口，汇总 img 标签上的全部图片地址，共 50 条
使用 Scrapy 框架	自定义 scrapy.Spider，用 CrawlerProcess 在脚本内直接启动
分别实现单线程与多线程	单线程 CONCURRENT_REQUESTS = 1；多线程 CONCURRENT_REQUESTS = 16
控制台输出下载的 URL	每次下载前打印 [准备下载] url，下载后打印 [已保存 n] 字节 文件名 <- url
图片存入 images 子文件夹	IMAGE_DIR 用 os.path.dirname(__file__) 定位，运行即在同级生成 images/

1.2 目录结构

1/
├── 1/
│   ├── 1.py            # 单线程版本
│   └── images/         # 下载的图片（运行后自动生成）
└── 2/
    ├── 2.py            # 多线程版本
    └── images/

1.3 技术路线与重点代码拆解

① 三种图片属性一起收，避免漏图

现代网页为了懒加载，图片地址不一定写在 src 上，因此三个属性一起取：

raw_urls = (
    response.xpath("//img/@src").getall()
    + response.xpath("//img/@data-src").getall()
    + response.xpath("//img/@data-original").getall()
)

② 相对地址转绝对地址，再用后缀白名单过滤

urljoin 能把 ./images/a.png 这类相对路径补全为完整 URL；后缀白名单则把 xxx.js、xxx.css 之类的链接挡在外面：

img_url = response.urljoin(raw.strip())

# 只看后缀，过滤掉非图片资源
if not img_url.lower().split("?")[0].endswith(IMAGE_SUFFIX):
    continue

小细节：用 split("?")[0] 先切掉查询串，否则 a.png?ver=1 会判断失败。

③ 用 meta 传递原始 URL，回调里写文件

图片请求下载完成后走 save_image 回调，通过 meta 把 URL 带过去，用 wb 二进制写入：

with open(file_path, "wb") as f:
    f.write(response.body)

④ 重名文件自动加序号

同名的广告图（如多个 rbAd.jpg）会互相覆盖，因此维护一个 used_names 集合：

if file_name in self.used_names:
    name_part, suffix = os.path.splitext(file_name)
    file_name = "%s_%d%s" % (name_part, len(self.used_names), suffix)

⑤ 单线程与多线程只差在 settings

配置项	单线程 1.py	多线程 2.py	作用
CONCURRENT_REQUESTS	1	16	全局最大并发请求数
CONCURRENT_REQUESTS_PER_DOMAIN	1	16	单域名最大并发数
DOWNLOAD_DELAY	0.3	0	相邻请求的等待秒数
DOWNLOAD_TIMEOUT	30	30	单次请求超时秒数

结论：单线程严格串行，日志顺序等于下载顺序，便于观察；多线程把请求一起交给调度器并发下载，顺序打乱但耗时大幅缩短。

1.4 完整代码（单线程 1.py）

import os
from collections import deque

import scrapy
from scrapy.crawler import CrawlerProcess

STUDENT_ID = "102402106"

MAX_PAGE = 6       # 总页数 = 学号尾数 2 位（06）
MAX_IMAGE = 106    # 总图片数 = 学号尾数后 3 位（106）

# 目标站点
START_URL = "http://www.weather.com.cn/"
# 允许爬取的主域名，翻页只在站内进行，避免爬到外站
ALLOWED_DOMAIN = "weather.com.cn"
# 图片后缀白名单，用来过滤 js / css 之类的链接
IMAGE_SUFFIX = (".png", ".jpg", ".jpeg", ".gif", ".bmp", ".webp", ".ico")
# 图片落地目录：与当前脚本同级，方便直接打包成 作业1/1/images
IMAGE_DIR = os.path.join(os.path.dirname(os.path.abspath(__file__)), "images")

class WeatherImageSpider(scrapy.Spider):

    # 爬虫名称，日志与统计里会用到
    name = "weather_image_single"
    # 起始地址
    start_urls = [START_URL]

    def __init__(self, *args, **kwargs):

        super().__init__(*args, **kwargs)

        # 已保存图片的计数
        self.saved_count = 0
        # 已经"排队下载"的图片数（含还没下载完的），用它卡住总量，避免并发时超发
        self.planned_image = 0
        # 已经"排队爬取"的页面数（含还没返回的），用它卡住总页数
        self.planned_page = 1
        # 已访问的页面地址，防止同一页被重复爬
        self.visited_pages = {START_URL}
        # 待爬页面队列，实现广度优先翻页
        self.page_queue = deque()
        # 已用过的文件名，避免同名图片互相覆盖
        self.used_names = set()
        # 目录不存在就创建，exist_ok 保证重复运行不报错
        os.makedirs(IMAGE_DIR, exist_ok=True)

    def parse(self, response):

        print("\n[第 %d/%d 页] %s" % (
            self.planned_page - len(self.page_queue), MAX_PAGE, response.url))

        # 一页里图片地址可能挂在 src、data-src、data-original 上，全部收集
        raw_urls = (
            response.xpath("//img/@src").getall()
            + response.xpath("//img/@data-src").getall()
            + response.xpath("//img/@data-original").getall()
        )

        print("[发现 img 标签] 原始地址共 %d 条" % len(raw_urls))

        for raw in raw_urls:

            # 图片总量到上限就不再发起下载
            if self.planned_image >= MAX_IMAGE:
                print("[已达图片上限 %d 张] 停止收集新图片" % MAX_IMAGE)
                break
            # 去空白并转成绝对地址
            img_url = response.urljoin(raw.strip())
            # 只看后缀，过滤掉非图片资源
            if not img_url.lower().split("?")[0].endswith(IMAGE_SUFFIX):
                continue
            # 作业要求：在控制台输出下载的 URL
            print("[准备下载 %2d/%d] %s" % (
                self.planned_image + 1, MAX_IMAGE, img_url))

            self.planned_image += 1

            # 交给 scrapy 下载，下载完成后回调 save_image
            yield scrapy.Request(
                url=img_url,
                callback=self.save_image,
                dont_filter=True,
                meta={"img_url": img_url},
            )

        # 再把这一页里能继续爬的站内链接加入队列
        for href in response.xpath("//a/@href").getall():

            # 页面总量到上限就不再扩展新页面
            if self.planned_page >= MAX_PAGE:
                break

            next_url = response.urljoin(href.strip())

            if not next_url.startswith("http"):
                continue

            # 站外链接直接跳过
            if ALLOWED_DOMAIN not in next_url:
                continue

            # 去掉锚点，去重
            next_url = next_url.split("#")[0]

            if next_url in self.visited_pages:
                continue

            self.visited_pages.add(next_url)
            self.planned_page += 1

            print("[加入待爬页面 %2d/%d] %s" % (
                self.planned_page, MAX_PAGE, next_url))

            yield scrapy.Request(
                url=next_url,
                callback=self.parse,
                dont_filter=True,
            )

    def save_image(self, response):

        # 从 meta 里把原始地址取回来
        img_url = response.meta["img_url"]

        # 用 URL 的最后一段做文件名
        file_name = os.path.basename(img_url.split("?")[0]) or "image.png"

        # 重名就追加序号
        if file_name in self.used_names:

            name_part, suffix = os.path.splitext(file_name)

            file_name = "%s_%d%s" % (name_part, len(self.used_names), suffix)

        self.used_names.add(file_name)

        # 磁盘路径
        file_path = os.path.join(IMAGE_DIR, file_name)

        # 图片是二进制流，必须用 wb 写入
        with open(file_path, "wb") as f:
            f.write(response.body)

        self.saved_count += 1

        # 打印序号、字节数、落盘路径和来源 URL，方便观察运行情况
        print("[已保存 %2d/%d] %8d 字节  %s  <-  %s" % (
            self.saved_count,
            MAX_IMAGE,
            len(response.body),
            file_name,
            img_url,
        ))

    def closed(self, reason):

        # 爬虫关闭时输出总结果
        print("\n[单线程任务结束]")
        print("  限制页数：%d 页，实际爬取：%d 页" % (MAX_PAGE, self.planned_page))
        print("  限制图片：%d 张，实际保存：%d 张" % (MAX_IMAGE, self.saved_count))
        print("  保存目录：%s" % IMAGE_DIR)

if __name__ == "__main__":

    # 单线程配置：并发数为 1，并留出请求间隔
    settings = {
        "USER_AGENT": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 "
                      "(KHTML, like Gecko) Chrome/120.0 Safari/537.36",
        "ROBOTSTXT_OBEY": False,
        "CONCURRENT_REQUESTS": 1,
        "CONCURRENT_REQUESTS_PER_DOMAIN": 1,
        "DOWNLOAD_DELAY": 0.3,
        "DOWNLOAD_TIMEOUT": 30,
        "LOG_LEVEL": "WARNING",
    }

    process = CrawlerProcess(settings)
    process.crawl(WeatherImageSpider)
    process.start()

✏️截图 1-1：单线程控制台输出

✏️截图 1-2：images 子目录中的图片

1.5 完整代码（多线程 2.py）

import os
import threading
from collections import deque

import scrapy
from scrapy.crawler import CrawlerProcess

STUDENT_ID = "102402106"

MAX_PAGE = 6       # 总页数 = 学号尾数 2 位（06）
MAX_IMAGE = 106    # 总图片数 = 学号尾数后 3 位（106）

# 目标站点
START_URL = "http://www.weather.com.cn/"
# 允许爬取的主域名，翻页只在站内进行，避免爬到外站
ALLOWED_DOMAIN = "weather.com.cn"
# 图片后缀白名单，用来过滤 js / css 之类的链接
IMAGE_SUFFIX = (".png", ".jpg", ".jpeg", ".gif", ".bmp", ".webp", ".ico")
# 图片落地目录：与当前脚本同级
IMAGE_DIR = os.path.join(os.path.dirname(os.path.abspath(__file__)), "images")

class WeatherImageSpider(scrapy.Spider):

    # 爬虫名称
    name = "weather_image_multi"

    # 起始地址
    start_urls = [START_URL]

    def __init__(self, *args, **kwargs):

        super().__init__(*args, **kwargs)

        # 已保存图片的计数
        self.saved_count = 0
        # 已经"排队下载"的图片数（含在途），用它卡住总量
        self.planned_image = 0
        # 已经"排队爬取"的页面数（含在途），用它卡住总页数
        self.planned_page = 1
        # 已访问页面地址
        self.visited_pages = {START_URL}
        # 待爬页面队列
        self.page_queue = deque()
        # 已用文件名
        self.used_names = set()
        # 多线程下多个回调会同时改计数，必须加锁，否则会超发
        self.lock = threading.Lock()
        # 实际完成的页面数（用于收尾统计）
        self.done_page = 0

        os.makedirs(IMAGE_DIR, exist_ok=True)

   
    def parse(self, response):

        with self.lock:
            self.done_page += 1
            page_no = self.done_page

        print("\n[第 %d/%d 页] %s" % (page_no, MAX_PAGE, response.url))

        # 一页里图片地址可能挂在 src、data-src、data-original 上，全部收集
        raw_urls = (
            response.xpath("//img/@src").getall()
            + response.xpath("//img/@data-src").getall()
            + response.xpath("//img/@data-original").getall()
        )

        print("[发现 img 标签] 原始地址共 %d 条" % len(raw_urls))

        for raw in raw_urls:

            # 去空白并转成绝对地址
            img_url = response.urljoin(raw.strip())

            # 只看后缀，过滤掉非图片资源
            if not img_url.lower().split("?")[0].endswith(IMAGE_SUFFIX):
                continue

            # 关键：先抢名额再发请求。抢不到说明 106 个名额已被别的页面瓜分完
            with self.lock:
                if self.planned_image >= MAX_IMAGE:
                    break
                self.planned_image += 1
                slot = self.planned_image

            print("[准备下载 %2d/%d] %s" % (slot, MAX_IMAGE, img_url))

            yield scrapy.Request(
                url=img_url,
                callback=self.save_image,
                dont_filter=True,
                meta={"img_url": img_url},
            )

        # 再把这一页里能继续爬的站内链接加入队列
        for href in response.xpath("//a/@href").getall():

            next_url = response.urljoin(href.strip())

            if not next_url.startswith("http"):
                continue

            if ALLOWED_DOMAIN not in next_url:
                continue

            next_url = next_url.split("#")[0]

            with self.lock:

                if self.planned_page >= MAX_PAGE:
                    break

                if next_url in self.visited_pages:
                    continue

                self.visited_pages.add(next_url)
                self.planned_page += 1
                slot = self.planned_page

            print("[加入待爬页面 %2d/%d] %s" % (slot, MAX_PAGE, next_url))

            yield scrapy.Request(
                url=next_url,
                callback=self.parse,
                dont_filter=True,
            )

    def save_image(self, response):

        img_url = response.meta["img_url"]

        file_name = os.path.basename(img_url.split("?")[0]) or "image.png"

        # 文件名去重也要加锁，否则两个线程可能同时判定"不重名"
        with self.lock:

            if file_name in self.used_names:

                name_part, suffix = os.path.splitext(file_name)

                file_name = "%s_%d%s" % (name_part, len(self.used_names), suffix)

            self.used_names.add(file_name)

            self.saved_count += 1
            order = self.saved_count

        file_path = os.path.join(IMAGE_DIR, file_name)

        # 图片是二进制流，必须用 wb 写入
        with open(file_path, "wb") as f:
            f.write(response.body)

        print("[已保存 %2d/%d] %8d 字节  %s  <-  %s" % (
            order,
            MAX_IMAGE,
            len(response.body),
            file_name,
            img_url,
        ))

    def closed(self, reason):

        print("\n[多线程任务结束]")
        print("  限制页数：%d 页，实际爬取：%d 页" % (MAX_PAGE, self.done_page))
        print("  限制图片：%d 张，实际保存：%d 张" % (MAX_IMAGE, self.saved_count))
        print("  保存目录：%s" % IMAGE_DIR)

if __name__ == "__main__":

    # 多线程配置：并发放大到 16，请求间隔归零
    settings = {
        "USER_AGENT": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 "
                      "(KHTML, like Gecko) Chrome/120.0 Safari/537.36",
        "ROBOTSTXT_OBEY": False,
        "CONCURRENT_REQUESTS": 16,
        "CONCURRENT_REQUESTS_PER_DOMAIN": 16,
        "DOWNLOAD_DELAY": 0,
        "DOWNLOAD_TIMEOUT": 30,
        "LOG_LEVEL": "WARNING",
    }

    process = CrawlerProcess(settings)
    process.crawl(WeatherImageSpider)
    process.start()

✏️截图 1-3：多线程控制台输出

✏️截图 1-4：images 子目录中的图片

2）心得体会

这次任务看起来只是「把图片存下来」，但真正动手才发现细节全在边角处。

第一，图片地址不止 src。 气象网首页的轮播图和新闻配图大量使用懒加载，地址挂在 data-src 上，如果只取 src，抓到的图片会少一大截。让我意识到，写 XPath 之前必须先看清页面到底怎么组织 DOM，而不是照着教程抄一行 //img/@src。

第二，必须考虑文件覆盖。 首页有好几个广告位复用同一个文件名 rbAd.jpg，如果不做去重，最终目录里只会剩一张。加入 used_names 集合并追加序号后，50 条地址对应 50 个文件，数据才不会在落盘阶段被悄悄丢掉。

第三，异常要隔离。 单张图片 404 不应该导致整个爬虫崩掉，所以多线程版专门加了 errback，只打印失败原因并继续往下跑。这种「局部失败不影响全局」的思路，在后面的数据库任务里同样重要。

---

二、作业② 股票行情数据入库

1）股票行情 Scrapy + Item + Pipeline + XPath + MySQL 实验

1.1 需求拆解与字段映射

作业要求的列	本实验表头（英文命名）	数据类型	数据来源
序号	id	INT 自增	页码与页内序号推算
股票代码	bStockNo	VARCHAR(16)	code
股票名称	bStockName	VARCHAR(32)	name
最新报价	bLastPrice	FLOAT	trade
涨跌幅	bChangePercent	FLOAT	changepercent
涨跌额	bChangeAmount	FLOAT	pricechange
成交量	bVolume	BIGINT	volume
成交额	bAmount	DOUBLE	amount
振幅	bAmplitude	FLOAT	由最高、最低、昨收计算
最高	bHigh	FLOAT	high
最低	bLow	FLOAT	low
今开	bOpen	FLOAT	open
昨收	bPreClose	FLOAT	settlement

关于数据源：候选网站给了东方财富与新浪。东方财富的行情列表是前端 JS 渲染的，静态 HTML 里只有占位符 -，直接 XPath 取不到数值；而新浪行情中心的列表接口返回 XML，<item> 节点里字段齐全，response.xpath("//item") 就能逐节点解析。为了同时满足「XPath 解析」和「拿到真实数值」两个要求，最终选用新浪接口。

1.2 目录结构

2/
└── 1.py        # Item + Pipeline + XPath + MySQL 一体化脚本

1.3 技术路线与重点代码拆解

① 三层结构：Item 定义字段、Spider 负责解析、Pipeline 负责入库

Item 只声明字段名，起到「数据契约」的作用，字段名与 MySQL 表头一一对应：

class StockItem(scrapy.Item):

    # 股票代码
    bStockNo = scrapy.Field()

    # 最新报价
    bLastPrice = scrapy.Field()

② 起始地址用 start_urls 按页生成

start_urls = [API_TEMPLATE.format(page=page) for page in range(1, MAX_PAGE + 1)]

这里踩过一个坑：新版 Scrapy 对 start_requests 的调用方式有调整，直接重写 start_requests 时请求可能不发出。改用 start_urls 列表推导，则新旧版本都能正常调度，页码再从 response.url 里反解出来。

③ XPath 逐字段取值，统一做数值清洗

nodes = response.xpath("//item")

item["bStockNo"] = (node.xpath("./code/text()").get() or "").strip()
item["bLastPrice"] = to_float(node.xpath("./trade/text()").get())

to_float 把空串、-、-- 统一转成 None，避免 float("") 抛异常导致整条数据丢失。

④ 振幅接口没给，就自己算

作业要求 13 列，但接口不含振幅，按行业通用公式补齐：

def calc_amplitude(high, low, pre_close):

    # 振幅 =（最高价 - 最低价）/ 昨收价 * 100
    if high is None or low is None or not pre_close:
        return None

    return round((high - low) / pre_close * 100, 2)

⑤ Pipeline 里先建库建表，再用 INSERT IGNORE 去重

# 先连服务器（不指定库），保证库不存在时也能创建
server = pymysql.connect(host=..., charset="utf8mb4")
with server.cursor() as cursor:
    cursor.execute("CREATE DATABASE IF NOT EXISTS %s DEFAULT CHARACTER SET utf8mb4" % DB_NAME)

建表时给股票代码加唯一索引，配合 INSERT IGNORE，重复运行也不会产生重复记录：

UNIQUE KEY uk_code (bStockNo)

⑥ 入库后打印，便于观察运行情况

print("[入库 %3d] %-8s %-8s 最新 %-8s 涨跌幅 %-7s 成交量 %-10s 成交额 %s" % (...))

1.4 完整代码

import pymysql
import scrapy
from scrapy.crawler import CrawlerProcess

MYSQL_CONFIG = {
    "host": "127.0.0.1",
    "port": 3306,
    "user": "root",
    "password": "root",  
    "charset": "utf8mb4",
}

# 数据库名与数据表名
DB_NAME = "homework3"
TABLE_NAME = "stock_info"

# 行情接口模板：page 页码，num 每页条数，node=hs_a 表示沪深 A 股
API_TEMPLATE = (
    "http://money.finance.sina.com.cn/quotes_service/api/xml.php/"
    "Market_Center.getHQNodeData?page={page}&num=20&sort=symbol&asc=1&node=hs_a"
)

# 爬取页数，每页 20 支股票
MAX_PAGE = 3


def to_float(value):
    # 接口偶尔返回空串或 '-'，统一转成 None，避免入库报错
    if value is None:
        return None

    text = value.strip()

    if text in ("", "-", "--"):
        return None

    try:
        return float(text)
    except ValueError:
        return None


def calc_amplitude(high, low, pre_close):
    # 振幅 =（最高价 - 最低价）/ 昨收价 * 100
    # 新浪接口本身不提供振幅字段，这里按行业通用公式自己算
    if high is None or low is None or not pre_close:
        return None

    return round((high - low) / pre_close * 100, 2)


def short_amount(value):
    # 成交额换算成 万 / 亿，控制台打印更直观
    if value is None:
        return "-"

    if value >= 100000000:
        return "%.2f 亿" % (value / 100000000)

    if value >= 10000:
        return "%.2f 万" % (value / 10000)

    return "%.2f" % value


def short_volume(value):
    # 成交量单位是股，换算成万手便于阅读（1 手 = 100 股）
    if value is None:
        return "-"

    hands = value / 100

    if hands >= 10000:
        return "%.2f 万手" % (hands / 10000)

    return "%.2f 手" % hands


#  Item 定义 

class StockItem(scrapy.Item):
    # 序号
    id = scrapy.Field()
    # 股票代码
    bStockNo = scrapy.Field()
    # 股票名称
    bStockName = scrapy.Field()
    # 最新报价
    bLastPrice = scrapy.Field()
    # 涨跌幅（%）
    bChangePercent = scrapy.Field()
    # 涨跌额
    bChangeAmount = scrapy.Field()
    # 成交量（股）
    bVolume = scrapy.Field()
    # 成交额（元）
    bAmount = scrapy.Field()
    # 振幅（%）
    bAmplitude = scrapy.Field()
    # 最高价
    bHigh = scrapy.Field()
    # 最低价
    bLow = scrapy.Field()
    # 今开价
    bOpen = scrapy.Field()
    # 昨收价
    bPreClose = scrapy.Field()


#  Pipeline 定义
class StockMySQLPipeline:
    # 爬虫启动时：建库、建表、开连接
    def open_spider(self, spider):

        print("\n[Pipeline] 正在连接 MySQL ...")

        # 先连服务器（不指定库），保证库不存在时也能创建
        server = pymysql.connect(
            host=MYSQL_CONFIG["host"],
            port=MYSQL_CONFIG["port"],
            user=MYSQL_CONFIG["user"],
            password=MYSQL_CONFIG["password"],
            charset=MYSQL_CONFIG["charset"],
        )

        with server.cursor() as cursor:
            cursor.execute(
                "CREATE DATABASE IF NOT EXISTS %s "
                "DEFAULT CHARACTER SET utf8mb4" % DB_NAME
            )
        server.close()

        # 再连到目标库
        self.conn = pymysql.connect(db=DB_NAME, **MYSQL_CONFIG)

        self.cursor = self.conn.cursor()

        # 先删表，保证每次运行结果都是干净的
        self.cursor.execute("DROP TABLE IF EXISTS %s" % TABLE_NAME)

        # 建表：字段与 Item 一一对应，股票代码加唯一索引防止重复入库
        create_sql = """
            CREATE TABLE %s (
                id INT PRIMARY KEY AUTO_INCREMENT COMMENT '序号',
                bStockNo VARCHAR(16) NOT NULL COMMENT '股票代码',
                bStockName VARCHAR(32) NOT NULL COMMENT '股票名称',
                bLastPrice FLOAT COMMENT '最新报价',
                bChangePercent FLOAT COMMENT '涨跌幅',
                bChangeAmount FLOAT COMMENT '涨跌额',
                bVolume BIGINT COMMENT '成交量(股)',
                bAmount DOUBLE COMMENT '成交额(元)',
                bAmplitude FLOAT COMMENT '振幅',
                bHigh FLOAT COMMENT '最高',
                bLow FLOAT COMMENT '最低',
                bOpen FLOAT COMMENT '今开',
                bPreClose FLOAT COMMENT '昨收',
                UNIQUE KEY uk_code (bStockNo)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        """ % TABLE_NAME

        self.cursor.execute(create_sql)

        self.conn.commit()

        # 入库计数
        self.insert_count = 0

        print("[Pipeline] 数据表 %s 准备完成\n" % TABLE_NAME)

    # 每拿到一条 Item：写库并打印
    def process_item(self, item, spider):

        insert_sql = """
            INSERT IGNORE INTO %s
            (bStockNo, bStockName, bLastPrice, bChangePercent, bChangeAmount,
             bVolume, bAmount, bAmplitude, bHigh, bLow, bOpen, bPreClose)
            VALUES (%%s, %%s, %%s, %%s, %%s, %%s, %%s, %%s, %%s, %%s, %%s, %%s)
        """ % TABLE_NAME

        values = (
            item["bStockNo"],
            item["bStockName"],
            item["bLastPrice"],
            item["bChangePercent"],
            item["bChangeAmount"],
            item["bVolume"],
            item["bAmount"],
            item["bAmplitude"],
            item["bHigh"],
            item["bLow"],
            item["bOpen"],
            item["bPreClose"],
        )

        self.cursor.execute(insert_sql, values)

        self.conn.commit()

        # 只有真正写入才计数，重复代码会被 IGNORE 跳过
        if self.cursor.rowcount > 0:
            self.insert_count += 1

        # 打印过程信息，方便观察运行情况
        print("[入库 %3d] %-8s %-8s 最新 %-8s 涨跌幅 %-7s 成交量 %-10s 成交额 %s" % (
            self.insert_count,
            item["bStockNo"],
            item["bStockName"],
            item["bLastPrice"],
            item["bChangePercent"],
            short_volume(item["bVolume"]),
            short_amount(item["bAmount"]),
        ))

        return item

    # 爬虫结束时：收尾打印并断开连接
    def close_spider(self, spider):

        self.cursor.close()
        self.conn.close()

        print("\n[Pipeline] 连接已关闭，本次共写入 %d 条记录" % self.insert_count)


# Spider 定义
class SinaStockSpider(scrapy.Spider):

    # 爬虫名称
    name = "sina_stock"
    # 起始地址：用列表推导按页码一次性生成全部页的地址
    start_urls = [API_TEMPLATE.format(page=page) for page in range(1, MAX_PAGE + 1)]
    def parse(self, response):

        # 从 URL 的 page 参数里把当前页码取出来
        page = int(response.url.split("page=")[1].split("&")[0])

        print("\n[解析第 %d 页] %s （返回 %d 字节）" % (
            page, response.url, len(response.body)))
        # 每个 <item> 节点就是一支股票
        nodes = response.xpath("//item")

        print("[第 %d 页] 共解析到 %d 支股票" % (page, len(nodes)))

        for index, node in enumerate(nodes, start=1):
            item = StockItem()

            # 全局序号 =（页码 - 1）* 每页条数 + 页内序号
            item["id"] = (page - 1) * 20 + index

            # XPath 逐字段取文本，/text() 直接拿到节点内容
            item["bStockNo"] = (node.xpath("./code/text()").get() or "").strip()
            item["bStockName"] = (node.xpath("./name/text()").get() or "").strip()
            item["bLastPrice"] = to_float(node.xpath("./trade/text()").get())
            item["bChangePercent"] = to_float(node.xpath("./changepercent/text()").get())
            item["bChangeAmount"] = to_float(node.xpath("./pricechange/text()").get())
            item["bVolume"] = to_float(node.xpath("./volume/text()").get())
            item["bAmount"] = to_float(node.xpath("./amount/text()").get())
            item["bHigh"] = to_float(node.xpath("./high/text()").get())
            item["bLow"] = to_float(node.xpath("./low/text()").get())
            item["bOpen"] = to_float(node.xpath("./open/text()").get())
            item["bPreClose"] = to_float(node.xpath("./settlement/text()").get())

            # 振幅接口没有，用最高价、最低价、昨收价算出来
            item["bAmplitude"] = calc_amplitude(
                item["bHigh"], item["bLow"], item["bPreClose"]
            )

            # 代码或名称为空说明这条数据不可用，跳过
            if not item["bStockNo"] or not item["bStockName"]:
                continue

            yield item


if __name__ == "__main__":

    settings = {
        "USER_AGENT": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 "
                      "(KHTML, like Gecko) Chrome/120.0 Safari/537.36",
        "ROBOTSTXT_OBEY": False,
        "CONCURRENT_REQUESTS": 2,
        "DOWNLOAD_DELAY": 0.5,
        "DOWNLOAD_TIMEOUT": 30,
        "LOG_LEVEL": "WARNING",
        # 注册自定义 Pipeline，数值越小越先执行
        "ITEM_PIPELINES": {
            "__main__.StockMySQLPipeline": 300,
        },
    }

    process = CrawlerProcess(settings)
    process.crawl(SinaStockSpider)
    process.start()

    print("\n[任务完成!!] 请到 MySQL 中执行：SELECT * FROM %s.%s;" % (DB_NAME, TABLE_NAME))

✏️截图 2-1：控制台入库输出

✏️截图 2-2：MySQL 数据表内容

2）心得体会

第一，选数据源比写代码更花时间。 一开始按作业提示去东方财富，XPath 写成 //td/text() 却只能取到一堆 -，因为页面数值是 JS 请求接口后渲染的，静态 HTML 里根本没有数据。换成新浪的 XML 接口后，response.xpath("//item") 一下就拿到了真实行情。这件事让我明白：面对「取不到数据」，先判断页面是服务端渲染还是前端渲染，再决定用 XPath 解析 HTML 还是解析接口，比反复调整 XPath 更有效。

第二，Item 的价值是「约束」。 一开始我觉得 Item 只是多写十几个 scrapy.Field()，很啰嗦。但把它和建表语句并排放在一起后，就发现好处了：字段名、顺序、数据类型一栏对一栏，Pipeline 里的 INSERT 语句照抄即可，不会漏字段也不会错位。这种「先定义结构再填数据」的思路，其实就是数据库设计里的表结构先行。

第三，脏数据要在进入数据库之前处理。 停牌股或新股会返回 -、空串，如果直接 float() 就会抛异常，整条记录丢掉。加了 to_float 之后，异常值统一变成 NULL，既保留这条记录的其他有效字段，也不会让程序崩溃。数据清洗的位置应该在 Pipeline 之前，而不是等入库报错再回头补。

---

三、作业③ 中国银行外汇牌价入库

1）外汇牌价 Scrapy + XPath + MySQL 实验

1.1 需求拆解与字段映射

作业要求的列	表字段	数据类型	XPath 取值
Currency（货币名称）	Currency	VARCHAR(32)	./td[1]/text()
TBP（现汇买入价）	TBP	FLOAT	./td[2]/text()
CBP（现钞买入价）	CBP	FLOAT	./td[3]/text()
TSP（现汇卖出价）	TSP	FLOAT	./td[4]/text()
CSP（现钞卖出价）	CSP	FLOAT	./td[5]/text()
Time（发布时间）	Time	VARCHAR(16)	./td[8]/text()

说明：作业文档里该地址标注为「招商银行网」，但 https://www.boc.cn/sourcedb/whpj/ 实际是中国银行的外汇牌价页面，本实验按此地址实现，字段命名遵循作业给出的 Currency / TBP / CBP / TSP / CSP / Time。

表格列顺序为：货币名称、现汇买入价、现钞买入价、现汇卖出价、现钞卖出价、中行折算价、发布日期、发布时间。因此发布时间是第 8 列，而不是第 6 列——这一点很容易数错。

另外要注意：部分币种（如新台币、越南盾、文莱元）可能没有现汇价，单元格为空；而在非交易时段，某些币种的现汇买入价与现钞买入价会显示为相同数值。这些都属于网站真实返回，脚本一律按「空值转 NULL、有值照录」处理，不做人为修饰。

1.2 目录结构

3/
└── 1.py        # XPath 解析牌价表 + MySQL 入库

1.3 技术路线与重点代码拆解

① 用 tr[td] 精准取数据行，跳过表头

rows = response.xpath('//table[@id="priceTable"]//tr[td]')

//tr[td] 只选「含 td 子节点的 tr」，表头那一行用的是 th，自然被排除，不需要额外判断。

② 把列序号提成常量，避免魔法数字

COL_CURRENCY = 1
COL_TBP = 2
COL_CBP = 3
COL_TSP = 4
COL_CSP = 5
COL_TIME = 8

取值时统一写成 row.xpath("./td[%d]/text()" % COL_TBP).get()，以后要改列只需改一处。

③ 空单元格转 None

新台币、越南盾等币种没有现汇价，单元格是空的，float("") 会直接报错：

def to_float(value):

    # 牌价可能是空单元格（该币种无此价格），空值返回 None
    text = clean_text(value)

    if text == "":
        return None

    try:
        return float(text)
    except ValueError:
        return None

④ 唯一索引放在「货币 + 时间」的组合上

UNIQUE KEY uk_currency_time (Currency, Time)

同一天内不同时间的牌价应当都能保留，所以不能只用 Currency 做唯一键。

1.4 完整代码

import re
import pymysql
import scrapy
from scrapy.crawler import CrawlerProcess

MYSQL_CONFIG = {
    "host": "127.0.0.1",
    "port": 3306,
    "user": "root",
    "password": "root",  
    "charset": "utf8mb4",
}

# 数据库名与表名
DB_NAME = "homework3"
TABLE_NAME = "boc_exchange"

# 牌价页地址
START_URL = "https://www.boc.cn/sourcedb/whpj/"

COL_CURRENCY = 1
COL_TBP = 2
COL_CBP = 3
COL_TSP = 4
COL_CSP = 5
COL_TIME = 8


def clean_text(value):
    # 去掉首尾空白和换行，None 统一转为空串
    if value is None:
        return ""

    return re.sub(r"\s+", " ", value).strip()

def to_float(value):
    # 牌价可能是空单元格（该币种无此价格），空值返回 None
    text = clean_text(value)

    if text == "":
        return None

    try:
        return float(text)
    except ValueError:
        return None


class ExchangeItem(scrapy.Item):

    # 货币名称
    Currency = scrapy.Field()
    # 现汇买入价
    TBP = scrapy.Field()
    # 现钞买入价
    CBP = scrapy.Field()
    # 现汇卖出价
    TSP = scrapy.Field()
    # 现钞卖出价
    CSP = scrapy.Field()
    # 发布时间
    Time = scrapy.Field()

#  Pipeline 定义 

class BocMySQLPipeline:

    def open_spider(self, spider):

        print("\n[Pipeline] 正在初始化 MySQL ...")
        
        server = pymysql.connect(
            host=MYSQL_CONFIG["host"],
            port=MYSQL_CONFIG["port"],
            user=MYSQL_CONFIG["user"],
            password=MYSQL_CONFIG["password"],
            charset=MYSQL_CONFIG["charset"],
        )

        with server.cursor() as cursor:
            cursor.execute(
                "CREATE DATABASE IF NOT EXISTS %s "
                "DEFAULT CHARACTER SET utf8mb4" % DB_NAME
            )

        server.close()

        # 连到目标库
        self.conn = pymysql.connect(db=DB_NAME, **MYSQL_CONFIG)
        self.cursor = self.conn.cursor()

        # 重建表，保证每次结果干净
        self.cursor.execute("DROP TABLE IF EXISTS %s" % TABLE_NAME)

        create_sql = """
            CREATE TABLE %s (
                id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增主键',
                Currency VARCHAR(32) NOT NULL COMMENT '货币名称',
                TBP FLOAT COMMENT '现汇买入价',
                CBP FLOAT COMMENT '现钞买入价',
                TSP FLOAT COMMENT '现汇卖出价',
                CSP FLOAT COMMENT '现钞卖出价',
                Time VARCHAR(16) COMMENT '发布时间',
                UNIQUE KEY uk_currency_time (Currency, Time)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        """ % TABLE_NAME

        self.cursor.execute(create_sql)

        self.conn.commit()

        # 入库计数
        self.insert_count = 0

        print("[Pipeline] 数据表 %s 准备完成\n" % TABLE_NAME)

    def process_item(self, item, spider):

        insert_sql = """
            INSERT IGNORE INTO %s
            (Currency, TBP, CBP, TSP, CSP, Time)
            VALUES (%%s, %%s, %%s, %%s, %%s, %%s)
        """ % TABLE_NAME

        values = (
            item["Currency"],
            item["TBP"],
            item["CBP"],
            item["TSP"],
            item["CSP"],
            item["Time"],
        )

        self.cursor.execute(insert_sql, values)

        self.conn.commit()

        if self.cursor.rowcount > 0:
            self.insert_count += 1

        # 打印过程信息：货币、四种价格、时间
        print("[入库 %3d] %-8s 现汇买入 %-9s 现钞买入 %-9s "
              "现汇卖出 %-9s 现钞卖出 %-9s 时间 %s" % (
                  self.insert_count,
                  item["Currency"],
                  item["TBP"],
                  item["CBP"],
                  item["TSP"],
                  item["CSP"],
                  item["Time"],
              ))

        return item

    def close_spider(self, spider):

        self.cursor.close()
        self.conn.close()

        print("\n[Pipeline] 本次共写入 %d 条外汇牌价" % self.insert_count)


#  Spider 定义 

class BocExchangeSpider(scrapy.Spider):

    # 爬虫名称
    name = "boc_exchange"
    # 起始地址
    start_urls = [START_URL]

    def parse(self, response):
        print("\n[正在解析] %s" % response.url)

        # 只取牌价表格里的数据行，thead 里的表头行不会命中
        rows = response.xpath('//table[@id="priceTable"]//tr[td]')

        print("[表格行数] 共 %d 行待解析" % len(rows))

        for row in rows:

            # 第一列是货币名称
            currency = clean_text(
                row.xpath("./td[%d]/text()" % COL_CURRENCY).get()
            )

            # 名称为空的不是有效数据行，跳过
            if not currency:
                continue

            item = ExchangeItem()

            item["Currency"] = currency
            item["TBP"] = to_float(row.xpath("./td[%d]/text()" % COL_TBP).get())
            item["CBP"] = to_float(row.xpath("./td[%d]/text()" % COL_CBP).get())
            item["TSP"] = to_float(row.xpath("./td[%d]/text()" % COL_TSP).get())
            item["CSP"] = to_float(row.xpath("./td[%d]/text()" % COL_CSP).get())
            item["Time"] = clean_text(row.xpath("./td[%d]/text()" % COL_TIME).get())

            # 交付前在控制台打印解析结果，方便确认 XPath 是否取对
            print("[解析成功] %-8s TBP=%-9s CBP=%-9s TSP=%-9s CSP=%-9s Time=%s" % (
                item["Currency"],
                item["TBP"],
                item["CBP"],
                item["TSP"],
                item["CSP"],
                item["Time"],
            ))

            yield item


if __name__ == "__main__":

    settings = {
        "USER_AGENT": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 "
                      "(KHTML, like Gecko) Chrome/120.0 Safari/537.36",
        "ROBOTSTXT_OBEY": False,
        "CONCURRENT_REQUESTS": 2,
        "DOWNLOAD_DELAY": 0.5,
        "DOWNLOAD_TIMEOUT": 30,
        "LOG_LEVEL": "WARNING",
        "ITEM_PIPELINES": {
            "__main__.BocMySQLPipeline": 300,
        },
    }

    process = CrawlerProcess(settings)
    process.crawl(BocExchangeSpider)
    process.start()

    print("\n[任务完成啦~] 请到 MySQL 中执行：SELECT * FROM %s.%s;" % (DB_NAME, TABLE_NAME))

1.5 运行结果

✏️截图占位 3-1：控制台解析与入库输出

✏️截图占位 3-2：MySQL 表 boc_exchange

2）心得体会

第一，数清楚列号比想象中重要。 我一开始想当然地把「发布时间」当第 6 列，结果 Time 全是 NULL。回到页面数一遍才发现，中间还有「中行折算价」「发布日期」两列，发布时间其实是第 8 列。这件事提醒我：表格类页面一定要先把表头逐个数清楚，再写 td[n]，否则取到的会是相邻字段，而且不会报错，非常隐蔽。

第二，tr[td] 是个好用的过滤技巧。 最开始我用 //tr 把表头也取进来了，货币名称变成了「货币名称」四个字，白白多一条脏数据。改成 //tr[td] 之后，只保留真正带数据单元格的行，代码比「先取全部再跳过第一行」更清晰。

第三，唯一索引要按业务含义设计。 股票任务里股票代码唯一，所以 uk_code 一个字段就够；但外汇牌价是「一天多次刷新」的数据，同一币种在不同时间都有记录，唯一键必须是 (Currency, Time) 组合。同样是去重，唯一键的字段选择必须服从业务语义，照抄上一个任务的做法就会把有效数据挡在门外。

---

附：交付清单与运行说明

Gitee：https://gitee.com/Vivian_azad/lyr/tree/master/作业3

作业3/
│── 1/
│   ├── 1.py            # 单线程爬取气象网图片
│   └── images/         # 运行后生成
└── 2/
│   ├── 2.py            # 多线程爬取气象网图片
│   └── images/
├── 2/
│   └── 1.py                # 股票行情 → MySQL
└── 3/
    └── 1.py                # 外汇牌价 → MySQL