Skip to content

在 Pandas 中优化 SQL 查询：Pandas 到 SQL 其实很简单！

Matt Popovic
Name
Matt Popovic

Updated on

Pandas 中的 to_sql() 方法 是一个非常强大的工具，可以将数据 从 Pandas DataFrame 写入 SQL 数据库。它常见的使用场景包括：回写批量机器学习预测结果、记录指标、存储预处理好的数据集等。不过，要把 to_sql() 用好，需要充分理解它的参数，并意识到其中可能存在的坑。

本文将系统介绍如何在 pandas 中使用 to_sql() 方法，重点放在编写 高性能、可维护的 SQL 的最佳实践与技巧上。内容涵盖从连接数据库、写入大数据集，到性能优化等多个方面。

想在不写代码的情况下，基于 Python Pandas DataFrame 快速做数据可视化？

PyGWalker 是一个用于可视化探索性数据分析的 Python 库。PyGWalker (opens in a new tab) 可以极大简化你在 Jupyter Notebook 里的数据分析与可视化流程，把 pandas dataframe（以及 polars dataframe）一键转换成类似 Tableau 的交互式可视化分析界面。

PyGWalker for Data visualization (opens in a new tab)

Pandas 到 SQL：快速上手

在使用 to_sql() 之前，需要先安装必要的包：pandas 和 sqlalchemy。推荐为每个项目单独创建一个 Python 环境，可以用 Conda 来完成：

conda create -n env_name python=3.9 pandas sqlalchemy
conda activate env_name

安装好依赖之后，下一步就是创建到数据库的连接。这个连接对象可以由 sqlite3 或 sqlalchemy 提供。本文使用的是 pandas 官方更推荐的 sqlalchemy 方案。

from sqlalchemy import create_engine
 
# Create connection to Snowflake using your account and user
account_identifier = '<account_identifier>'
user = '<user_login_name>'
password = '<password>'
conn_string = f"snowflake://{user}:{password}@{account_identifier}/"
engine = create_engine(conn_string)

如何使用 Pandas 的 to_sql() 方法

to_sql() 方法通过一系列参数来控制写入逻辑和写入到数据库的数据内容，主要包括：

  • name：SQL 表名（必填）
  • con：由 sqlite 或 sqlalchemy 提供的连接对象（必填）
  • schema：数据库 schema 名
  • if_exists：当表已存在时怎么处理，可选 'fail'、'replace'、'append'
  • index：是否将 DataFrame 的索引列写入表中
  • index_label：如果写入索引，为该列指定名字
  • chunksize：每次写入的行数（分批写入）
  • dtype：列名到数据类型的字典映射
  • method：指定 SQL 插入的方式

理解这些参数，是高效使用 to_sql() 的关键。

Pandas to_sql() 实战示例

现在我们已经安装了依赖、创建了连接，并了解了方法的主要参数，就可以开始往数据库里写数据了。下面的例子演示如何把一个非常简单的 DataFrame 写入 Snowflake 中的一张表：

import pandas as pd
from sqlalchemy import create_engine
 
# Create your DataFrame
table_name = 'jobs'
df = pd.DataFrame(data=[['Stephen','Data scientist'],['Jane','Data analyst']],columns=['Name','Job'])
 
# What to do if the table exists? replace, append, or fail?
if_exists = 'replace'
 
# Write the data to Snowflake
with engine.connect() as con:
 df.to_sql(
 name=table_name.lower(), 
 con=con, 
 if_exists=if_exists
 )

这段代码构造了一个包含两列（Name 和 Job）、两行记录的 DataFrame，然后将其写入 Snowflake 中的一张表。if_exists 参数设置为 'replace'，表示如果该表已经存在，就会被新的 DataFrame 完全替换掉。

如何优化 Pandas to_sql() 的性能

虽然 to_sql() 使用起来非常方便，但在写入大表时可能会变得很慢。这是因为默认情况下，to_sql() 是一行一行地向数据库插入数据。对于大数据量来说，每条 INSERT 操作都有非忽略不计的开销，因此整体会比较低效。

一个常见的加速方式是利用部分数据库驱动提供的 fast_executemany 选项。启用该选项后，to_sql() 可以批量写入数据，而不是一行一行写，大幅减少单次插入的开销，从而明显提升性能。

要使用 fast_executemany（部分 ODBC 连接器支持，如通过 pyodbc 连接 SQL Server），可以在创建 engine 时加上 fast_executemany=True，例如：

engine = create_engine(conn_string, fast_executemany=True)

对于 SQLAlchemy 2.x 的 engine，推荐使用 engine.begin() 或 with engine.connect() as con:（如上例所示）来确保连接可以被正常关闭；如果后端支持多行插入，也可以在 to_sql() 中指定 method="multi" 来减少网络往返次数（round-trips）。

