如何使用 Python 的 sqlite3 模块操作 SQLite 数据库？

如何使用 Python 的 sqlite3 模块操作 SQLite 数据库？

SQLite 是一种轻量级的数据库，它不需要服务器，数据库被存储在一个文件中，非常适合嵌入式系统或桌面应用程序。Python 标准库中包含了一个名为 sqlite3 的模块，可以直接用于与 SQLite 数据库进行交互。本文将详细介绍如何使用 Python 的 sqlite3 模块来操作 SQLite 数据库，适合初学者和有数据库基础的人理解。

一、SQLite 简介

1.1 什么是 SQLite？

SQLite 是一种自包含、无服务器、零配置的关系型数据库管理系统（RDBMS）。它的特点是数据存储在一个单一的文件中，不需要像 MySQL、PostgreSQL 等那样配置复杂的数据库服务器，非常适合轻量级应用或快速开发时使用。

主要特点：

• 轻量级：SQLite 是一个小型的数据库引擎，易于嵌入应用中。
• 无服务器：它不需要像其他数据库管理系统那样的服务器进程，数据库文件就存储在本地。
• 易用性：SQLite 操作简单，且无需特别的配置，尤其适合开发和测试阶段。
• 跨平台：SQLite 可以在各种操作系统上运行，包括 Windows、Linux、macOS 等。

1.2 SQLite 的使用场景

• 嵌入式应用：SQLite 常用于手机应用或嵌入式设备中，因为它不需要服务器支持，使用灵活。
• 桌面应用：很多桌面应用也使用 SQLite 来存储配置信息或用户数据。
• 开发和测试：由于安装配置简单，开发人员常常在开发和测试阶段使用 SQLite 来验证数据库功能。
• 小型网站：对于一些不需要大量并发和数据操作的应用，SQLite 也是一种很好的选择。

二、Python 的 sqlite3 模块

Python 内置的 sqlite3 模块可以让我们非常方便地操作 SQLite 数据库。接下来我们会介绍如何通过这个模块创建数据库、创建表、插入数据、查询数据等基本操作。

2.1 安装与导入 sqlite3 模块

sqlite3 是 Python 标准库的一部分，所以不需要安装第三方库，直接导入即可。

import sqlite3

2.2 创建或连接到 SQLite 数据库

在 SQLite 中，数据库是存储在文件中的。如果指定的数据库文件不存在，sqlite3 会自动创建一个新的数据库文件。

import sqlite3

# 连接到数据库，如果数据库不存在，则会创建一个新文件
conn = sqlite3.connect('example.db')

# 创建游标对象，操作数据库的SQL语句要通过游标来执行
cursor = conn.cursor()

print("数据库连接成功！")

上述代码将连接或创建一个名为 example.db 的数据库文件。如果文件存在，connect 方法将连接到现有的数据库；如果文件不存在，则创建新的数据库文件。

2.3 创建表

创建表的语句与标准 SQL 语法相同。在 SQLite 中，你可以通过 CREATE TABLE 语句来创建新表。

# 创建一个名为 users 的表
cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        age INTEGER,
        email TEXT
    )
''')

print("表创建成功！")

这里，我们创建了一个 users 表，包含四个字段：

• id：主键，自动递增（AUTOINCREMENT）。
• name：用户的名字，非空（NOT NULL）。
• age：用户的年龄，整数类型。
• email：用户的电子邮件地址。

2.4 插入数据

插入数据使用 INSERT INTO 语句。可以通过参数化查询来避免 SQL 注入。

# 插入单条记录
cursor.execute('''
    INSERT INTO users (name, age, email)
    VALUES (?, ?, ?)
''', ("Alice", 25, "[email protected]"))

# 插入多条记录
users = [
    ("Bob", 30, "[email protected]"),
    ("Charlie", 22, "[email protected]"),
    ("Diana", 28, "[email protected]")
]

cursor.executemany('''
    INSERT INTO users (name, age, email)
    VALUES (?, ?, ?)
''', users)

# 提交事务
conn.commit()

print("数据插入成功！")

在这里，我们通过 ? 占位符来代替具体的值，防止 SQL 注入攻击。使用 executemany 方法可以批量插入多条数据。

2.5 查询数据

查询数据使用 SELECT 语句，查询结果可以通过游标的 fetchone()、fetchall() 方法获取。

# 查询所有用户
cursor.execute("SELECT * FROM users")

# 获取所有结果
rows = cursor.fetchall()

for row in rows:
    print(row)

查询结果是一个包含每一行数据的列表，每一行数据是一个元组。

2.6 更新数据

更新数据使用 UPDATE 语句。

# 更新用户的年龄
cursor.execute('''
    UPDATE users SET age = ? WHERE name = ?
