Python+pandas：MySQL大数据量批量导出Excel自动化方案

直接开工。这篇东西不是写给“想学 Python”的人看的，而是写给那些手里已经有一堆数据库、老板每周一要报表、每次手动导出到想摔键盘的人。我先把丑话说在前面：如果你只想导一次数据，直接用 Navicat 或者 DBeaver 点导出就行，真没必要写脚本。但如果你每个月、每星期、甚至每天都要导，而且导完还要做格式调整、多表合并、发给不同的人，那你花一个下午把脚本写好，后面能省下你几十个下午。

我这次就用一个真实场景来拆解：某业务系统的订单表、用户表和明细表，总计几百万行数据，需要定期批量导出成多个 Excel 文件，每个文件还要按业务类型拆分成不同 sheet。整个过程涉及环境准备、方案选型、代码实现、性能优化和坑点排查，我把每一步都记录下来，包括我踩过的坑。

1. 需求分析与整体方案设计

1.1 这个项目到底在解决什么问题

先别急着写代码，把需求看清楚。大多数人说“导出数据到 Excel”，其实背后藏着三种完全不同的需求，对应的方案也完全不同：

第一种是“一次性导出”，就是某个临时需求，导一次就完事了。这种我建议你直接用数据库客户端，比如 Navicat、DBeaver、DataGrip，鼠标点几下，导出 Excel 或 CSV，五分钟搞定，没必要动用 Python。

第二种是“定期重复导出”，比如每周一导出上周的订单数据，或者每天凌晨导出前一天的用户明细。这种需求重复性高、格式要求固定，一旦手动操作，迟早会出错——今天忘了加过滤条件，明天忘了改文件名，后天格式不对被业务部门吐槽。这种场景适合写脚本。

第三种是“加工后导出”，不是简单地把数据库表原样导出来，而是要做聚合、关联、清洗、透视，然后把结果输出成 Excel。这种场景数据量通常不小，而且 Excel 格式要求花哨——合并单元格、条件格式、多级表头之类的。这种才是 Python 的主场，因为 pandas 做数据处理太方便了，配合 openpyxl 或 xlsxwriter 能做各种精细控制。

我这次处理的场景属于第二种加第三种：数据库里有订单主表、订单明细表、用户信息表三张表，需要按月份拆分导出订单数据，每个月份的 Excel 文件里包含“订单汇总”和“明细数据”两个 sheet，而且明细数据的列顺序、表头名称、日期格式都有明确要求。

1.2 方案对比：为什么选 Python 而不是其他工具

很多人问：Excel 本身就能连接数据库，Power Query 也能做，为什么还要用 Python？

我承认，如果你的数据量在十万行以下，Excel 的 Power Query 确实能搞定。但这里有几个实际痛点：

第一，Excel 的行数上限是 1048576 行，超过这个数就无能为力了。我这次要导出的订单明细表有三百多万行，单表就超过 Excel 上限，必须拆分。

第二，Excel 连接数据库做查询，在大数据量场景下非常慢，而且容易卡死。有一次我用 Power Query 拉一张 80 万行的表，等了十分钟还没出来，最后直接没响应了。

第三，多个表关联、清洗、聚合这些操作，用 Excel 做起来其实很痛苦。你用 VLOOKUP 关联两张大表试试，文件直接卡成狗。

第四，这是最重要的一点——可重复性。脚本写好了，以后双击就能跑，跑完自动发邮件给需要的人，全程不需要人工参与。你用 Excel 手动操作，每次都要重复一遍，而且人总会犯错。

所以在这个场景下，Python 加 pandas 是性价比最高的方案。pandas 的 read_sql 能直接从数据库查询结果构建 DataFrame，to_excel 能直接输出 Excel 文件，中间的数据清洗、聚合、列重命名都顺手做了，一套流程下来代码不过几十行。

1.3 整体流程拆解

我拿到这个需求之后，第一步不是写代码，而是把整个流程在脑子里过了一遍：

数据库连接 → 查询数据 → 数据处理（重命名、格式化、排序） → 按月份分组 → 每个月份生成一个 Excel 文件 → 每个文件里写入多个 sheet → 格式美化（列宽、表头加粗、冻结首行） → 输出到指定目录。

这个流程看起来简单，但每个环节都有细节。比如数据库连接用什么库，pymysql 还是 SQLAlchemy？数据量大的时候怎么避免内存爆炸？Excel 写入用 openpyxl 还是 xlsxwriter？日期格式怎么处理才不会变成一串数字？列宽怎么根据内容自动调整？

