第11课：网页爬虫｜数据的存储【存入 MySQL 数据库】本课以MySQL为例，教你如何将爬虫采集的数据存入关系型数据库

网页爬虫_正文.jpg

数据落地是爬虫工程的“最后一公里”。前面10课我们学会了抓取、解析、存储为文件。但对于规模化、商业化的爬虫系统，文件存储远远不够——你需要能随机查询、高效更新的数据库。

本课以MySQL为例，教你如何将爬虫采集的数据存入关系型数据库，实现专业化数据管理。

一、为什么爬虫需要数据库？

1.1 CSV的局限

回顾第10课，CSV/Excel格式有以下硬伤：

对比维度	CSV/Excel	MySQL
数据量上限	Excel约104万行，CSV无上限但打开慢	千万/亿级，毫秒级查询
查询能力	只能Ctrl+F，无法快速条件筛选	`SELECT ... WHERE` 秒级过滤
多表关联	不支持	`JOIN` 跨表查询
并发写入	多进程同时写会损坏文件	事务锁+连接池，支持高并发
数据一致性	无约束，脏数据随意写入	主键/外键/唯一索引自动校验
增量更新	靠读文件全量+重写，低效	`INSERT ... ON DUPLICATE KEY UPDATE`

一句话结论：CSV适合“临时看一眼”，MySQL适合“系统化采集和管理”。

1.2 爬虫+MySQL的典型数据流

爬虫发送HTTP请求 → 解析抓取HTML → 数据清洗/归一化 → 存入MySQL → 报表/分析/应用读取

存到数据库后，你可以：

增量采集时自动去重，避免重复插入
按日期/分类/价格区间快速筛选数据
外挂可视化平台（Grafana、Superset、PowerBI）做实时监控
与业务系统无缝集成（电商价格监控、舆情告警平台）

二、10分钟搭建MySQL环境

2.1 Windows安装MySQL（新手推荐方案）

方案一：MySQL Installer（图形化安装）

访问 dev.mysql.com/downloads/i… 下载 MySQL Installer
运行安装包，选择 "Developer Default" 安装类型
一路下一步，设置root密码（务必记下来：例如 123456）
安装完成后，MySQL会在Windows后台自动运行

方案二：集成开发环境 > 手动配置

如果不想让MySQL长期占用系统资源，轻量级替代方案是在爬虫项目目录跑SQLite。但既然以MySQL教学为目的，至少搭一个本地测试环境。

2.2 验证MySQL是否正常运行

打开命令行，连接测试：

mysql -u root -p
# 输入你设置的密码

如果能进入 mysql> 提示符，说明MySQL正常运行。

2.3 创建数据库和用户

一个独立爬虫项目要有独立的数据库和受限权限的用户（不要用root账号做日常写入操作，这是生产环境的基本修养——万一爬虫代码被注入，删库灾难也最多丢一个库。）

-- 1. 创建数据库（指定utf8mb4编码，支持emoji和全Unicode）
CREATE DATABASE IF NOT EXISTS books_db
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

-- 2. 创建专用用户（密码随意，有测试权限即可）
CREATE USER IF NOT EXISTS 'crawler'@'localhost' IDENTIFIED BY 'crawler123';

-- 3. 授予该用户对books_db的所有权限
GRANT ALL PRIVILEGES ON books_db.* TO 'crawler'@'localhost';

-- 4. 刷新权限
FLUSH PRIVILEGES;

完成后，就可以用 crawler/crawler123 连接 books_db 数据库了。

2.4 安装Python MySQL驱动

Python连接MySQL需要驱动库，目前最主流的是 PyMySQL（纯Python实现）和 mysql-connector-python（官方提供）。

推荐新手使用 PyMySQL，原因：纯Python实现100%跨平台、不依赖系统环境变量、出错信息对新手友好、教学错例修复方便。

安装：

pip install pymysql

验证（可选步骤）：

import pymysql
print(pymysql.__version__)

三、pymysql核心使用

3.1 连接数据库

import pymysql

