Python实现数据库数据高效导出Excel的完整方案

原创

于 2026-07-30 09:47:08 发布 · 372 阅读

1. 项目背景与需求分析

在日常数据处理工作中，我们经常需要将数据库中的大量记录导出到Excel文件进行后续分析或报表制作。手动操作不仅效率低下，而且容易出错。Python作为数据处理领域的利器，配合适当的库可以轻松实现自动化导出功能。

这个项目的核心价值在于：

实现数据库查询结果的批量导出
支持自定义导出字段和格式
自动化处理大量数据的分页导出
生成符合业务需求的Excel报表

2. 技术方案设计

2.1 技术选型

对于这个需求，我们需要以下几个核心组件：

数据库连接器 ：
- MySQL：PyMySQL/mysql-connector-python
- PostgreSQL：psycopg2
- Oracle：cx_Oracle
- SQL Server：pyodbc
Excel操作库 ：
- openpyxl：适合处理.xlsx格式
- xlwt/xlrd：处理旧版.xls格式
- pandas：高级数据处理+导出
辅助工具 ：
- SQLAlchemy：ORM框架
- configparser：配置文件读取

提示：推荐使用pandas作为核心库，因为它集成了数据分析和Excel导出功能，代码更简洁。

2.2 架构设计

整体流程分为四个阶段：

连接数据库
执行查询
数据处理
Excel导出

graph TD
    A[连接数据库] --> B[执行SQL查询]
    B --> C[获取结果集]
    C --> D[数据处理]
    D --> E[导出Excel]

3. 核心代码实现

3.1 数据库连接

import pandas as pd
from sqlalchemy import create_engine

def create_db_connection(config_file):
    """
    创建数据库连接
    :param config_file: 配置文件路径
    :return: 数据库引擎
    """
    import configparser
    config = configparser.ConfigParser()
    config.read(config_file)
    
    db_type = config.get('database', 'type')
    if db_type == 'mysql':
        conn_str = f"mysql+pymysql://{config.get('database', 'user')}:{config.get('database', 'password')}@{config.get('database', 'host')}:{config.get('database', 'port')}/{config.get('database', 'dbname')}"
    elif db_type == 'postgresql':
        conn_str = f"postgresql+psycopg2://{config.get('database', 'user')}:{config.get('database', 'password')}@{config.get('database', 'host')}:{config.get('database', 'port')}/{config.get('database', 'dbname')}"
    
    engine = create_engine(conn_str)
    return engine

3.2 批量查询与导出

def export_to_excel(engine, sql_query, output_file, chunk_size=10000):
    """
    分批次查询数据并导出到Excel
    :param engine: 数据库引擎
    :param sql_query: SQL查询语句
    :param output_file: 输出文件路径
    :param chunk_size: 每次查询的记录数
    """
    writer = pd.ExcelWriter(output_file, engine='openpyxl')
    
    # 第一次查询获取总记录数
    total_count = pd.read_sql(f"SELECT COUNT(*) as cnt FROM ({sql_query}) as t", engine).iloc[0]['cnt']
    
    # 分页处理
    for offset in range(0, total_count, chunk_size):
        chunk_query = f"{sql_query} LIMIT {chunk_size} OFFSET {offset}"
        df = pd.read_sql(chunk_query, engine)
        
        if offset == 0:
            df.to_excel(writer, index=False)
        else:
            # 追加数据到已有工作表
            workbook = writer.book
            worksheet = workbook.active
            for row in dataframe_to_rows(df, index=False, header=False):
                worksheet.append(row)
    
    writer.save()

4. 高级功能实现

4.1 多Sheet导出

def export_multiple_sheets(engine, queries, output_file):
    """
    将多个查询结果导出到同一个Excel的不同Sheet
    :param engine: 数据库引擎
    :param queries: 字典{sheet_name: sql_query}
    :param output_file: 输出文件路径
    """
    with pd.ExcelWriter(output_file) as writer:
        for sheet_name, query in queries.items():
            df = pd.read_sql(query, engine)
            df.to_excel(writer, sheet_name=sheet_name, index=False)

4.2 带格式导出

def export_with_formatting(output_file):
    """
    导出带格式的Excel文件
    """
    from openpyxl.styles import Font, Alignment, Border, Side
    
    workbook = openpyxl.load_workbook(output_file)
    worksheet = workbook.active
    
    # 设置标题行样式
    header_font = Font(bold=True, color="FFFFFF")
    header_fill = PatternFill(start_color="4F81BD", end_color="4F81BD", fill_t

标签