这些我后面会一个个详细拆解。先把最核心的技术选型说清楚。

2. 技术选型与准备工作

2.1 数据库连接方案

Python 连接数据库的方案有很多，我按照适用场景排个序：

• SQLAlchemy：最推荐，尤其是配合 pandas 使用的时候。它的 create_engine 创建连接池，pandas 的 read_sql 直接接收 engine 作为参数，省去很多连接管理的麻烦。而且它可以兼容 MySQL、PostgreSQL、SQLite 等多种数据库，以后换库不用改代码。
• pymysql：纯 Python 实现的 MySQL 客户端库，轻量直接，适合简单的查询。缺点是手动管理连接，用完了要记得 close。
• psycopg2：PostgreSQL 专用，性能好，但只针对 PG。
• sqlite3：Python 内置，无需安装，适合本地小规模数据处理，但性能和并发能力有限。

我这次用的是 MySQL 数据库，所以选择的组合是 SQLAlchemy + pymysql。SQLAlchemy 作为 ORM 框架层，负责连接管理和方言转换，pymysql 作为底层的数据库驱动。

安装命令：

bash复制pip install sqlalchemy pymysql pandas openpyxl

这里解释一下为什么需要 openpyxl。pandas 的 to_excel 方法本身不直接写 Excel 文件，它需要依赖一个引擎。openpyxl 是处理 xlsx 格式的库，xlsxwriter 也能做，两者都能用，但我建议用 openpyxl，因为它在读取和修改现有 Excel 文件方面更强，而且和 pandas 的兼容性最稳定。

2.2 Excel 写入引擎的选择

这里有个很重要的细节：pandas 写入 Excel 有两个引擎可选——openpyxl 和 xlsxwriter。很多人不知道它们有什么区别，随便选一个，结果遇到限制才回头换。

openpyxl：功能全面，支持读写，可以打开现有的 Excel 文件进行修改，支持图表、图片、条件格式等复杂功能。缺点是写入大文件时速度偏慢，内存占用也稍高。

xlsxwriter：专注于写入，速度更快，尤其适合大数据量写入，而且支持很多 Excel 高级格式——比如条件格式、数据验证、图表等。缺点是不能读取现有的文件，只能在代码里创建新文件。

我的建议是：简单场景用 openpyxl，大数据量优先考虑 xlsxwriter。如果你还要在导出的文件基础上做二次处理，比如读取某个已有 Excel 文件往里追加数据，那只能用 openpyxl。

2.3 环境准备与版本确认

开始写代码之前，先确认环境。我用的 Python 版本是 3.10，pandas 版本 2.0 以上。这里要特别提醒一句：pandas 2.x 和 1.x 在某些 API 上不完全兼容，尤其是 to_excel 的引擎参数和数据类型推断逻辑。如果你用的是旧版 pandas，建议升级到最新版本：

bash复制pip install --upgrade pandas

另外，如果你要连接的 MySQL 是 8.0 以上版本，注意 pymysql 和 cryptography 的版本兼容问题。MySQL 8.0 默认的认证插件是 caching_sha2_password，老版本的 pymysql 可能连不上。解决办法是安装最新版 pymysql：

bash复制pip install --upgrade pymysql cryptography

这个坑我踩过，当时报错 RuntimeError: 'cryptography' package is required for sha256_password or caching_sha2_password auth methods，装的 cryptography 就好了。

3. 核心代码实现与实操拆解

3.1 数据库连接与查询封装

先写一个数据库连接的工具函数。用 SQLAlchemy 的方式：

python复制from sqlalchemy import create_engine
import pandas as pd

def get_engine():
    """创建数据库连接引擎"""
    host = '192.168.1.100'
    port = 3306
    user = 'readonly_user'
    password = 'your_password'
    database = 'business_db'
    
    # 创建连接引擎，pool_pre_ping 可以自动检测连接是否有效
    engine = create_engine(
        f'mysql+pymysql://{user}:{password}@{host}:{port}/{database}?charset=utf8mb4',
        pool_size=5,
        pool_recycle=3600,
        pool_pre_ping=True,
        echo=False
    )
    return engine

几个参数的说明：

