pandas df.to_sql() 写入到关系型数据库

看过来

《pandas 教程》持续更新中，提供建议、纠错、催更等加作者微信: gairuo123（备注：pandas教程）和关注公众号「盖若」ID: gairuo。跟作者学习，请进入 Python学习课程。欢迎关注作者出版的书籍：《深入浅出Pandas》和《Python之光》。

pandas.DataFrame.to_sql() 是 Pandas 库中用于将 DataFrame 对象中的数据写入到关系型数据库中的方法。通过此方法，可以轻松地将数据存储到各种数据库系统中，如 SQLite、MySQL、PostgreSQL 等，便于后续的数据查询、分析和管理。

参考

函数签名

DataFrame.to_sql(
    name, 
    con, 
    *, 
    schema=None, 
    if_exists='fail', 
    index=True, 
    index_label=None, 
    chunksize=None, 
    dtype=None, 
    method=None
)

参数详解

name
- 类型: str
- 默认值: 无
- 意义: 指定目标数据库中的表名。数据将被写入到这个表中。如果表不存在，将根据 if_exists 参数的设置决定是否创建新表。
con
- 类型: SQLAlchemy engine 或 sqlite3.Connection
- 默认值: 无
- 意义: 数据库连接对象。可以是通过 SQLAlchemy 创建的引擎对象，也可以是 SQLite 的连接对象。用于指定数据写入的目标数据库。
schema
- 类型: str，可选
- 默认值: None
- 意义: 指定数据库中的模式（schema）。如果数据库支持模式，可以通过此参数指定数据写入的特定模式。
if_exists
- 类型: {'fail', 'replace', 'append'}，可选
- 默认值: 'fail'
- 意义: 指定当目标表已经存在时的处理方式。
  - 'fail': 抛出 ValueError，不进行任何操作。
  - 'replace': 删除已存在的表，并创建一个新表，然后写入数据。
  - 'append': 在已存在的表中追加数据。
index
- 类型: bool
- 默认值: True
- 意义: 指定是否将 DataFrame 的索引写入数据库表中。如果设置为 True，索引将作为一列写入；如果为 False，索引将被忽略。
index_label
- 类型: str 或 sequence of str，可选
- 默认值: None
- 意义: 指定索引列的名称。如果未指定，将使用 DataFrame 索引的名称；如果索引没有名称，将使用默认的列名。
chunksize
- 类型: int，可选
- 默认值: None
- 意义: 指定每次写入数据库的行数。这对于处理大型 DataFrame 非常有用，可以分块写入以节省内存并提高性能。
dtype
- 类型: dict，可选
- 默认值: None
- 意义: 指定列的数据类型。通过传递一个字典，键为列名，值为对应的 SQL 数据类型，可以精确控制每一列在数据库中的类型。
method
- 类型: str、callable 或 None，可选
- 默认值: None
- 意义: 指定写入方法。
  - None: 使用默认的 INSERT 语句。
  - 'multi': 使用多行插入语句，提高写入速度。
  - callable: 提供一个自定义的插入方法函数。

返回值

类型: None
意义: 此方法主要执行数据写入操作，不返回任何值。如果 buf 参数为 None，则返回生成的 SQL 语句字符串；否则，数据将被写入到指定的数据库表中。

使用场景

DataFrame.to_sql() 主要用于以下场景：

数据持久化: 将分析后的数据存储到数据库中，便于长期保存和管理。
数据集成: 将多个数据源的数据整合到一个数据库中，方便统一查询和分析。
Web应用: 将数据写入数据库，供 Web 应用程序实时查询和展示。
备份: 将重要的数据备份到数据库中，防止数据丢失。

示例

示例 1：将 DataFrame 写入 SQLite 数据库

import pandas as pd
import sqlite3

# 构造示例数据
data = {
    '姓名': ['艾丽丝', '鲍勃', '查理'],
    '年龄': [30, 25, 35],
    '城市': ['纽约', '洛杉矶', '芝加哥']
}
df = pd.DataFrame(data)

# 创建 SQLite 数据库连接
conn = sqlite3.connect('example.db')

# 将 DataFrame 写入数据库表 'persons'
df.to_sql('persons', con=conn, if_exists='replace', index=False)

# 读取并打印数据库中的数据
df_from_db = pd.read_sql('SELECT * FROM persons', conn)
print(df_from_db)

# 关闭数据库连接
conn.close()

输出

姓名  年龄     城市
0  艾丽丝   30     纽约
1    鲍勃   25  洛杉矶
2  查理   35  芝加哥

