sqlite python如何在 Python 中使用 SQLite 数据库

【sqlite python】如何在 Python 中使用 SQLite 数据库

【sqlite python】如何在 Python 中使用 SQLite 数据库？ Python 内置的 `sqlite3` 模块可以轻松地与 SQLite 数据库进行交互，无需安装额外的数据库服务器。你可以使用它来创建数据库文件、执行 SQL 查询、插入、更新和删除数据，以及管理数据库结构。

SQLite 是一个轻量级的、基于文件的关系型数据库管理系统。它的主要优势在于部署简单，无需独立的服务器进程，数据直接存储在单个文件中，非常适合嵌入式应用、小型项目、原型开发以及本地数据存储。Python 通过其内置的 `sqlite3` 模块提供了对 SQLite 的强大支持，使得在 Python 程序中操作 SQLite 数据库变得异常便捷。

本文将深入探讨如何利用 `sqlite3` 模块在 Python 中进行 SQLite 数据库的开发，包括连接数据库、创建表、插入数据、查询数据、更新数据、删除数据，以及更高级的操作和注意事项。

一、 连接到 SQLite 数据库

与 SQLite 数据库交互的第一步是建立连接。Python 的 `sqlite3` 模块提供了 `connect()` 函数来实现这一点。

1. 创建或连接到数据库文件

当你调用 `sqlite3.connect(database.db)` 时，如果 `database.db` 文件不存在，它会被创建。如果文件已存在，则会连接到该现有文件。

python import sqlite3 # 连接到名为 mydatabase.db 的数据库文件 # 如果文件不存在，则会被创建 conn = sqlite3.connect(mydatabase.db) print("成功连接到 SQLite 数据库！") # 连接建立后，通常需要关闭它 # conn.close()

重要提示： 数据库连接在完成操作后应该被显式关闭，以释放资源并确保数据被正确写入。这通常在 `try...finally` 块中完成，或者使用 `with` 语句来自动管理连接的生命周期。

2. 使用 `with` 语句管理连接

使用 `with` 语句可以更安全地管理数据库连接，它能确保在代码块执行完毕或发生异常时自动提交事务（如果配置了自动提交）或回滚，并关闭连接。

python import sqlite3 try: with sqlite3.connect(mydatabase.db) as conn: print("使用 with 语句成功连接到 SQLite 数据库！") # 在这里执行数据库操作 except sqlite3.Error as e: print(f"数据库连接错误: {e}") # 连接在 with 块结束后会自动关闭

二、 创建数据库表

在数据库中存储数据之前，你需要定义表的结构。这通过执行 SQL 的 `CREATE TABLE` 语句来完成。

1. 创建游标对象

要执行 SQL 命令，你需要一个游标（cursor）对象。游标对象允许你在数据库上执行命令，并遍历 SQL 查询的结果。

python import sqlite3 conn = sqlite3.connect(mydatabase.db) cursor = conn.cursor() print("已创建游标对象。")

2. 执行 `CREATE TABLE` 语句

使用游标的 `execute()` 方法来执行 SQL 语句。下面是一个创建名为 `users` 的表的例子，包含 `id`、`name` 和 `age` 列。

python import sqlite3 try: with sqlite3.connect(mydatabase.db) as conn: cursor = conn.cursor() # 创建 users 表 cursor.execute( CREATE TABLE IF NOT EXISTS users ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, age INTEGER ) ) print("表 users 创建成功（如果不存在）。") # 提交事务 conn.commit() except sqlite3.Error as e: print(f"创建表时发生错误: {e}")

解释：

• `CREATE TABLE IF NOT EXISTS users`: 创建一个名为 `users` 的表，如果该表已经存在，则不会执行创建操作。
• `id INTEGER PRIMARY KEY AUTOINCREMENT`: 定义一个名为 `id` 的列，类型为整数，是主键，并且在插入新记录时会自动递增。
• `name TEXT NOT NULL`: 定义一个名为 `name` 的列，类型为文本，并且不能为空。
• `age INTEGER`: 定义一个名为 `age` 的列，类型为整数。
• `conn.commit()`: 提交当前事务。对于 DDL（数据定义语言）语句，如 `CREATE TABLE`，通常需要提交。

三、 插入数据

向表中插入数据是数据库操作的基本组成部分。你可以使用 SQL 的 `INSERT INTO` 语句来完成。

1. 单条插入

使用 `execute()` 方法结合 `INSERT INTO` 语句插入单条记录。