• pool_size=5：连接池大小，避免每次查询都重新建立连接，提高复用效率。
• pool_recycle=3600：连接回收时间，MySQL 默认的 wait_timeout 是 8 小时，如果连接空闲超过这个时间，会被服务端断开，下次使用时就报错。设置 pool_recycle 可以定期回收重建连接。
• pool_pre_ping=True：每次从连接池取连接之前，先 ping 一下确认连接是否有效，无效则重连。这个参数在数据库重启或者网络不稳定时特别好用。
• charset=utf8mb4：一定要用 utf8mb4 而不是 utf8，否则 emoji 和生僻字会变成问号。

3.2 查询数据并构建 DataFrame

查询的时候要注意，不要一股脑地把几百万行全塞进内存。pandas 的 read_sql 虽然方便，但大数据量直接读内存，很容易 OOM（内存溢出）。我先分析这个需求：订单明细表三百多万行，单行大概 200 字节，总数据量约 600MB 左右，普通 16GB 内存的电脑勉强能扛住，但加上 DataFrame 的索引开销和中间处理过程，风险不小。

我的方案是分批次查询：按月份拆分，每个月的数据单独查询、单独处理、单独写入文件。这样每个批次的数据量最多也就三四十万行，内存压力小很多，而且正好契合“按月导出”的业务需求。

python复制def export_monthly_data(engine, year, month):
    """导出指定年月的订单数据"""
    
    start_date = f'{year}-{month:02d}-01'
    if month == 12:
        end_date = f'{year + 1}-01-01'
    else:
        end_date = f'{year}-{month + 1:02d}-01'
    
    sql = f"""
        SELECT 
            o.order_id,
            o.order_no,
            o.user_id,
            u.user_name,
            u.user_phone,
            o.product_id,
            p.product_name,
            o.quantity,
            o.unit_price,
            o.total_amount,
            o.order_status,
            o.created_at
        FROM orders o
        LEFT JOIN users u ON o.user_id = u.id
        LEFT JOIN products p ON o.product_id = p.id
        WHERE o.created_at >= '{start_date}'
          AND o.created_at < '{end_date}'
        ORDER BY o.created_at
    """
    
    df = pd.read_sql(sql, engine)
    return df

这里有几个要点：

SQL 里尽量做 JOIN，不要全部查出来再用 pandas 合并。 数据库是专门为关联查询优化的，把 JOIN 逻辑放在 SQL 里，效率远高于把数据拉回内存后用 pandas 的 merge 操作。而且这样代码更简洁，不需要处理重复列的问题。

WHERE 条件里时间范围用 >= 和 <，不要用 BETWEEN。 这是为了避免边界问题：如果某条记录的 created_at 恰好是 2024-04-01 00:00:00 或者 2024-04-30 23:59:59，BETWEEN 的闭合区间可能导致数据重复或遗漏。用左闭右开区间（>= start AND < end）是最保险的。

ORDER BY 尽量加上。 导出的数据顺序如果不固定，每次跑出来的 Excel 内容顺序都不一样，业务人员会怀疑数据是不是有问题。按时间排序是最合理的默认排序方式，如果业务还需要按订单金额排序再处理，可以在 pandas 里再做 sort_values。

3.3 数据清洗与格式处理

数据从数据库查出来之后，不能直接写进 Excel。原始数据通常有这些问题：

字段名是英文的，业务方要中文表头。 直接写个映射关系，用 pandas 的 rename 统一处理：

python复制column_mapping = {
    'order_id': '订单ID',
    'order_no': '订单编号',
    'user_id': '用户ID',
    'user_name': '用户姓名',
    'user_phone': '手机号',
    'product_id': '商品ID',
    'product_name': '商品名称',
    'quantity': '数量',
    'unit_price': '单价',
    'total_amount': '订单金额',
    'order_status': '订单状态',
    'created_at': '下单时间'
}

df.rename(columns=column_mapping, inplace=True)

手机号、订单号这类字段在 Excel 里会变成科学计数法。 这是最常见的坑。Excel 对超过 11 位的数字会默认显示为科学计数法，比如 13812345678 会变成 1.38123E+10，用户 ID 如果是雪花算法生成的长整型，会更夸张。解决办法是在写入 Excel 时把这一列的数据格式设置为文本，或者直接在数据里转换成字符串并在单元格格式中设置。

我强烈建议：凡是长度超过 11 位的数字字段，在导出前统一转为字符串。 这样一来，Excel 虽然还是可能会显示为科学计数法，但至少不会丢失精度。要彻底解决显示问题，需要在写入 Excel 时对列设置文本格式。