connection = pymysql.connect(
    host='localhost',        # MySQL服务器地址
    user='crawler',          # 用户名（不要用root）
    password='crawler123',   # 密码
    database='books_db',     # 数据库名
    charset='utf8mb4',       # 字符集（支持emoji）
    cursorclass=pymysql.cursors.DictCursor  # 返回字典格式结果
)

参数说明：

charset='utf8mb4' 是强制要求。如果给了utf8会报Incorrect string value的错误，因为emoji写入utf8列时报错。
cursorclass=DictCursor 让查询结果的每一行以Python字典返回，用起来更舒服。不指定则默认以元组返回。
建议生产环境直接用 cursorclass=pymysql.cursors.DictCursor，除非数据量极其巨大（元组省资源）。

3.2 游标与基本操作

创建游标 → 执行SQL → 提交→ 关闭游标/关闭连接

# 创建游标
cursor = connection.cursor()

# 执行SQL
sql = "INSERT INTO books (title, price, stock) VALUES (%s, %s, %s)"
cursor.execute(sql, ('Test Book', 29.99, 10))

# 提交事务（非常重要！忘记commit数据不会真的写入）
connection.commit()

# 关闭游标和连接
cursor.close()
connection.close()

单条execute + 多条executemany

# 单条
cursor.execute("INSERT INTO books (title, price) VALUES (%s, %s)", ('三体', 45.00))

# 多条（强烈推荐批量插入时用）
data = [('三体', 45.00), ('流浪地球', 35.00), ('乡村教师', 28.00)]
cursor.executemany("INSERT INTO books (title, price) VALUES (%s, %s)", data)
connection.commit()

四、创建数据表

4.1 数据表设计

以第8课 books.toscrape.com 的书籍数据为例，确定字段：

字段名	类型	约束	说明
`id`	INT	PRIMARY KEY AUTO_INCREMENT	自增主键
`title`	VARCHAR(255)	NOT NULL	书名
`price`	DECIMAL(10, 2)		价格（英镑）
`stock`	INT	DEFAULT 0	库存数量
`rating`	TINYINT		星级1-5
`img_url`	VARCHAR(500)		图片相对路径
`category`	VARCHAR(100)		分类名称
`created_at`	DATETIME	DEFAULT CURRENT_TIMESTAMP	创建时间

为什么用 VARCHAR 存URL？

URL长度不固定且可以索引，这是爬虫里最合适的方案。特殊列 category 里只存文本，不拆表就让爬虫简单起步。

4.2 建表SQL

CREATE TABLE IF NOT EXISTS books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2),
    stock INT DEFAULT 0,
    rating TINYINT,
    img_url VARCHAR(500),
    category VARCHAR(100),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

AUTO_INCREMENT 的含义：插入数据时，id字段自动生成递增的唯一编号，无需手动指定。为UNIQUE去重提前设计好 —— 之后对 title 建唯一索引才能用 ON DUPLICATE KEY UPDATE。

4.3 在Python中创建表

import pymysql

def create_table():
    conn = pymysql.connect(
        host='localhost', user='crawler', password='crawler123',
        database='books_db', charset='utf8mb4'
    )
    cursor = conn.cursor()
    
    create_sql = """
    CREATE TABLE IF NOT EXISTS books (
        id INT AUTO_INCREMENT PRIMARY KEY,
        title VARCHAR(255) NOT NULL,
        price DECIMAL(10, 2),
        stock INT DEFAULT 0,
        rating TINYINT,
        img_url VARCHAR(500),
        category VARCHAR(100),
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    """
    
    cursor.execute(create_sql)
    conn.commit()
    cursor.close()
    conn.close()
    print("✅ 数据表创建完成")

if __name__ == '__main__':
    create_table()

五、插入数据（单条与批量）

5.1 单条插入