python import sqlite3 try: with sqlite3.connect(mydatabase.db) as conn: cursor = conn.cursor() # 插入一条记录 cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", (Alice, 30)) print("成功插入一条记录。") # 提交事务 conn.commit() except sqlite3.Error as e: print(f"插入数据时发生错误: {e}")

参数化查询： 上述例子中使用了 `?` 作为占位符，并将实际值作为元组传递给 `execute()` 方法。这是防止 SQL 注入攻击的最佳实践。

2. 多条插入

对于需要插入多条记录的情况，可以使用 `executemany()` 方法，这比多次调用 `execute()` 更高效。

python import sqlite3 try: with sqlite3.connect(mydatabase.db) as conn: cursor = conn.cursor() # 要插入的多条记录 users_data = [ (Bob, 25), (Charlie, 35), (David, 28) ] # 批量插入记录 cursor.executemany("INSERT INTO users (name, age) VALUES (?, ?)", users_data) print("成功批量插入多条记录。") # 提交事务 conn.commit() except sqlite3.Error as e: print(f"批量插入数据时发生错误: {e}")

四、 查询数据

从数据库中检索数据是查询操作的核心。使用 SQL 的 `SELECT` 语句来执行此操作。

1. 获取所有数据

使用 `SELECT * FROM table_name` 来获取表中的所有列和所有行。

python import sqlite3 try: with sqlite3.connect(mydatabase.db) as conn: cursor = conn.cursor() # 查询所有用户 cursor.execute("SELECT id, name, age FROM users") rows = cursor.fetchall() # 获取所有查询结果 print(" 所有用户信息:") for row in rows: print(row) # row 是一个包含列值的元组 except sqlite3.Error as e: print(f"查询数据时发生错误: {e}")

获取查询结果的方法：

• `cursor.fetchone()`: 获取查询结果中的下一行，返回一个元组，如果已无行则返回 `None`。
• `cursor.fetchall()`: 获取查询结果中的所有行，返回一个包含元组的列表。
• `cursor.fetchmany(size)`: 获取指定数量的行，返回一个包含元组的列表。

2. 条件查询

使用 `WHERE` 子句来根据特定条件过滤查询结果。

python import sqlite3 try: with sqlite3.connect(mydatabase.db) as conn: cursor = conn.cursor() # 查询年龄大于 30 岁的用户 cursor.execute("SELECT name, age FROM users WHERE age > ?", (30,)) older_users = cursor.fetchall() print(" 年龄大于 30 岁用户:") for user in older_users: print(user) except sqlite3.Error as e: print(f"条件查询时发生错误: {e}")

3. 查询特定列

你可以只选择你需要的列，而不是 `*`。

python import sqlite3 try: with sqlite3.connect(mydatabase.db) as conn: cursor = conn.cursor() # 只查询用户姓名 cursor.execute("SELECT name FROM users") names = cursor.fetchall() print(" 所有用户姓名:") for name_tuple in names: print(name_tuple[0]) # name_tuple 是 (Alice,), 所以取第一个元素 except sqlite3.Error as e: print(f"查询特定列时发生错误: {e}")

五、 更新数据

修改表中已有的记录使用 SQL 的 `UPDATE` 语句。

python import sqlite3 try: with sqlite3.connect(mydatabase.db) as conn: cursor = conn.cursor() # 更新 Alice 的年龄为 31 cursor.execute("UPDATE users SET age = ? WHERE name = ?", (31, Alice)) print(f"更新了 {cursor.rowcount} 条记录。") # cursor.rowcount 返回受影响的行数 # 提交事务 conn.commit() except sqlite3.Error as e: print(f"更新数据时发生错误: {e}")

注意： `UPDATE` 语句如果没有 `WHERE` 子句，将会更新表中所有行的指定列。务必谨慎使用。

六、 删除数据

从表中移除记录使用 SQL 的 `DELETE FROM` 语句。

python import sqlite3 try: with sqlite3.connect(mydatabase.db) as conn: cursor = conn.cursor() # 删除名字是 David 的用户 cursor.execute("DELETE FROM users WHERE name = ?", (David,)) print(f"删除了 {cursor.rowcount} 条记录。") # 提交事务 conn.commit() except sqlite3.Error as e: print(f"删除数据时发生错误: {e}")

注意： `DELETE FROM` 语句如果没有 `WHERE` 子句，将会删除表中的所有记录。务必谨慎使用。

七、 事务管理

SQLite 默认是自动提交事务的，这意味着每次执行 DML（数据操作语言）语句（如 `INSERT`, `UPDATE`, `DELETE`）都会立即生效。然而，在执行一系列相关的操作时，你可能希望将它们打包成一个原子事务，以保证数据的一致性。