''', (26, "Alice"))

# 提交事务
conn.commit()

print("数据更新成功！")

这里将名字为 “Alice” 的用户年龄更新为 26。

2.7 删除数据

删除数据使用 DELETE FROM 语句。

# 删除名字为 'Bob' 的用户
cursor.execute('''
    DELETE FROM users WHERE name = ?
''', ("Bob",))

# 提交事务
conn.commit()

print("数据删除成功！")

这条语句将删除名字为 “Bob” 的用户记录。

2.8 关闭连接

完成数据库操作后，一定要记得关闭游标和数据库连接。

# 关闭游标
cursor.close()

# 关闭数据库连接
conn.close()

print("数据库连接已关闭")

三、处理事务

SQLite 默认运行在自动提交模式下，这意味着每一条执行的 SQL 语句（如 INSERT、UPDATE、DELETE）都会立即生效。如果你希望将多个操作作为一个事务来执行，可以使用 BEGIN TRANSACTION 和 COMMIT 语句。

try:
    # 开始事务
    conn.execute("BEGIN TRANSACTION")

    # 执行一系列数据库操作
    cursor.execute('''
        INSERT INTO users (name, age, email)
        VALUES (?, ?, ?)
    ''', ("Eve", 29, "[email protected]"))

    cursor.execute('''
        UPDATE users SET age = ? WHERE name = ?
    ''', (30, "Charlie"))

    # 提交事务
    conn.commit()
    print("事务提交成功！")

except sqlite3.Error as e:
    # 出现错误时回滚事务
    conn.rollback()
    print(f"发生错误，事务回滚：{e}")

在这个例子中，我们将多条操作包裹在一个事务中，如果在某个操作中发生错误，我们可以使用 rollback 回滚事务，确保数据库不会被修改。

四、参数化查询和 SQL 注入

SQL 注入是常见的数据库攻击手段之一，通过向 SQL 查询中插入恶意代码，攻击者可以获取或修改数据库中的敏感信息。为了避免这种攻击，应该始终使用参数化查询而不是直接拼接字符串。

错误的做法：

# 拼接字符串容易导致 SQL 注入
name = "Alice"
age = 25
cursor.execute(f"INSERT INTO users (name, age) VALUES ('{name}', {age})")

正确的做法：

# 使用参数化查询
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", (name, age))

通过使用 ? 占位符和参数化查询，可以避免 SQL 注入的风险。

五、SQLite 的数据类型

SQLite 使用动态类型系统，它的类型系统不像 MySQL、PostgreSQL 那样严格。在 SQLite 中，数据类型大致分为五类：

• NULL：值是空值。
• INTEGER：整数类型，正数或负数。
• REAL：浮点数类型。
• TEXT：字符串类型。
• BLOB：二进制大对象，存储二进制数据。

虽然你可以在创建表时指定字段的类型，但是 SQLite 在插入数据时允许你插入与字段类型不匹配的数据。这意味着即使你为某个字段声明了 INTEGER 类型，你仍然可以插入一个文本字符串。

六、SQLite 的限制与适用场景

SQLite 虽然轻量且易于使用，但它并不适用于所有场景。它的性能和并发能力相对有限，因此更适合单用户桌面应用或嵌入式设备。

6.1 适合的场景

• 单用户桌面应用
• 嵌入式设备
• 开发

、测试数据库

• 小型网站或应用

6.2 不适合的场景

• 高并发、多用户的 Web 应用
• 需要复杂事务处理的大型应用
• 数据量非常大的项目

总结

通过 Python 的 sqlite3 模块，你可以轻松地与 SQLite 数据库进行交互，创建数据库、表，执行插入、查询、更新、删除等操作。SQLite 的轻量级特性使其非常适合嵌入式应用、桌面应用和快速原型开发。

尽管 SQLite 并不适合处理高并发或大规模的数据存储，但对于小型应用或项目，它是一个非常实用的数据库选择。Python 内置的 sqlite3 模块大大简化了 SQLite 数据库操作，非常适合初学者使用。

掌握了这些基础操作后，你可以根据自己的项目需求，灵活地使用 SQLite 或其他数据库进行开发。