The to_sql函数允许您将存储在 DataFrame 中的记录写入 SQL 数据库。通过使用 SQLAlchemy，可以使用该库支持的任何数据库。
在接下来的部分中，我们将更深入地研究此功能并探索更多功能。
基本语法和用法
的基本语法to_sql函数如下：
DataFrame.to_sql(name, con, schema=None, if_exists='fail', index=True, index_label=None, chunksize=None, dtype=None, method=None)
让我们简单讨论一下每个参数：
-
-
name：您将向其写入 DataFrame 的 SQL 表的名称。
-
con:SQLAlchemy 引擎或 DBAPI2 连接。使用 SQLAlchemy 可以使用该库支持的任何数据库。
-
schema：数据库中要写入的 SQL 模式的名称（可选）。
-
如果存在: 如果数据库中已经存在该表怎么办。选项包括：“失败”（默认）、“替换”和“追加”。
-
index：是否写入DataFrame的索引。默认为 True。
-
索引标签：用于 DataFrame 索引的列标签。如果 DataFrame 使用 MultiIndex，则这应该是一个序列。如果
None给定并且 DataFrame 使用 MultiIndex，将使用索引名称。
-
块大小：行将一次按此大小批量写入。如果
None给定后，所有行将被立即写入。
-
dtype：SQLAlchemy 类型的列名字典（或 SQLite3 传统模式的字符串）。这用于覆盖 Pandas 推断的默认数据类型。
-
method：控制使用的 SQL 插入子句。 0.24.0 版本中的新增内容。
连接到各种数据库
The to_sql函数于Pandas需要 SQLAlchemy 引擎或 sqlite3 数据库连接。建立这些连接有两种主要方法：
SQLite:SQLite 是一个提供关系数据库管理系统的软件库。它是一个独立、无服务器、零配置的数据库引擎。要创建与 SQLite 的连接，您可以使用sqlite3模块Python 标准库附带的：
import sqlite3
conn = sqlite3.connect('my_database.db')
SQL炼金术：SQLAlchemy 提供了一个通用接口，用于创建与不同数据库系统（包括 MySQL、PostgreSQL 和 Oracle）的连接。
但是，出于本教程的目的，我们将使用 SQLAlchemy，因为 SQLAlchemy 使得可以使用该库支持的任何数据库，包括 SQLite。
SQLite
要使用 SQLAlchemy 建立与 SQLite 数据库的连接，您可以执行以下操作：
from sqlalchemy import create_engine
engine = create_engine('sqlite:///:memory:')
在此示例中，我们创建了一个使用存储在内存中的 SQLite 数据库的引擎。这是一个特殊情况，非常适合测试和开发，因为您不必担心清理测试数据库。
然而，更常见的是，您希望将 SQLite 数据库存储在磁盘上的文件中。在这种情况下，您只需提供相对或绝对文件路径即可create_engine函数而不是“：内存：”：
from sqlalchemy import create_engine
engine = create_engine('sqlite:///my_database.db')
这里，“my_database.db”是 SQLite 数据库文件的名称。如果该文件不存在，SQLAlchemy 将在建立连接时自动创建它。
MySQL
连接 MySQL 最常见的驱动程序是mysqlclient，但其他人喜欢PyMySQL也可用。以下是建立与 MySQL 数据库的连接的方法：
from sqlalchemy import create_engine
engine = create_engine('mysql+mysqldb://username:password@localhost/db_name')
在此连接字符串中，“username”和“password”是您的 MySQL 用户名和密码，“localhost”是运行 MySQL 服务器的主机，“db_name”是您要连接的数据库的名称。
如果您使用不同的驱动程序，请将“mysqldb”替换为适当的名称。
例如，如果您使用 PyMySQL，您的连接字符串将如下所示：
from sqlalchemy import create_engine
engine = create_engine('mysql+pymysql://username:password@localhost/db_name')
请注意，您需要安装必要的 MySQL 驱动程序才能建立连接。为了mysqlclient，您可以通过 pip 安装它：
pip install mysqlclient
对于 PyMySQL：
pip install PyMySQL
PostgreSQL
您可以使用psycopg2库作为 SQLAlchemy 连接到 PostgreSQL 数据库的驱动程序。
以下是连接到 PostgreSQL 数据库的方法：
from sqlalchemy import create_engine
engine = create_engine('postgresql://username:password@localhost/db_name')
在此连接字符串中，“username”和“password”是您的 PostgreSQL 用户名和密码，“localhost”是运行 PostgreSQL 服务器的主机，“db_name”是您要连接的数据库的名称。
注意：您需要拥有psycopg2安装库以建立连接。您可以使用 pip 安装它：
pip install psycopg2
如果您安装时遇到问题psycopg2，尝试安装psycopg2-binary反而：
pip install psycopg2-binary
Oracle
您可以使用cx_Oracle用于连接到 Oracle 数据库的库。以下是建立与 Oracle 数据库的连接的方法：
from sqlalchemy import create_engine
engine = create_engine('oracle+cx_oracle://username:password@localhost:1521/sid')
在此连接字符串中，“username”和“password”是您的 Oracle 用户名和密码，“localhost”是运行 Oracle 服务器的主机，“1521”是 Oracle 服务器正在侦听的端口，“sid”是您要连接的 Oracle 数据库的系统 ID (SID)。
请注意，您需要拥有cx_Oracle安装库以建立连接。您可以使用 pip 安装它：
pip install cx_Oracle
请记住，您还需要在计算机上安装 Oracle 客户端库。您可以从 Oracle 网站下载它们。
使用 Pandas to_sql 将 DataFrame 导出到 SQL
现在我们知道如何使用 SQLAlchemy 连接到各种数据库，让我们深入了解如何使用to_sql函数将数据从 Pandas DataFrame 写入 SQL 数据库。
让我们从创建一个简单的数据框:
import pandas as pd
data = {'Name': ['John', 'Anna', 'Peter', 'Linda'],
'Age': [28, 24, 35, 32],
'City': ['New York', 'Paris', 'Berlin', 'London']}
df = pd.DataFrame(data)
print(df)
Output:
Name Age City
0 John 28 New York
1 Anna 24 Paris
2 Peter 35 Berlin
3 Linda 32 London
现在，假设我们有一个 SQLite 数据库，并且希望将此 DataFrame 写入数据库中的表中。我们需要首先创建一个 SQLAlchemy 引擎：
from sqlalchemy import create_engine
engine = create_engine('sqlite:///:memory:')
然后，我们可以使用to_sql将 DataFrame 写入 SQLite 数据库：
df.to_sql('People', con=engine, if_exists='replace', index=False)
在此示例中，我们将 DataFrame 写入名为“People”的表。这if_exists='replace'参数告诉 Pandas 替换该表（如果它已经存在）。这index=False参数告诉 Pandas 不要将 DataFrame 的索引写入 SQL 表。
然后，您可以使用以下命令验证数据是否已写入 SQLite 表read_sql:
result_df = pd.read_sql('People', con=engine)
print(result_df)
Output:
Name Age City
0 John 28 New York
1 Anna 24 Paris
2 Peter 35 Berlin
3 Linda 32 London
如您所见，我们已成功将 DataFrame 写入 SQL 表并从表中取回数据。
了解 if_exists 参数
The if_exists中的参数to_sql函数控制表已存在于数据库中时的行为方式。该参数可以接受三个可能的值：“fail”、“replace”和“append”。
选择正确的值if_exists非常重要，因为它可能会导致不同的结果。您必须仔细考虑是否要替换整个表、追加到表中，或者在表存在的情况下失败。
自定义数据类型和转换
使用以下命令将 DataFrame 写入 SQL 数据库时to_sql，pandas自动将DataFrame的数据类型转换为SQL类型。
但是，有时您可能想要为某些列指定自定义 SQL 数据类型。您可以使用dtype的论证to_sql.
The dtype参数应该是一个字典，其中键是列名，值应该是 SQLAlchemy 类型或 SQLite3 旧模式的字符串。
例如，假设我们有一个包含“Name”（字符串）、“Age”（整数）和“Salary”（浮点数）列的 DataFrame。
我们可以指定“Age”应在 SQL 数据库中存储为整数，“Salary”应存储为浮点数，如下所示：
from sqlalchemy.types import Integer, Float
df.to_sql('Employees', con=engine, if_exists='replace', index=False,
dtype={'Name': String, 'Age': Integer, 'Salary': Float})
在此示例中，“Name”将存储为字符串，“Age”将存储为整数，“Salary”将存储为 SQL 表中的浮点数。
使用时请注意dtype，如果支持，pandas 将尝试将每个 DataFrame 列的类型与 SQLAlchemy 匹配。如果没有，它将使用该列数据类型的默认 SQL 类型。
同样重要的是要记住，并非所有 SQL 数据库都支持所有数据类型，因此dtype应谨慎使用论证。
指数管理
在pandas中，当创建DataFrame时，也会自动生成索引。该索引可以基于一列或多列，分别形成单个索引或多个索引。
当我们想要获取或操作数据时，索引特别有用。
In the to_sql函数中，我们可以通过使用以下命令来控制是否要将DataFrame的索引写入SQL数据库index范围。
If index=True（这是默认设置），索引将作为单独的列写入 SQL 数据库。如果index=False，索引不会被写入。
df.to_sql('Employees', con=engine, if_exists='replace', index=False)
多索引支持
MultiIndex 或分层索引是一种特殊类型的索引，它允许您在单个轴上拥有多个级别的索引。
如果DataFrame使用MultiIndex，则可以使用以下命令将其写入SQL数据库to_sql函数，每个级别的索引在数据库中形成一个单独的列。
让我们看一个例子，其中有一个带有 MultiIndex 的 DataFrame：
index = pd.MultiIndex.from_tuples([(1, 'red'), (1, 'blue'), (2, 'red'), (2, 'blue')],
names=['number', 'color'])
df = pd.DataFrame({'Name': ['John', 'Anna', 'Peter', 'Linda'],
'Age': [28, 24, 35, 32]}, index=index)
df.to_sql('People', con=engine, if_exists='replace')
在这种情况下，DataFrame使用MultiIndex（‘number’和‘color’），并且在使用时，每个级别的索引将被写入SQL数据库中的单独列to_sql.
管理插入（无、多、可调用）
您可以使用method使用时的参数to_sql方法来控制如何将数据插入到 SQL 数据库中。
您可以使用三个主要选项：None, multi, or a callable.
让我们从创建一个简单的 DataFrame 开始：
import pandas as pd
data = {'Name': ['John', 'Anna', 'Peter', 'Linda'],
'Age': [28, 24, 35, 32],
'City': ['New York', 'Paris', 'Berlin', 'London']}
df = pd.DataFrame(data)
None
When method=None，DataFrame 的每一行将单独写入 SQL 表。这是默认行为：
df.to_sql('People', con=engine, if_exists='replace', index=False, method=None)
Multi
When method='multi'，将一次写入多行。这可以提高性能，特别是对于较大的 DataFrame：
df.to_sql('People', con=engine, if_exists='replace', index=False, method='multi')
可调用（自定义插入）
对于可调用方法，可以定义自定义函数来修改SQL插入语句：
def insert_with_replace(dataframe, conn, keys, data_iter):
raw_conn = conn.raw_connection()
cursor = raw_conn.cursor()
insert_statement = "INSERT OR REPLACE INTO People VALUES (?, ?, ?)"
cursor.executemany(insert_statement, data_iter)
raw_conn.commit()
df.to_sql('People', con=engine, if_exists='replace', index=False, method=insert_with_replace)
在此示例中，我们定义了一个自定义函数，用“INSERT OR REPLACE”语句替换标准 SQL INSERT 语句。
这会将新行插入“People”表中，但如果已存在具有相同主键的行，则该行将被替换。
在这三种方法中，我们可以通过从表中取回数据来验证数据是否已写入：
result_df = pd.read_sql('People', con=engine)
print(result_df)
Output:
Name Age City
0 John 28 New York
1 Anna 24 Paris
2 Peter 35 Berlin
3 Linda 32 London
处理大数据集
当您处理大型 DataFrame 时，由于内存限制，将整个 DataFrame 一次性写入 SQL 数据库可能不可行。在这种情况下，pandas 提供了以块的形式写入数据的选项。
您可以使用chunksize的参数to_sql函数定义一次应写入的行数。
让我们考虑一个大的 DataFramedf有 100,000 行。我们可以将这些数据以 10,000 行为单位写入 SQL 数据库，如下所示：
df.to_sql('LargeTable', con=engine, if_exists='replace', index=False, chunksize=10000)
在此示例中，pandas 将一次向“LargeTable”插入 10,000 行。
但是，您应该注意使用chunksize可能会增加将整个 DataFrame 写入 SQL 数据库所需的总时间，因为 Pandas 需要为每个块创建一个新的 SQL 插入语句。
进一步阅读
https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_sql.html
