我的个人技术笔记

23.4 数据库与Excel导出

4 min read

核心内容

将 DataFrame 导出到 SQL 数据库（to_sql） 与 Excel 文件（to_excel）。

一、to_sql() — 导出 SQL 数据库

需要数据库连接（SQLAlchemy 引擎或 sqlite3 连接）。

import pandas as pd
from sqlalchemy import create_engine
 
df = pd.DataFrame({
    'id': [1, 2, 3],
    'name': ['Alice', 'Bob', 'Charlie'],
    'score': [95.5, 88.0, 76.25]
})
 
# 创建 SQLAlchemy 引擎
engine = create_engine('sqlite:///mydb.db')
# engine = create_engine('postgresql://user:pass@host:5432/db')
# engine = create_engine('mysql+pymysql://user:pass@host:3306/db')
 
# 基础导出
df.to_sql('students', con=engine)
 
# 常用参数
df.to_sql(
    'students',
    con=engine,
    schema='public',           # 数据库 schema
    if_exists='replace',       # 'fail' / 'replace' / 'append'
    index=False,               # 是否写索引
    index_label='id',          # 索引列名
    chunksize=1000,            # 批量写入大小
    dtype={                    # 指定列 SQL 类型
        'id': Integer,
        'name': String(50),
        'score': Float
    },
    method=None,               # 自定义写入方法
    engine='sqlalchemy'
)
 
# 追加模式
df.to_sql('students', con=engine, if_exists='append', index=False)
参数说明
name表名
conSQLAlchemy 引擎 / 连接对象
schema数据库 schema（PostgreSQL 等）
if_exists’fail’ 报错 / ‘replace’ 重建 / ‘append’ 追加
index是否写入索引
dtype字段 SQL 类型映射
chunksize批量写入块大小

sqlite3 直接使用

import sqlite3
 
conn = sqlite3.connect('mydb.db')
df.to_sql('students', conn, if_exists='replace', index=False)
conn.close()

读取验证

# 读取回 DataFrame
df_test = pd.read_sql('SELECT * FROM students', engine)

注意

  • 写 PostgreSQL / MySQL 需安装对应驱动（psycopg2 / pymysql）
  • if_exists='replace' 会先 DROP 表，注意数据安全
  • 大表建议使用 chunksize

二、to_excel() — 导出 Excel

# 基础导出
df.to_excel('output.xlsx', sheet_name='Sheet1')
 
# 常用参数
df.to_excel(
    'output.xlsx',
    sheet_name='数据',           # 工作表名
    na_rep='',                   # 缺失值表示
    float_format='%.2f',         # 浮点格式
    columns=['name', 'score'],   # 选择列
    header=True,                 # 写列名
    index=True,                  # 写索引
    index_label='id',            # 索引列名
    startrow=2,                  # 起始行（从 0 开始）
    startcol=1,                  # 起始列
    engine='openpyxl',           # 'openpyxl' / 'xlsxwriter'
    merge_cells=True,            # 合并多级索引单元格
    encoding='utf-8',
    inf_rep='inf',               # 无穷值表示
    freeze_panes=(1, 0),         # 冻结首行
    storage_options={}
)
 
# 不写索引
df.to_excel('output.xlsx', index=False)
参数说明
sheet_name工作表名
float_format浮点格式
columns导出列
startrow / startcol起始单元格位置
engine’openpyxl’ / ‘xlsxwriter’
merge_cells是否合并多级索引单元格
freeze_panes冻结窗格（如 (1, 0) 冻结首行）
inf_rep无穷值显示

ExcelWriter 多 sheet 导出

# 方法 1：with 上下文（推荐）
with pd.ExcelWriter('multi.xlsx', engine='openpyxl') as writer:
    df1.to_excel(writer, sheet_name='Sheet1', index=False)
    df2.to_excel(writer, sheet_name='Sheet2', index=False)
    df3.to_excel(writer, sheet_name='Sheet3', startrow=1)
 
# 方法 2：显式创建
writer = pd.ExcelWriter('multi.xlsx')
df1.to_excel(writer, sheet_name='Sheet1')
df2.to_excel(writer, sheet_name='Sheet2')
writer.close()   # 或 writer.save()

ExcelWriter 高级参数

with pd.ExcelWriter(
    'styled.xlsx',
    engine='xlsxwriter',
    date_format='yyyy-mm-dd',
    datetime_format='yyyy-mm-dd hh:mm:ss',
    mode='a',                    # 'w' 覆盖 / 'a' 追加
    if_sheet_exists='replace'    # 追加模式下遇到同名 sheet
) as writer:
    df.to_excel(writer, sheet_name='数据')
    
    # 通过 xlsxwriter 自定义格式
    workbook = writer.book
    worksheet = writer.sheets['数据']
    worksheet.set_column('A:C', 15)          # 设置列宽
    worksheet.freeze_panes(1, 0)             # 冻结首行

与 Styler 结合

需要保留颜色的 Excel 导出，使用 df.style.to_excel()（见 22.1 导出）。

相关笔记

Graph View

Backlinks