如何在Python3中使用sqlite3模块

来自菜鸟教程
跳转至:导航、​搜索

作者选择了 COVID-19 Relief Fund 作为 Write for DOnations 计划的一部分来接受捐赠。

介绍

SQLite 是一个独立的、基于文件的 SQL 数据库。 SQLite 与 Python 捆绑在一起,可以在任何 Python 应用程序中使用,而无需安装任何额外的软件。

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

在本教程中,我们将主要处理鱼的库存,当鱼被添加到虚构的水族馆或从虚构的水族馆中移除时,我们需要对其进行修改。

先决条件

为了充分利用本教程,建议您熟悉 Python 编程和 SQL 的一些基本背景。

您可以查看这些教程以获取必要的背景信息:

第 1 步——创建到 SQLite 数据库的连接

当我们连接到 SQLite 数据库时,我们正在访问最终驻留在我们计算机上的文件中的数据。 SQLite 数据库是功能齐全的 SQL 引擎,可用于多种用途。 现在,我们将考虑一个跟踪虚构水族馆中鱼类库存的数据库。

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

import sqlite3

connection = sqlite3.connect("aquarium.db")

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

我们可以通过运行来验证我们是否成功创建了我们的 connection 对象:

print(connection.total_changes)

如果我们运行这个 Python 代码,我们将看到如下输出:

Output0

connection.total_changes 是由 connection 更改的数据库行总数。 由于我们还没有执行任何 SQL 命令,所以 0 total_changes 是正确的。

如果在任何时候,我们发现我们想重新开始本教程,我们可以从我们的计算机中删除 aquarium.db 文件。

注意: 也可以通过将特殊字符串 ":memory:" 传递到 sqlite3.connect() 来连接到严格驻留在内存中(而不是文件中)的 SQLite 数据库。 例如,sqlite3.connect(":memory:")。 一旦你的 Python 程序退出,一个 ":memory:" SQLite 数据库就会消失。 如果您想要一个临时沙箱在 SQLite 中尝试某些东西,并且在程序退出后不需要保留任何数据,这可能会很方便。


第 2 步 — 将数据添加到 SQLite 数据库

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

在 SQL 数据库中,数据存储在表中。 表定义了一组列,并包含 0 行或更多行,其中包含每个已定义列的数据。

我们将创建一个名为 fish 的表来跟踪以下数据:

姓名 物种 罐号
萨米 鲨鱼 1
杰米 乌贼 7

fish 表将跟踪水族馆中每条鱼的 namespeciestank_number 的值。 列出了两个示例 fish 行:一行用于名为 Sammyshark,另一行用于名为 Jamiecuttlefish

我们可以使用我们在步骤 1 中创建的 connection 在 SQLite 中创建这个 fish 表:

cursor = connection.cursor()
cursor.execute("CREATE TABLE fish (name TEXT, species TEXT, tank_number INTEGER)")

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

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

cursor.execute("INSERT INTO fish VALUES ('Sammy', 'shark', 1)")
cursor.execute("INSERT INTO fish VALUES ('Jamie', 'cuttlefish', 7)")

我们调用 cursor.execute() 两次:一次是在 1 中为墨鱼 Jamie 插入一行坦克 7"INSERT INTO fish VALUES ..." 是一个 SQL 语句,它允许我们向表中添加行。

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

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

在第 2 步中,我们向名为 fish 的 SQLite 表添加了两行。 我们可以使用 SELECT SQL 语句检索这些行:

rows = cursor.execute("SELECT name, species, tank_number FROM fish").fetchall()
print(rows)

如果我们运行此代码,我们将看到如下输出:

Output[('Sammy', 'shark', 1), ('Jamie', 'cuttlefish', 7)]

cursor.execute() 函数运行 SELECT 语句以检索 [ 中 namespeciestank_number 列的值X132X] 表。 fetchall() 检索 SELECT 语句的所有结果。 当我们 print(rows) 时,我们看到一个包含两个元组的列表。 每个元组有三个条目; 我们从 fish 表中选择的每一列都有一个条目。 这两个元组具有我们在步骤 2 中插入的数据:一个元组用于 Sammy shark,一个元组用于 Jamie cuttlefish

如果我们想检索 fish 表中匹配特定条件集的行,我们可以使用 WHERE 子句:

target_fish_name = "Jamie"
rows = cursor.execute(
    "SELECT name, species, tank_number FROM fish WHERE name = ?",
    (target_fish_name,),
).fetchall()
print(rows)

如果我们运行它,我们将看到如下输出:

Output[('Jamie', 'cuttlefish', 7)]

