使用 SQLAlchemy 将 pandas DataFrame 导出到 SQLite-我爱分享网

在执行探索性数据分析时，例如在使用 pandas 检查 COVID-19 数据时，从 CSV、XML 或 JSON 等文件加载到 apandas DataFrame 中是很常见的。然后，您可以对 DataFrame 中的数据进行一些处理，并希望将其存储在更持久的位置，如关系数据库。

本教程介绍了如何从 CSV 文件加载 pandas DataFrame，从完整数据集中提取一些数据，然后使用 SQLAlchemy 将数据子集保存到 SQLite 数据库。

配置我们的开发环境

确保安装了 Python 3。截至目前，Python 3.8.2 是最新的 Python 版本。

在本教程中，我们还将使用：

pandas（项目主页和源代码），本教程的版本 1.0.3
SQLAlchemy（项目主页和源代码），本教程的版本 1.3.15
SQLite (项目主页和源代码），其中 Python 包含一个连接器作为 Python 标准库的一部分

使用以下命令将上述代码库安装到新的 Python 虚拟环境中：

python -m venv pandasexport
source pandasexport/bin/activate

pip install pandas==1.0.3 sqlalchemy==1.3.15

我们的开发环境现在已准备好下载示例 COVID-19 数据集，将其加载到 pandasDataFrame 中，对其执行一些分析，然后保存到 SQLite 数据库中。

获取 COVID-19 数据

在您的网络浏览器中转到下载今天关于 COVID-19 病例全球地理分布的数据页面。它应该类似于以下屏幕截图。

应该有下载 CSV 格式数据的链接，但该组织在过去几周内多次更改页面布局，这使得很难找到 Excel (XLSX) 以外的格式。如果您在获取 CSV 版本时遇到问题，只需从 GitHub 下载这个版本，它与 2020 年 3 月 28 日下载的副本挂钩。

将 CSV 文件导入 pandas

原始数据在 CSV 文件中，我们需要通过 apandas DataFrame 将其加载到内存中。

首先在命令行上运行 Python Read-Evaluate-Print Loop (REPL)：

python

>>>

REPL 已准备好执行代码，但我们首先需要导入 pandas 库以便使用它。

from pandas import read_csv

df = read_csv("covid-19-cases-march-28-2020.csv", encoding="ISO-8859-1")

数据现在被加载到 df 变量中，它是 pandas DataFrame 类的一个实例。

当我们在此 DataFrame 上运行 count 函数时，我们返回它有 7320 行。

df.count()

接下来，我们将获取这组 7320 行数据，并仅切出与美国有关的行。

从原来的DataFrame创建一个新的DataFrame

我们可以使用 pandas 函数将 countriesAndTerritories 列匹配到我们选择的国家/地区，从而挑选出单个国家/地区的所有数据行。

save_df = df[df['countriesAndTerritories']=="United_States_of_America"]

save_df 变量包含较小的数据子集。你可以让它自己打印出来，看看里面有什么：

save_df

您应该看到类似于以下输出的内容：

         dateRep  day  month  year  cases  deaths   countriesAndTerritories geoId countryterritoryCode  popData2018
7082  28/03/2020   28      3  2020  18695     411  United_States_of_America    US                  USA  327167434.0
7083  27/03/2020   27      3  2020  16797     246  United_States_of_America    US                  USA  327167434.0
7084  26/03/2020   26      3  2020  13963     249  United_States_of_America    US                  USA  327167434.0
7085  25/03/2020   25      3  2020   8789     211  United_States_of_America    US                  USA  327167434.0
7086  24/03/2020   24      3  2020  11236     119  United_States_of_America    US                  USA  327167434.0
...          ...  ...    ...   ...    ...     ...                       ...   ...                  ...          ...
7166  04/01/2020    4      1  2020      0       0  United_States_of_America    US                  USA  327167434.0
7167  03/01/2020    3      1  2020      0       0  United_States_of_America    US                  USA  327167434.0
7168  02/01/2020    2      1  2020      0       0  United_States_of_America    US                  USA  327167434.0
7169  01/01/2020    1      1  2020      0       0  United_States_of_America    US                  USA  327167434.0
7170  31/12/2019   31     12  2019      0       0  United_States_of_America    US                  USA  327167434.0

[89 rows x 10 columns]

原始 7320 行中的 89 行数据。让我们继续将这个子集保存到 SQLite 关系数据库。

将 DataFrame 保存到 SQLite

我们将使用 SQLAlchemy 创建一个到新 SQLite 数据库的连接，在本例中，该数据库将存储在名为 save_pandas.db 的文件中。您当然可以使用任何您想要的名称将文件保存在任何位置，而不仅仅是您执行 Python REPL 的目录。

首先从 create_engine 库中导入 sqlalchemy 函数。

from sqlalchemy import create_engine

使用导入的create_engine 函数创建连接，然后在其上调用connect 方法。

engine = create_engine('sqlite:///save_pandas.db', echo=True)
sqlite_connection = engine.connect()

我们设置echo=True 来查看来自我们数据库连接的所有输出。连接成功后，您将看到类似于以下内容的输出：

2020-03-29 20:44:08,198 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2020-03-29 20:44:08,198 INFO sqlalchemy.engine.base.Engine ()
2020-03-29 20:44:08,199 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2020-03-29 20:44:08,199 INFO sqlalchemy.engine.base.Engine ()
<sqlalchemy.engine.base.Connection object at 0x7fd4d932ec88>

用您想要创建的表名的字符串设置一个变量名。然后在 to_sql 对象上调用 save_df 方法时使用该变量，这是我们的 pandas DataFrame，它是原始数据集的一个子集，其中有 89 行是从原来的 7320。

请注意，在这种情况下，如果表已存在于数据库中，我们将失败。您可以将 if_exists 更改为 replace 或 append 并在此程序的更强大版本中添加您自己的异常处理。查看 pandas.DataFrame.to_sql 文档以了解有关您的选项的详细信息。

sqlite_table = "Covid19"
save_df.to_sql(sqlite_table, sqlite_connection, if_exists='fail')

echo 输出应该有一堆输出。

2020-03-29 20:45:09,066 INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info("Covid19")
2020-03-29 20:45:09,066 INFO sqlalchemy.engine.base.Engine ()
2020-03-29 20:45:09,067 INFO sqlalchemy.engine.base.Engine PRAGMA temp.table_info("Covid19")
2020-03-29 20:45:09,067 INFO sqlalchemy.engine.base.Engine ()
2020-03-29 20:45:09,069 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE "Covid19" (
    "index" BIGINT, 
    "dateRep" TEXT, 
    day BIGINT, 
    month BIGINT, 
    year BIGINT, 
    cases BIGINT, 
    deaths BIGINT, 
    "countriesAndTerritories" TEXT, 
    "geoId" TEXT, 
    "countryterritoryCode" TEXT, 
    "popData2018" FLOAT
)


2020-03-29 20:45:09,069 INFO sqlalchemy.engine.base.Engine ()
2020-03-29 20:45:09,070 INFO sqlalchemy.engine.base.Engine COMMIT
2020-03-29 20:45:09,070 INFO sqlalchemy.engine.base.Engine CREATE INDEX "ix_Covid19_index" ON "Covid19" ("index")
2020-03-29 20:45:09,070 INFO sqlalchemy.engine.base.Engine ()
2020-03-29 20:45:09,071 INFO sqlalchemy.engine.base.Engine COMMIT
2020-03-29 20:45:09,072 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2020-03-29 20:45:09,074 INFO sqlalchemy.engine.base.Engine INSERT INTO "Covid19" ("index", "dateRep", day, month, year, cases, deaths, "countriesAndTerritories", "geoId", "countryterritoryCode", "popData2018") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
2020-03-29 20:45:09,074 INFO sqlalchemy.engine.base.Engine ((7082, '28/03/2020', 28, 3, 2020, 18695, 411, 'United_States_of_America', 'US', 'USA', 327167434.0), (7083, '27/03/2020', 27, 3, 2020, 16797, 246, 'United_States_of_America', 'US', 'USA', 327167434.0), (7084, '26/03/2020', 26, 3, 2020, 13963, 249, 'United_States_of_America', 'US', 'USA', 327167434.0), (7085, '25/03/2020', 25, 3, 2020, 8789, 211, 'United_States_of_America', 'US', 'USA', 327167434.0), (7086, '24/03/2020', 24, 3, 2020, 11236, 119, 'United_States_of_America', 'US', 'USA', 327167434.0), (7087, '23/03/2020', 23, 3, 2020, 8459, 131, 'United_States_of_America', 'US', 'USA', 327167434.0), (7088, '22/03/2020', 22, 3, 2020, 7123, 80, 'United_States_of_America', 'US', 'USA', 327167434.0), (7089, '21/03/2020', 21, 3, 2020, 5374, 110, 'United_States_of_America', 'US', 'USA', 327167434.0)  ... displaying 10 of 89 total bound parameter sets ...  (7169, '01/01/2020', 1, 1, 2020, 0, 0, 'United_States_of_America', 'US', 'USA', 327167434.0), (7170, '31/12/2019', 31, 12, 2019, 0, 0, 'United_States_of_America', 'US', 'USA', 327167434.0))
2020-03-29 20:45:09,074 INFO sqlalchemy.engine.base.Engine COMMIT
2020-03-29 20:45:09,075 INFO sqlalchemy.engine.base.Engine SELECT name FROM sqlite_master WHERE type='table' ORDER BY name
2020-03-29 20:45:09,075 INFO sqlalchemy.engine.base.Engine ()

我们的表格及其所有数据现在应该都设置好了。关闭数据库连接。

sqlite_connection.close()

我们可以通过sqlite3 命令行查看器查看数据，以确保它已正确保存到 SQLite 文件中。

在命令行（不在 Python REPL 中），键入：

sqlite3

这将打开命令行提示符以与 SQLite 数据库进行交互。但是，我们还没有连接到我们的 save_pandas.db 文件。

SQLite version 3.28.0 2019-04-15 14:49:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>

使用 .open 命令和我们的 save_pandas.db 文件名来访问数据库。然后使用标准 SQL 查询从 Covid19 表中获取所有记录。

sqlite> .open save_pandas.db
sqlite> select * from Covid19;

SQLite 资源管理器应该产生如下所示的输出：

7082|28/03/2020|28|3|2020|18695|411|United_States_of_America|US|USA|327167434.0
7083|27/03/2020|27|3|2020|16797|246|United_States_of_America|US|USA|327167434.0
7084|26/03/2020|26|3|2020|13963|249|United_States_of_America|US|USA|327167434.0
7085|25/03/2020|25|3|2020|8789|211|United_States_of_America|US|USA|327167434.0
7086|24/03/2020|24|3|2020|11236|119|United_States_of_America|US|USA|327167434.0
7087|23/03/2020|23|3|2020|8459|131|United_States_of_America|US|USA|327167434.0
7088|22/03/2020|22|3|2020|7123|80|United_States_of_America|US|USA|327167434.0
7089|21/03/2020|21|3|2020|5374|110|United_States_of_America|US|USA|327167434.0
7090|20/03/2020|20|3|2020|4835|0|United_States_of_America|US|USA|327167434.0
7091|19/03/2020|19|3|2020|2988|42|United_States_of_America|US|USA|327167434.0
7092|18/03/2020|18|3|2020|1766|23|United_States_of_America|US|USA|327167434.0
7093|17/03/2020|17|3|2020|887|16|United_States_of_America|US|USA|327167434.0
7094|16/03/2020|16|3|2020|823|12|United_States_of_America|US|USA|327167434.0
7095|15/03/2020|15|3|2020|777|10|United_States_of_America|US|USA|327167434.0
7096|14/03/2020|14|3|2020|511|7|United_States_of_America|US|USA|327167434.0
7097|13/03/2020|13|3|2020|351|10|United_States_of_America|US|USA|327167434.0
7098|12/03/2020|12|3|2020|287|2|United_States_of_America|US|USA|327167434.0
7099|11/03/2020|11|3|2020|271|2|United_States_of_America|US|USA|327167434.0
7100|10/03/2020|10|3|2020|200|5|United_States_of_America|US|USA|327167434.0
7101|09/03/2020|9|3|2020|121|4|United_States_of_America|US|USA|327167434.0
7102|08/03/2020|8|3|2020|95|3|United_States_of_America|US|USA|327167434.0
7103|07/03/2020|7|3|2020|105|2|United_States_of_America|US|USA|327167434.0
7104|06/03/2020|6|3|2020|74|1|United_States_of_America|US|USA|327167434.0
7105|05/03/2020|5|3|2020|34|2|United_States_of_America|US|USA|327167434.0
7106|04/03/2020|4|3|2020|22|3|United_States_of_America|US|USA|327167434.0
7107|03/03/2020|3|3|2020|14|4|United_States_of_America|US|USA|327167434.0
7108|02/03/2020|2|3|2020|20|1|United_States_of_America|US|USA|327167434.0
7109|01/03/2020|1|3|2020|3|1|United_States_of_America|US|USA|327167434.0
7110|29/02/2020|29|2|2020|6|0|United_States_of_America|US|USA|327167434.0
7111|28/02/2020|28|2|2020|1|0|United_States_of_America|US|USA|327167434.0
7112|27/02/2020|27|2|2020|6|0|United_States_of_America|US|USA|327167434.0
7113|26/02/2020|26|2|2020|0|0|United_States_of_America|US|USA|327167434.0
7114|25/02/2020|25|2|2020|18|0|United_States_of_America|US|USA|327167434.0
7115|24/02/2020|24|2|2020|0|0|United_States_of_America|US|USA|327167434.0
7116|23/02/2020|23|2|2020|0|0|United_States_of_America|US|USA|327167434.0
7117|22/02/2020|22|2|2020|19|0|United_States_of_America|US|USA|327167434.0
7118|21/02/2020|21|2|2020|1|0|United_States_of_America|US|USA|327167434.0
7119|20/02/2020|20|2|2020|0|0|United_States_of_America|US|USA|327167434.0
7120|19/02/2020|19|2|2020|0|0|United_States_of_America|US|USA|327167434.0
7121|18/02/2020|18|2|2020|0|0|United_States_of_America|US|USA|327167434.0
7122|17/02/2020|17|2|2020|0|0|United_States_of_America|US|USA|327167434.0
7123|16/02/2020|16|2|2020|0|0|United_States_of_America|US|USA|327167434.0
7124|15/02/2020|15|2|2020|0|0|United_States_of_America|US|USA|327167434.0
7125|14/02/2020|14|2|2020|1|0|United_States_of_America|US|USA|327167434.0
7126|13/02/2020|13|2|2020|1|0|United_States_of_America|US|USA|327167434.0
7127|12/02/2020|12|2|2020|0|0|United_States_of_America|US|USA|327167434.0
7128|11/02/2020|11|2|2020|1|0|United_States_of_America|US|USA|327167434.0
7129|10/02/2020|10|2|2020|0|0|United_States_of_America|US|USA|327167434.0
7130|09/02/2020|9|2|2020|0|0|United_States_of_America|US|USA|327167434.0
7131|08/02/2020|8|2|2020|0|0|United_States_of_America|US|USA|327167434.0
7132|07/02/2020|7|2|2020|0|0|United_States_of_America|US|USA|327167434.0
7133|06/02/2020|6|2|2020|1|0|United_States_of_America|US|USA|327167434.0
7134|05/02/2020|5|2|2020|0|0|United_States_of_America|US|USA|327167434.0
7135|04/02/2020|4|2|2020|0|0|United_States_of_America|US|USA|327167434.0
7136|03/02/2020|3|2|2020|3|0|United_States_of_America|US|USA|327167434.0
7137|02/02/2020|2|2|2020|1|0|United_States_of_America|US|USA|327167434.0
7138|01/02/2020|1|2|2020|1|0|United_States_of_America|US|USA|327167434.0
7139|31/01/2020|31|1|2020|1|0|United_States_of_America|US|USA|327167434.0
7140|30/01/2020|30|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7141|29/01/2020|29|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7142|28/01/2020|28|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7143|27/01/2020|27|1|2020|3|0|United_States_of_America|US|USA|327167434.0
7144|26/01/2020|26|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7145|25/01/2020|25|1|2020|1|0|United_States_of_America|US|USA|327167434.0
7146|24/01/2020|24|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7147|23/01/2020|23|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7148|22/01/2020|22|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7149|21/01/2020|21|1|2020|1|0|United_States_of_America|US|USA|327167434.0
7150|20/01/2020|20|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7151|19/01/2020|19|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7152|18/01/2020|18|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7153|17/01/2020|17|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7154|16/01/2020|16|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7155|15/01/2020|15|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7156|14/01/2020|14|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7157|13/01/2020|13|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7158|12/01/2020|12|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7159|11/01/2020|11|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7160|10/01/2020|10|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7161|09/01/2020|9|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7162|08/01/2020|8|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7163|07/01/2020|7|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7164|06/01/2020|6|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7165|05/01/2020|5|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7166|04/01/2020|4|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7167|03/01/2020|3|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7168|02/01/2020|2|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7169|01/01/2020|1|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7170|31/12/2019|31|12|2019|0|0|United_States_of_America|US|USA|327167434.0
sqlite>

countriesAndTerritories 列匹配United_States_of_America 的所有数据都在那里！我们成功地将数据从 DataFrame 导出到 SQLite 数据库文件中。

下一步是什么？

我们只是将数据从 CSV 导入到 pandas DataFrame，选择该数据的一个子集，然后将其保存到关系数据库。

您应该查看通过探索 COVID-19 数据来学习 pandas 教程，以了解有关如何从更大的 DataFrame 中选择数据子集的更多信息，或者前往 pandas 页面以获取 Python 社区其他成员提供的更多教程。

您还可以通过阅读 Full Stack Python 目录页面了解在您的 Python 项目中接下来要编写什么代码。

有问题吗？通过 Twitter@fullstackpython 或@mattmakai 与我联系。我也在 GitHub 上，用户名是 mattmakai。

这篇文章有问题吗？在 GitHub 上创建此页面的源代码并提交拉取请求。

开放的编程资料库

使用 SQLAlchemy 将 pandas DataFrame 导出到 SQLite

配置我们的开发环境

获取 COVID-19 数据

将 CSV 文件导入 pandas

从原来的DataFrame创建一个新的DataFrame

将 DataFrame 保存到 SQLite

下一步是什么？

感觉很棒！可以赞赏支持我哟~

相关推荐

热门标签

近期文章

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏

回顶部