1. 项目背景与需求分析
在日常数据处理工作中,我们经常需要将数据库中的大量数据导出到Excel文件进行后续分析或报表制作。手动操作不仅效率低下,而且容易出错。Python作为数据处理领域的利器,结合其丰富的数据库连接库和Excel操作库,可以完美解决这个问题。
我最近接手了一个需要从MySQL数据库导出50万条销售记录到Excel的任务,通过Python脚本实现了自动化处理。整个过程从最初的单表导出,逐步完善到支持多表关联查询、字段映射、分批次导出等功能,最终形成了一套稳定的解决方案。
需要模型API调用? 免费领10W Token,多模型网关一键接入 Claude、DeepSeek 等主流模型。
2. 技术选型与工具准备
2.1 核心库选择
对于数据库操作,我选择了SQLAlchemy作为ORM工具,它提供了统一的API来操作不同类型的数据库。相比直接使用各数据库的专用驱动,SQLAlchemy的抽象层让代码更具可移植性。
python复制# 安装必要库
pip install sqlalchemy openpyxl pandas
对于Excel操作,openpyxl是处理xlsx格式的最佳选择。它支持Excel 2010及以上版本的所有功能,包括公式、图表、样式等。相比xlrd/xlwt,openpyxl对现代Excel文件的支持更完善。
2.2 数据库连接配置
我创建了一个通用的数据库连接管理器,支持多种数据库类型:
python复制from sqlalchemy import create_engine
def get_db_engine(db_type, host, port, user, password, database):
if db_type == 'mysql':
conn_str = f"mysql+pymysql://{user}:{password}@{host}:{port}/{database}"
elif db_type == 'postgresql':
conn_str = f"postgresql://{user}:{password}@{host}:{port}/{database}"
else:
raise ValueError("Unsupported database type")
return create_engine(conn_str, pool_recycle=3600)
注意:生产环境中建议将数据库凭证存储在环境变量或配置文件中,不要硬编码在脚本里。
3. 核心实现逻辑
3.1 基础导出功能
最基本的导出功能只需要几行代码:
python复制import pandas as pd
def export_to_excel_basic(engine, sql, output_file):
df = pd.read_sql(sql, engine)
df.to_excel(output_file, index=False)
这个简单版本适合小数据量导出,但对于大数据集会有内存问题。我测试导出10万条记录时,内存占用就达到了1GB以上。
3.2 分批次导出优化
为了解决内存问题,我实现了分批次查询和写入:
python复制def export_large_data(engine, sql, output_file, batch_size=50000):
with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
offset = 0
while True:
batch_sql = f"{sql} LIMIT {batch_size} OFFSET {offset}"
df = pd.read_sql(batch_sql, engine)
if df.empty:
break
df.to_excel(writer, sheet_name=f"Batch_{offset//batch_size + 1}",
index=False)
offset += batch_size
这个版本通过LIMIT和OFFSET实现分批查询,每批数据写入单独的工作表。测试导出100万条记录时,内存使用稳定在200MB左右。
3.3 多表关联导出
实际业务中经常需要关联多个表查询:
python复制def export_joined_tables(engine, tables, join_conditions, output_file):
select_fields = []
for table in tables:
select_fields.extend(f"{ta
