Python爬虫+MySQL数据存储：从CSV到数据库的完整实践方案

简介面向需要从网页抓取数据并写入 MySQL 的开发者这份资料包整合了 Python 连接与操作 MySQL 的完整示例覆盖 pymysql 安装配置、连接池、参数化查询、批量插入及 SQLite 工具类同时演示如何将爬虫解析结果结构化落库并兼顾安全与性能优化。包内共 17 个文件以 Python 脚本、Markdown 说明文档为主辅以 systemd 服务文件、Dockerfile、日志、SQLite 数据库、JSON 配置与 License整体仅 76KB结构清晰便于按模块调用Python 脚本中保留了可直接修改复用的封装函数适合在本地环境快速跑通。另外资源中附带 luck-prometheus-exporter-mysql-develop 相关实现可用于采集 MySQL 查询速率、内存使用等性能指标帮助读者在实际项目中完成监控与调优。已有 306 人学习适合熟悉 Python 基础、正在搭建数据采集或数据库写入管线的初中级工程师参考。 做了几年的爬虫和数据采集我最深的体会是爬虫本身并不难难的是把抓下来的数据整理好、存好、用起来。早期我图省事抓下来的数据直接存CSV文件等数据量到了几万条光是去重、筛选、更新就让人头大更别说后续还要做关联查询和统计。后来老老实实把MySQL接进来数据管道才算真正跑通。这篇博客就把一个完整的“爬虫技术 MySQL存储”方案拆开讲适合已经会用Python写简单爬虫、但还没系统做过数据落库的读者也适合那些把数据存Excel存到想哭、准备换数据库的朋友。我用的方案很朴素Python requests BeautifulSoup 抓取解析网页pymysql 负责和 MySQL 8.0 交互。整体链路是“网页请求 → HTML解析 → 数据清洗 → 批量入库”每一环都有值得注意的细节。下面按实际开发顺序展开尽量把每一步的“为什么这么做”说清楚。1. 整体思路与方案选型为什么是“爬虫 MySQL”而不是“爬虫 CSV”1.1 从CSV到MySQL爬虫项目接数据库到底解决了什么很多刚接触爬虫的人习惯把结果写成CSV因为代码最少、肉眼可见、Excel直接打开。但数据量一上来CSV的问题就非常明显去重得自己写逻辑更新一条记录要遍历整个文件并发写入直接乱套更没有事务和索引的概念。说白了CSV适合“一次性采集、人工查看”不适合“持续采集、反复查询、增量更新”的场景。而MySQL恰好把这几件事都做掉了唯一索引帮你挡重复数据INSERT ... ON DUPLICATE KEY UPDATE可以实现增量更新WHERE条件加索引后查询毫秒级返回事务机制保证批量写入不会写一半就崩。爬虫项目一旦有“长期维护、定期抓取、数据要给别人用”的苗头就应该在第一天就接上数据库而不是等CSV爆炸了再迁移。1.2 技术栈选型与取舍先说我最终用的组合Python 3.10生态最全写爬虫和数据处理都顺手。requests BeautifulSoup4requests做HTTP请求BeautifulSoup解析HTML够用且容易调试。pymysql纯Python实现的MySQL客户端pip装完直接用不需要编译。MySQL 8.0稳定版支持utf8mb4字符集、窗口函数、CTE对爬虫数据的存储和后续分析都够用。有朋友会问为什么不用Scrapy。Scrapy确实是重型爬虫框架自带调度器、中间件、Item Pipeline功能很强但它的学习曲线和项目结构对一个中小型采集任务来说偏重。我的原则是能用脚本解决的问题不急着上框架。当你发现需要分布式采集、需要爬虫管理界面、需要和调度系统集成时再迁移到Scrapy也不迟数据管道本身是通用的。数据库驱动方面pymysql、mysql-connector-python、SQLAlchemy都试过。SQLAlchemy是ORM写起来优雅但多了一层抽象调试批量插入时反而不直观mysql-connector-python是官方驱动性能不错但遇到MySQL 8.0的caching_sha2_password认证插件时老版本驱动会报错。pymysql兼容性最稳而且API简单适合直接在代码里控制SQL所以我最终选了它。1.3 数据链路的整体设计整个采集任务我拆成了四个阶段每个阶段只负责一件事请求阶段构造HTTP请求带上合理的headers拿到HTML响应。解析阶段用BeautifulSoup定位目标数据所在的DOM节点抽取字段。清洗阶段把字符串去空格、转换日期格式、处理缺失值保证入库前数据类型一致。入库阶段拼接SQL用参数化查询不是字符串拼接批量写入MySQL。这个分层的意义在于每一层出问题都能单独定位。比如抓到的HTML是乱码问题在请求阶段的编码处理解析出来是空列表问题在解析阶段的选择器入库报字段超长那就是清洗阶段没做长度校验。各层职责清晰调试起来非常快。2. 环境准备与MySQL基础先把“数据仓库”搭结实2.1 Python环境与依赖安装我用虚拟环境管理项目依赖避免污染全局Python。命令很简单python3 -m venv venv source venv/bin/activate # Windows下是 venv\Scripts\activate pip install requests beautifulsoup4 pymysql三个库各司其职requests负责网络请求beautifulsoup4负责HTML解析pymysql负责数据库交互。如果后续要处理更复杂的反爬可能还会加lxml解析速度更快和fake-useragent随机UA但初期这三个就够了。2.2 MySQL 8.0安装要点本地装还是Docker装MySQL 8.0的安装是网上教程最多的部分之一我自己踩过不少坑这里只讲关键点。本地安装的话官网下载MySQL Community Server安装包一路Next即可但有几个地方必须注意选Server Only别装那些用不上的组件。设置root密码时认证方式选“Use Strong Password Encryption”对应caching_sha2_password插件。安装完成后MySQL服务默认开机自启可以通过mysql -u root -p验证能否登录。如果不想污染本机环境Docker一条命令搞定这是我最推荐的方式docker run -d \ --name mysql8 \ -p 3306:3306 \ -e MYSQL_ROOT_PASSWORDroot123 \ -e MYSQL_DATABASEspider_data \ mysql:8.0这里给root用户设了密码root123创建了名为spider_data的数据库。-p 3306:3306把容器的3306端口映射到宿主机这样宿主机上的Python代码直接连localhost:3306就能访问到容器里的MySQL。注意Docker方式如果容器删了数据就没了生产环境一定要挂载数据卷比如加-v /my/own/datadir:/var/lib/mysql把MySQL的数据文件持久化到宿主机。2.3 库表设计与建表SQL字符集和索引是重中之重爬虫数据最大的特点是“不可控”来源网站可能用各种奇怪的编码字段长度可能超预期同一个字段在不同页面可能格式不一致。所以建表时必须把所有隐患提前堵住。第一数据库和表必须用utf8mb4字符集。utf8mb4是utf8的超集能存emoji和生僻字而MySQL里的utf8实际上是utf8mb3遇到4字节字符会报错。建库建表时显式指定CREATE DATABASE IF NOT EXISTS spider_data DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE spider_data; CREATE TABLE IF NOT EXISTS books ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, isbn VARCHAR(20) NOT NULL, title VARCHAR(200) NOT NULL, author VARCHAR(100) NOT NULL, price DECIMAL(10, 2) NOT NULL DEFAULT 0.00, detail_url VARCHAR(500) NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uk_isbn (isbn), KEY idx_title (title) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci;这张表有几个设计细节值得说isbn加了唯一索引这是数据去重的第一道防线。爬虫重复抓取同一本书时INSERT会因唯一键冲突被数据库挡住。price用DECIMAL(10,2)而不是FLOAT避免浮点数精度误差。网页上抓下来的价格是字符串入库前要先转成Decimal。detail_url长度给到500因为生产环境里有些网站的URL很长默认的255容易被截断。created_at和updated_at用TIMESTAMP类型自动维护创建和更新时间省去在Python里手动写时间戳。3. 爬虫抓取与数据清洗入库之前的所有处理3.1 requests请求的细节别让你的爬虫一眼被看穿requests写起来很简单但直接裸请求很容易被网站拦截。我总结了几个必须注意的细节首先是请求头。浏览器的请求头里会有User-Agent、Accept、Accept-Language、Referer等字段其中User-Agent最重要。伪造一个常见的浏览器UAheaders { User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36, Accept: text/html,application/xhtmlxml,application/xml;q0.9,image/webp,*/*;q0.8, Accept-Language: zh-CN,zh;q0.9,en;q0.8, }其次是超时和重试。网络请求永远是爬虫项目里最不可控的环节不设置timeoutrequests会一直等下去整个程序就卡死了。我的做法是设10秒超时捕获requests.exceptions.RequestException连续失败3次就跳过当前页面try: resp requests.get(url, headersheaders, timeout10) resp.raise_for_status() except requests.exceptions.RequestException as e: print(f请求失败: {url}, 错误: {e}) continue最后是请求频率。控制请求间隔不仅是为了避免被封IP也是基本的网络礼仪。我的经验是动态间隔比如在2到5秒之间随机取一个值import time import random time.sleep(random.uniform(2, 5))3.2 解析HTML与字段抽取选择器怎么写最稳解析HTML我优先用BeautifulSoup加lxml解析器。lxml解析速度快对格式不规范的HTML容错能力也强。代码结构如下from bs4 import BeautifulSoup soup BeautifulSoup(resp.text, lxml) items soup.select(div.book-item) for item in items: title item.select_one(h2.book-title a) author item.select_one(span.author) price item.select_one(span.price) detail_url item.select_one(h2.book-title a) data { title: title.text.strip() if title else , author: author.text.strip() if author else , price_str: price.text.strip() if price else 0, detail_url: detail_url[href] if detail_url else , } # 后续清洗和入库这里有个实操经验用select_one时先判断是否为None再取.text或属性值因为目标节点一旦不存在直接访问属性就会抛AttributeError。用三元表达式写成一行既简洁又安全。3.3 数据清洗的常规操作乱数据不进库网页上抓下来的数据几乎是“脏”的直接入库不仅浪费存储空间还会让后续查询结果不可信。我通常做四件事去空白用.strip()去掉字符串首尾空格并把内部的连续空白替换成单个空格。类型转换价格字段一般是¥59.00这种带符号的字符串用正则提取数字再转成Decimalimport re from decimal import Decimal price_str ¥59.00 price Decimal(re.sub(r[^\d.], , price_str))日期标准化不同网站日期格式五花八门统一转成YYYY-MM-DDfrom datetime import datetime date_str 2024年12月18日 dt datetime.strptime(date_str, %Y年%m月%d日) formatted dt.strftime(%Y-%m-%d)字段长度校验数据库里title字段是VARCHAR(200)如果抓到一个500字的标题直接入库会报Data too long。入库前做个len()检查超长就截断或丢弃我自己习惯截断并记录日志方便后续排查。4. 数据入库从Python到MySQL的最后一公里4.1 连接MySQL的正确姿势pymysql连接MySQL 8.0必须注意charset参数。不写charset的话默认是latin1中文入库就是乱码import pymysql conn pymysql.connect( hostlocalhost, port3306, userroot, passwordroot123, databasespider_data, charsetutf8mb4, cursorclasspymysql.cursors.DictCursor, )cursorclass设为DictCursor后查询结果返回的是字典字段名可以直接当key用比默认的元组可读性强很多。4.2 批量插入executemany比逐条insert快得多爬虫抓到几百条数据后如果逐条执行INSERT每条都要走一次网络往返性能极差。实测下来用executemany批量插入1000条数据比逐条插入快3到5倍。写法也很简单data_list [ (9787115428028, Python编程从入门到实践, 埃里克·马瑟斯, Decimal(89.00), http://example.com/book/1), # ... 更多数据 ] sql INSERT INTO books (isbn, title, author, price, detail_url) VALUES (%s, %s, %s, %s, %s) with conn.cursor() as cursor: cursor.executemany(sql, data_list) conn.commit()注意两点第一SQL里用%s占位符参数通过第二个参数传进去这是参数化查询能有效防止SQL注入第二executemany之后必须调用conn.commit()否则事务没提交数据不会真正写入。4.3 去重与增量更新唯一索引加ON DUPLICATE KEY UPDATE同一批数据可能会被爬虫反复抓到如果每次都是直接INSERT表里全是重复数据。我的方案是依赖之前建表时设的唯一索引配合INSERT ... ON DUPLICATE KEY UPDATE实现“有则更新无则插入”sql INSERT INTO books (isbn, title, author, price, detail_url) VALUES (%s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE title VALUES(title), author VALUES(author), price VALUES(price), detail_url VALUES(detail_url) 这样同一本ISBN的书被抓到第二次时不会新增记录而是把价格、标题等信息更新到最新。这个特性在爬取价格、库存这类经常变化的字段时特别有用。4.4 连接池与断线重连爬虫跑几天不挂的秘诀爬虫任务经常是长跑型脚本可能连续跑几个小时。MySQL默认的wait_timeout是8小时连接超过8小时没活动就会被服务端断开。等脚本再次执行INSERT时就会抛出“MySQL server has gone away”。解决思路有两个一是每次批量插入前检测连接是否可用不可用就重连二是用连接池。我的做法是写一个简单的重连包装def get_connection(): return pymysql.connect( hostlocalhost, port3306, userroot, passwordroot123, databasespider_data, charsetutf8mb4, cursorclasspymysql.cursors.DictCursor, ) try: with conn.cursor() as cursor: cursor.executemany(sql, data_list) conn.commit() except pymysql.err.OperationalError as e: if MySQL server has gone away in str(e): conn get_connection() with conn.cursor() as cursor: cursor.executemany(sql, data_list) conn.commit()在for循环的每一批处理前也可以用conn.ping(reconnectTrue)自动重连这个方法更省事原理是如果连接断开就重新建立。5. 实战案例抓取图书信息存入MySQL并验证数据5.1 完整代码实现下面的代码是一个最小可运行的完整案例抓取一个示例网站的图书列表清洗后批量写入MySQL。我把前面讲的所有关键点都浓缩进来import random import re import time from decimal import Decimal import pymysql import requests from bs4 import BeautifulSoup headers { User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36, } def get_connection(): return pymysql.connect( hostlocalhost, port3306, userroot, passwordroot123, databasespider_data, charsetutf8mb4, cursorclasspymysql.cursors.DictCursor, ) def fetch_and_parse(url): resp requests.get(url, headersheaders, timeout10) resp.raise_for_status() soup BeautifulSoup(resp.text, lxml) items soup.select(div.book-item) results [] for item in items: title_node item.select_one(h2.book-title a) author_node item.select_one(span.author) price_node item.select_one(span.price) if not title_node or not author_node or not price_node: continue price_match re.search(r[\d.], price_node.text.strip()) results.append({ isbn: re.sub(r\D, , title_node[href]), title: title_node.text.strip(), author: author_node.text.strip(), price: Decimal(price_match.group()) if price_match else Decimal(0), detail_url: title_node[href], }) return results def save_to_mysql(conn, data_list): if not data_list: return 0 sql INSERT INTO books (isbn, title, author, price, detail_url) VALUES (%s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE title VALUES(title), author VALUES(author), price VALUES(price), detail_url VALUES(detail_url) rows [(d[isbn], d[title], d[author], d[price], d[detail_url]) for d in data_list] with conn.cursor() as cursor: cursor.executemany(sql, rows) conn.commit() return len(data_list) if __name__ __main__: conn get_connection() total 0 for page in range(1, 6): url fhttp://example.com/books?page{page} try: data fetch_and_parse(url) count save_to_mysql(conn, data) total count print(f第{page}页入库{count}条累计{total}条) except Exception as e: print(f第{page}页失败: {e}) time.sleep(random.uniform(2, 5)) conn.close()5.2 运行结果示例与验证跑完脚本后登录MySQL验证数据。命令行输入mysql -u root -p spider_data然后执行SELECT isbn, title, author, price, created_at FROM books LIMIT 10;正常会看到类似下面的输出------------------------------------------------------------------------------------------------- | isbn | title | author | price | created_at | ------------------------------------------------------------------------------------------------- | 9787115428028 | Python编程从入门到实践 | 埃里克·马瑟斯 | 89.00 | 2025-01-12 10:23:45 | | 9787111213826 | 利用Python进行数据分析 | 韦斯·麦金尼 | 79.00 | 2025-01-12 10:23:45 | -------------------------------------------------------------------------------------------------再验证去重效果把同一个页面重新抓一遍然后统计总行数会发现行数没变但updated_at时间更新了。这证明唯一索引和ON DUPLICATE KEY UPDATE机制生效了。6. 常见问题与排查技巧实录6.1 error 2002 (HY000)连不上本地MySQL怎么办这个报错在MySQL使用中出现的频率最高完整提示一般是Cant connect to local MySQL server through socket /var/run/mysqld/mysqld.sock (2)。原因有两个大类MySQL服务没启动或者客户端默认用了socket协议去连。排查步骤是按照下面的顺序来的检查服务状态。Linux下执行systemctl status mysql如果显示inactive就启动systemctl start mysql。检查端口监听。执行netstat -tlnp | grep 3306确认3306端口在监听。如果没有输出说明mysqld没起来。Python连接时如果报这个错很多时候是host写成了localhost。localhost在Unix系统里默认走socket而不是TCP改成host127.0.0.1强制走TCP就能解决。我自己常用Docker部署MySQL容器里的socket路径和宿主机不一样所以连接时一律用127.0.0.1加端口可以绕开socket相关的所有问题。6.2 中文乱码四个地方必须统一爬虫一遇到中文乱码先别急着改代码按“响应编码 → 数据库字符集 → 表字符集 → 连接字符集”逐层排查。页面响应编码可以在requests里通过resp.encoding判断如果发现是gbk或gb2312就手动重置resp.encoding resp.apparent_encoding数据库和表的字符集可以通过SQL查询确认SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME spider_data;最关键的是连接字符串里的charset必须写utf8mb4。早期我漏掉这个参数解析出来的中文在Python里正常一入库就变成问号排查了半小时才发现是连接层字符集没指定。6.3 插入性能太慢先检查事务和批量大小如果1000条数据插入要好几秒首先看是不是在循环里反复execute然后又commit。事务要尽量大批次尽量批量我习惯每500到1000条提交一次既不会让事务过大又能充分利用MySQL的批处理能力。其次检查表上索引是否过多。索引不是越多越好特别是唯一索引和普通索引叠加后每次INSERT都要更新所有索引会拖慢写入速度。只给真正需要查询和去重的字段加索引其他字段保持普通列就好。6.4 锁表问题长事务是元凶爬虫脚本在写入时如果开启了事务但没及时commit或者中途抛异常没回滚会一直持有行锁甚至表锁导致其他查询全部阻塞。排查方法是用下面这个SQL查看当前有哪些事务在跑SELECT * FROM information_schema.INNODB_TRX\G找到长时间未提交的事务用KILL trx_mysql_thread_id把它干掉。平时写代码时务必把commit放在finally里或者用上下文管理器确保事务一定能关闭。6.5 “数据抓下来但库里没有”先看commit再查异常新手最容易遇到的问题脚本运行完没报错但数据库里一条数据都没有。这种90%是忘了commit。pymysql默认autocommit是False所有的INSERT、UPDATE都要显式调用conn.commit()才会真正落盘。我现在的习惯是封装一个save函数commit放在批量写入之后并且用try/except包住任何异常都要打印堆栈避免“看似成功实则失败”的假象。爬虫接MySQL这套组合我实际用了两年多踩过上面这些坑之后最大的收获是数据管道越早设计好后面越省心。抓数据只是第一步让数据变得可靠、可查、可更新才是爬虫项目真正产生价值的地方。最后再分享一个实用小技巧每次入库后在程序里顺手统计一下本次新增行数和更新行数打印到日志里时间长了你就知道哪些网站的数据在持续变化哪些已经不再更新对调度策略的调整非常有帮助。本文还有配套的精品资源点击获取