启用这些选项后，to_sql() 会以批量方式写入数据，尤其在处理大数据集时，速度提升往往非常可观。

在 Pandas to_sql() 中处理 NULL 或 NaN 值

使用 to_sql() 将 DataFrame 写入 SQL 数据库时，一个常见问题是如何处理 NULL 或 NaN。默认行为是：pandas 会把 DataFrame 中的 NaN 转换成 SQL 中的 NULL。通常这正是我们想要的，因为这样数据库就可以按自身的数据类型和约束规则来处理缺失值。

不过，在有些业务场景下，你可能希望在写入数据库之前，用其他值替代 NaN。例如，你可以在写入前使用 pandas 的 fillna() 方法：

df = df.fillna(value)

这行代码会把 DataFrame 中所有的 NaN 用你指定的 value 替换掉。

使用 Pandas to_sql() 向已存在的表追加记录

to_sql() 同样也支持往一张已存在的 SQL 表中追加记录。只需要把 if_exists 参数设置为 'append' 即可：

df.to_sql(name=table_name, con=con, if_exists='append')

这段代码会把 DataFrame 中的记录追加到现有表中。如果该表不存在，则会自动创建。

使用 Pandas to_sql() 更新已有记录

to_sql() 本身并不直接支持 “更新表中已有记录” 的操作，但可以通过与其他 SQL 操作组合来实现。

一种常见的做法是：先用 to_sql() 把 DataFrame 写入数据库中的一个临时表，然后再通过 SQL 的 UPDATE 语句，按照某个主键或唯一键，把目标表中的记录更新为临时表中的值。

示例代码如下：

# Write DataFrame to a temporary table
df.to_sql(name='temp_table', con=con, if_exists='replace')
 
# Create a SQL UPDATE statement
update_sql = """
UPDATE target_table
SET target_table.column1 = temp_table.column1,
    target_table.column2 = temp_table.column2,
    ...
FROM temp_table
WHERE target_table.id = temp_table.id
"""
 
# Execute the UPDATE statement
with engine.connect() as con:
    con.execute(update_sql)

这段代码首先将 DataFrame 写入数据库中的一张临时表 temp_table，然后构造一条 SQL UPDATE 语句，根据临时表的记录更新目标表 target_table 中对应主键的记录，最后通过连接对象执行这条 UPDATE。

总结

总的来说，利用 to_sql() 方法可以高效地把数据从 pandas 传输到 SQL 中，并且配合合适的参数配置，可以实现对 NULL/NaN 值的合理处理、向表中追加记录等功能。在理解其行为和参数含义之后，你可以根据具体场景灵活组合使用（是否分批、是否批量插入、是否先写临时表再更新等），大幅提升数据读写和数据管道的效率。

无论你是数据分析师、数据科学家，还是后端/数据工程方向的开发者，熟练掌握 “pandas 到 SQL” 这条路径，都会显著增强你的数据处理与分析能力。关键是理解工具的行为，并按照自己的场景和偏好，合理配置与组合使用。

想在不写代码的情况下，基于 Python Pandas DataFrame 快速做数据可视化？

PyGWalker 是一个用于可视化探索性数据分析的 Python 库。PyGWalker (opens in a new tab) 可以极大简化你在 Jupyter Notebook 里的数据分析与可视化流程，把你的 pandas dataframe（以及 polars dataframe）一键转换成类似 Tableau 的可视分析界面。

PyGWalker for Data visualization (opens in a new tab)

常见问题 FAQ

  1. pandas 中的 to_sql() 方法是什么？

pandas 中的 to_sql() 方法可以将 DataFrame 中的数据写入到 SQL 数据库。它为长期存储数据、在系统之间共享数据，或者在数据库中继续用 SQL 做进一步处理，提供了一种非常方便的方式。

  1. 使用 to_sql() 时，如何处理 NULL 或 NaN？

默认情况下，pandas 会在写入数据库时把 DataFrame 中的 NaN 转换为 SQL 的 NULL。如果你想用别的值替换 NaN，可以在写入之前使用 fillna() 方法，将 NaN 替换为你指定的值。

  1. 可以使用 to_sql() 向现有的表中追加记录吗？

可以。只需在调用 to_sql() 时将 if_exists 参数设置为 'append'，就可以把 DataFrame 中的记录追加到已存在的表中。

  1. “pandas translate to SQL” 是什么？

pandasql 是一个可以把 SQL 查询翻译成 pandas 操作的库。它允许你用 SQL 类似的语法来操作和查询 pandas DataFrame。