时间字段的格式处理。 pandas 从数据库读取 datetime 类型时，默认得到的是 Timestamp 对象，直接写入 Excel 时，如果格式不对，显示会是一串数字（Excel 内部的时间序列值）。需要手动格式化为字符串：

python复制df['下单时间'] = pd.to_datetime(df['下单时间']).dt.strftime('%Y-%m-%d %H:%M:%S')

空值处理。 数据库里的 NULL 在 pandas 里会变成 NaN，写入 Excel 后显示为 NaN，非常难看。可以选择填充为 '' 空字符串，或者保留 NaN 再在 Excel 里做特殊格式化：

python复制# 把空值替换为空字符串
df = df.fillna('')

但要注意：如果某列是数值列，填充为字符串后整列都会变成字符串类型，可能导致后续计算出问题。所以这个操作建议在最终写入 Excel 前再执行。

3.4 多 sheet 写入 Excel

现在到了最核心的部分：把处理好的 DataFrame 写入 Excel 文件。需求要求每个月份的 Excel 文件里包含“订单汇总”和“明细数据”两个 sheet。

订单汇总 sheet 的数据源是明细按商品维度的聚合结果：

python复制def generate_summary(df):
    """根据明细数据生成汇总数据"""
    summary = df.groupby('商品名称').agg(
        订单数量=('订单ID', 'count'),
        销售总量=('数量', 'sum'),
        销售总额=('订单金额', 'sum')
    ).reset_index()
    summary['销售总额'] = summary['销售总额'].round(2)
    return summary

然后写入 Excel：

python复制def write_to_excel(df_detail, df_summary, filepath):
    """把明细和汇总写入同一个 Excel 文件的两个 sheet"""
    with pd.ExcelWriter(filepath, engine='openpyxl') as writer:
        df_summary.to_excel(writer, sheet_name='订单汇总', index=False)
        df_detail.to_excel(writer, sheet_name='明细数据', index=False)

这里 pd.ExcelWriter 是 pandas 写入 Excel 的统一入口，可以同时写多个 sheet，而且是在一个文件里完成多个 sheet 的写入。注意 index=False，这个参数一定不要忘记，否则 DataFrame 的索引列会莫名其妙写进 Excel 里。

3.5 Excel 格式美化

数据写完只是第一步。业务方拿到手之后，打开一看，列宽乱七八糟、表头没有加粗、日期格式不对，马上就会有人来找你。所以格式美化不是加分项，是必须项。

用 openpyxl 对 Excel 做后置格式化：

python复制from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter

def format_excel_file(filepath, sheet_names, column_widths_map):
    """美化 Excel 文件格式"""
    wb = load_workbook(filepath)
    
    for sheet_name in sheet_names:
        ws = wb[sheet_name]
        
        # 表头加粗 + 背景色 + 居中
        for cell in ws[1]:
            cell.font = Font(bold=True, size=11)
            cell.fill = PatternFill(start_color='D9E1F2', end_color='D9E1F2', fill_type='solid')
            cell.alignment = Alignment(horizontal='center', vertical='center')
        
        # 设置列宽
        if sheet_name in column_widths_map:
            for col_idx, width in column_widths_map[sheet_name].items():
                col_letter = get_column_letter(col_idx)
                ws.column_dimensions[col_letter].width = width
        
        # 冻结首行
        ws.freeze_panes = 'A2'
    
    wb.save(filepath)

这里有几个细节值得展开：

表头样式。 加粗是第一优先级，背景色其次。我用的浅蓝色背景 D9E1F2 是业务方指定的颜色，你可以根据自己公司的品牌色调整。样式统一之后，Excel 文件的专业感会明显提升。

列宽设置。 这是一个取舍问题。如果列宽设为固定值，内容多的列会显示不全，查看不方便。如果根据内容长度自动计算，每列都要扫描一遍数据，数据量大时开销不小。我采用的方法是：文本类型的数据列按“列头长度和内容最大长度的较大值”计算，最高不超过 50；数值列统一用 15。

python复制def calc_column_widths(df):
    """根据 DataFrame 内容计算列宽"""
    widths = {}
    for idx, col in enumerate(df.columns, start=1):
        # 取列头长度和内容最大长度的较大值
        max_len = len(str(col)) * 2  # 中文字符占两个英文字符宽度
        for value in df[col].astype(str).head(500):  # 只抽样前500行计算
            length = len(value)
            if length > max_len:
                max_len = length
        # 限制最大宽度，防止超长文本把表格撑爆
        widths[idx] = min(max_len + 2, 50)
    return widths

