1. 为什么要把Excel留在爬虫和数据库之间这个三件套的合理吗1.1 这条链路到底在解决什么问题先说说我接到这个需求时的第一反应。某业务方要我做一个采集程序把目标平台上的数据抓下来最后落到自家的MySQL里供后台查询。需求文档写得很直白“先给Excel再进数据库”。我一开始也觉得多此一举——爬虫直接连库不行吗后来跑了一周才明白这条链路从来不是技术能力的竞赛而是容错率的博弈。爬虫代码的产出永远是“无法保证100%干净的数据”。网络抖动、页面改版、接口限流、某个字段突然变成空字符串都是家常便饭。如果爬虫直连数据库任何一次解析异常都可能污染线上表。而Excel文件在中间充当了一堵缓冲墙程序跑完先落成Excel人可以预览、抽样检查甚至可以手动修正后再入库。很多非技术同事也能打开Excel帮忙确认数据对不对这对小型团队来说太重要了。所以这个项目的核心路线就变成了爬虫采集原始数据pandas做清洗Excel做检查点最后SQLAlchemy批量入库。这四个环节缺一不可而我今天想分享的正是每个环节里那些不试不知道的坑。1.2 技术选型与环境版本一上来就能省很多事选型这件事不同版本之间差异极大建议直接照我这个组合来别贪新也别守着老版本不放组件版本说明Python3.103.8以下很多第三方库的二进制包不好装requests2.31.x老牌且稳定适合大多数静态请求pandas2.x数据清洗主力接口变化大建议新版本openpyxl3.1.xExcel读写神器支持格式调整SQLAlchemy2.x负责数据库连接与写入PyMySQL1.x纯Python实现的MySQL驱动安装可以直接用requirements.txt锁版本我个人的建议是尽量用虚拟环境避免把系统Python搞乱。网络采集环境的依赖管理我习惯装完加一行pip freeze requirements.txt这样重跑环境只需要30秒。另外说一句关于数据库连接池的事。很多新手直接用pymysql裸写循环插入发现越跑越慢其实是连接没复用的缘故。SQLAlchemy的Engine默认自带了连接池管理我们用create_engine(mysqlpymysql://user:passhost/db, pool_size5, pool_recycle3600)就能自动复用连接这个后面入库部分会再展开。2. 爬虫取数先保证来源稳定再谈清洗和入库2.1 静态请求的几个基础习惯 headers、超时和重试绝大多数目标网站的页面数据其实藏在接口里不需要浏览器渲染所以requests作为第一选择是合理的。真正让我吃过亏的是请求头不完整。很多网站会校验User-Agent和Referer缺一个就返回403。我写采集脚本时会在一开始就定义好一个完整的请求头字典并且用requests.Session()来保持会话import requests from requests.adapters import HTTPAdapter from urllib3.util.retry import Retry session requests.Session() session.headers.update({ User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 Chrome/120.0 Safari/537.36, Accept: application/json, text/plain, */*, Accept-Language: zh-CN,zh;q0.9, }) retry Retry(total3, backoff_factor1, status_forcelist[500, 502, 503, 504]) adapter HTTPAdapter(max_retriesretry) session.mount(http://, adapter) session.mount(https://, adapter)这里最核心的是Retry策略。我实际统计过加了重试之后一个5000条数据的采集任务成功率从93%直接提升到99.6%。backoff_factor1表示重试前等待1秒、2秒、4秒给服务器喘息的机会也避免触发更狠的封禁机制。还有一件事必须养成习惯每次请求之间做延迟。我用的是最简单的time.sleep(random.uniform(0.5, 1.5))这个随机间隔既不像固定频率那样容易被识别也不会因为太激进给目标站造成压力。做采集的都知道礼礼貌貌地取数大家都能长久合作。2.2 动态页面场景先找接口再用Selenium兜底现在很多前端框架渲染的页面直接GET返回的HTML里根本没有数据全在JavaScript的XHR请求里。新手最容易犯的错误是一上来就开Selenium模拟浏览器又慢又耗内存。我的做法是先打开浏览器开发者工具里的网络面板刷新页面过滤XHR请求大概率能找到返回JSON数据的接口地址。拿到接口后还是用requests去请求速度提升一个数量级。有个技巧接口返回的JSON里往往嵌套很深写解析代码时别一层层用中括号取容易崩。我是用jsonpath或纯Python递归函数来定位字段。比如要找data.list[0].item.name这样的路径写个小函数遍历字典比手写十行中括号强壮得多。只有当数据确实在页面渲染后才通过JS动态计算生成且碰不到底层接口时我才考虑Selenium。用Selenium时记得设置无头模式并显式等待元素出现千万别用time.sleep硬等。我习惯WebDriverWait(driver, 10).until(EC.presence_of_element_located((By.CSS_SELECTOR, xxx)))这样元素加载完才继续不会因为网络慢白白等死。还有一个容易被忽略的登录态问题。很多接口需要Cookie登录后的Cookie复制到脚本里是最快的但会过期。更好的做法是用requests的Session登录一次把登录接口的账号密码参数化每次任务跑之前自动刷新Cookie。Session对象会自动保存Set-Cookie后续请求直接带上这个处理很简单但非常实用。2.3 给原始数据做快照是清洗前最划算的保险我强烈建议在爬虫和清洗之间加一步把每次抓取到的原始响应存下来无论是HTML还是JSON。哪怕只是临时存在本地文件中。原因很简单清洗规则是你自己写的可能一半逻辑测试时才发现不对此时原始数据没了就只能重新抓可重抓的成本不仅是时间还有被封的风险。我的做法是在项目里建一个raw_data/2026-01-15/的目录按日期建子目录用json.dump或response.text原样保存。文件名带上页码或时间戳。这样后面清洗脚本每改一次就能拿同一份原始数据反复测不会因为目标网站内容变化导致清洗逻辑验证失真。这个习惯帮我省了不止一次的重抓时间。有一次业务方第二天才说“那个价格字段我们要带单位的原字段不要只要数字”如果原始快照不在我估计要哭。快照文件给清洗阶段留下了充分的试错空间。3. 数据清洗的核心操作pandas这一关处理不好后面全白搭3.1 第一关缺失值和重复值的识别不能只看表面数据从爬虫落到DataFrame之后第一件事永远是df.info()和df.head()。但我看到太多人只看这两步就开始处理完全没意识到数据分布里可能藏着问题。我习惯再加一步df.describe(includeall)对数值列看分位数对对象列看unique数量能快速发现异常分布。缺失值处理不是无脑dropna或fillna而是分场景判断。比如爬虫没有爬到某个可选字段缺失率只有2%直接删除这几行是划算的如果“商品价格”这类核心字段缺失率达到20%就得回头查爬虫代码是不是漏取了而不是清洗硬扛。我的经验法则是缺失率低于5%且不是核心字段直接填充默认值缺失率5%~20%考虑基于其他字段推算超过20%优先修复源头。# 先看每列缺失率 miss_rate df.isna().mean().sort_values(ascendingFalse) print(miss_rate[miss_rate 0])重复值也是同理。df.drop_duplicates()默认全列相同才算重复但真实业务里可能只是某个业务键重复比如同一商品的“平台商品ID”重复就代表采集重了。这时要写df.drop_duplicates(subset[product_id], keepfirst)。我还会保留一个去重后的行数统计记录去重比例写进日志里方便后续溯源。3.2 第二关类型转换和脏字符串清理细节决定成败爬虫拿到的东西十有八九是字符串哪怕它看起来是数字。“价格”可能是¥19.90“销量”可能是1.2万“日期”可能是2026年01月15日。这些不洗干净进数据库就是一场灾难。我当时处理价格字段的代码是这样写的import pandas as pd def clean_price(value): if value is None: return None text str(value).replace(¥, ).replace(元, ).strip() if 万 in text: num float(text.replace(万, )) return round(num * 10000, 2) return pd.to_numeric(text, errorscoerce)这里最关键的是errorscoerce。碰到无法转换的值pandas会置为NaN而不是抛异常然后我们再回头分析这些NaN是源数据问题还是规则问题。这个设计让清洗脚本能一次性跑完而不是中断在中间。日期格式化也有讲究Excel单元格里经常出现“2026/1/15”“15-01-2026”“45242”这种Excel日期序列号。pandas的pd.to_datetime能解析大部分但最好统一指定格式。我习惯先做一个自动识别df[pub_date] pd.to_datetime(df[pub_date], errorscoerce)如果真的碰到Excel日期序列号一个数字要先转成datetime再走pandas解析from datetime import datetime, timedelta excel_serial 45242 dt datetime(1899, 12, 30) timedelta(daysexcel_serial)这个坑我在Excel文件处理专题里还会细讲这里先记住一点日期列必须显式转换成datetime64类型否则入库后比较和排序都会很痛苦。文本清理方面我习惯统一去掉首尾空格、全角空格以及把中文标点统一成半角。df[title] df[title].str.strip().str.replace( , ).str.replace(, :, regexFalse)注意这里str.replace加regexFalse因为很多标题里含括号、点号这些正则元字符不关正则解析容易出现莫名报错。3.3 第三关清洗结果的验证这才是清洗的专业含金量清洗完不是直接保存就完事我至少要过三遍验证第一遍是数量验证清洗后行数与源数据行数对比缺失行数在合理范围。第二遍是类型验证df.dtypes里每个字段是否符合预期比如价格是float日期是datetime。第三遍是业务合理性验证写出几个统计指标比如价格最大值不超过某个合理阈值、日期范围落在采集时段内如果指标异常说明清洗规则有漏洞。我习惯把这三遍验证封装成一个validate()函数里面用assert或显式抛出异常。一旦某一步不合格立刻停住不往下游写Excel、不碰数据库。宁可让任务失败也不能让脏数据扩散。有一个很典型的案例某个数字字段爬回来是“-”我当时没做强制转换入库后MySQL自动把字符串转成了0导致所有真实数据都被误判。多加一步pd.to_numeric(..., errorscoerce)再加一个非负校验这个问题直接消失。清洗这活儿不复杂但要把每一步都当成过滤器每一层都挡住一类问题最后出来的数据才经得起业务查询。4. Excel读写实战格式、合并单元格、大数据量逐个击破4.1 读取Excel的姿势直接影响后续清洗工作量如果数据源头之一就是Excel文件读取时就要小心了。我是用pd.read_excel作为默认入口但有两个参数几乎是必备的dtypeobject和skiprows。dtypeobject的意思是让pandas先别自作主张推断类型全部读成字符串再统一处理这样能避免“001”被自动转成1的悲剧。很多编号、编码字段是一个前导0的字符串如果让pandas推断成int前导0直接丢失回头对不上业务数据。skiprows则是应对Excel里第一行是标题或说明文字的情况。我见过不少Excel第一行写着“数据统计截止到2026年1月”第二行才是列表头不跳过的话整个DataFrame的列名就乱了。读取之前先打开文件看一眼结构这是一个值得付出的“两分钟成本”。也有一种特殊情况Excel里用了合并单元格读取后只有第一行有值后面都是NaN。处理方法是先用df.fillna(methodffill)把合并单元格的值广播到下面的空行再参与清洗流程。用ffill方向填充一定要确认合并方向默认向下填充是对的。4.2 写Excel时让人舒服的格式不止是数据对就行清洗好的数据写回Excel给业务方检查这里有个小讲究不要df.to_excel一锤子买卖至少把列宽调整一下表头加粗并且带背景色。这看起来是面子工程但实际业务中对方打开Excel的第一感受决定了他们对整个项目的信任度。用openpyxl实现很简单from openpyxl import load_workbook from openpyxl.styles import Font, PatternFill, Alignment wb load_workbook(output.xlsx) ws wb.active # 表头加粗 for cell in ws[1]: cell.font Font(boldTrue) cell.fill PatternFill(start_colorDDDDDD, end_colorDDDDDD, fill_typesolid) cell.alignment Alignment(horizontalcenter, verticalcenter) # 调整列宽 for col in ws.columns: max_len max(len(str(cell.value)) for cell in col if cell.value) ws.column_dimensions[col[0].column_letter].width max_len 2 wb.save(output.xlsx)如果你的数据量很大写Excel要留意一个事实xlsxwriter引擎写速度比openpyxl快很多但它在格式处理上的API和openpyxl有点不一样。如果你的场景是十万行以内我推荐直接pd.ExcelWriter(output.xlsx, engineopenpyxl)能把控制格式和写数据放在同一个流程里。超过二十万行Excel已经不适合当检查层了这一步我会跳过直接在清洗后入库。4.3 Excel常见坑读出来全是NaN的三种根源“read_excel读出来全是NaN”是搜索词里反复出现的痛我给你把根源一次性总结清楚第一种是sheet名不对。Excel文件里有多个Sheetpd.read_excel默认只读第一个如果数据不在第一个Sheet读出来自然不是预期内容但“全是NaN”更像第二种情况——表头不在第一行。第三种最隐蔽Excel某些单元格是公式但公式计算结果没缓存openpyxl读取时拿不到计算结果返回None。解决办法是让pandas读到单元格的值而不是公式本身。pd.read_excel在engineopenpyxl下默认读取的是缓存值如果遇到空值建议用Excel打开文件按一下保存让公式结果落盘再重新读取。另外如果你从网页复制数据粘贴到Excel经常会出现不可见字符比如NBSP\xa0和零宽空格。清理时用str.replace(\\xa0, )可以一并处理。这个细节很小但能卡住一整套入库流程因为隐形字符往往让字符串匹配全部失败。5. 数据入库从Excel到数据库的三种方案与性能实测5.1 库表设计字段类型、主键和索引怎么定写入之前先建表。这个表设计得好不好直接影响后面查询和维护体验。我的经验是尽量让字段类型和pandas清洗后的数据类型对齐。price字段用DECIMAL(10,2)日期字段用DATETIME长文本用VARCHAR(255)或TEXT布尔字段用TINYINT(1)。主键的策略我要多说两句。爬虫数据往往天然不稳定如果直接取业务ID做主键万一源站改了ID规则很容易冲突。我的做法是增加一个自增ID做物理主键同时给业务ID建唯一索引这样既能保证去重又能避免业务上重复数据。CREATE TABLE products ( id BIGINT AUTO_INCREMENT PRIMARY KEY, product_id VARCHAR(64) NOT NULL COMMENT 平台商品ID, title VARCHAR(255), price DECIMAL(10,2), pub_date DATETIME, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uk_product (product_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这里utf8mb4是必须的中文加emoji都能存很久以前那个坑是用utf8导致表情符号存不进去。5.2 方案一pandas的to_sql适合中小数据量的省心模式如果清洗后的Excel数据在几万行以内df.to_sql是最省心的方案九成的项目够用了。关键代码就两行但有一些隐藏参数值得仔细看from sqlalchemy import create_engine engine create_engine( mysqlpymysql://root:password127.0.0.1:3306/mydb?charsetutf8mb4, pool_size5, pool_recycle3600 ) df.to_sql( nameproducts, conengine, if_existsappend, indexFalse, chunksize5000, dtype{ product_id: sqlalchemy.types.VARCHAR(64), price: sqlalchemy.types.DECIMAL(10, 2), pub_date: sqlalchemy.types.DATETIME(), } )这三个参数的价值分别是if_existsappend保证不覆盖已有表数据indexFalse防止DataFrame索引被当成一列写进去chunksize5000让写入分块提交避免一个超大事务把MySQL卡死。实测下来5万行数据走这个方案通常几十秒内完成属于“泡杯茶等一等”的水平。7万行以上to_sql单条单条拼接INSERT的开销就开始明显速度会掉到1万行/分钟左右这时就该换方案二。5.3 方案二executemany批量插入大数据量的手动档当数据量到几十万行时我改用pymysql或SQLAlchemy的connection直接走executemany把数据拼成列表一次性提交一批。核心原理是减少数据库操作次数把每条INSERT的往返次数变成每批一次。import pymysql conn pymysql.connect(host127.0.0.1, userroot, passwordxxx, dbmydb, charsetutf8mb4) cursor conn.cursor() sql INSERT INTO products (product_id, title, price, pub_date) VALUES (%s, %s, %s, %s) batch_size 2000 values [ (row[product_id], row[title], row[price], row[pub_date]) for _, row in df.iterrows() ] for i in range(0, len(values), batch_size): cursor.executemany(sql, values[i:ibatch_size]) conn.commit() cursor.close() conn.close()这个方案在20万行数据上实测能比to_sql快3~5倍瓶颈基本只在于Python生成列表和MySQL网络传输。如果量级再大比如百万行就该考虑LOAD DATA INFILE或者先用CSV导入但那已经脱离Excel中间层的适用场景我就不展开了。5.4 增量更新和断点续传别每次全量重建数据库写入不是跑一次就完的爬虫都是定时任务每天可能跑一次。这时要考虑的是增量更新而不是每次全量删除重建。最简单的增量策略是去重键查询每次入库前先把目标表里的现有product_id查出来然后过滤掉这些ID再插入可以用一条SQL操作。也可以用“插入后更新”的upsert写法MySQL里是INSERT INTO products (product_id, title, price, pub_date) VALUES (%s, %s, %s, %s) ON DUPLICATE KEY UPDATE title VALUES(title), price VALUES(price), pub_date VALUES(pub_date);这个写法的好处是如果爬虫重新抓取了某条旧数据直接覆盖更新不产生重复行。配合唯一索引uk_product程序会变得非常健壮。我在实际项目里首选这种upsert遇到当天重跑脚本也不用担心重复数据污染表。6. 运行中遇到的实际问题和排查顺序6.1 一次典型的“Excel读出来全是NaN”排查过程有一回业务方发来一个Excel我跑read_excel后一看几千行全是NaN差点以为对方给的是空文件。后来我观察到只要跳过前两行数据就正常了——他们的表头前面有一行合并单元格的入口图片被pandas误认为标题行。解决方式是用header2指定正确的表头行。排查的过程也很有代表性第一步打开Excel用肉眼看结构第二步用pd.read_excel(..., nrows5)只读5行观察输出第三步看df.columns是否为预期表头第四步检查df.head()数据是否正常。这个排查顺序其实适用于所有数据读取问题从表头到数据内容的逐层缩小范围比直接改参数瞎碰有效得多。6.2 定时任务和数据更新的迭代思路我的项目最终跑在服务器的cron上每天凌晨跑一次爬虫抓完清洗落Excel再入库。有一个容易被忽视的问题是日志。一开始我只在控制台print后来发现任务半夜跑挂了没人知道第二天业务方问起来才知道。后来我在每个关键环节加上了日志文件记录包括爬了多少条、清洗后剩多少条、入库多少条、耗时多少秒并且发一个简单的通知消息。定时任务里我还加了断点续传的逻辑如果某天的采集中断了下次先读取Excel检查点只补齐缺失的数据而不是全部重跑。这个设计在数据量变大之后尤其重要否则全量重跑的时间和风险都是成倍增加的。还有一条来自实际项目的教训数据库密码不要硬编码在脚本里环境变量或用配置文件管理。有一次我把密码写死在代码库项目上线第二天数据库就被扫到的高危端口攻击连了一整夜。从那以后所有项目我都走环境变量加白名单这个习惯只看代码的人可能不理解但真的出事时你会感恩。整个“爬虫—Excel—数据库”的组合核心不在于某个单一技术有多高级而在于每个环节都考虑到出错的可能性并把这些可能性通过检查点、日志、增量更新等手段提前消化掉。现在这套流程在我手上已经稳定跑了三个多月基本做到了“无人值守挂了能查”。如果哪天你的数据采集任务开始频繁返工不妨回头看看是不是每个环节都给了数据一次被检查的机会这条链路的设计初衷从来都是如此。