SQLite是最受欢迎的关系数据库管理系统（RDBMS）之一。它体积小，意味着在您的系统上占用的空间很少。它最出色的特点之一是无需服务器，因此您无需安装或管理一个单独的服务器即可使用它。
相反，它将所有内容存储在您计算机上的一个简单文件中。它还无需任何配置，因此没有复杂的设置过程，使其非常适合初学者和小项目。
SQLite是小型到中型应用程序的一个非常好的选择，因为它易于使用，速度快，并且可以处理大多数大型数据库可以执行的任务，而无需管理额外的软件。无论您是在构建个人项目还是原型设计新应用程序，SQLite都是一个可靠的选择，可以帮助您快速启动和运行。
在本教程中，您将学习如何使用Python与SQLite数据库一起工作。在本教程中，我们将介绍以下内容：
本教程非常适合希望不深入复杂设置就能开始学习数据库的人。
如何设置Python环境
在开始使用SQLite之前，让我们确保您的Python环境已经准备就绪。以下是设置所有内容的步骤。
安装Python
如果您还没有在系统上安装Python，您可以从官方Python网站下载。按照您操作系统的安装说明（Windows、macOS或Linux）。
要检查Python是否已安装，请打开您的终端（或命令提示符）并输入：
python --version
这应该显示已安装的Python当前版本。如果没有安装，请遵循Python网站上的说明。
安装SQLite3模块
好消息是，SQLite3 随Python一起内置了！您不需要单独安装，因为它包含在标准Python库中。这意味着您可以直接开始使用它，无需任何额外的设置。
如何创建虚拟环境（可选但推荐）
为每个项目创建一个虚拟环境是一个好主意，这样可以使您的依赖关系保持有序。虚拟环境就像是一张干净的白纸，您可以在不影响到全局Python安装的情况下安装包。
要创建虚拟环境，请按照以下步骤操作：
-
首先，打开您的终端或命令提示符，并导航到您想要创建项目的目录。
-
运行以下命令来创建一个虚拟环境：
python -m venv env
这里，env 是虚拟环境的名称。您可以随意为其命名。
- 激活虚拟环境：
# 使用以下命令适用于Windows
env\Scripts\activate
# 使用以下命令适用于macOS/Linux：
env/bin/activate
激活虚拟环境后，您会注意到您的终端提示发生了变化，显示了虚拟环境的名称。这意味着您现在正在它内部工作。
安装必要的库
我们将需要一些额外的库来完成这个项目。具体来说，我们将使用：
-
pandas：这是一个用于处理和显示数据表格格式的可选库，对于高级用例非常有用。 -
faker：这个库将帮助我们生成假数据，如随机名称和地址，我们可以将其插入到我们的数据库中进行测试。
要安装pandas和faker，只需运行以下命令：
pip install pandas faker
这将pandas和faker安装到您的虚拟环境中。有了这个，您的环境设置完成，您准备好开始在Python中创建和管理SQLite数据库了！
如何创建SQLite数据库
数据库是一种结构化的存储和管理数据的方式，以便可以轻松访问、更新和组织。它就像一个数字文件系统，允许您高效地存储大量数据，无论是用于简单的应用程序还是更复杂的系统。数据库使用表来组织数据，行和列代表单个记录及其属性。
SQLite数据库是如何工作的
与大多数其他数据库系统不同，SQLite 是一种无服务器数据库。这意味着它不需要设置或管理服务器，使其轻量级且易于使用。所有数据都存储在您计算机上的单个文件中，您可以轻松地移动、共享或备份它。尽管它简单，但 SQLite 足够强大，可以处理许多常见的数据库任务，并被广泛应用于移动应用程序、嵌入式系统和中小型项目中。
如何创建新的 SQLite 数据库
让我们使用 Python 的 sqlite3 库创建一个新的 SQLite 数据库并学习如何与之交互。
连接到数据库
由于 sqlite3 已经预装，您只需要在 Python 脚本中导入它。要创建新数据库或连接到现有数据库，我们使用 sqlite3.connect() 方法。此方法需要数据库文件名作为参数。如果文件不存在，SQLite 将自动创建它。
import sqlite3
# 连接到 SQLite 数据库（如果不存在则创建）
connection = sqlite3.connect('my_database.db')
在这个例子中，将创建一个名为 my_database.db 的文件，该文件与您的脚本位于同一目录中。如果文件已存在，SQLite 只需打开到它的连接。
创建游标
一旦建立了连接，下一步是创建一个游标对象。游标负责在数据库上执行 SQL 命令和查询。
# 创建一个游标对象
cursor = connection.cursor()
关闭连接
完成数据库操作后，关闭连接以释放资源是非常重要的。您可以使用以下命令关闭连接：
# 关闭数据库连接
connection.close()
但是，您应该在完成所有操作后才关闭连接。
当您运行Python脚本时，一个名为my_database.db的文件将在您的当前工作目录中创建。现在您已经成功创建了第一个SQLite数据库！
使用上下文管理器打开和关闭连接的方法
Python通过with语句（也称为上下文管理器）提供了处理数据库连接更高效、更清洁的方法。with语句会自动打开和关闭连接，确保即使在数据库操作过程中出现错误，连接也能正确关闭。这消除了手动调用connection.close()的需要。
以下是使用with语句处理数据库连接的方法：
import sqlite3
# 步骤1：使用'with'连接到数据库（或创建一个），并在完成后自动关闭它
with sqlite3.connect('my_database.db') as connection:
# 步骤2：创建一个游标对象来与数据库交互
cursor = connection.cursor()
print("Database created and connected successfully!")
# 无需调用connection.close(); 它会自动完成！
从现在开始，在我们即将举得的代码示例中，我们将使用with语句来高效地管理数据库连接。这将使代码更简洁，也更容易维护。
如何创建数据库表
既然我们已经创建了一个SQLite数据库并且连接到了它，下一步就是在这个数据库中创建表。表是我们存储数据的地方，数据以行（记录）和列（属性）的形式组织。作为这个示例，我们将创建一个名为Students的表，用于存储有关学生的信息，我们将在接下来的部分中重复使用它。
要创建一个表，我们使用SQL的CREATE TABLE语句。这个命令定义了表结构，包括每个列的列名和数据类型。
下面是一个简单的SQL命令，用于创建带有以下字段的Students表：
-
id：每个学生的唯一标识符（整数）。 -
name：学生的姓名（文本）。
-
age：学生的年龄（整数）。
-
email：学生的电子邮件地址（文本）。
创建这个表的SQL命令如下所示：
CREATE TABLE Students (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
age INTEGER,
email TEXT
);
我们可以在Python中使用sqlite3库执行这个CREATE TABLESQL命令。让我们来看看如何操作。
import sqlite3
# 使用 'with' 连接到SQLite数据库，并在完成后自动关闭连接
with sqlite3.connect('my_database.db') as connection:
# 创建一个游标对象
cursor = connection.cursor()
# 编写创建Students表的SQL命令
create_table_query = '''
CREATE TABLE IF NOT EXISTS Students (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
age INTEGER,
email TEXT
);
'''
# 执行SQL命令
cursor.execute(create_table_query)
# 提交更改
connection.commit()
# 打印一条确认信息
print("Table 'Students' created successfully!")
-
IF NOT EXISTS：这确保了只在表不存在时创建表，防止如果表之前已创建时出现错误。 -
connection.commit()：这会将更改保存（提交）到数据库中。
当你运行上面的Python代码时，它将在my_database.db数据库文件中创建一个Students表。你还将看到终端中有一条信息，确认表已成功创建。
如果你正在使用Visual Studio Code，你可以安装SQLite Viewer扩展来查看SQLite数据库。
SQLite中的数据类型及其在Python中的映射
SQLite支持多种数据类型，在定义我们的表时，我们需要了解这些类型。以下是SQLite常见数据类型及其在Python中的等效类型的快速概述：
| SQLite数据类型 | 描述 | Python等效 |
| INTEGER | 整数 | int |
| TEXT | 文本字符串 | str |
| REAL | 浮点数 | float |
| BLOB | 二进制数据（例如，图片，文件） | bytes |
| NULL | 表示没有值或缺失数据 | None |
在我们的Students表中：
-
id是INTEGER类型，对应于Python的int。 -
name和email是TEXT类型，对应于Python的str。 -
age也是INTEGER类型，映射到Python中的int。
如何向表中插入数据
既然我们已经创建了Students表，是时候开始向数据库中插入数据了。在本节中，我们将介绍如何使用Python和SQLite插入单个和多个记录，以及如何使用参数化查询避免常见的SQL注入等安全问题。
插入单个记录
要向数据库插入数据，我们使用INSERT INTOSQL命令。让我们先向我们的Students表插入一个记录。
以下是插入单个记录的基本SQL语法：
INSERT INTO Students (name, age, email)
VALUES ('John Doe', 20, '[email protected]');
然而，我们不会直接在Python脚本中编写SQL代码和硬编码值，而是使用参数化查询来使我们的代码更加安全和灵活。参数化查询有助于防止SQL注入，这是一种常见的攻击方式，恶意用户可以通过传递有害输入来操纵SQL查询。
以下是我们如何使用参数化查询将单个记录插入到Students表中的示例：
import sqlite3
# 使用 'with' 来自动打开和关闭连接
with sqlite3.connect('my_database.db') as connection:
cursor = connection.cursor()
# 将记录插入到学生表中
insert_query = '''
INSERT INTO Students (name, age, email)
VALUES (?, ?, ?);
'''
student_data = ('Jane Doe', 23, '[email protected]')
cursor.execute(insert_query, student_data)
# 自动提交更改
connection.commit()
# 无需调用 connection.close(); 它会自动完成!
print("Record inserted successfully!")
问号 `?` 占位符代表要插入表中的值。实际的值作为元组（`student_data`）在 `cursor.execute()` 方法中传递。
如何插入多条记录
如果您想一次性插入多条记录，可以在 Python 中使用 `executemany()` 方法。该方法接受一个元组列表，其中每个元组代表一条记录。
为了让我们的示例更具动态性，我们可以使用 `Faker` 库来生成随机的的学生数据。这在测试和模拟真实世界场景时非常有用。
from faker import Faker
import sqlite3
# 初始化 Faker
fake = Faker(['en_IN'])
# 使用 'with' 来自动打开和关闭连接
with sqlite3.connect('my_database.db') as connection:
cursor = connection.cursor()
# 将记录插入到学生表中
insert_query = '''
INSERT INTO Students (name, age, email)
VALUES (?, ?, ?);
'''
students_data = [(fake.name(), fake.random_int(
min=18, max=25), fake.email()) for _ in range(5)]
# 执行多条记录的查询
cursor.executemany(insert_query, students_data)
# 提交更改
connection.commit()
# 打印确认信息
print("Fake student records inserted successfully!")
在此代码中：
-
`Faker()` 为学生生成随机的姓名、年龄和电子邮件地址。传递 locale（`[‘en_IN’]`）是可选的。
-
`cursor.executemany()`：此方法允许我们一次性插入多条记录，使代码更加高效。
-
`students_data`：一个元组的列表，每个元组代表一个学生的数据。
如何处理常见问题：SQL 注入
SQL 注入是一种安全漏洞，攻击者可以通过提供有害的输入来插入或操纵 SQL 查询。例如，攻击者可能会尝试注入如下代码 `’; DROP TABLE Students; –` 以删除表。
通过使用参数化查询（如上所示），我们避免了这个问题。参数化查询中的 `?` 占位符确保输入值被视为数据，而不是 SQL 命令的一部分。这使得恶意代码无法执行。
如何查询数据
既然我们已经将一些数据插入到我们的学生表中，那么让我们学习如何从表中检索数据。我们将在Python中探索不同的方法来获取数据，包括fetchone()，fetchall()和fetchmany()。
要从表中查询数据，我们使用SELECT语句。下面是一个简单的SQL命令，用于从学生表中选择所有列：
SELECT * FROM Students;
此命令从学生表中检索所有记录和列。我们可以在Python中执行这个SELECT查询并获取结果。
如何获取所有记录
以下是我们如何从学生表中获取所有记录的方法：
import sqlite3
# 使用'with'连接到SQLite数据库
with sqlite3.connect('my_database.db') as connection:
# 创建一个游标对象
cursor = connection.cursor()
# 编写SQL命令，从学生表中选择所有记录
select_query = "SELECT * FROM Students;"
# 执行SQL命令
cursor.execute(select_query)
# 获取所有记录
all_students = cursor.fetchall()
# 在终端中显示结果
print("All Students:")
for student in all_students:
print(student)
在这个例子中，fetchall()方法检索查询返回的所有行，作为一个元组的列表。
All Students:
(1, 'Jane Doe', 23, '[email protected]')
(2, 'Bahadurjit Sabharwal', 18, '[email protected]')
(3, 'Zayyan Arya', 20, '[email protected]')
(4, 'Hemani Shukla', 18, '[email protected]')
(5, 'Warda Kara', 20, '[email protected]')
(6, 'Mitali Nazareth', 19, '[email protected]')
如何获取单个记录
如果您只想检索一个记录，您可以使用fetchone()方法：
import sqlite3
# 使用 'with' 连接到 SQLite 数据库
with sqlite3.connect('my_database.db') as connection:
# 创建一个游标对象
cursor = connection.cursor()
# 编写 SQL 命令，从 Students 表中选择所有记录
select_query = "SELECT * FROM Students;"
# 执行 SQL 命令
cursor.execute(select_query)
# 获取一条记录
student = cursor.fetchone()
# 显示结果
print("First Student:")
print(student)
输出：
First Student:
(1, 'Jane Doe', 23, '[email protected]')
如何获取多条记录
要获取特定数量的记录，您可以使用 fetchmany(size) 方法：
import sqlite3
# 使用 'with' 连接到 SQLite 数据库
with sqlite3.connect('my_database.db') as connection:
# 创建一个游标对象
cursor = connection.cursor()
# 编写 SQL 命令，从 Students 表中选择所有记录
select_query = "SELECT * FROM Students;"
# 执行 SQL 命令
cursor.execute(select_query)
# 获取三条记录
three_students = cursor.fetchmany(3)
# 显示结果
print("Three Students:")
for student in three_students:
print(student)
输出：
Three Students:
(1, 'Jane Doe', 23, '[email protected]')
(2, 'Bahadurjit Sabharwal', 18, '[email protected]')
(3, 'Zayyan Arya', 20, '[email protected]')
如何使用 pandas 进行更好的数据展示
为了更好的数据展示，我们可以使用 pandas 库将从我们的查询结果中创建一个 DataFrame。这使得数据操作和可视化变得更加容易。
以下是如何获取所有记录并将其显示为 pandas DataFrame 的方法：
import sqlite3
import pandas as pd
# 使用 'with' 连接到SQLite数据库
with sqlite3.connect('my_database.db') as connection:
# 编写SQL命令，从Students表中选择所有记录
select_query = "SELECT * FROM Students;"
# 使用pandas直接将SQL查询读取到DataFrame中
df = pd.read_sql_query(select_query, connection)
# 显示DataFrame
print("All Students as DataFrame:")
print(df)
输出：
All Students as DataFrame:
id name age email
0 1 Jane Doe 23 [email protected]
1 2 Bahadurjit Sabharwal 18 [email protected]
2 3 Zayyan Arya 20 [email protected]
3 4 Hemani Shukla 18 [email protected]
4 5 Warda Kara 20 [email protected]
5 6 Mitali Nazareth 19 [email protected]
pd.read_sql_query() 函数执行SQL查询，并直接将结果作为pandas DataFrame返回。
如何更新和删除数据
在本节中，我们将学习如何使用Python中的SQL命令更新Students表中的现有记录和删除记录。这对于有效地管理和维护您的数据至关重要。
更新现有记录
要修改数据库中的现有记录，我们使用SQL UPDATE 命令。该命令允许我们根据指定条件更改一个或多个行中特定列的值。
例如，如果我们想要更新一个学生的年龄，SQL命令看起来像这样：
UPDATE Students
SET age = 21
WHERE name = 'Jane Doe';
现在，让我们编写Python代码来更新我们Students表中特定学生的年龄。
import sqlite3
# 使用 'with' 连接到 SQLite 数据库
with sqlite3.connect('my_database.db') as connection:
cursor = connection.cursor()
# 更新学生年龄的 SQL 命令
update_query = '''
UPDATE Students
SET age = ?
WHERE name = ?;
'''
# 更新的数据
new_age = 21
student_name = 'Jane Doe'
# 使用数据执行 SQL 命令
cursor.execute(update_query, (new_age, student_name))
# 提交更改以保存更新
connection.commit()
# 打印确认信息
print(f"Updated age for {student_name} to {new_age}.")
在这个例子中，我们使用了参数化查询来防止 SQL 注入。
如何从表中删除记录
要从数据库中删除记录，我们使用 SQL DELETE 命令。这个命令允许我们根据特定条件删除一个或多个行。
例如，如果我们想要删除一个名叫 ‘Jane Doe’ 的学生，SQL 命令如下所示：
DELETE FROM Students
WHERE name = 'Jane Doe';
让我们用 Python 代码删除 Students 表中的特定学生，使用 with 语句。
import sqlite3
# 使用 'with' 连接到 SQLite 数据库
with sqlite3.connect('my_database.db') as connection:
cursor = connection.cursor()
# 删除学生的 SQL 命令
delete_query = '''
DELETE FROM Students
WHERE name = ?;
'''
# 要删除的学生姓名
student_name = 'Jane Doe'
# 使用数据执行 SQL 命令
cursor.execute(delete_query, (student_name,))
# 提交更改以保存删除
connection.commit()
# 打印确认信息
print(f"Deleted student record for {student_name}.")
重要注意事项
-
条件：在更新或删除记录时总是使用
WHERE子句，以避免修改或删除表中的所有行。没有WHERE子句，该命令会影响表中的每一行。
-
备份：在执行更新或删除操作之前，特别是在生产环境中，对数据库进行备份是一个好的实践。
如何使用事务
事务是一系列一个或多个SQL操作的序列，被视为一个工作单元。在数据库的上下文中，事务允许您执行多个操作，这些操作要么全部成功，要么全部失败。这确保了您的数据库即使在出现错误或意外问题时也能保持一致状态。
例如，如果您在两个银行账户之间转账，您希望从一个账户中扣除的金额和向另一个账户中的贷记要么一起成功，要么一起失败。如果一个操作失败，另一个操作不应该被执行，以保持一致性。
为什么使用事务？
-
原子性：事务确保一系列操作被视为单个单元。如果一个操作失败，数据库将不会应用任何操作。
-
一致性：事务通过确保遵循所有规则和约束来帮助维护数据库的完整性。
-
隔离性：每个事务独立运行，防止意外干扰。
-
持久性：一旦事务提交，更改将是永久的，即使系统发生故障。
何时使用事务？
应使用事务的情况包括：
-
执行多个相关操作，这些操作必须一起成功或失败。
-
修改需要一致性和完整性的关键数据。
-
处理可能失败的操作，例如财务交易或数据迁移。
如何在Python中管理事务
在SQLite中，事务是通过BEGIN、COMMIT和ROLLBACK命令来管理的。然而，在使用Python中的sqlite3模块时，通常是通过连接对象来管理事务。
开始一个事务
当您执行任何SQL语句时，事务隐式地开始。要显式地开始一个事务，您可以使用BEGIN命令：
cursor.execute("BEGIN;")
然而，通常不需要手动开始事务，因为SQLite在您执行SQL语句时会自动开始一个事务。
如何提交事务
要保存事务期间所做的所有更改，您使用commit()方法。这使得所有修改永久保存在数据库中。
connection.commit()
我们已经在上面提供的示例中使用了commit()方法。
回滚事务
如果出现错误或您想要撤销事务期间所做的更改，您可以使用rollback()方法。这将撤销自事务开始以来的所有更改。
connection.rollback()
使用Python进行事务操作的示例。
为了说明事务在现实世界场景中的使用，我们将创建一个名为Customers的新表来管理客户账户。在这个例子中，我们假设每个客户都有一个balance。我们将向这个表中添加两名客户并执行他们之间的资金转账操作。
首先，让我们创建Customers表并插入两名客户：
import sqlite3
# 创建Customers表并添加两名客户
with sqlite3.connect('my_database.db') as connection:
cursor = connection.cursor()
# 创建Customers表
create_customers_table = '''
CREATE TABLE IF NOT EXISTS Customers (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE,
balance REAL NOT NULL
);
'''
cursor.execute(create_customers_table)
# 插入两名客户
cursor.execute(
"INSERT INTO Customers (name, balance) VALUES (?, ?);", ('Ashutosh', 100.0))
cursor.execute(
"INSERT INTO Customers (name, balance) VALUES (?, ?);", ('Krishna', 50.0))
connection.commit()
现在，让我们在Ashutosh和Krishna之间执行资金转账操作：
import sqlite3
def transfer_funds(from_customer, to_customer, amount):
with sqlite3.connect('my_database.db') as connection:
cursor = connection.cursor()
try:
# 开始一个事务
cursor.execute("BEGIN;")
# 从发送者扣除金额
cursor.execute(
"UPDATE Customers SET balance = balance - ? WHERE name = ?;", (amount, from_customer))
# 向接收者添加金额
cursor.execute(
"UPDATE Customers SET balance = balance + ? WHERE name = ?;", (amount, to_customer))
# 提交更改
connection.commit()
print(
f"Transferred {amount} from {from_customer} to {to_customer}.")
except Exception as e:
# 如果发生错误，回滚事务
connection.rollback()
print(f"Transaction failed: {e}")
# 示例用法
transfer_funds('Ashutosh', 'Krishna', 80.0)
在这个示例中，我们首先创建了一个Customers表，并插入了两位客户，Ashutosh账户余额为₹100，Krishna账户余额为₹50。然后我们从Ashutosh账户转出₹80到Krishna账户。通过使用事务，我们确保了从Ashutosh账户的贷方和Krishna账户的借方操作作为一个单一的原子操作执行，以防任何错误发生，从而保持了数据完整性。如果转账失败（例如，由于资金不足），事务将会回滚，使得两个账户保持不变。
如何通过索引优化SQLite查询性能
索引是一种强大的技术，用于提高数据库中的查询性能。索引本质上是一种数据结构，它根据特定列值存储行的位置，就像书籍末尾的索引能帮助你快速找到主题一样。
没有索引的情况下，SQLite必须逐行扫描整个表以找到相关数据，这在数据集增长时变得效率低下。使用索引，SQLite可以直接跳转到所需的行，显著加快查询执行速度。
如何用假数据填充数据库
为了有效地测试索引的影响，我们需要一个足够大的数据集。手动添加记录既费时又费力，我们可以使用faker库快速生成假数据。在本节中，我们将生成10,000条假记录并插入到我们的Students表中。这将模拟现实世界中的场景，数据库变得很大，查询性能变得很重要。
我们将使用`executemany()`方法插入记录，如下所示：
import sqlite3
from faker import Faker
# 初始化Faker库
fake = Faker(['en_IN'])
def insert_fake_students(num_records):
"""Generate and insert fake student data into the Students table."""
fake_data = [(fake.name(), fake.random_int(min=18, max=25),
fake.email()) for _ in range(num_records)]
# 使用'with'处理数据库连接
with sqlite3.connect('my_database.db') as connection:
cursor = connection.cursor()
# 将假数据插入到Students表中
cursor.executemany('''
INSERT INTO Students (name, age, email)
VALUES (?, ?, ?);
''', fake_data)
connection.commit()
print(f"{num_records} fake student records inserted successfully.")
# 在Students表中插入10,000条假记录
insert_fake_students(10000)
运行此脚本将在Students表中添加10,000条假学生记录。在下一节中，我们将查询数据库并比较带索引和不带索引的查询性能。
没有索引的查询方式
在本节中，我们将没有任何索引地查询Students表，以观察当没有任何优化措施时SQLite的性能。这将作为后来添加索引时的基准来比较性能。
没有索引时，SQLite执行全表扫描，这意味着它必须检查表中的每一行以找到匹配的结果。对于小数据集，这是可以管理的，但随着记录数量的增加，搜索所需的时间会增加 dramatically。让我们通过运行一个基本的`SELECT`查询来查找特定名称的学生并测量它需要多长时间来观察这一点。
首先，我们将查询`Students`表，寻找具有特定名称的学生。我们将使用Python的`time`模块记录查询执行所需的时间，以测量性能。
import sqlite3
import time
def query_without_index(search_name):
"""Query the Students table by name without an index and measure the time taken."""
# 使用 'with' 连接数据库
with sqlite3.connect('my_database.db') as connection:
cursor = connection.cursor()
# 测量开始时间
start_time = time.perf_counter_ns()
# 执行 SELECT 查询，按姓名查找学生
cursor.execute('''
SELECT * FROM Students WHERE name = ?;
''', (search_name,))
# 获取所有结果（实际上应该只有一个或几个）
results = cursor.fetchall()
# 测量结束时间
end_time = time.perf_counter_ns()
# 计算总耗时
elapsed_time = (end_time - start_time) / 1000
# 显示结果和耗时
print(f"Query completed in {elapsed_time:.5f} microseconds.")
print("Results:", results)
# 示例：按姓名查找学生
query_without_index('Ojasvi Dhawan')
输出结果如下：
Query completed in 1578.10000 microseconds.
Results: [(104, 'Ojasvi Dhawan', 21, '[email protected]')]
运行上述脚本，您可以了解在没有索引的情况下查询Students表所需的时间。例如，如果表中有10,000条记录，查询可能需要1000-2000微秒，这取决于表的大小和您的硬件配置。对于小数据集来说，这可能看起来并不慢，但随着记录的增加，性能将会下降。
我们使用time.perf_counter_ns()以纳秒为单位测量查询执行所需的时间。这种方法对于基准测试小时间间隔非常准确。我们将时间转换为微秒（us），以便于阅读。
介绍查询计划
在使用数据库时，了解查询是如何执行的可以帮助您识别性能瓶颈并优化您的代码。SQLite为此提供了一个有用的工具，称为EXPLAIN QUERY PLAN，它允许您分析SQLite检索数据的步骤。
在本节中，我们将介绍如何使用EXPLAIN QUERY PLAN来可视化和理解查询的内部工作方式——具体来说，当没有索引存在时，SQLite如何执行全表扫描。
让我们使用EXPLAIN QUERY PLAN来查看没有索引的Students表时SQLite如何检索数据。我们将通过姓名搜索学生，查询计划将揭示SQLite找到匹配行的步骤。
import sqlite3
def explain_query(search_name):
"""Explain the query execution plan for a SELECT query without an index."""
with sqlite3.connect('my_database.db') as connection:
cursor = connection.cursor()
# 使用 EXPLAIN QUERY PLAN 分析查询的执行方式
cursor.execute('''
EXPLAIN QUERY PLAN
SELECT * FROM Students WHERE name = ?;
''', (search_name,))
# 获取并显示查询计划
query_plan = cursor.fetchall()
print("Query Plan:")
for step in query_plan:
print(step)
# 示例：分析按姓名搜索的查询计划
explain_query('Ojasvi Dhawan')
当你运行这段代码时，SQLite将返回一个关于如何执行查询的详细 breakdown。以下是输出可能看起来像的样子：
Query Plan:
(2, 0, 0, 'SCAN Students')
这表示SQLite计划执行全表扫描来查找name列与提供的值（Ojasvi Dhawan）匹配的行。由于name列上没有索引，SQLite必须检查表中的每一行。
如何创建索引
创建索引可以在查询操作期间使SQLite更快地找到行。SQLite可以利用索引直接跳到相关行，从而显著加快查询速度，尤其是涉及到大数据集的查询。
要创建索引，请使用以下SQL命令：
CREATE INDEX IF NOT EXISTS index-name ON table (column(s));
在此示例中，将在Students表的name列上创建索引。您可以使用以下Python方法来执行此操作：
import sqlite3
import time
def create_index():
"""Create an index on the name column of the Students table."""
with sqlite3.connect('my_database.db') as connection:
cursor = connection.cursor()
# 在name列上创建索引的SQL命令
create_index_query = '''
CREATE INDEX IF NOT EXISTS idx_name ON Students (name);
'''
# 测量开始时间
start_time = time.perf_counter_ns()
# 执行创建索引的SQL命令
cursor.execute(create_index_query)
# 测量开始时间
end_time = time.perf_counter_ns()
# 提交更改
connection.commit()
print("Index on 'name' column created successfully!")
# 计算总耗时
elapsed_time = (end_time - start_time) / 1000
# 显示结果及耗时
print(f"Query completed in {elapsed_time:.5f} microseconds.")
# 调用创建索引的函数
create_index()
输出：
Index on 'name' column created successfully!
Query completed in 102768.60000 microseconds.
尽管创建索引可能需要这么长时间（102768.6微秒），但这是一次性操作。在执行多个查询时，您仍然会获得很大的速度提升。在接下来的部分中，我们将再次查询数据库以观察由此索引带来的性能改进。
如何带索引查询
在本节中，我们将执行与之前相同的SELECT查询，但这次我们将利用在Students表的name列上创建的索引。我们将测量并记录执行时间，以观察索引提供的性能改进。
import sqlite3
import time
def query_with_index(student_name):
"""Query the Students table using an index on the name column."""
with sqlite3.connect('my_database.db') as connection:
cursor = connection.cursor()
# 通过姓名选择学生的SQL命令
select_query = 'SELECT * FROM Students WHERE name = ?;'
# 测量执行时间
start_time = time.perf_counter_ns() # 开始计时
# 使用提供的学生姓名执行查询
cursor.execute(select_query, (student_name,))
result = cursor.fetchall() # 获取所有结果
end_time = time.perf_counter_ns() # 结束计时
# 以微秒为单位计算经过的时间
execution_time = (end_time - start_time) / 1000
# 显示结果和执行时间
print(f"Query result: {result}")
print(f"Execution time with index: {execution_time:.5f} microseconds")
# 示例：按姓名搜索学生
query_with_index('Ojasvi Dhawan')
以下是我们在输出中得到的内容：
Query result: [(104, 'Ojasvi Dhawan', 21, '[email protected]')]
Execution time with index: 390.70000 microseconds
我们可以观察到，与未使用索引执行查询时相比，执行时间有显著减少。
让我们分析一下在Students表的name列上使用索引的查询执行计划。如果你再次执行相同的脚本来解释查询，你会得到下面的输出：
Query Plan:
(3, 0, 0, 'SEARCH Students USING INDEX idx_name (name=?)')
现在计划显示查询使用了索引idx_name，大大减少了需要扫描的行数，从而加快了查询执行速度。
性能结果对比
现在，让我们总结一下带索引和不带索引查询时得到的性能结果。
执行时间对比
| 查询类型 | 执行时间（微秒） |
| 不带索引 | 1578.1 |
| 带索引 | 390.7 |
性能提升总结
-
带索引的查询比不带索引的查询大约快4.04倍。
-
在添加索引后，执行时间提高了大约75.24%。
使用索引的最佳实践
索引可以显著提高您的SQLite数据库的性能，但您应该明智地使用它们。在使用索引时，请考虑以下一些最佳实践：
何时以及为什么使用索引
-
频繁查询的列
SELECT：特别是在WHERE、JOIN和ORDER BY子句中频繁使用的列上使用索引。这是因为对这些列进行索引可以大幅减少查询执行时间。 -
唯一性约束：当你有必须保持唯一值的列（如用户名或电子邮件地址）时，创建索引可以有效地强制这一约束。
-
大数据集：对于记录数量庞大的表，索引变得越来越有益。它们实现了快速的查找，这对于数据增长时保持性能至关重要。
-
复合索引：考虑为过滤或按多个列排序的查询创建复合索引。例如，如果你经常按
姓名和年龄搜索学生，两个列上的索引可以优化此类查询。
索引的潜在缺点
虽然索引提供了显著的优势，但也存在一些潜在缺点：
-
插入/更新操作变慢：当你在带有索引的表中插入或更新记录时，SQLite也必须更新索引，这可能会减慢这些操作的速度。这是因为每次插入或更新都需要额外的开销来维护索引结构。
-
增加存储需求：索引会消耗额外的磁盘空间。对于大型表，存储成本可能会很高。在设计数据库架构时，特别是在存储资源有限的情况下，需要考虑这一点。
-
复杂索引管理：索引太多可能会使数据库管理变得复杂。这可能导致您拥有冗余的索引，而不是提高性能。定期回顾和优化您的索引是一个良好的实践。
索引是优化数据库查询的强大工具，但它们需要仔细考虑。在提高读取性能和潜在的写入操作开销之间保持平衡是关键。以下是实现此平衡的一些策略：
-
监控查询性能：使用SQLite的
EXPLAIN QUERY PLAN来分析您的查询在带索引和不带索引的情况下的性能。这可以帮助您确定哪些索引是有益的，哪些可能是不必要的。 -
定期维护：定期回顾您的索引，评估它们是否仍然需要。删除冗余或很少使用的索引，以简化您的数据库操作。
-
测试与评估：在将索引实施到生产环境之前，进行彻底的测试，以了解它们对读写操作的影响。
遵循这些最佳实践，您可以利用索引的好处，同时最小化潜在的缺点，从而最终提高SQLite数据库的性能和效率。
如何处理错误和异常
在本节中，我们将讨论在使用Python中的SQLite时如何处理错误和异常。正确的错误处理对于维护数据库的完整性以及确保应用程序的行为可预测至关重要。
SQLite操作中的常见错误
在与SQLite数据库交互时，可能会出现一些常见错误：
-
约束违反：当您尝试插入或更新违反数据库约束的数据时会发生此情况，例如主键唯一性或外键约束。例如，尝试插入重复的主键将触发错误。
-
数据类型不匹配：尝试插入错误类型的数据（例如，插入字符串而期望是数字）可能导致错误。
-
数据库锁定错误：如果数据库正在被另一个进程或连接写入，尝试访问它可能会导致“数据库已锁定”的错误。
-
语法错误：SQL语法错误会导致执行命令时出现错误。
如何使用Python的异常处理
Python 的内置异常处理机制（try 和 except）对于管理 SQLite 操作中的错误至关重要。通过使用这些构造，您可以在程序崩溃之前捕获异常并做出适当的响应。
以下是一个基本的例子，展示如何在插入数据时处理错误：
import sqlite3
def add_customer_with_error_handling(name, balance):
"""Add a new customer with error handling."""
try:
with sqlite3.connect('my_database.db') as connection:
cursor = connection.cursor()
cursor.execute(
"INSERT INTO Customers (name, balance) VALUES (?, ?);", (name, balance))
connection.commit()
print(f"Added customer: {name} with balance: {balance}")
except sqlite3.IntegrityError as e:
print(f"Error: Integrity constraint violated - {e}")
except sqlite3.OperationalError as e:
print(f"Error: Operational issue - {e}")
except Exception as e:
print(f"An unexpected error occurred: {e}")
# 示例用法
add_customer_with_error_handling('Vishakha', 100.0) # 有效
add_customer_with_error_handling('Vishakha', 150.0) # 重复条目
在这个例子中：
-
我们捕获了
IntegrityError，它用于表示违反唯一性约束等异常。 -
我们捕获了
OperationalError，用于处理一般数据库相关问题（如数据库锁定错误）。 -
我们还包含了一个通用的
except块，用于处理任何意外的异常。
输出：
Added customer: Vishakha with balance: 100.0
Error: Integrity constraint violated - UNIQUE constraint failed: Customers.name
确保数据库完整性的最佳实践
-
使用事务：在执行多个相关操作时，始终使用事务（如前一节所讨论的）。这有助于确保所有操作要么全部成功，要么全部失败，从而保持一致性。
-
验证输入数据：在执行SQL命令之前，验证输入数据以确保其符合预期的条件（例如，正确的类型，处于允许的范围内）。
-
捕获特定异常：始终捕获特定异常以适当地处理不同类型的错误。这将有助于更清晰地处理错误和调试。
-
记录错误：不仅仅将错误打印到控制台，考虑将其记录到文件或监控系统中。这将帮助您追踪生产中的问题。
-
优雅降级
: 设计您的应用程序以优雅地处理错误。如果一个操作失败，为用户提供有意义的反馈，而不是让应用程序崩溃。
-
定期备份数据: 定期备份您的数据库，以防在关键故障或损坏的情况下丢失数据。
-
使用预处理语句: 预处理语句有助于防止SQL注入攻击，还可以为重复查询提供更好的性能。
如何导出和导入数据 [进阶部分]
在本节中，我们将学习如何使用Python从SQLite数据库导出数据到常见的格式如CSV和JSON，以及如何从这些格式导入数据到SQLite。这对于数据分享、备份和与其他应用程序的集成非常有用。
从SQLite导出数据到CSV
使用Python内置库将数据导出到CSV（逗号分隔值）文件是一个简单的过程。CSV文件广泛用于数据存储和交换，使其成为导出数据的方便格式。
以下是将SQLite表中的数据导出到CSV文件的方法：
import sqlite3
import csv
def export_to_csv(file_name):
"""Export data from the Customers table to a CSV file."""
with sqlite3.connect('my_database.db') as connection:
cursor = connection.cursor()
执行查询以获取所有客户数据
cursor.execute("SELECT * FROM Customers;")
customers = cursor.fetchall()
将数据写入CSV文件
with open(file_name, 'w', newline='') as csv_file:
csv_writer = csv.writer(csv_file)
csv_writer.writerow(['ID', 'Name', 'Balance']) 写入标题行
csv_writer.writerows(customers) 写入数据行
print(f"Data exported successfully to {file_name}.")
示例用法
export_to_csv('customers.csv')
如何将数据导出到JSON文件：
同样，您也可以将数据导出到JSON（JavaScript对象表示法）文件，这是一种在网络应用程序中特别流行的数据交换格式。
以下是导出数据到JSON文件的示例：
import json
import sqlite3
def export_to_json(file_name):
"""Export data from the Customers table to a JSON file."""
with sqlite3.connect('my_database.db') as connection:
cursor = connection.cursor()
执行查询以获取所有客户数据
cursor.execute("SELECT * FROM Customers;")
customers = cursor.fetchall()
将数据转换为字典列表
customers_list = [{'ID': customer[0], 'Name': customer[1],
'Balance': customer[2]} for customer in customers]
将数据写入JSON文件
with open(file_name, 'w') as json_file:
json.dump(customers_list, json_file, indent=4)
print(f"Data exported successfully to {file_name}.")
示例用法
export_to_json('customers.json')
如何从CSV文件将数据导入SQLite：
您还可以将CSV文件中的数据导入SQLite数据库。这对于使用现有数据集填充数据库非常有用。
以下是从未知CSV文件导入数据的步骤：
import csv
import sqlite3
def import_from_csv(file_name):
"""Import data from a CSV file into the Customers table."""
with sqlite3.connect('my_database.db') as connection:
cursor = connection.cursor()
# 以读取模式打开CSV文件
with open(file_name, 'r') as csv_file:
csv_reader = csv.reader(csv_file)
next(csv_reader) # 跳过标题行
# 将每一行插入到Customers表中
for row in csv_reader:
cursor.execute(
"INSERT INTO Customers (name, balance) VALUES (?, ?);", (row[1], row[2]))
connection.commit()
print(f"Data imported successfully from {file_name}.")
# 示例用法
import_from_csv('customer_data.csv')
如何将数据从JSON导入到SQLite中
同样，从JSON文件导入数据也很简单。您可以读取JSON文件并将数据插入到您的SQLite表中。
以下是操作步骤：
import json
import sqlite3
def import_from_json(file_name):
"""Import data from a JSON file into the Customers table."""
with sqlite3.connect('my_database.db') as connection:
cursor = connection.cursor()
# 以读取模式打开JSON文件
with open(file_name, 'r') as json_file:
customers_list = json.load(json_file)
# 将每个客户信息插入到Customers表中
for customer in customers_list:
cursor.execute("INSERT INTO Customers (name, balance) VALUES (?, ?);", (customer['Name'], customer['Balance']))
connection.commit()
print(f"Data imported successfully from {file_name}.")
# 示例用法
import_from_json('customer_data.json')
总结
到此为止！本指南已经向您介绍了在Python中使用SQLite的基本知识，涵盖了从设置环境到查询和操作数据，以及导出和导入信息的所有内容。我希望您觉得它有帮助，并且它已经激发了您使用SQLite为您的项目工作的兴趣。
现在该是将您新学到的知识付诸实践的时候了！我鼓励您使用SQLite和Python创建您的项目。无论是用于管理图书馆的简单应用程序，预算工具，还是其他独特的东西，可能性都是无限的。
一旦您完成了项目，请在Twitter上分享并标记我！我很乐意看看您创造了什么，并庆祝您的成就。
您可以在GitHub上找到本教程的所有代码。感谢您跟随教程，祝您编程愉快！
使用TOC生成器工具，免费为您的freeCodeCamp文章生成目录。
Source:
https://www.freecodecamp.org/news/work-with-sqlite-in-python-handbook/