抽样 500 行算列宽是我实测过的经验值：全部扫描几百万行太慢，只扫描前面一部分基本够用，因为数据的长度分布差异不会太大。如果你的数据里偶尔有超长文本，可以多抽一些行。

冻结窗格。 freeze_panes = 'A2' 表示冻结第一行，滚动表格时表头始终可见。这是最基本的操作，必须加。如果表头是两行的，就写 'A3'。

3.6 批量循环导出

核心函数都写好了，最后就是按月份循环调用：

python复制def main():
    engine = get_engine()
    
    # 要导出的月份范围
    export_months = [(2024, 1), (2024, 2), (2024, 3), (2024, 4)]
    
    for year, month in export_months:
        print(f'正在导出 {year}-{month:02d} 数据...')
        
        # 查询明细数据
        df_detail = export_monthly_data(engine, year, month)
        
        if df_detail.empty:
            print(f'{year}-{month:02d} 无数据，跳过')
            continue
        
        # 生成汇总数据
        df_summary = generate_summary(df_detail)
        
        # 数据清洗
        df_detail['下单时间'] = pd.to_datetime(df_detail['下单时间']).dt.strftime('%Y-%m-%d %H:%M:%S')
        df_detail = df_detail.fillna('')
        
        # 列名转中文
        df_detail.rename(columns=column_mapping, inplace=True)
        df_summary.rename(columns={'商品名称': '商品名称'}, inplace=True)  # 汇总已用中文列名
        
        # 计算列宽
        detail_widths = calc_column_widths(df_detail)
        summary_widths = calc_column_widths(df_summary)
        
        # 写入文件
        filepath = f'output/订单数据_{year}{month:02d}.xlsx'
        write_to_excel(df_detail, df_summary, filepath)
        
        # 格式美化
        format_excel_file(
            filepath, 
            ['订单汇总', '明细数据'],
            {'订单汇总': summary_widths, '明细数据': detail_widths}
        )
        
        print(f'完成: {filepath}')
    
    print('全部导出完成')

if __name__ == '__main__':
    main()

这个逻辑已经很完整了。但真正的生产环境里，你还会遇到数据量大到内存扛不住的情况，这也是很多人用 Python 导数据时翻车最多的地方。下一节专门说这个问题。

4. 大数据量导出的性能优化与避坑指南

4.1 分页查询与逐批写入

如果单个月份的数据量特别大——比如遇到大促月份，订单量突破 200 万，直接 read_sql 全部查出来再一次性 to_excel，内存瞬间爆炸，进程直接被杀掉。

解决思路是分页查询 + 逐批写入 + 定时清理内存。代码层面的实现：

python复制def export_large_table_by_batch(engine, sql, filepath, batch_size=50000):
    """大数据量分批导出到 Excel"""
    
    offset = 0
    first_batch = True
    
    while True:
        batch_sql = f"{sql} LIMIT {batch_size} OFFSET {offset}"
        df = pd.read_sql(batch_sql, engine)
        
        if df.empty:
            break
        
        # 数据处理（列名转换、日期格式化、空值处理）
        df = process_dataframe(df)
        
        # 写入 Excel，第一个批次用写入模式，后续批次用追加模式
        if first_batch:
            df.to_excel(filepath, index=False, sheet_name='明细数据')
            first_batch = False
        else:
            with pd.ExcelWriter(filepath, engine='openpyxl', mode='a') as writer:
                df.to_excel(writer, sheet_name='明细数据', index=False)
        
        offset += batch_size
        del df  # 释放内存
        
        import gc
        gc.collect()
    
    # 重新读取并美化格式
    format_excel_file(filepath, ['明细数据'], None)

这个方案有几个注意点：

不要用 OFFSET 做深分页。 当偏移量特别大时，比如第 500 万条，MySQL 还是要扫描前 4999999 条才能定位到目标，性能会急剧下降。更优的方案是“基于上次最大 ID 的分页”：

sql复制WHERE id > {last_max_id} ORDER BY id LIMIT {batch_size}

但前提是表中有一个单调递增的主键列。如果没有，就用时间字段做过滤条件，每次查询 WHERE created_at >= '2024-04-01' AND created_at < '2024-04-02' 这种更细粒度的拆分。