def insert_one(title, price, stock, rating, img_url, category):
    conn = get_connection()  # 假设已写好连接函数
    cursor = conn.cursor()
    
    sql = """
        INSERT INTO books (title, price, stock, rating, img_url, category)
        VALUES (%s, %s, %s, %s, %s, %s)
    """
    try:
        cursor.execute(sql, (title, price, stock, rating, img_url, category))
        conn.commit()
        return cursor.lastrowid  # 返回新插入记录的自增ID
    except pymysql.Error as e:
        print(f"插入失败: {e}")
        conn.rollback()
        return None
    finally:
        cursor.close()
        conn.close()

注意：参数化查询的占位符统一用 %s，无论数据类型是什么——int、float、string 在pymysql里都占一个 %s 槽位。

5.2 批量插入（executemany）

def insert_many(books_list):
    conn = get_connection()
    cursor = conn.cursor()
    
    sql = """
        INSERT INTO books (title, price, stock, rating, img_url, category)
        VALUES (%s, %s, %s, %s, %s, %s)
    """
    # books_list: 每个元素是 (title, price, stock, rating, img_url, format category)
    data = [(b['title'], b['price'], b['stock'], b['rating'], b['img_url'], b['category']) for b in books_list]
    
    try:
        cursor.executemany(sql, data)
        conn.commit()
        print(f"✅ 成功插入 {len(books_list)} 条记录")
    except pymysql.Error as e:
        print(f"批量插入失败: {e}")
        conn.rollback()
    finally:
        cursor.close()
        conn.close()

5.3 批量插入性能再提效（重点）

上面 executemany 在驱动底层依然对每条记录单独执行execute()。爬虫场景1万条数据入库耗时动辄20秒以上——那是因为每条insert额外等一次1ms网络往返请求。

真正的批量插入应该用 手写多值INSERT 语句或启用驱动的 multi=True 参数。

def insert_many_efficient(books_list, batch_size=100):
    """
    高效批量插入：手拼多值VALUES
    """
    conn = get_connection()
    cursor = conn.cursor()
    
    sql_template = "INSERT INTO books (title, price, stock, rating, img_url, category) VALUES %s"
    
    for i in range(0, len(books_list), batch_size):
        batch = books_list[i:i+batch_size]
        values_list = []
        params = []
        for book in batch:
            values_list.append("(%s, %s, %s, %s, %s, %s)")
            params.extend([book['title'], book['price'], book['stock'], 
                          book['rating'], book['img_url'], book['category']])
        sql = sql_template % ','.join(values_list)
        try:
            cursor.execute(sql, params)
        except pymysql.Error as e:
            print(f"批次 {i//batch_size + 1} 插入失败: {e}")
            conn.rollback()
            continue
    
    conn.commit()
    cursor.close()
    conn.close()

六、读取与查询

def select_all():
    conn = get_connection()
    cursor = conn.cursor()
    
    cursor.execute("SELECT id, title, price, rating FROM books")
    results = cursor.fetchall()      # 全部结果
    # results = cursor.fetchone()    # 一条
    # results = cursor.fetchmany(10) # 10条
    
    for row in results:
        print(f"ID:{row['id']} | {row['title']} | £{row['price']} | {row['rating']}星")
    
    cursor.close()
    conn.close()

七、数据去重入库（核心实战）

爬虫最大的痛点：重复采集同一页面会导致数据翻倍。

7.1 方案一：先查后插（代码控制去重）

def insert_if_not_exists(title, price, stock, rating, img_url, category):
    conn = get_connection()
    cursor = conn.cursor()
    
    # 检查是否存在
    cursor.execute("SELECT id FROM books WHERE title = %s", (title,))
    if cursor.fetchone():
        print(f"书籍 '{title}' 已存在，跳过插入")
        return False
    
    # 不存在则插入
    sql = "INSERT INTO books (title, price, stock, rating, img_url, category) VALUES (%s, %s, %s, %s, %s, %s)"
    cursor.execute(sql, (title, price, stock, rating, img_url, category))
    conn.commit()
    cursor.close()
    conn.close()
    return True

缺点：每次插入都要两次查询 + 更新，在百万条数据下网络往返负载至少乘以2，效率很差。