1. 手动提交和回滚

通过调用 `conn.commit()` 来提交当前的事务，使所有更改永久生效。如果希望撤销自上次提交以来的所有更改，则调用 `conn.rollback()`。

python import sqlite3 try: with sqlite3.connect(mydatabase.db) as conn: cursor = conn.cursor() # 尝试插入两条记录 cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", (Eve, 22)) cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", (Frank, 40)) # 如果一切顺利，提交事务 conn.commit() print("成功提交事务，插入 Eve 和 Frank。") except sqlite3.Error as e: print(f"事务处理错误: {e}") # 如果发生错误，回滚事务 if conn: conn.rollback() print("事务已回滚。")

2. 禁用自动提交（不推荐在 `with` 语句中使用）

当你需要完全手动控制事务时，可以禁用自动提交模式。

python import sqlite3 conn = None # 初始化 conn 为 None try: conn = sqlite3.connect(mydatabase.db) conn.isolation_level = None # 禁用自动提交 cursor = conn.cursor() cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", (Grace, 29)) # ... 其他操作 ... conn.commit() # 需要手动提交 except sqlite3.Error as e: print(f"手动事务错误: {e}") if conn: conn.rollback() finally: if conn: conn.close()

注意： 在大多数情况下，使用 `with sqlite3.connect(...)` 配合默认的自动提交行为，或者在 `with` 块结束后手动调用 `conn.commit()`，已经足够满足需求。手动禁用自动提交会增加出错的可能性，需要更仔细的管理。

八、 数据库文件的更多操作

1. 获取最后插入的行的 ID

如果你在创建表时定义了 `AUTOINCREMENT` 的主键，可以使用 `cursor.lastrowid` 来获取最后一次 `INSERT` 操作生成的 ID。

python import sqlite3 try: with sqlite3.connect(mydatabase.db) as conn: cursor = conn.cursor() cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", (Heidi, 27)) last_id = cursor.lastrowid print(f"最后插入的记录 ID 是: {last_id}") conn.commit() except sqlite3.Error as e: print(f"获取最后 ID 时发生错误: {e}")

2. 关闭数据库连接

无论使用哪种方式连接，最后都应该关闭连接，以释放资源。

python import sqlite3 conn = sqlite3.connect(mydatabase.db) # ... 执行数据库操作 ... conn.close() print("数据库连接已关闭。")

如前所述，使用 `with` 语句可以自动处理连接的关闭。

九、 进阶概念与注意事项

1. 数据类型

SQLite 支持以下数据类型：

• NULL
• INTEGER
• REAL
• TEXT
• BLOB

Python 的 `sqlite3` 模块会自动处理 Python 类型与 SQLite 类型之间的转换（例如，Python 的 `int` 对应 `INTEGER`，`float` 对应 `REAL`，`str` 对应 `TEXT`，`bytes` 对应 `BLOB`）。

2. 错误处理

数据库操作可能会因为各种原因失败（如违反约束、语法错误等）。使用 `try...except sqlite3.Error` 块来捕获和处理这些异常是至关重要的。

3. SQL 注入防护

始终使用参数化查询（占位符 `?` 或 `:name`）来传递用户输入或变量到 SQL 语句中。 永远不要直接将用户输入拼接到 SQL 字符串中，这会导致严重的安全漏洞。

4. 数据库文件路径

`sqlite3.connect()` 函数可以接受一个文件路径。如果路径是 `:memory:`，则数据库将被创建在内存中，程序结束后数据将丢失。

python # 在内存中创建一个数据库 conn_in_memory = sqlite3.connect(:memory:) print("已在内存中创建数据库。") conn_in_memory.close()

5. Schema Migrations

对于复杂的应用程序，管理数据库结构的变化（schema migrations）会变得复杂。有许多第三方库（如 Alembic）可以帮助你管理数据库的迁移。

6. 并发访问

SQLite 是一个文件锁定的数据库。当一个进程写入数据库时，其他进程可能会被阻塞。对于高并发写入场景，SQLite 可能不是最佳选择。

总结

Python 的 `sqlite3` 模块为使用 SQLite 数据库提供了极其便捷的接口。通过理解连接、游标、SQL 语句的执行、数据插入、查询、更新、删除以及事务管理，你可以轻松地在 Python 程序中实现本地数据存储和管理功能。

掌握 **sqlite python** 的这些基础知识，将为你处理各种需要本地数据持久化的场景奠定坚实的基础。