作者选择了 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
表将跟踪水族馆中每条鱼的 name
、species
和 tank_number
的值。 列出了两个示例 fish
行:一行用于名为 Sammy
的 shark
,另一行用于名为 Jamie
的 cuttlefish
。
我们可以使用我们在步骤 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
语句以检索 [ 中 name
、species
和 tank_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 数据库中的数据
可以使用 UPDATE
和 DELETE
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 语句,将 Sammy
的 tank_number
更改为其新值 2
。 UPDATE
语句中的 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 语句来删除 Sammy
和 shark
的行。 DELETE
语句中的 WHERE
子句确保我们只删除具有 name = "Sammy"
的行。
如果我们运行以下 SELECT
语句,我们可以确认我们的删除是正确的:
rows = cursor.execute("SELECT name, species, tank_number FROM fish").fetchall() print(rows)
如果我们运行此代码,我们将看到如下输出:
Output[('Jamie', 'cuttlefish', 7)]
请注意,Sammy
和 shark
的行现在消失了,只剩下 Jamie
和 cuttlefish
。
第 5 步 — 使用 with
语句进行自动清理
在本教程中,我们使用了两个主要对象与 "aquarium.db"
SQLite 数据库进行交互:一个名为 connection
的 Connection 对象 和一个 Cursor 对象[X166X ] 命名为 cursor
。
就像我们处理完 Python 文件应该关闭它们一样,Connection
和 Cursor
对象在不再需要时也应该关闭。
我们可以使用 with
语句来帮助我们自动关闭 Connection
和 Cursor
对象:
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)
closing
是 contextlib 模块 提供的便利函数。 当 with
语句退出时,closing
确保在传递给它的任何对象上调用 close()
。 closing
函数在本例中使用了两次。 一次保证sqlite3.connect()
返回的Connection
对象自动关闭,第二次保证connection.cursor()
返回的Cursor
对象自动关闭.
如果我们运行此代码,我们将看到如下输出:
Output[(1,)]
由于 "SELECT 1"
是一条 SQL 语句,它始终返回单行和单列,其值为 1
,因此将 1
视为唯一的单个元组是有意义的我们的代码返回的值。
结论
sqlite3
模块是 Python 标准库的强大部分; 它使我们无需安装任何其他软件即可使用功能齐全的磁盘 SQL 数据库。
在本教程中,我们学习了如何使用 sqlite3
模块连接到 SQLite 数据库,向该数据库添加数据,以及读取和修改该数据库中的数据。 在此过程中,我们还了解了 SQL 注入攻击的风险以及如何使用 contextlib.closing 在 with
语句中的 Python 对象上自动调用 close()
。
从这里我们可以在 SQLite vs MySQL vs PostgreSQL:关系数据库管理系统的比较 中了解有关 SQL 数据库的更多信息。