7.2 方案二：UNIQUE索引 + `ON DUPLICATE KEY UPDATE`（推荐）

先在 title 字段上添加唯一索引：

ALTER TABLE books ADD UNIQUE INDEX idx_title (title);

然后用 ON DUPLICATE KEY UPDATE 一步完成“存在则更新、不存在则插入”：

def upsert_book(book_data):
    conn = get_connection()
    cursor = conn.cursor()
    
    sql = """
        INSERT INTO books (title, price, stock, rating, img_url, category)
        VALUES (%s, %s, %s, %s, %s, %s)
        ON DUPLICATE KEY UPDATE
            price = VALUES(price),
            stock = VALUES(stock),
            rating = VALUES(rating),
            img_url = VALUES(img_url),
            category = VALUES(category)
    """
    
    cursor.execute(sql, (book_data['title'], book_data['price'], book_data['stock'],
                         book_data['rating'], book_data['img_url'], book_data['category']))
    conn.commit()
    
    affected = cursor.rowcount
    if affected == 1:
        print(f"新增: {book_data['title']}")
    else:
        print(f"更新: {book_data['title']}")
    
    cursor.close()
    conn.close()

7.3 去重方案对比

方案	性能	代码复杂度	适用场景
先查后插	低（2次查询）	低	低频写入
`ON DUPLICATE KEY UPDATE`	高（1条SQL搞定）	中	生产推荐
`INSERT IGNORE`	高	低	只插入不更新
`REPLACE INTO`	中（删除+插入）	低	要覆盖旧记录但风险高

八、完整实战项目

将第8课爬虫 + MySQL入库整合成完整脚本。

项目结构

book_crawler/
├── config.py          # 数据库配置
├── db_utils.py        # 数据库操作工具
├── crawler.py         # 爬虫逻辑
└── main.py            # 主程序入口

config.py —— 数据库配置

DB_CONFIG = {
    'host': 'localhost',
    'user': 'crawler',
    'password': 'crawler123',
    'database': 'books_db',
    'charset': 'utf8mb4',
    'cursorclass': 'pymysql.cursors.DictCursor'
}

db_utils.py —— 数据库工具模块

import pymysql
from config import DB_CONFIG

def get_connection():
    return pymysql.connect(**DB_CONFIG)

def create_table_if_not_exists():
    conn = get_connection()
    cursor = conn.cursor()
    sql = """
        CREATE TABLE IF NOT EXISTS books (
            id INT AUTO_INCREMENT PRIMARY KEY,
            title VARCHAR(255) NOT NULL UNIQUE,
            price DECIMAL(10, 2),
            stock INT DEFAULT 0,
            rating TINYINT,
            img_url VARCHAR(500),
            category VARCHAR(100),
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    """
    cursor.execute(sql)
    conn.commit()
    cursor.close()
    conn.close()

def upsert_books(books_list, batch_size=50):
    conn = get_connection()
    cursor = conn.cursor()
    
    sql = """
        INSERT INTO books (title, price, stock, rating, img_url, category)
        VALUES (%s, %s, %s, %s, %s, %s)
        ON DUPLICATE KEY UPDATE
            price = VALUES(price),
            stock = VALUES(stock),
            rating = VALUES(rating),
            img_url = VALUES(img_url),
            category = VALUES(category)
    """
    
    for i in range(0, len(books_list), batch_size):
        batch = books_list[i:i+batch_size]
        data = [(b['title'], b['price'], b['stock'], b['rating'], b['img_url'], b['category']) for b in batch]
        try:
            cursor.executemany(sql, data)
            conn.commit()
            print(f"批次 {i//batch_size + 1}: {len(batch)} 条处理完成")
        except pymysql.Error as e:
            print(f"批次失败: {e}")
            conn.rollback()
    
    cursor.close()
    conn.close()

def get_stats():
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT COUNT(*) as total, AVG(price) as avg_price FROM books")
    return cursor.fetchone()

crawler.py —— 爬虫逻辑（复用第8课的解析代码）

