Python 使用 sqlite3 介绍

简介

SQLite 是一个自包含的基于文件的 SQL 数据库。SQLite 随 Python 一起捆绑发布，可以在任何 Python 应用程序中使用，无需安装其他软件。

在本教程中，我们将介绍 Python 3 中的 sqlite3 模块。我们将创建与 SQLite 数据库的连接，向该数据库添加表，插入数据，并读取和修改表中的数据。

在本教程中，我们将主要使用一个虚构的水族馆中的鱼类清单进行操作，当鱼类被添加或从水族馆中移除时需要对其进行修改。

参考资料

• How To Use the sqlite3 Module in Python 3，这是本文主要参考的内容；
• sqlite3 --- SQLite 数据库 DB-API 2.0 接口模块，sqlite3 官方文档；

Python SQLite 使用案例

第一步 — 创建与 SQLite 数据库的连接

当我们连接到 SQLite 数据库时，实际上是访问计算机上的一个文件中的数据。SQLite 数据库是完整的 SQL 引擎，可用于许多目的。在本教程中，我们将考虑一个虚构水族馆中鱼类清单的数据库。

我们可以使用 Python 的 sqlite3 模块连接到 SQLite 数据库：

在上面的代码中：

• import sqlite3 让我们的 Python 程序可以访问 sqlite3 模块。
• sqlite3.connect() 函数返回一个 Connection 对象，我们将使用它来与存储在名为 aquarium.db 的文件中的 SQLite 数据库进行交互。
• 如果计算机上尚未存在 aquarium.db，sqlite3.connect() 会自动创建该文件。

第二步 — 向 SQLite 数据库添加数据

现在，我们已经连接到 aquarium.db 的 SQLite 数据库，可以开始向其中插入和读取数据了。

在 SQL 数据库中，数据存储在表中。表定义了一组列，并包含了每个定义列的数据的 0 个或多个行。我们将创建一个名为 fish 的表，用于跟踪以下数据：

fish 表将跟踪水族馆中每条鱼的名称、物种和水缸编号。下面列出了两个示例鱼的行：一行是名为 Sammy 的鲨鱼（shark），一行是名为 Jamie 的乌贼（cuttlefish）。

我们可以使用第一步中创建的连接在 SQLite 中创建这个 fish 表：

在上面的代码中：

• connection.cursor() 返回一个 Cursor 对象。Cursor 对象允许我们使用 cursor.execute() 将 SQL 语句发送到 SQLite 数据库。
• 字符串 "CREATE TABLE fish ..." 是一个 SQL 语句，用于创建一个名为 fish 的表，其中包含前面描述的三列：类型为 TEXT 的 name、类型为 TEXT 的 species 和类型为 INTEGER 的 tank_number。

现在我们已经创建了一个表，可以向其中插入数据行：

我们调用 cursor.execute() 两次：

• "INSERT INTO fish VALUES ..." 是一个 SQL 语句，允许我们向表中添加行。
• 一次插入一个行，表示鲨鱼 Sammy 在水缸 1 中；
• 一次插入一行，表示乌贼 Jamie 在水缸 7 中。

在下一节中，我们将使用 SQL SELECT 语句检查刚刚插入到 fish 表中的行。

第三步 — 从 SQLite 数据库读取数据

在第二步中，我们向一个名为 fish 的 SQLite 表中添加了两行数据。我们可以使用 SELECT SQL 语句检索这些行：

如果我们运行这段代码，将会看到如下输出：

如果我们想要检索 fish 表中符合特定条件的行，我们可以使用 WHERE 子句：

如果我们运行这段代码，将会看到如下输出：

与之前的示例类似，cursor.execute(<SQL 语句>).fetchall() 允许我们获取 SELECT 语句的所有结果。SELECT 语句中的 WHERE 子句根据 name 的值过滤行。

请注意，我们使用 ? 将 target_fish_name 变量替换到 SELECT 语句中。我们预期只匹配一行，确实只返回了 Jamie 这只乌贼的行。

第四步 — 修改 SQLite 数据库中的数据

可以使用 UPDATE 和 DELETE 的 SQL 语句修改 SQLite 数据库中的行。

更新数据表里的值

例如，假设 Sammy 这只鲨鱼被移动到了水缸编号为 2 的位置。我们可以更改 fish 表中 Sammy 的行以反映这个变化：

我们发出一个 UPDATE 的 SQL 语句，将 Sammy 的 tank_number 更改为新值 2。UPDATE 语句中的 WHERE 子句确保仅在行具有 name = "Sammy" 的情况下才更改 tank_number 的值。

此时如果我们运行以下 SELECT 语句，可以确认我们的更新操作已经正确执行：

如果我们运行这段代码，将会看到如下输出：

可以看到此时 Sammy 的行现在 tank_number 列的值为 2。

删除数据表中的行

假设 Sammy 这只鲨鱼已经被放生到野外，不再由水族馆保管。由于 Sammy 不再在水族馆中，从 fish 表中删除 Sammy 的行是合理的。

我们发出 DELETE 这个 SQL 语句以删除一行：

我们发出一个 DELETE SQL 语句，以删除鲨鱼 Sammy 的行。DELETE 语句中的 WHERE 子句确保仅在行具有 name = "Sammy" 的情况下才删除该行。

如果我们运行以下 SELECT 语句，可以确认我们的删除操作已经正确执行：

如果我们运行这段代码，将会看到如下输出：

第五步 — 使用 with 语句进行自动清理

在本教程中，我们使用了两个主要对象来与 aquarium.db 的 SQLite 数据库进行交互：一个名为 connection 的 Connection 对象和一个名为 cursor 的 Cursor 对象。

与 Python 文件在完成工作后应关闭一样，当不再需要 Connection 和 Cursor 对象时，也应该将它们关闭。我们可以使用 with 语句来帮助我们自动关闭 Connection 和 Cursor 对象：

closing 是 contextlib 模块提供的一个便利函数。当一个 with 语句退出时，closing 确保在其参数上调用 close()。

在此示例中，closing 函数被使用两次。

• 一次用于确保由 sqlite3.connect() 返回的 Connection 对象自动关闭；
• 另一次用于确保由 connection.cursor() 返回的 Cursor 对象自动关闭。

总结

sqlite3 模块是 Python 标准库中功能强大的一部分；它使我们能够在磁盘上使用一个完整的 SQL 数据库，而无需安装任何其他软件。

在本教程中，我们学习了如何使用 sqlite3 模块连接到 SQLite 数据库，向数据库中添加数据，并读取和修改数据库中的数据。在此过程中，我们还了解了如何使用 contextlib.closing 在 with 语句中自动调用 Python 对象的 close() 方法。