示例 2：将 DataFrame 追加到已存在的数据库表中

import pandas as pd
import sqlite3

# 构造新的示例数据
new_data = {
    '姓名': ['大卫', '伊丽莎'],
    '年龄': [28, 32],
    '城市': ['波士顿', '旧金山']
}
new_df = pd.DataFrame(new_data)

# 创建 SQLite 数据库连接
conn = sqlite3.connect('example.db')

# 将新数据追加到数据库表 'persons'
new_df.to_sql('persons', con=conn, if_exists='append', index=False)

# 读取并打印数据库中的所有数据
df_from_db = pd.read_sql('SELECT * FROM persons', conn)
print(df_from_db)

# 关闭数据库连接
conn.close()

输出

姓名  年龄     城市
0  艾丽丝   30     纽约
1    鲍勃   25  洛杉矶
2  查理   35  芝加哥
3    大卫   28   波士顿
4  伊丽莎   32  旧金山

示例 3：指定列的数据类型

import pandas as pd
from sqlalchemy import create_engine, Integer, String

# 构造示例数据
data = {
    '姓名': ['艾丽丝', '鲍勃', '查理'],
    '年龄': [30, 25, 35],
    '城市': ['纽约', '洛杉矶', '芝加哥']
}
df = pd.DataFrame(data)

# 创建 SQLAlchemy 引擎
engine = create_engine('sqlite:///example.db')

# 指定列的数据类型
dtype_mapping = {
    '姓名': String(50),
    '年龄': Integer(),
    '城市': String(100)
}

# 将 DataFrame 写入数据库表 'persons_dtype'，指定数据类型
df.to_sql('persons_dtype', con=engine, if_exists='replace', index=False, dtype=dtype_mapping)

# 读取并打印数据库中的数据
df_from_db = pd.read_sql('SELECT * FROM persons_dtype', engine)
print(df_from_db)

输出

姓名  年龄     城市
0  艾丽丝   30     纽约
1    鲍勃   25  洛杉矶
2  查理   35  芝加哥

示例 4：使用自定义写入方法提高写入速度

import pandas as pd
from sqlalchemy import create_engine

# 构造示例数据
data = {
    '姓名': ['艾丽丝'] * 1000,
    '年龄': [30] * 1000,
    '城市': ['纽约'] * 1000
}
df = pd.DataFrame(data)

# 创建 SQLAlchemy 引擎
engine = create_engine('sqlite:///example.db')

# 使用 'multi' 方法一次插入多行，提高写入速度
df.to_sql('persons_multi', con=engine, if_exists='replace', index=False, method='multi')

# 读取并打印部分数据库中的数据
df_from_db = pd.read_sql('SELECT * FROM persons_multi LIMIT 5', engine)
print(df_from_db)

输出

姓名  年龄   城市
0  艾丽丝   30  纽约
1  艾丽丝   30  纽约
2  艾丽丝   30  纽约
3  艾丽丝   30  纽约
4  艾丽丝   30  纽约

示例 5：不包含索引列写入数据库

import pandas as pd
import sqlite3

# 构造示例数据
data = {
    '姓名': ['艾丽丝', '鲍勃', '查理'],
    '年龄': [30, 25, 35],
    '城市': ['纽约', '洛杉矶', '芝加哥']
}
df = pd.DataFrame(data)

# 创建 SQLite 数据库连接
conn = sqlite3.connect('example.db')

# 将 DataFrame 写入数据库表 'persons_no_index'，不包含索引
df.to_sql('persons_no_index', con=conn, if_exists='replace', index=False)

# 读取并打印数据库中的数据
df_from_db = pd.read_sql('SELECT * FROM persons_no_index', conn)
print(df_from_db)

# 关闭数据库连接
conn.close()

输出

姓名  年龄     城市
0  艾丽丝   30     纽约
1    鲍勃   25  洛杉矶
2  查理   35  芝加哥

总结

pandas.DataFrame.to_sql() 是一个强大的方法，能够将 Pandas 的 DataFrame 数据高效地写入到各种关系型数据库中。通过灵活配置参数，如指定表名、处理已存在表的方式、控制索引的写入、分块写入大数据集以及自定义数据类型等，可以满足不同的数据存储需求。在数据分析、数据集成和数据持久化等多个领域，掌握 to_sql() 的使用方法能够显著提升数据管理和处理的效率。

参考

https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_sql.html

< to_html() 转换为 HTML 表格格式 pandas 文件或数据的读取和导出 to_parquet() 保存为 parquet 文件 >

更新时间：2024-10-10 09:21:45 标签：pandas python 数据库