mode='a' 追加模式只在文件存在时扩展 sheet。 如果 sheet 已经存在，会报错或者追加到已有 sheet 末尾。我的代码里第一个批次用 to_excel 直接创建文件，后续批次用 ExcelWriter 追加模式。但这里有个坑：用追加模式时 sheet 名如果相同，默认会追加到已有 sheet 的末尾，而不是覆盖——这其实正好符合我们按批次追加数据的需求。然而如果中间某一步出错了，文件里可能残留之前批次的数据，重新运行时会重复。所以建议先删掉旧的输出文件再跑。

del df 和 gc.collect() 真的有用吗？ 有用，但作用有限。pandas 的内存释放取决于 Python 的垃圾回收机制，del 只是减少引用计数，真正的内存释放还是得等垃圾回收。gc.collect() 是手动触发回收，但有时 DataFrame 内部引用了大量内存块，释放不彻底。最有效的方式是确保每个 batch 处理完后当前作用域里的 DataFrame 不被后续操作引用，然后让循环继续迭代。

4.2 CSV 作为 Excel 的替代方案

如果你的数据量实在太大，比如单表几千万行，就不要死磕 xlsx 格式了，Ultimate 的方案是直接导出 CSV。

CSV 本质是纯文本文件，写入速度比 Excel 快很多倍，文件体积也更小。Excel 也能直接打开 CSV（注意中文乱码问题，需要带 BOM 的 UTF-8 编码）。

python复制df.to_csv(filepath, index=False, encoding='utf-8-sig')

utf-8-sig 是带 BOM 的 UTF-8，Excel 打开时能正确识别中文。不加 BOM，Excel 默认用 ANSI 编码解析，中文会显示成乱码。

这是一个权衡：如果业务方必须要 .xlsx 格式，那没办法，只能分批写入。如果业务方只要数据，不在乎格式，CSV 是更好的选择。我能给的建议是：先问业务方的真实需求，不要自己默认一定用 xlsx，很多时候“Excel 文件”的要求只是习惯性的，你给他们 CSV，他们也能接受。

4.3 数据库查询性能优化

导出慢，很多时候不是 Python 的锅，是 SQL 查询本身太慢。数据量大时尤其明显。

三个实测有效的优化手段：

*第一，只查需要的列，不要 SELECT 。 很多人习惯性写 SELECT *，把不需要的大字段（比如备注、JSON 字段）也查出来，白白增加网络传输和内存消耗。这次导出的订单表里有一个 5000 字的备注字段，完全不必要导出，去掉后查询时间减少了一半。

第二，在 WHERE 条件的字段上建索引。 如果表已经建了很多索引，再加索引会影响写入性能，这是 DBA 会考虑的事。如果是你自己建的临时表或者小规模业务表，在 created_at 上建索引，查询效率提升是数量级的。

第三，避免在 WHERE 条件里对索引字段做函数运算。 比如 WHERE DATE(created_at) = '2024-04-01'，这种写法会导致索引失效，全表扫描。正确做法是 WHERE created_at >= '2024-04-01' AND created_at < '2024-04-02'。

4.4 连接池配置与超时处理

大数据量导出时，查询时间长是正常的。但 MySQL 的 wait_timeout 默认是 8 小时，如果你单次查询超过这个时间，连接会被服务端断开。虽然我设置过 pool_recycle=3600，但更保险的做法是在 SQLAlchemy 引擎里设置 connect_args：

python复制engine = create_engine(
    f'mysql+pymysql://{user}:{password}@{host}:{port}/{database}?charset=utf8mb4',
    connect_args={
        'connect_timeout': 30,
        'read_timeout': 3600,
        'write_timeout': 3600,
    }
)

read_timeout 设置成 3600 秒，保证即使是执行时间很长的查询也不会被客户端主动断开。

另外一个值得注意的点：如果导出任务要跑很久，而你的代码是在服务器上跑的话（比如 Linux 服务器里的 cron 定时任务），要确保脚本是在后台运行的，不会因为 SSH 断开而被终止。用 nohup 或者直接用系统服务的方式。

5. 常见问题与排查技巧实录

5.1 中文乱码与编码问题

中文乱码是导出 Excel 里最常被业务方吐槽的问题。乱码有两种：

读取阶段的乱码。 连接 MySQL 时没有设置 charset=utf8mb4，导致读出来的中文直接是乱码。这个检查一下 get_engine 里的连接串就行。

写入阶段的乱码。 写入 Excel 文件时编码设置不对。xlsx 格式本身是基于 XML 的，内部统一用 UTF-8 编码，所以正常情况下不会乱码。如果出现了乱码，大概率是数据在读取阶段就已经是乱码了，要回到数据库连接去查。