import requests
from bs4 import BeautifulSoup

def fetch_books():
    url = 'http://books.toscrape.com'
    headers = {'User-Agent': 'Mozilla/5.0 ...'}
    resp = requests.get(url, headers=headers, timeout=10)
    soup = BeautifulSoup(resp.text, 'lxml')
    
    books = []
    for article in soup.find_all('article', class_='product_pod'):
        title = article.h3.a.get('title', '')
        price_tag = article.find('p', class_='price_color')
        price = float(price_tag.text[1:]) if price_tag else 0
        
        stock_tag = article.find('p', class_='instock availability')
        import re
        stock = int(re.search(r'(\d+)', stock_tag.text).group(1)) if stock_tag else 0
        
        rating_map = {'One': 1, 'Two': 2, 'Three': 3, 'Four': 4, 'Five': 5}
        rating = 0
        for cls in article.get('class', []):
            if cls in rating_map:
                rating = rating_map[cls]
                break
        
        img_tag = article.find('img')
        img_url = img_tag.get('src', '') if img_tag else ''
        if img_url.startswith('../'):
            img_url = img_url[3:]
        
        books.append({
            'title': title,
            'price': price,
            'stock': stock,
            'rating': rating,
            'img_url': f'http://books.toscrape.com/{img_url}',
            'category': '未分类'
        })
    return books

main.py —— 程序入口

from db_utils import create_table_if_not_exists, upsert_books, get_stats
from crawler import fetch_books

def main():
    print("=== 图书爬虫 + MySQL入库 ===")
    
    # 1. 建表
    create_table_if_not_exists()
    
    # 2. 爬取数据
    books = fetch_books()
    print(f"爬取到 {len(books)} 本书")
    
    # 3. 入库
    upsert_books(books)
    
    # 4. 查看统计
    stats = get_stats()
    print(f"\n✅ 入库完成！当前共 {stats['total']} 本书，均价 £{stats['avg_price']:.2f}")

if __name__ == '__main__':
    main()

运行 python main.py，即可完成“爬取 → 解析 → 去重入库 → 统计输出”的完整流程。

九、常见编程错误与解决方案

错误1：`pymysql.err.OperationalError: (1049, "Unknown database")`

原因：连接字符串中的数据库名不存在。

解决：先确认建库，或者在连接时不指定database，建完库再指定。

错误2：`pymysql.err.ProgrammingError: (1146, "Table 'books_db.books' doesn't exist")`

原因：建表语句未执行就尝试插入。

解决：先执行建表，或改用 CREATE TABLE IF NOT EXISTS。

错误3：`pymysql.err.IntegrityError: (1062, "Duplicate entry '...' for key 'title'")`

原因：唯一约束冲突，而SQL未指定防重复处理。

解决：使用 ON DUPLICATE KEY UPDATE 或 INSERT IGNORE。

错误4：`pymysql.err.InterfaceError: (0, '')`

原因：MySQL连接空闲超过8小时后自动断开。

解决：

方案A：每次操作前用 connection.ping(reconnect=True) 重连。
方案B：开启连接池（SQLAlchemy + pool_pre_ping）。
方案C：设置 wait_timeout 的MySQL全局参数（不推荐给新手）。

ping() 解决方式：

def safe_execute(connection, sql, params):
    try:
        connection.ping(reconnect=True)
        cursor = connection.cursor()
        cursor.execute(sql, params)
        connection.commit()
        cursor.close()
    except pymysql.Error as e:
        print(f"执行失败，尝试重建连接: {e}")
        connection = pymysql.connect(**DB_CONFIG)
        safe_execute(connection, sql, params)

错误5：`Incorrect string value: '\\xF0\\x9F\\x98\\x80'`

原因：表或连接的字符集不是utf8mb4，却试图存储emoji。

解决：建表和连接时强制指定 utf8mb4，而不是 utf8。

错误6：`pymysql.err.ProgrammingError: (1064, ...) near ...`

原因：SQL语法错误，常见原因占位符写错，或在mysql模式用了?占位符。