与前面的示例一样,cursor.execute(<SQL statement>).fetchall() 允许我们获取 SELECT 语句的所有结果。 SELECT 语句中的 WHERE 子句过滤 name 的值为 target_fish_name 的行。 请注意,我们使用 ?target_fish_name 变量替换为 SELECT 语句。 我们期望只匹配一行,实际上我们只看到 Jamie 返回的 cuttlefish 行。

警告: 切勿使用 Python 字符串操作来动态创建 SQL 语句字符串。 使用 Python 字符串操作组装 SQL 语句字符串会使您容易受到 SQL 注入攻击 。 SQL 注入攻击可用于窃取、更改或以其他方式修改存储在数据库中的数据。 始终在 SQL 语句中使用 ? 占位符来动态替换 Python 程序中的值。 将值元组作为第二个参数传递给 Cursor.execute() 以将您的值绑定到 SQL 语句。 此处以及本教程的其他部分也演示了这种替换模式。


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

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

例如,假设鲨鱼萨米被转移到 2 号油箱。 我们可以更改 fish 表中 Sammy 的行以反映此更改:

new_tank_number = 2
moved_fish_name = "Sammy"
cursor.execute(
    "UPDATE fish SET tank_number = ? WHERE name = ?",
    (new_tank_number, moved_fish_name)
)

我们发出 UPDATE SQL 语句,将 Sammytank_number 更改为其新值 2UPDATE 语句中的 WHERE 子句确保我们仅在行具有 name = "Sammy" 时才更改 tank_number 的值。

如果我们运行以下 SELECT 语句,我们可以确认我们的更新是正确的:

rows = cursor.execute("SELECT name, species, tank_number FROM fish").fetchall()
print(rows)

如果我们运行它,我们将看到如下输出:

Output[('Sammy', 'shark', 2), ('Jamie', 'cuttlefish', 7)]

请注意,Sammy 行的 tank_number 列现在具有 2 的值。

假设鲨鱼萨米被释放到野外,不再由水族馆持有。 由于 Sammy 不再住在水族馆,因此从 fish 表中删除 Sammy 行是有意义的。

发出 DELETE SQL 语句来删除一行:

released_fish_name = "Sammy"
cursor.execute(
    "DELETE FROM fish WHERE name = ?",
    (released_fish_name,)
)

我们发出 DELETE SQL 语句来删除 Sammyshark 的行。 DELETE 语句中的 WHERE 子句确保我们只删除具有 name = "Sammy" 的行。

如果我们运行以下 SELECT 语句,我们可以确认我们的删除是正确的:

rows = cursor.execute("SELECT name, species, tank_number FROM fish").fetchall()
print(rows)

如果我们运行此代码,我们将看到如下输出:

Output[('Jamie', 'cuttlefish', 7)]

请注意,Sammyshark 的行现在消失了,只剩下 Jamiecuttlefish

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

在本教程中,我们使用了两个主要对象与 "aquarium.db" SQLite 数据库进行交互:一个名为 connectionConnection 对象 和一个 Cursor 对象[X166X ] 命名为 cursor

就像我们处理完 Python 文件应该关闭它们一样,ConnectionCursor 对象在不再需要时也应该关闭。

我们可以使用 with 语句来帮助我们自动关闭 ConnectionCursor 对象:

from contextlib import closing

with closing(sqlite3.connect("aquarium.db")) as connection:
    with closing(connection.cursor()) as cursor:
        rows = cursor.execute("SELECT 1").fetchall()
        print(rows)

closingcontextlib 模块 提供的便利函数。 当 with 语句退出时,closing 确保在传递给它的任何对象上调用 close()closing 函数在本例中使用了两次。 一次保证sqlite3.connect()返回的Connection对象自动关闭,第二次保证connection.cursor()返回的Cursor对象自动关闭.

如果我们运行此代码,我们将看到如下输出:

Output[(1,)]

由于 "SELECT 1" 是一条 SQL 语句,它始终返回单行和单列,其值为 1,因此将 1 视为唯一的单个元组是有意义的我们的代码返回的值。

结论

sqlite3 模块是 Python 标准库的强大部分; 它使我们无需安装任何其他软件即可使用功能齐全的磁盘 SQL 数据库。

在本教程中,我们学习了如何使用 sqlite3 模块连接到 SQLite 数据库,向该数据库添加数据,以及读取和修改该数据库中的数据。 在此过程中,我们还了解了 SQL 注入攻击的风险以及如何使用 contextlib.closingwith 语句中的 Python 对象上自动调用 close()

从这里我们可以在 SQLite vs MySQL vs PostgreSQL:关系数据库管理系统的比较 中了解有关 SQL 数据库的更多信息。