排查乱码问题有一个通用思路：打印几行原始数据看看是否是乱码。如果读出来就乱，改连接；如果读出来正常写入后乱，考虑 openpyxl 版本问题或者 Excel 打开时的编码判断。

5.2 数字变成科学计数法

手机号变成 1.38123E+10，长订单号变成 1.23457E+17。这是 Excel 的默认行为：数字太长就自动转科学计数法，而且会丢失精度。

解决方案有两种：

第一种，应用代码层面：把这些长数字列在写入 Excel 前转成字符串，然后对 Excel 列设置文本格式。

python复制# 方法1：写 Excel 的时候用 openpyxl 的单元格格式
from openpyxl.styles import numbers

def write_with_text_format(writer, df, sheet_name, text_columns):
    df.to_excel(writer, sheet_name=sheet_name, index=False)
    ws = writer.sheets[sheet_name]
    for col_name in text_columns:
        col_idx = df.columns.get_loc(col_name) + 1
        for row in range(2, len(df) + 2):
            ws.cell(row=row, column=col_idx).number_format = '@'  # '@' 表示文本格式

第二种，数据库层面：查询时直接用 CAST(phone AS CHAR) 把列转成字符串。

我推荐第二种，因为从源头解决更可靠，而且应用的代码不需要额外做类型判断。

5.3 时间字段在 Excel 里显示为数字

Excel 内部存储日期的本质是浮点数，显示什么格式取决于单元格的数字格式。pandas 写入的 datetime 如果没有显式设置格式，Excel 有时会显示成一个奇怪的数字。

解决办法是提前格式化字符串：

python复制df['created_at'] = pd.to_datetime(df['created_at']).dt.strftime('%Y-%m-%d %H:%M:%S')

或者写入之后再设置单元格格式：

python复制ws.column_dimensions['H'].number_format = 'YYYY-MM-DD HH:MM:SS'

我更推荐前一种，因为字符串格式写死在数据里，无论谁打开 Excel 显示都一样，不会因为设备或 Excel 版本不同而出现差异。

5.4 to_excel 时报错 "Excel file format cannot be distinguished"

这个报错通常发生在打开文件做后续修改时，原因是文件不是真正的 xlsx 格式，或者 openpyxl 无法识别。常见场景：文件后缀是 .xlsx，但内部实际是 HTML 表格或者 CSV 文本。

我用 load_workbook 打开一个文件时报过这个错，排查了半天发现是之前用 df.to_csv(filepath, encoding='utf-8-sig') 生成的文件，只是把后缀改成了 .xlsx，内部根本不是 Excel 格式。业务方拿到这个文件打开也会提示格式不正确。所以写入时的引擎和实际文件格式一定要匹配，不要玩“改后缀”这种小聪明。

5.5 内存溢出与进程卡死

数据量大时最常见的问题，我已经在前面专门讲过分页方案。再补充一个实际经验：如果数据库服务器内存有限，不要在数据库层面把整个结果集查出来再传输，尽量用流式查询（服务端游标方式）。pymysql 支持 SSCursor（流式游标）：

python复制import pymysql
from pymysql.cursors import SSCursor

conn = pymysql.connect(
    host='...',
    user='...',
    password='...',
    database='...',
    cursorclass=SSCursor
)

这样查询结果会留在数据库服务端，Python 每次只取一批数据，内存占用非常稳定。但要注意：使用 SSCursor 后，同一个连接上不能立刻执行其他查询，必须等当前游标的数据全部消费完才行，否则会报错。处理大数据量还是建议连接池 + 分页查询的组合。

5.6 导出的 Excel 打开后提示“文件已损坏”

这个坑我印象很深。有一次脚本跑完，文件生成成功了，但用户双击打开时提示“文件已损坏，是否尝试修复”。

排查后发现原因：写入 Excel 的断点续传逻辑有问题——多个 sheet 同时写入时，文件被重复打开和保存，导致数据不完整。另一个常见场景是代码运行过程中抛异常中断了，Pandas 已经创建了部分文件，但没有完整写入尾部信息。Excel 对不完整的文件会直接报损坏。

解决方案很简单：用临时文件写入，全部写入成功后再重命名。这本质上是个原子操作思想——不把中间状态直接暴露给使用方。

python复制import os
import tempfile

