数据采集第三次作业：图片并发爬取与Scrapy金融数据入库
本次作业分为图片采集和金融数据采集两部分。图片选择影像福大作为入口；股票使用东方财富的个股页面及其行情接口，外汇使用中国银行外汇牌价。
学号为 102302152，因此设置 最多访问52个HTML页面、最多下载152个图片URL。股票和外汇使用 Scrapy 的 Item、Pipeline 与 MySQL，源网页、接口响应和数据库导出文件均随代码保存。
本轮采集日期为 2026年11月10日。
一、限定范围内的单线程与多线程图片爬取
1. 作业代码与运行结果
范围与实现方法
“抓取所有图片”必须同时服从题目的数量限制。本程序以影像福大为起点，按广度优先访问同域名的 .htm/.html 页面，链接深度最多2层；从 img 的 src、data-src 和 data-original 中提取图片地址。图片也限制在 news.fzu.edu.cn 域名内，不采集CSS背景图或执行脚本后生成的图片。
每发出一个HTML请求前先检查52页上限，失败页面也占用页数额度。图片URL去掉片段标识后去重，候选数达到152就停止发现。本次实际访问 50页 后得到152个不同图片URL，因此没有继续请求剩余页面，也不把结果表述为“全站图片”。
发现阶段只执行一次，两组下载共用 targets.json。单线程使用1个工作线程，多线程使用4个工作线程，均调用同一个下载函数。这样比较的是同一批图片的下载阶段耗时，网页发现耗时不计入两组比较。
单张图片设置10 MiB大小上限、连接与读取超时。收到HTTP响应后还要用Pillow验证内容，避免把返回200的错误网页保存成图片；文件扩展名取实际图片格式。工作线程返回结构化结果，由主线程输出URL并生成清单，避免多线程打印相互穿插。
image_crawler/crawl.py
"""在页数、图片数上限内发现图片；用相同URL清单比较1线程与4线程。"""
import argparse
import hashlib
import io
import json
import time
from collections import deque
from concurrent.futures import ThreadPoolExecutor, as_completed
from datetime import datetime
from pathlib import Path
from urllib.parse import urldefrag, urljoin, urlsplit, urlunsplit
import requests
from lxml import html, etree
from PIL import Image
ROOT = Path(__file__).resolve().parent
PAGE_LIMIT, IMAGE_LIMIT = 52, 152
MAX_BYTES = 10 * 1024 * 1024
HEADERS = {'User-Agent': 'Mozilla/5.0 (compatible; DataCollectionCourse/1.0)'}
def canonical_url(base, value):
"""合并相对路径并去掉片段；拒绝data/javascript等非网络地址。"""
if not value:
return None
url = urldefrag(urljoin(base, value.strip()))[0]
parts = urlsplit(url)
if parts.scheme not in ('http', 'https') or not parts.hostname:
return None
return urlunsplit((parts.scheme.lower(), parts.netloc.lower(), parts.path, parts.query, ''))
def extract_links(text, base, host):
"""图片按DOM顺序去重；页面和图片都限制在种子域名。"""
try:
doc = html.fromstring(text)
except etree.ParserError as error:
raise ValueError("空页面或无法解析的HTML") from error
images, pages = [], []
for raw in doc.xpath('//img/@src | //img/@data-src | //img/@data-original'):
url = canonical_url(base, raw)
if url and urlsplit(url).hostname == host and url not in images:
images.append(url)
for raw in doc.xpath('//a/@href'):
url = canonical_url(base, raw)
if (url and urlsplit(url).hostname == host and not urlsplit(url).query
and urlsplit(url).path.lower().endswith(('.htm', '.html')) and url not in pages):
pages.append(url)
return images, pages
def validate_limits(max_pages, max_images):
if not 1 <= max_pages <= PAGE_LIMIT or not 1 <= max_images <= IMAGE_LIMIT:
raise ValueError('页数必须为1..52，图片数必须为1..152（由学号102302152确定）')
def discover(seed, max_pages=PAGE_LIMIT, max_images=IMAGE_LIMIT, max_depth=2, delay=0.3):
"""广度优先发现；先检查额度再发请求，失败页面也计入访问上限。"""
validate_limits(max_pages, max_images)
host = urlsplit(seed).hostname
queue, queued, visited, targets, seen = deque([(seed, 0)]), {seed}, [], [], set()
folder = ROOT / 'sources'
folder.mkdir(exist_ok=True)
started = time.perf_counter()
with requests.Session() as session:
session.headers.update(HEADERS)
while queue and len(visited) < max_pages and len(targets) < max_images:
url, depth = queue.popleft()
record = {'url': url, 'depth': depth}
visited.append(record)
try:
response = session.get(url, timeout=(8, 20), allow_redirects=False)
response.raise_for_status()
if response.status_code != 200 or 'html' not in response.headers.get('Content-Type', ''):
raise ValueError('非200 HTML页面或重定向，不继续跟随')
response.encoding = 'utf-8'
path = folder / f'page_{len(visited):03d}.html'
path.write_bytes(response.content)
record.update(status=response.status_code, file=str(path.relative_to(ROOT)))
images, links = extract_links(response.text, url, host)
for image_url in images:
if image_url not in seen and len(targets) < max_images:
seen.add(image_url)
targets.append({'id': len(targets) + 1, 'url': image_url, 'page_url': url})
if depth < max_depth:
for link in links:
if link not in queued:
queued.add(link)
queue.append((link, depth + 1))
print(f'[页面 {len(visited):02d}/{max_pages}] {url}；累计图片URL {len(targets)}', flush=True)
except (requests.RequestException, ValueError, OSError) as error:
record['error'] = str(error)
print(f'[页面失败] {url} {error}', flush=True)
time.sleep(delay)
result = {'seed': seed, 'collected_at': datetime.now().astimezone().isoformat(),
'max_pages': max_pages, 'max_images': max_images, 'max_depth': max_depth,
'pages_visited': len(visited), 'page_records': visited, 'images': targets,
'discovery_seconds': round(time.perf_counter() - started, 3)}
(ROOT / 'targets.json').write_text(json.dumps(result, ensure_ascii=False, indent=2), encoding='utf-8')
if not targets:
raise ValueError('没有发现图片，不能进行下载比较')
return result
def download_one(target, output):
"""限制响应大小，验证真实图片后原样保存；失败不留下伪图片。"""
result = dict(target)
try:
with requests.get(target['url'], headers={**HEADERS, 'Referer': target['page_url']},
timeout=(8, 25), stream=True, allow_redirects=False) as response:
response.raise_for_status()
if response.status_code != 200:
raise ValueError('图片响应不是200')
chunks, size = [], 0
for chunk in response.iter_content(65536):
size += len(chunk)
if size > MAX_BYTES:
raise ValueError('超过单张10MiB限制')
chunks.append(chunk)
data = b''.join(chunks)
with Image.open(io.BytesIO(data)) as picture:
extension = {'JPEG': 'jpg', 'PNG': 'png', 'GIF': 'gif', 'WEBP': 'webp', 'MPO': 'mpo'}.get(picture.format)
dimensions = picture.size
picture.verify()
if extension is None:
raise ValueError('不支持的图片格式')
digest = hashlib.sha256(data).hexdigest()
filename = f"{target['id']:03d}_{hashlib.sha256(target['url'].encode()).hexdigest()[:10]}.{extension}"
(output / filename).write_bytes(data)
result.update(ok=True, file='images/' + filename, bytes=len(data), sha256=digest, dimensions=dimensions)
except (requests.RequestException, ValueError, OSError, SyntaxError, Image.DecompressionBombError) as error:
result.update(ok=False, error=str(error))
return result
def download_manifest(mode, retry_failed=False):
manifest = json.loads((ROOT / 'targets.json').read_text(encoding='utf-8'))
targets = manifest['images']
validate_limits(manifest['max_pages'], len(targets))
if len({x['url'] for x in targets}) != len(targets):
raise ValueError('下载清单存在重复URL')
workers = {'single': 1, 'multi': 4}[mode]
run = ROOT / mode
output = run / 'images'
output.mkdir(parents=True, exist_ok=True)
previous = None
if retry_failed:
previous = json.loads((run / 'manifest.json').read_text(encoding='utf-8'))
targets = [x for x in previous['images'] if not x['ok']]
if not targets:
return previous
if not retry_failed and any(output.iterdir()):
raise ValueError(f'{output}已有文件，请先移到备份目录，避免新旧结果混合')
started_at = datetime.now().astimezone().isoformat()
started, results = time.perf_counter(), []
with ThreadPoolExecutor(max_workers=workers) as pool:
futures = [pool.submit(download_one, item, output) for item in targets]
for future in as_completed(futures):
item = future.result()
results.append(item)
print(f"[{'成功' if item['ok'] else '失败'} {item['id']:03d}] {item['url']}" +
(f" -> {item['file']}" if item['ok'] else ' ' + item['error']), flush=True)
results.sort(key=lambda x: x['id'])
summary = {'mode': mode, 'workers': workers, 'started_at': started_at,
'pages_visited': manifest['pages_visited'], 'attempted': len(targets),
'success': sum(x['ok'] for x in results), 'failed': sum(not x['ok'] for x in results),
'download_seconds': round(time.perf_counter() - started, 3), 'images': results}
if previous is not None:
retry_summary = {k:v for k,v in summary.items() if k != 'images'}
replacements = {x['id']:x for x in results}
merged = [replacements.get(x['id'],x) for x in previous['images']]
for old in previous['images']:
if old['id'] in replacements:
replacements[old['id']]['previous_error'] = old.get('error')
replacements[old['id']].pop('error', None) if replacements[old['id']]['ok'] else None
summary = {**previous, 'initial_success':previous.get('initial_success',previous['success']),
'initial_failed':previous.get('initial_failed',previous['failed']),
'retries':previous.get('retries',[]) + [retry_summary], 'images':merged,
'success':sum(x['ok'] for x in merged), 'failed':sum(not x['ok'] for x in merged)}
(run / 'manifest.json').write_text(json.dumps(summary, ensure_ascii=False, indent=2), encoding='utf-8')
print(json.dumps({k:v for k,v in summary.items() if k != 'images'}, ensure_ascii=False), flush=True)
return summary
if __name__ == '__main__':
parser = argparse.ArgumentParser(description=__doc__)
parser.add_argument('action', choices=['discover', 'single', 'multi'])
parser.add_argument('--retry-failed', action='store_true', help='只补采失败URL，保留首轮统计')
parser.add_argument('--seed', default='https://news.fzu.edu.cn/yxfd.htm')
parser.add_argument('--max-pages', type=int, default=52)
parser.add_argument('--max-images', type=int, default=152)
args = parser.parse_args()
if args.action == 'discover':
discover(args.seed, args.max_pages, args.max_images)
else:
result = download_manifest(args.action, args.retry_failed)
if result['failed']:
raise SystemExit(1)
运行命令（进入作业3目录）：
python -m pip install -r requirements.txt
python image_crawler/crawl.py discover
python image_crawler/crawl.py single
python image_crawler/crawl.py multi
| 项目 | 单线程 | 多线程 |
|---|---|---|
| 工作线程数 | 1 | 4 |
| 共用网页发现数量 | 50 | 50 |
| 首轮下载请求数 | 152 | 152 |
| 首轮成功数 | 150 | 150 |
| 补采成功数 | 2 | 2 |
| 最终成功数 | 152 | 152 |
| 最终失败数 | 0 | 0 |
| 首轮下载耗时/秒 | 481.269 | 125.041 |
首轮两组均有2张图片未保存：Pillow识别出其实际格式是MPO（多图JPEG），而初版只接受JPEG/PNG/GIF/WebP。补上MPO支持后，用 single --retry-failed、multi --retry-failed 分别补采这2个URL，原样保存为 .mpo。首轮日志和计时保留，补采单独记录，未重新下载成功文件。最终每组均为152张。
图1：两次真实运行的统计结果；完整逐条日志位于 results/image_single.log、results/image_multi.log。
上图将真实控制台输出换行排版，保留原始URL与完成顺序；不是模拟运行的终端截图。
图片分别保存在 image_crawler/single/images 和 image_crawler/multi/images。两组均独立发起下载请求，文件清单记录来源页、图片URL、字节数、尺寸和SHA-256。核验结果：152个对应文件的SHA-256一致。
图2：实际文件清单与校验结果，文件名由序号和URL摘要组成，避免不同页面使用同名图片时覆盖文件。
2. 作业心得
数量限制应放在提交任务之前。如果把152个URL交给线程池后，才在下载完成时判断是否超限，其他线程可能已经发出了多余请求。本程序先固定不超过152条的清单，再交给线程池处理，从调度入口控制数量。
本次单线程先运行，多线程后运行；上表首轮（均成功150张）耗时之比为 3.85。网络下载包含等待时间，多个线程可以重叠等待，但结果还受网络带宽、服务器响应和缓存影响，因此这一次测量不能代表固定的加速倍数，也不能据此认为线程越多越好。
另一个实际遇到的细节是：部分URL以 .jpg 结尾，响应内容却是PNG。只凭URL后缀命名会掩盖真实格式，所以保存前先验证图片内容。测试还覆盖了空HTML和PNG校验损坏的情况，让单个失败进入日志，而不是令整批结果清单丢失。
二、Scrapy采集股票与外汇并存入MySQL
1. 作业代码与运行结果
技术路线与采集范围
股票选择20只固定沪深样本，数量和代码在 SYMBOLS 中明确列出。本次没有采集全部股票。东方财富的HTML会返回名称和代码，但价格区域是占位符：直接对这些价格节点执行XPath会得到 -。因此先用XPath提取名称和六位代码，再由Scrapy请求该页面使用的行情接口，核对股票身份后生成 StockItem。数值读取JSON，未将JSON包装成HTML来充当XPath结果。
外汇直接对中国银行的 priceTable 使用XPath，按单元格取值。页面中的空单元格保留原位置，存为 None，入库后是SQL NULL。本次采集当前页 45种货币，不翻页采集历史牌价。
两只爬虫共用 MysqlPipeline：参数化SQL写入InnoDB事务，全部请求和解析完成且数量吻合后提交；失败则回滚。每次运行使用新的 run_id 保存独立批次，重复运行不混淆不同时间的数据。提交成功后才输出JSON、CSV、控制台表格和成功摘要。
作业②股票爬虫 · 作业③外汇爬虫 · 共用项目与数据库文件
字段与单位
| 股票输出列 | 数据库字段 | 处理方式 |
|---|---|---|
| 序号 | id | 本轮样本序号 |
| 股票代码 | code | CHAR(6)，保留前导零 |
| 股票名称 | name | HTML中由XPath提取 |
| 最新报价 | latest | 元 |
| 涨跌幅 | change_pct | 百分点，如1.23表示1.23% |
| 涨跌额 | change_amount | 元 |
| 成交量 | volume_lots | 手 |
| 振幅 | amplitude_pct | 百分点 |
| 最高、最低 | high、low | 元 |
| 今开、昨收 | open、previous_close | 元 |
股票请求使用 fltt=2，返回的价格已经格式化，不能再除以100。外汇 TBP/CBP/TSP/CSP 分别对应现汇买入、现钞买入、现汇卖出、现钞卖出。中国银行页面注明报价单位为每100单位外币折合人民币；除了题目中的 Time，数据库还保存 published_at，避免不同日期的同一时刻混淆。价格采用DECIMAL存储。
Item与解析函数
finance/finance_lab/items.py
"""Scrapy Item 明确声明输出字段，避免字段拼写不一致。"""
import scrapy
class StockItem(scrapy.Item):
id = scrapy.Field()
code = scrapy.Field()
name = scrapy.Field()
latest = scrapy.Field()
change_pct = scrapy.Field()
change_amount = scrapy.Field()
volume_lots = scrapy.Field()
amplitude_pct = scrapy.Field()
high = scrapy.Field()
low = scrapy.Field()
open = scrapy.Field()
previous_close = scrapy.Field()
source_url = scrapy.Field()
collected_at = scrapy.Field()
class ForexItem(scrapy.Item):
id = scrapy.Field()
Currency = scrapy.Field()
TBP = scrapy.Field()
CBP = scrapy.Field()
TSP = scrapy.Field()
CSP = scrapy.Field()
Time = scrapy.Field()
published_at = scrapy.Field()
source_url = scrapy.Field()
collected_at = scrapy.Field()
finance/finance_lab/parsers.py
"""XPath字段提取与金融数值转换；空值保留为None。"""
import re
import unicodedata
from decimal import Decimal, InvalidOperation
from datetime import datetime
def number(value):
text = str(value).strip().replace(',', '').removesuffix('%') if value is not None else ''
if text in ('', '-', '--', 'None'):
return None
try:
value = Decimal(text)
except InvalidOperation as error:
raise ValueError(f'无法识别数值：{text}') from error
if not value.is_finite():
raise ValueError('非有限数值')
return value
def parse_identity(response):
name = response.xpath('normalize-space(//span[contains(concat(" ",normalize-space(@class)," ")," quote_title_name ")])').get()
code = response.xpath('normalize-space(//span[@class="quote_title_code"])').get()
if not name or not re.fullmatch(r'\d{6}', code or ''):
raise ValueError('网页未包含有效股票名称或六位代码')
return code, name
def parse_quote(data, code, name):
# 网页XPath会折叠空格；接口可能保留排版空格和全角字母。
normalize = lambda text: ''.join(unicodedata.normalize('NFKC', str(text)).split())
if data.get('f57') != code or normalize(data.get('f58')) != normalize(name):
raise ValueError('HTML和行情接口的股票身份不一致')
mapping = {'latest':'f43', 'change_pct':'f170', 'change_amount':'f169',
'volume_lots':'f47', 'amplitude_pct':'f171', 'high':'f44',
'low':'f45', 'open':'f46', 'previous_close':'f60'}
result = {'code': code, 'name': name}
for field, key in mapping.items():
if key not in data:
raise ValueError(f'行情接口缺少字段{key}')
result[field] = number(data[key])
if result['latest'] is None or result['previous_close'] is None:
raise ValueError('本次样本缺少最新价或昨收，不将空响应当成有效行情')
return result
def parse_forex(response):
rows = response.xpath('//table[@id="priceTable"]//tr[td]')
if not rows:
raise ValueError('未找到中国银行外汇牌价表')
for row in rows:
cells = [cell.xpath('normalize-space(.)').get() for cell in row.xpath('./td')]
if len(cells) != 8:
raise ValueError(f'外汇表列数变化：{len(cells)}')
stamp = cells[6] if ' ' in cells[6] else cells[6] + ' ' + cells[7]
published = datetime.strptime(stamp, '%Y/%m/%d %H:%M:%S')
if published.strftime('%H:%M:%S') != cells[7]:
raise ValueError('发布日期与时间列不一致')
yield {'Currency': cells[0], 'TBP': number(cells[1]), 'CBP': number(cells[2]),
'TSP': number(cells[3]), 'CSP': number(cells[4]), 'Time': cells[7],
'published_at': published.strftime('%Y-%m-%d %H:%M:%S')}
爬虫与原始响应保存
finance/finance_lab/spiders/base.py
"""保存本轮原始响应；请求失败必须使整轮结果标为失败。"""
import json
import uuid
from datetime import datetime
from pathlib import Path
from zoneinfo import ZoneInfo
import scrapy
ROOT = Path(__file__).resolve().parents[2]
class EvidenceSpider(scrapy.Spider):
def __init__(self, run_id=None, **kwargs):
super().__init__(**kwargs)
self.run_id = run_id or datetime.now().strftime('%Y%m%d_%H%M%S_') + uuid.uuid4().hex[:6]
if not self.run_id.replace('_','').isalnum():
raise ValueError('run_id只能包含字母、数字、下划线')
self.folder = ROOT / 'sources' / self.run_id / self.name
self.folder.mkdir(parents=True, exist_ok=True)
self.failed = False
def archive(self, response, filename):
(self.folder / filename).write_bytes(response.body)
(self.folder / (filename + '.meta.json')).write_text(json.dumps({
'url': response.url, 'status': response.status,
'collected_at': self.now(), 'encoding': response.encoding,
}, ensure_ascii=False, indent=2), encoding='utf-8')
@staticmethod
def now():
return datetime.now(ZoneInfo('Asia/Shanghai')).strftime('%Y-%m-%d %H:%M:%S')
def request_failed(self, failure):
self.failed = True
self.logger.error('请求失败：%s %s', failure.request.url, failure.value)
finance/finance_lab/spiders/stocks.py
"""XPath提取股票身份，Scrapy继续请求官方行情接口读取动态数值。"""
from urllib.parse import urlencode
import scrapy
from finance_lab.items import StockItem
from finance_lab.parsers import parse_identity, parse_quote
from .base import EvidenceSpider
# 固定20只沪深样本，采集范围明确，不把样本描述为全市场。
SYMBOLS = ['sh600519','sh600036','sh601318','sh601398','sh600000',
'sh600030','sh600887','sh600900','sh601857','sh601088',
'sz000001','sz000002','sz000333','sz000651','sz000858',
'sz002415','sz002594','sz300059','sz300750','sh600276']
class StocksSpider(EvidenceSpider):
name = 'stocks'
expected_count = len(SYMBOLS)
async def start(self):
for index, symbol in enumerate(SYMBOLS, 1):
yield scrapy.Request(f'https://quote.eastmoney.com/{symbol}.html',
callback=self.parse, errback=self.request_failed,
cb_kwargs={'index':index, 'symbol':symbol})
def parse(self, response, index, symbol):
self.archive(response, symbol + '.html')
code, name = parse_identity(response)
if code != symbol[2:]:
raise ValueError('网页代码与请求的股票代码不一致')
params = {'secid': ('1.' if symbol.startswith('sh') else '0.') + code,
'fields':'f43,f44,f45,f46,f47,f57,f58,f60,f169,f170,f171',
'fltt':'2', 'invt':'2'}
yield scrapy.Request('https://push2.eastmoney.com/api/qt/stock/get?' + urlencode(params),
headers={'Referer':response.url}, callback=self.parse_quote_response,
errback=self.request_failed, cb_kwargs={'index':index,'code':code,'name':name})
def parse_quote_response(self, response, index, code, name):
self.archive(response, code + '.json')
payload = response.json()
if payload.get('rc') != 0 or not payload.get('data'):
raise ValueError('行情接口返回异常状态或空数据')
values = parse_quote(payload['data'], code, name)
yield StockItem(id=index, **values, source_url=response.url, collected_at=self.now())
finance/finance_lab/spiders/forex.py
"""直接使用XPath提取中国银行当页外汇牌价。"""
import scrapy
from finance_lab.items import ForexItem
from finance_lab.parsers import parse_forex
from .base import EvidenceSpider
class ForexSpider(EvidenceSpider):
name = 'forex'
async def start(self):
yield scrapy.Request('https://www.boc.cn/sourcedb/whpj/',
callback=self.parse, errback=self.request_failed)
def parse(self, response):
self.archive(response, 'boc.html')
rows = list(parse_forex(response))
self.expected_count = len(rows)
for index, values in enumerate(rows, 1):
yield ForexItem(id=index, **values, source_url=response.url, collected_at=self.now())
MySQL建表与Pipeline
finance/schema.sql
CREATE TABLE IF NOT EXISTS stock_quotes (
run_id VARCHAR(64) NOT NULL,
id INT NOT NULL,
code CHAR(6) NOT NULL,
name VARCHAR(40) NOT NULL,
latest DECIMAL(14,4),
change_pct DECIMAL(12,4),
change_amount DECIMAL(14,4),
volume_lots BIGINT,
amplitude_pct DECIMAL(12,4),
high DECIMAL(14,4),
low DECIMAL(14,4),
open DECIMAL(14,4),
previous_close DECIMAL(14,4),
source_url TEXT NOT NULL,
collected_at DATETIME NOT NULL,
PRIMARY KEY (run_id,id),
UNIQUE KEY unique_stock (run_id,code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS forex_quotes (
run_id VARCHAR(64) NOT NULL,
id INT NOT NULL,
Currency VARCHAR(64) NOT NULL,
TBP DECIMAL(16,6),
CBP DECIMAL(16,6),
TSP DECIMAL(16,6),
CSP DECIMAL(16,6),
Time TIME NOT NULL,
published_at DATETIME NOT NULL,
source_url TEXT NOT NULL,
collected_at DATETIME NOT NULL,
PRIMARY KEY (run_id,id),
UNIQUE KEY unique_currency (run_id,Currency)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
finance/finance_lab/pipelines.py
"""Item -> 参数化SQL -> MySQL事务 -> JSON/CSV序列化与控制台输出。"""
import csv
import json
import os
from decimal import Decimal
from pathlib import Path
import pymysql
from itemadapter import ItemAdapter
from scrapy import signals
from tabulate import tabulate
ROOT = Path(__file__).resolve().parents[1]
STOCK_FIELDS = ['id','code','name','latest','change_pct','change_amount','volume_lots',
'amplitude_pct','high','low','open','previous_close']
FOREX_FIELDS = ['Currency','TBP','CBP','TSP','CSP','Time']
def connection_options():
"""凭据仅从环境读取，不写入仓库；支持本地socket与常规TCP。"""
options = {'user':os.getenv('MYSQL_USER','root'), 'password':os.getenv('MYSQL_PASSWORD',''),
'database':os.getenv('MYSQL_DATABASE','collection_lab3'),
'charset':'utf8mb4', 'autocommit':False, 'connect_timeout':8}
if os.getenv('MYSQL_SOCKET'):
options['unix_socket'] = os.environ['MYSQL_SOCKET']
else:
options.update(host=os.getenv('MYSQL_HOST','127.0.0.1'), port=int(os.getenv('MYSQL_PORT','3306')))
return options
class MysqlPipeline:
@classmethod
def from_crawler(cls, crawler):
pipeline = cls()
pipeline.crawler = crawler
pipeline.failed = False
crawler.signals.connect(pipeline.spider_error, signal=signals.spider_error)
return pipeline
def spider_error(self, failure, response, spider):
self.failed = True
def open_spider(self, spider):
self.connection = pymysql.connect(**connection_options())
with self.connection.cursor() as cursor:
for statement in (ROOT / 'schema.sql').read_text(encoding='utf-8').split(';'):
if statement.strip():
cursor.execute(statement)
self.connection.commit()
self.connection.begin()
self.rows = []
self.table = {'stocks':'stock_quotes','forex':'forex_quotes'}[spider.name]
def process_item(self, item, spider):
row = dict(ItemAdapter(item))
columns = ['run_id'] + list(row)
sql = f"INSERT INTO `{self.table}` ({','.join('`'+x+'`' for x in columns)}) VALUES ({','.join(['%s']*len(columns))})"
try:
with self.connection.cursor() as cursor:
cursor.execute(sql, [spider.run_id] + list(row.values()))
except Exception:
self.failed = True
raise
self.rows.append(row)
return item
def close_spider(self, spider):
output = ROOT / 'results' / spider.run_id
output.mkdir(parents=True, exist_ok=True)
ok = not (self.failed or spider.failed) and len(self.rows) == getattr(spider,'expected_count',-1) and bool(self.rows)
try:
if not ok:
self.connection.rollback()
raise RuntimeError('本轮请求、解析或入库不完整，MySQL事务已回滚')
self.connection.commit()
with self.connection.cursor() as cursor:
cursor.execute(f'SELECT COUNT(*) FROM `{self.table}` WHERE run_id=%s', (spider.run_id,))
count = cursor.fetchone()[0]
cursor.execute('SELECT VERSION()')
version = cursor.fetchone()[0]
if count != len(self.rows):
raise RuntimeError('数据库行数与Item数不一致')
rows = sorted(self.rows, key=lambda x:x['id'])
serial = [{k:(str(v) if isinstance(v,Decimal) else v) for k,v in row.items()} for row in rows]
(output / f'{spider.name}.json').write_text(json.dumps(serial,ensure_ascii=False,indent=2),encoding='utf-8')
with (output / f'{spider.name}.csv').open('w',encoding='utf-8-sig',newline='') as file:
writer = csv.DictWriter(file,fieldnames=list(serial[0]));writer.writeheader();writer.writerows(serial)
fields = STOCK_FIELDS if spider.name == 'stocks' else FOREX_FIELDS
table = tabulate([[r[k] if r[k] is not None else 'NULL' for k in fields] for r in serial],
headers=fields,tablefmt='simple',disable_numparse=True)
text = f'MySQL {version} | {self.table} | run_id={spider.run_id} | {count} rows\n' + table + '\n'
(output / f'{spider.name}.txt').write_text(text,encoding='utf-8')
(output / f'{spider.name}_summary.json').write_text(json.dumps({
'success':True,'table':self.table,'run_id':spider.run_id,'rows':count,'mysql_version':version,
'completed_at':spider.now()},ensure_ascii=False,indent=2),encoding='utf-8')
print(text,flush=True)
finally:
self.connection.close()
finance/finance_lab/settings.py
BOT_NAME = 'collection_lab3'
SPIDER_MODULES = ['finance_lab.spiders']
NEWSPIDER_MODULE = 'finance_lab.spiders'
USER_AGENT = 'Mozilla/5.0 (compatible; DataCollectionCourse/1.0)'
CONCURRENT_REQUESTS = 2
CONCURRENT_REQUESTS_PER_DOMAIN = 2
DOWNLOAD_DELAY = 0.4
DOWNLOAD_TIMEOUT = 25
RETRY_TIMES = 1
ROBOTSTXT_OBEY = False
COOKIES_ENABLED = False
TELNETCONSOLE_ENABLED = False
ITEM_PIPELINES = {'finance_lab.pipelines.MysqlPipeline': 300}
LOG_LEVEL = 'INFO'
FEED_EXPORT_ENCODING = 'utf-8'
TWISTED_REACTOR = 'twisted.internet.asyncioreactor.AsyncioSelectorReactor'
运行与数据库检查
本次实际使用Python 3.12、Scrapy 2.13.4和MySQL 8.0.44。MySQL使用独立的本地实验实例，未修改其他数据库；代码也支持通过主机、端口和账号连接普通MySQL服务。
先在自己的MySQL中创建 collection_lab3 数据库，再通过环境变量设置 MYSQL_HOST、MYSQL_PORT、MYSQL_USER、MYSQL_PASSWORD 和 MYSQL_DATABASE。若使用本地Unix套接字，可设置 MYSQL_SOCKET。密码不写入源代码或提交到码云。
CREATE DATABASE collection_lab3 CHARACTER SET utf8mb4;
cd finance
python run_finance.py
python verify_finance.py
run_finance.py 依次运行两只爬虫，并检查MySQL成功摘要，避免仅凭Scrapy进程退出就宣称采集成功。完整运行脚本和离线原始响应校验程序见码云目录。
图3：本轮20条股票记录的实际输出，包含题目要求的12列。采集时点按每条记录的 collected_at 保存，各请求存在先后，不视为严格同一时刻的市场快照。
图4：本轮45条外汇记录的前18条与末尾3条；完整结果见CSV、JSON和MySQL。NULL表示原网页该报价为空，不表示零。
图5：来自真实MySQL查询和逐字段复核结果。股票20条、外汇45条，均与留存的原始响应和序列化文件一致；导出的 collection_lab3.sql 已导入另一个测试库并再次通过核验。
2. 作业心得
Scrapy把下载、解析和存储分开后，问题更容易定位。XPath只负责提取页面里实际存在的内容；动态行情还需要继续请求接口。遇到页面价格全是横线时，应先确认数据是否在初始HTML里，而不是不断更换XPath表达式。
实际采集时，“万科Ａ”在HTML和接口里的排版空格数量不同。最初直接比较名称字符串，程序把它判为不一致并回滚了这一轮。修正时只对身份比较统一全角字符和空白，仍严格核对六位股票代码，并补充测试，避免为了通过校验而放过其他公司的数据。
外汇的空报价不能跳过。若先收集所有非空文本再按下标对应字段，现钞买入为空时，后面的卖出价就会错位。逐个 td 提取，再将空字符串转换成None，才能保持四种价格的对应关系。
最后，控制台显示“抓取结束”并不等于数据库已经保存成功。本次同时检查了Item数量、MySQL记录数和逐字段内容，并实际恢复SQL备份。这样既能确认采集结果，也能发现存储单位、精度或字段顺序上的问题。代码共通过11项测试，覆盖URL和数量限制、损坏图片、XPath字段、空值、股票身份及单/多线程文件一致性。
浙公网安备 33010602011771号