解决：检查占位符：pymysql只用 %s；? 是SQLite语法，不能混用。

错误7：忘记 `conn.commit()` 导致数据没写入

原因：pymysql默认 autocommit=False，执行写入操作后必须commit。

解决：每批数据插入后调用 conn.commit()。

错误8：字符集不匹配导致中文乱码

原因：连接时的 charset 与建表指定的字符集不同。

解决：统一使用 utf8mb4，一处错处处错。

十、总结

核心知识清单

知识点	掌握程度
MySQL环境搭建与数据库/用户创建	能独立搭建本地测试库
pymysql连接与游标操作	能写出get_connection()和cursor基础用法
建表SQL（数据类型、约束、索引）	能为爬虫数据设计表
参数化查询防注入	理解`%s`占位符的作用，`commit`时不会遗漏
单条/批量插入	能用executemany或手拼多值VALUES优化性能
去重入库	掌握ON DUPLICATE KEY UPDATE
连接超时处理	能用ping()检测并重建连接
完整爬虫入库项目	能独立开发爬虫+入库脚本

技术选型建议

数据量 < 1万条：CSV足够，不需要数据库
数据量 1万～100万条：MySQL + ON DUPLICATE KEY UPDATE 合适
数据量 > 100万条：MySQL + 索引优化 + 批量写入 + 连接池
需要长连接生产环境：SQLAlchemy连接池 + pool_pre_ping
需要横向扩展：分布式爬虫配合Kafka + Elasticsearch

进阶方向

连接池：SQLAlchemy的QueuePool自动管理连接复用，避免频繁建连/断连的开销
异步入库：aiomysql 配合 asyncio 提升并发入库能力
ORM框架：SQLAlchemy让数据库操作更Pythonic
数据清洗：入库前数值归一化、非法字符过滤、缺失值填充

十一、课后作业

作业1：环境搭建与建表（必做）

在本机安装MySQL并创建 crawler_db 数据库
创建一个专门的数据表 news，字段包含 id(自增主键)、title(VARCHAR)、url(VARCHAR)、publish_date(DATE)、created_at(DATETIME)
在Python中编写建表函数，执行后验证表是否创建成功

作业2：爬虫数据入库（必做）

用requests+BeautifulSoup爬取 http://quotes.toscrape.com 首页的名言
提取每条名言的 text（内容）、author（作者）、tags（标签，逗号拼接）
将数据插入到 quotes 表中，表包含 id、text、author、tags、created_at
要求： 运行两次，第二次不会重复插入相同内容的行（使用ON DUPLICATE KEY或应用层去重）

作业3：性能对比（选做，进阶）

准备200条测试数据，分别用以下三种方式写入数据库，记录耗时：

方式A：循环单条insert，每次commit
方式B：循环单条insert，最后commit一次
方式C：executemany批量插入，最后commit一次
方式D：手拼多值INSERT，批量提交

撰写实验报告，分析耗时差异的原因。

作业4：按分类统计（选做）

在 books 表已有数据的基础上：

用 GROUP BY category 统计每个分类的书籍数量和平均价格
按平均价格排序，找出价格最高的分类
找出库存量低于5本的书籍，并按价格从高到低排序

作业5：MySQL工具使用（推荐）

安装并熟悉 MySQL Workbench（官方图形化工具）或 Navicat for MySQL，完成：

用工具连接到本地数据库
执行 SELECT * FROM books 查看入库数据
在工具中写一条 UPDATE 语句，批量修改某个字段的值

第11课到此结束。 你现在已经掌握了爬虫数据存入MySQL的完整技能，从环境搭建到建表，从单条插入到批量去重入库。下一课我们将学习 AJAX和API接口爬取——跳出HTML解析的藩篱，直接定位网站的数据接口，绕过页面渲染，直取核心数据。

🔗《精讲20课｜网页爬虫从入门到精通》系列课程导航

去订阅

第11课：网页爬虫｜数据的存储【存入 MySQL 数据库】