def safe_write_excel(df_list, sheet_names, final_path):
    """先写临时文件，成功后重命名"""
    tmp_path = final_path + '.tmp'
    
    with pd.ExcelWriter(tmp_path, engine='openpyxl') as writer:
        for df, sheet_name in zip(df_list, sheet_names):
            df.to_excel(writer, sheet_name=sheet_name, index=False)
    
    os.replace(tmp_path, final_path)

os.replace 在 Linux 和 Windows 上都是原子操作，不会出现在替换过程中被读到半个文件的情况。

6. 定时任务与自动化扩展

6.1 定时执行方案

脚本写好了，怎么让它自动跑？我推荐 cron（Linux/macOS）和“任务计划程序”（Windows）。

Linux 的 crontab 配置示例，每天凌晨 2 点执行：

bash复制0 2 * * * cd /path/to/project && /usr/bin/python3 export_script.py >> logs/export_$(date +\%Y\%m\%d).log 2>&1

这里我踩过几个坑：

cron 的环境变量和手动执行时不一样。 Python 路径要用绝对路径，因为 cron 的 PATH 很精简，直接写 python3 可能找不到。数据库连接配置里如果用到环境变量，也要设置完整。

日志一定要重定向到文件。 脚本运行过程中如果出错，你不会在终端看到报错，只能看日志。之前我有个同事没有配日志，脚本出错了三天没人知道，直到业务方来问数据呢才发现。

cron 里 % 需要转义成 \%。 这在 crontab 里是个特殊字符，不加转义会解析失败。

Windows 上的配置大同小异，打开“任务计划程序”，新建任务，触发器设置每天凌晨 2 点，操作指向 Python 解释器和脚本路径即可。

6.2 导出后自动发送邮件

导出完文件之后，通常还要发给相关人员。用 Python 发邮件不算复杂，但注意不要泄露数据库密码或敏感信息。下面是一个简化示例，用 yagmail 库可以大幅减少代码量，但如果你不想装额外的库，用标准库 smtplib 也行。

这里更推荐的方式是：先发到内部共享网盘或者对象存储，再在邮件里附上链接，而不是把 Excel 文件作为附件。因为 Excel 附件动辄几十 MB，企业邮箱根本发不出去。

6.3 SQL 查询结果缓存

如果同一份报表每天导出的数据变化不大，可以考虑把前一天的结果缓存起来，今天只查增量部分。但这个方案的复杂度不是线性增加的，需要维护增量同步的逻辑。对大多数中小业务来说，全量导出一天跑一次完全够用，没必要做增量。

实话说，能坚持每天完整导一次已经很不错了。

7. 一些补充的经验碎碎念

写到这里，主体内容其实已经覆盖了从需求分析、代码实现到性能优化和问题排查的完整链路。最后聊几个我在实际项目中总结的小经验，不展开，都是直接的结论。

经验一：业务方说“导出 Excel”，你要先问清楚三件事——多少行数据、什么格式、多久导一次。 这三个问题直接决定了方案走向。如果是 5000 行的数据，用 pandas 就是大材小用，直接 SQL 导出 CSV 或者让业务方用 BI 工具自己拉就行。如果是 5000 万行，那你要想清楚 Excel 根本装不下，可能得考虑 CSV 分卷或者数据库直连分析方案。

经验二：凡是超过 11 位的数字字段，默认在导出前一天内先转成字符串。 这能避免 90% 以上的 Excel 显示问题。

经验三：加格式美化的时候，不要太花哨。 我见过有人把表头做成彩虹色、条件格式搞了十几种颜色规则，结果文件打开一次要卡半分钟，业务方直接嫌弃。适度加粗表头、自动列宽、冻结首行，这三点做到位，文件就已经很专业了。其他花里胡哨的，不加。

经验四：别在生产环境直接跑你的开发脚本。 先拿一小段数据验证逻辑，把列名映射、日期格式、空值处理全确认好，再在大数据集上跑。数据量大了之后调试成本是指数级上升的，别问我为什么知道。

经验五：导出的文件路径要规范，文件名要带上日期。 我见过最崩溃的情况是所有人都在导出“订单数据.xlsx”，最后根本分不清哪个是最新的。文件名里带上日期和业务范围，比如 订单数据_20240401_20240430.xlsx，这是最小的成本换取最大的清晰度。

如果你还有类似的定时导出需求，但数据量更大、表更复杂、或者需要对接其他系统，完全可以在这个脚本基础上继续扩展。核心思路不变：数据库连接 → 查询处理 → 分批写入 → 格式美化 → 自动化调度。把这五步吃透，以后遇到再复杂的导出需求，你都能一眼看清该怎么拆解。