如果有人让你“每天把某张业务表导出成Excel”，你的第一反应可能是：写个Python脚本连上数据库，pd.read_sql()一行查出来，再to_excel()一行存文件，完事。但当你真正接到这个需求，面对几千万行数据、几十个字段、要按日期分表、要控制单文件大小、还要保证导出过程中不拖垮线上数据库的时候，就会发现这事儿远没有两行代码那么简单。
我做过几个类似的导出工具，后来沉淀成一个可配置的批量导出方案，核心解决三件事：从数据库高效取数、按不同维度拆分写出Excel、保证长时间跑批的稳定性。这篇文章就围绕这套方案拆开讲，包括选型思路、完整实现、参数取舍，以及一些文档里查不到的坑。适合正在写数据导出工具的开发者，以及被“Excel报表”反复折腾的运维和数据分析同学参考。
1. 整体设计：先把导出这件事拆成四个环节
接到“批量导出数据库数据至Excel”这个需求，先别着急写代码。我习惯先把它拆成四个环节：取数、转换、写出、调度与容错。这四个环节对应的问题分别是：怎么高效地把数据从库里拿出来，怎么把数据库类型安全地转成Excel能表达的类型，怎么控制文件大小和内存，以及跑挂了怎么重试、怎么通知。
1.1 为什么不能一把梭直接 read_sql 然后 to_excel
很多新手上来就写这样一段：
import pandas as pd from sqlalchemy import create_engine engine = create_engine("mysql+pymysql://user:pass@host:3306/db") df = pd.read_sql("SELECT * FROM big_table", engine) df.to_excel("output.xlsx", index=False)小数据量没问题，一两万行随便跑。但数据量一旦上来，两个问题立刻暴露：
SELECT *会把全表数据一次性加载到内存，几千万行、几十个字段，内存直接爆掉，轻则进程被杀，重则拖垮同机上的其他服务。- 单文件Excel有行数上限和大小限制。老版Excel单表最多65536行，新版.xlsx格式到1048576行，但文件一大，别人打开、筛选、排序都卡成PPT，实际业务中超过10万行就要考虑拆分了。
所以设计的第一步，是明确数据量级和使用场景：数据是要给别人在Excel里手工看，还是做系统间数据交换？是每天增量几万行，还是历史全量几千万行？这直接决定后续取数和拆分策略。
1.2 一个可落地的四层架构
我最终沉淀出的结构是这样的：
| 层次 | 职责 | 关键点 |
|---|---|---|
| 配置层 | 数据库连接、导出路径、表名、拆分规则 | 用配置文件驱动，不硬编码 |
| 取数层 | 分批从数据库查询，避免全量加载 | 游标分批、流式读取 |
| 转换层 | 字段类型映射、时间格式化、空值处理 | 解决数据库类型和Excel类型的差异 |
| 写出层 | 按行数/日期拆分Excel、多Sheet、压缩打包 | 控制单文件大小，便于分发 |
每一层独立，改一处不影响其他。比如今天要从MySQL换成PostgreSQL，只改配置层的连接串和取数层的方言；明天要改成按日期拆文件，只改写出层的拆分逻辑。这个设计帮我省掉了大量重复返工。
2. 核心选型：连接库、Excel引擎和拆分策略怎么定
工具选型是这类项目最容易反复折腾的地方。我用过纯pymysql手写SQL拼装，也用过pandas全家桶，还试过openpyxl和xlsxwriter混搭，最终根据不同场景确定了组合方案。
2.1 数据库连接：SQLAlchemy 还是原生驱动
取数层我推荐用SQLAlchemy做连接管理，底层驱动按数据库类型选择。原因有三个：
- 连接串统一，换数据库不用改业务代码，只改URL的方言部分。
- 配合
pandas.read_sql时，参数化查询和类型推断比较省心。 - 连接池管理成熟，长时间跑批不容易断连。
但要注意：pd.read_sql()默认会把整个结果集拉到本地内存。对于大表，我不用read_sql一次拉全量，而是用服务端游标（server-side cursor）加分批取数。MySQL里用pymysql的SSCursor，PostgreSQL用psycopg2的server_side_cursor。核心逻辑是先拿一个游标，然后每次fetchmany(50000)，处理完一批再取下一批，内存始终只保留5万行左右。
比较常见的错误是：用了SSCursor之后，同一个连接上再做别的查询会报错，因为游标还没释放。所以分批取数的循环里不要穿插其他SQL操作，取完一批立即处理、立即释放。
2.2 Excel引擎：openpyxl 与 xlsxwriter 的取舍
写出Excel这一步，pandas默认有两个引擎：openpyxl和xlsxwriter。我在不同阶段都踩过坑，简单对比一下：
| 能力 | openpyxl | xlsxwriter |
|---|---|---|
| 写入大文件 | 慢，内存占用高 | 快，内存占用低 |
| 对已有文件追加修改 | 支持 | 不支持 |
| 单元格格式控制 | 一般 | 强，支持条件格式、图表 |
| 处理超长文本 | 容易报错 | 更稳健 |
| 多Sheet | 支持 | 支持 |
如果数据量很大，我优先选xlsxwriter，它写大文件的性能和内存控制明显好。但如果你需要在已有的Excel上追加Sheet，或者要读取再修改，那就只能用openpyxl，xlsxwriter只能从零创建新文件。
这里有一个容易忽略的点：xlsxwriter默认没法直接写出datetime.date以外的Python原生日期类型，如果你从数据库查出来的是字符串时间，最好在转换层统一格式化。否则写进Excel的是一个字符串，别人后续做透视表、日期筛选会非常痛苦。
2.3 拆分策略：按行数拆、按日期拆、还是按业务维度拆
Excel单文件不能无限大，所以拆分是批量导出的核心。我一般按三种维度选：
- 按行数拆：每5万行一个文件，适合数据量大且无明确时间分区的场景。文件名后缀
_part1、_part2，简单粗暴。 - 按日期拆：每天一个文件，适合按时间归档的报表场景。文件名带日期，管理方便，增量导出也能复用。
- 按业务维度拆：比如按地区、按渠道拆，适合业务方已经有固定分类的场景，拆分结果直接发给对应负责人。
拆分粒度也要看下游怎么用。给别人看的数据，单个文件控制在10万行以内、20MB以内比较稳妥；做系统间批量导入的话，反而要关注行数和字段顺序的严格一致，文件大小反而不是首要问题。
3. 完整实现：一个可跑的批量导出脚本
下面给出一套完整的参考实现。为了让代码具备复用性，我用配置文件驱动，把数据库连接、导出表、拆分规则都抽出来。实际项目里我会再加日志和告警，这里先展示主干。
3.1 环境准备与依赖安装
建议用virtualenv或conda建独立环境，避免和线上Python环境互相污染。依赖如下：
pip install pymysql sqlalchemy pandas xlsxwriter openpyxl其中openpyxl和xlsxwriter可以共存，前者作为兜底，后者作为主力写引擎。如果是PostgreSQL，把pymysql换成psycopg2-binary即可。
配置我用一个config.yaml来放连接串和任务参数：
database: url: "mysql+pymysql://user:password@host:3306/dbname?charset=utf8mb4" server_side_cursor: true export: output_dir: "./exports" batch_size: 50000 split_mode: "rows" # rows / date / none rows_per_file: 100000 date_column: "created_at" date_format: "%Y-%m-%d" excel_engine: "xlsxwriter" single_sheet: true3.2 取数层：分批游标查询的核心写法
取数层的关键是分批拉取，防止内存暴涨。我用SQLAlchemy拿到原始连接，再包一层SSCursor。
from sqlalchemy import create_engine import pymysql engine = create_engine(config["database"]["url"]) def fetch_rows_in_batches(sql, batch_size=50000): raw_conn = engine.raw_connection() try: inner = raw_conn.connection cursor = inner.cursor(pymysql.cursors.SSCursor) cursor.execute(sql) while True: rows = cursor.fetchmany(batch_size) if not rows: break yield rows cursor.close() finally: raw_conn.close()注意，使用SSCursor时在循环里不要执行同连接的其他SQL，否则游标会失效。每一批数据出来之后，直接交给转换层处理，处理完释放，内存占用始终稳定。
3.3 转换层：字段类型、时间格式和空值处理
数据库字段到Excel字段，最大的坑集中在三块：
- 时间字段：数据库里的
datetime、timestamp，pandas读出来通常是Timestamp或字符串，写出前统一格式化成YYYY-MM-DD HH:MM:SS。 - 空值：数据库里的
NULL，在Excel里默认是空单元格，但如果下游要导入其他系统，空值和空字符串有时会被区别对待，需要根据场景决定保留NULL还是替换成空字符串。 - 超长文本：单元格超过32767字符时，
openpyxl会直接报错，xlsxwriter相对宽松，但建议在转换层就做截断处理。
我通常定义一个convert_row函数，把每批数据处理成整齐的列表格式：
from datetime import datetime, date def convert_row(row, columns): converted = [] for col, val in zip(columns, row): if isinstance(val, (datetime, date)): val = val.strftime("%Y-%m-%d %H:%M:%S") elif val is None: val = "" elif isinstance(val, (int, float)) and str(val).endswith(".0"): val = int(val) converted.append(val) return converted这里有个小细节：数据库里的DECIMAL或FLOAT类型，读出来可能是Decimal('123.00')或123.0，直接写进Excel会出现一长串小数位，影响观感。我一般先判断数值是否为整数值，是就转成int，否则保留两位小数。
3.4 写出层：多Sheet、多文件与压缩
写出层按split_mode处理。先看一下统一入口：
import os import pandas as pd from datetime import datetime def export_table(table_name, columns, rows_iter): os.makedirs(config["export"]["output_dir"], exist_ok=True) mode = config["export"]["split_mode"] if mode == "rows": export_by_rows(table_name, columns, rows_iter) elif mode == "date": export_by_date(table_name, columns, rows_iter) else: export_single(table_name, columns, rows_iter)按行数拆分的方法是把所有批次的行累加到一个临时列表，达到rows_per_file就写一个文件，然后清空列表继续。这种写法比先把全部数据收集到内存再统一切片要稳。
def export_by_rows(table_name, columns, rows_iter): batch_rows = [] part_no = 1 for rows in rows_iter: for row in rows: batch_rows.append(convert_row(row, columns)) if len(batch_rows) >= config["export"]["rows_per_file"]: _write_excel(f"{table_name}_part{part_no}.xlsx", columns, batch_rows) batch_rows.clear() part_no += 1 if batch_rows: _write_excel(f"{table_name}_part{part_no}.xlsx", columns, batch_rows) def _write_excel(filename, columns, rows): filepath = os.path.join(config["export"]["output_dir"], filename) df = pd.DataFrame(rows, columns=columns) df.to_excel(filepath, index=False, engine=config["export"]["excel_engine"])按日期拆分略有不同，背后的思路是把每批数据按日期归组，再按组写出。如果业务数据很强地依赖时间，这种拆分很直观，而且后续增量导出可以直接按日期断点续跑。
写完后如果要发给别人，我会用zipfile把整个目录打包，再清理临时文件。压缩这一步能有效解决“几十个Excel文件零散传输”的问题，尤其是走邮件或者网盘的时候。
3.5 调度与日志：跑批任务不会一直顺风顺水
导出任务往往是夜里的定时任务，不能人肉盯着。所以我一般在脚本里加两样东西：执行日志和结果落盘检查。
日志用Python标准库的logging就能满足，关键是记录每个阶段的耗时和行数，方便事后排查：
import logging logging.basicConfig( level=logging.INFO, format="%(asctime)s - %(levelname)s - %(message)s", handlers=[ logging.FileHandler("export.log"), logging.StreamHandler() ] )配合schedule或系统的crontab做定时触发。跑批结束之后校验生成文件的行数和数据库查询行数是否一致，不一致就触发告警，避免“文件生成了但数据少了一半”这种事故。之前我遇到过一次：数据库limit没写对，导致导出结果只有预期的三分之一，文件还一切正常，直到下游反馈数据对不上才发现。从那以后，行数校验就成了标配。
4. 常见问题与排查技巧实录
这类脚本跑多了，问题和解决方案基本能汇总成一张速查表。我把高频问题和排查思路整理出来，大家可以直接对照。
4.1 导出过程中数据库连接中断
长时间跑批时，MySQL的wait_timeout和interactive_timeout默认值可能只有几十秒，脚本里某个批次处理时间超过阈值，连接就被服务端断开。表现是：前半段跑得好好的，跑到中间报MySQL Connection not available。
排查和规避手段：
- 取数循环里定期重连，或者把连接池的
pre_ping打开。 - 处理SQL查询时不要一次查太多关联表，控制单批次数据量。
- 分批大小不是越大越好，
fetchmany的批次越大，单批处理时间越长，越容易触发超时。我的经验是5万行左右比较平衡，既不会太多内存，也不会频繁交互。
4.2 生成的Excel打开提示文件损坏
大概率是写出过程中进程被强制终止，或者磁盘空间满了，导致Excel文件没有正常关闭、索引信息不完整。排查思路：
- 检查脚本是否有
except分支没有正常关闭writer。 - 确认输出目录磁盘空间充足。
- 避免在写出过程中kill进程；不得已要停，也要等当前批次写完再停。
还有一个隐蔽原因：文件名或Sheet名里带了Excel非法字符，比如/、\、?、*、:等。xlsxwriter遇到这些字符可能直接写入非法路径，导致文件打不开。我一般在拼接文件名时会做一层清洗。
4.3 大文件写出变慢，CPU占用持续走高
openpyxl引擎在大数据量下性能很差，这是正常的。换成xlsxwriter后会明显改善。如果换引擎之后还是慢，多半是转换层在做逐行逐字段的格式化，Python级别的循环在大数据量下非常耗时。
优化思路有三个方向：
- 能用向量化操作就不用Python循环，比如pandas的
apply虽然看着像循环，但底层C实现比手动for快不少。 - 没必要转换的字段就不要转，比如ID列保持数值，别格式化字符串。
- 多进程并行处理分片数据，但要注意数据库连接不能跨进程共享，每个进程单独建立连接。
4.4 数据量太大，单文件超过Excel上限
前面提过，老格式.xls上限65536行，新格式.xlsx上限1048576行，但实际用下来超过30万行Excel就非常卡了。遇到这种场景，我会主动拆多Sheet或拆多文件。
如果业务方坚持要一个文件，就拆多个Sheet。利用xlsxwriter的add_worksheet实现多Sheet导出，每个Sheet控制在10万行以内。这样既保留“一个文件”的便利，又让下游打开不卡。
4.5 导出的时间字段和数据库不一致
常见原因：pandas读取时把datetime转成了Timestamp，写出到Excel时又默认新格式的日期时间，或者写成了UTC时间。如果数据库和脚本所在机器时区不一致，还会出现几小时偏移。
规避方式：取数SQL里显式格式化时间字段，不要指望后续转换。比如：
SELECT id, DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s') AS created_at FROM my_table这样能确保数据库先转好，后续pandas直接按字符串处理，不参与时区推断。
4.6 字段类型被Excel自动转换，手机号、订单号变科学计数法
这是导出任务里被抱怨最多的问题之一。数据库里的订单号、手机号、身份证号等长数字，写出到Excel后会被自动转成科学计数法，或者末尾几位变成0。
解决方式是在写入前把这类字段统一转成字符串，并在_write_excel里用xlsxwriter设置单元格格式为文本：
worksheet.set_column('A:A', 20, None) worksheet.set_column('B:B', 20, None)如果用的是pandas加to_excel，可以在转换层就把长数字字段拼成字符串，比如str(val) + "\t"，或者用xlsxwriter的write_string方法直接写入文本格式。前者看起来不够优雅，但效果直接。
5. 经验总结与扩展建议
这套批量导出方案我用过几个版本，从最早纯pandas一把梭，到后来分片游标加多文件拆分，稳定性和可维护性都有质的提升。下面几点是我实际使用过程中的体会。
5.1 关于批量和拆分，值得提前想清楚的三件事
- 先问清楚数据下游的使用场景，再决定拆分规则。给别人做透视分析用的，和给系统导入用的，优化方向完全不同。
- 导出任务的失败重试策略要提前设计，最好支持断点续传。按日期拆分的任务比较容易做断点：记录每个日期的导出状态，失败后从失败日期重跑。
- 脚本要能在开发环境小数据量测试，也要能在生产环境大数据量跑。最好提供一个
--limit参数，开发调试时只查几百行验证格式，生产跑批不传。
5.2 后续可以扩展的方向
如果数据量持续增长，导出会成为瓶颈。可以考虑：
- 引入消息队列，把导出任务异步化，用户在页面发起导出后，后台执行完成再通知下载。
- 数据量特别大时，改用CSV分片加编码压缩，CSV可以用标准库快速流式写出，配合
gzip压缩，速度和体积都比Excel有优势。但最终是否能用CSV，取决于下游是否接受这种格式。 - 如果下游只是查数，不一定要导Excel。给一个只读的查询平台或BI看板，可能比“导出-清洗-再分析”更符合实际需求。技术方案的让步往往是为了解决真正的业务问题，而不是把导出工具做到极致。
5.3 最后分享一个小技巧
如果数据库表字段很多，别在代码里手写列名列表。我一般直接从information_schema读取字段名，生成columns配置，再和业务方确认排序列。这样可以避免“字段增删之后脚本没同步，导出结果和表结构对不上”的低级错误。尤其是频繁变动的业务表，这个方法能省掉很多麻烦。
总的来说，批量导出数据库数据至Excel不是写几十行代码就结束的小事，而是一个要综合考虑数据量、内存、文件大小、任务调度和下游使用体验的工程问题。希望这篇文章里的思路和代码能帮你少踩几个坑。如果你们的导出场景有更特殊的需求，也可以在评论区一起交流。
