Python 内置的 `sqlite3` 模块提供了一个轻量级的、无需独立服务器的磁盘数据库接口。其使用方法可概括为：**建立连接 -> 获取游标 -> 执行SQL -> 提交事务 -> 关闭连接**。 ### 核心使用方法 #### 1. 导入模块并建立连接 ```python import sqlite3 # 连接到数据库文件。如果文件不存在，会自动在当前目录创建。 conn = sqlite3.connect('example.db') # 也可以使用 ':memory:' 在内存中创建临时数据库，程序退出即消失。 # conn = sqlite3.connect(':memory:') ``` #### 2. 创建游标对象 游标是执行 SQL 语句和获取结果的主要工具。 ```python cursor = conn.cursor() ``` #### 3. 执行 SQL 命令（DDL & DML） 使用游标的 `execute()` 方法执行 SQL 语句。 ```python # 1. 创建表 (DDL) cursor.execute('''CREATE TABLE IF NOT EXISTS stocks (date text, trans text, symbol text, qty real, price real)''') # 2. 插入数据 (DML) cursor.execute("INSERT INTO stocks VALUES ('2023-10-27', 'BUY', 'RHAT', 100, 35.14)") # 使用参数化查询，防止SQL注入，这是推荐做法 cursor.execute("INSERT INTO stocks VALUES (?, ?, ?, ?, ?)", ('2023-10-28', 'SELL', 'IBM', 50, 215.0)) # 3. 批量插入数据 purchases = [('2023-10-29', 'BUY', 'MSFT', 10, 310.0), ('2023-10-30', 'SELL', 'AAPL', 5, 175.0)] cursor.executemany('INSERT INTO stocks VALUES (?,?,?,?,?)', purchases) # 4. 查询数据 (DQL) cursor.execute("SELECT * FROM stocks WHERE symbol = ?", ('RHAT',)) # 获取所有结果 rows = cursor.fetchall() for row in rows: print(row) # row 是一个元组，例如：('2023-10-27', 'BUY', 'RHAT', 100.0, 35.14) # 5. 更新数据 (DML) cursor.execute("UPDATE stocks SET price = ? WHERE symbol = ?", (36.14, 'RHAT')) # 6. 删除数据 (DML) cursor.execute("DELETE FROM stocks WHERE qty < ?", (20,)) ``` #### 4. 提交事务与关闭连接 SQLite 默认在自动提交模式关闭的情况下运行，需要显式提交更改。 ```python # 提交当前事务，使所有更改永久保存到数据库 conn.commit() # 关闭游标和连接 cursor.close() conn.close() ``` #### 5. 使用上下文管理器（推荐） 使用 `with` 语句可以自动管理连接和提交/回滚事务。 ```python import sqlite3 # 连接在 with 块结束后会自动提交并关闭 with sqlite3.connect('example.db') as conn: cursor = conn.cursor() cursor.execute("SELECT * FROM stocks") rows = cursor.fetchall() # 无需显式调用 conn.commit() 和 conn.close() ``` #### 6. 设置行工厂与类型转换 默认返回的行是元组，可以设置为返回字典或自定义对象。 ```python import sqlite3 # 设置为返回字典 def dict_factory(cursor, row): d = {} for idx, col in enumerate(cursor.description): d[col[0]] = row[idx] return d conn = sqlite3.connect('example.db') conn.row_factory = dict_factory # 设置行工厂 cursor = conn.cursor() cursor.execute("SELECT * FROM stocks LIMIT 1") row = cursor.fetchone() print(row) # 输出: {'date': '2023-10-27', 'trans': 'BUY', ...} ``` ### sqlite3 命令行工具所有子命令详细说明 `sqlite3` 也是一个独立的命令行工具，Python 安装后通常附带。在命令行输入 `sqlite3` 进入交互模式后，可以使用一系列以点 `.` 开头的**点命令**来管理数据库。这些命令不是 SQL 语句。 1. **`.help`** * **功能**：显示所有可用的点命令及其简要说明。这是最常用的命令，用于查询帮助。 * **示例**： ``` sqlite> .help ``` 2. **`.backup ?DB? FILE`** * **功能**：备份数据库。将指定的数据库（默认为主数据库）备份到给定的文件。 * **示例**：`sqlite> .backup main backup.db` 3. **`.bail on|off`** * **功能**：设置遇到错误时是否停止。默认为 `off`，错误后继续执行。设为 `on` 时，遇到任何错误立即停止。 * **示例**：`sqlite> .bail on` 4. **`.databases`** * **功能**：列出当前连接中所有附加的数据库的名称和文件。主数据库通常是 `main`，临时数据库是 `temp`[ref_2]。 * **示例**： ``` sqlite> .databases main: /path/to/your.db ``` 5. **`.dump ?TABLE?`** * **功能**：以 SQL 文本格式导出整个数据库或指定表的结构和数据。输出结果是一系列 `CREATE` 和 `INSERT` 语句，可用于重建数据库[ref_2]。 * **示例**： ``` sqlite> .dump # 导出整个数据库 sqlite> .dump stocks # 仅导出 stocks 表 sqlite> .output backup.sql # 结合 .output 命令将导出结果保存到文件 sqlite> .dump sqlite> .output stdout ``` 6. **`.echo on|off`** * **功能**：打开或关闭命令回显。设置为 `on` 时，输入的 SQL 命令会在执行前显示。 * **示例**：`sqlite> .echo on` 7. **`.exit` 或 `.quit`** * **功能**：退出 sqlite3 命令行程序[ref_1]。 * **示例**：`sqlite> .exit` 8. **`.headers on|off`** * **功能**：控制查询结果是否显示列标题（字段名）。默认为 `off`。 * **示例**： ``` sqlite> .headers on sqlite> SELECT * FROM stocks LIMIT 1; ``` 9. **`.import FILE TABLE`** * **功能**：将指定文件（通常是 CSV 文件）的数据导入到指定的表中。文件的分隔符由 `.separator` 命令定义。 * **示例**：`sqlite> .import data.csv stocks` 10. **`.indexes ?TABLE?`** * **功能**：显示所有索引或指定表的索引。 * **示例**： ``` sqlite> .indexes # 显示所有索引 sqlite> .indexes stocks # 显示 stocks 表的索引 ``` 11. **`.mode MODE`** * **功能**：设置输出模式，影响查询结果的显示格式。常用模式有： * `list`：默认模式，用分隔符分隔字段。 * `column`：类似表格，字段按列对齐（常用）[ref_2]。 * `csv`：输出为逗号分隔值。 * `html`：输出为 HTML 表格。 * `insert`：输出为 `INSERT` 语句。 * `line`：每行一个字段值。 * `tabs`：用制表符分隔字段。 * **示例**：`sqlite> .mode column` 12. **`.nullvalue STRING`** * **功能**：设置用于表示 `NULL` 值的输出字符串。默认为空字符串。 * **示例**：`sqlite> .nullvalue NULL` 13. **`.output ?FILE?`** * **功能**：将后续的所有输出重定向到指定文件。如果省略文件名或使用 `.output stdout`，则恢复输出到屏幕[ref_1]。 * **示例**： ``` sqlite> .output query_result.txt sqlite> SELECT * FROM stocks; sqlite> .output stdout ``` 14. **`.print STRING...`** * **功能**：逐字打印其参数，类似于编程语言中的 `print` 函数。 * **示例**：`sqlite> .print Hello, World!` 15. **`.prompt MAIN CONTINUE`** * **功能**：更改 sqlite3 的提示符。`MAIN` 是主提示符，`CONTINUE` 是换行后的续行提示符。 * **示例**：`sqlite> .prompt ‘sqlite3> ‘ ‘…> ‘` 16. **`.read FILENAME`** * **功能**：读取指定文件中的 SQL 语句并执行。常用于运行脚本文件。 * **示例**：`sqlite> .read init.sql` 17. **`.restore ?DB? FILE`** * **功能**：从指定文件恢复数据库内容到当前连接的数据库。是 `.backup` 的逆操作。 * **示例**：`sqlite> .restore main backup.db` 18. **`.save FILE`** * **功能**：将内存数据库（如 `:memory:`）保存到磁盘文件。 * **示例**：`sqlite> .save persistent.db` 19. **`.schema ?TABLE?`** * **功能**：显示数据库的 `CREATE` 语句。如果不指定表名，则显示所有表、索引、视图和触发器的创建语句；如果指定表名，则只显示该对象的创建语句[ref_1][ref_2]。 * **示例**： ``` sqlite> .schema # 显示所有对象的结构 sqlite> .schema stocks # 只显示 stocks 表的创建语句 ``` 20. **`.separator COL ?ROW?`** * **功能**：为 `.mode list` 输出设置字段分隔符（COL）和行分隔符（ROW，可选）。常用于 CSV 导入/导出[ref_2]。 * **示例**： ``` sqlite> .separator , # 设置字段分隔符为逗号 sqlite> .separator ‘|‘ # 设置字段分隔符为竖线 ``` 21. **`.show`** * **功能**：显示当前各种设置的当前值，如输出模式、分隔符、是否显示头部等。 * **示例**：`sqlite> .show` 22. **`.stats on|off`** * **功能**：打开或关闭统计信息显示。开启后，在执行每条语句后会显示内存使用等统计信息。 * **示例**：`sqlite> .stats on` 23. **`.system CMD`** * **功能**：在 shell 中执行系统命令。 * **示例**： ``` sqlite> .system ls -l # 在 Unix/Linux/Mac 下列出文件 sqlite> .system dir # 在 Windows 下列出文件 ``` 24. **`.tables ?TABLE?`** * **功能**：列出所有表名，或匹配指定模式（LIKE 模式）的表名。 * **示例**： ``` sqlite> .tables # 列出所有表 sqlite> .tables %s # 列出所有以 ‘s‘ 结尾的表 ``` 25. **`.timeout MS`** * **功能**：尝试打开锁定的表时等待的超时时间（毫秒）。 * **示例**：`sqlite> .timeout 5000` 26. **`.timer on|off`** * **功能**：打开或关闭 SQL 语句执行时间的测量和显示。 * **示例**：`sqlite> .timer on` 27. **`.trace ?FILE?`** * **功能**：将所有的 SQL 语句输出到文件或屏幕。用于调试。 * **示例**： ``` sqlite> .trace debug.log sqlite> .trace stdout ``` 28. **`.vfsname ?AUX?`** * **功能**：打印当前使用的 VFS（虚拟文件系统）堆栈的名称。 * **示例**：`sqlite> .vfsname` 29. **`.width NUM1 NUM2 ...`** * **功能**：为 “column” 模式设置各列的宽度。这对于调整列式输出的对齐非常有用。 * **示例**：`sqlite> .width 10 20 15` （设置前三列的宽度） **总结**：Python 的 `sqlite3` 模块通过简单的连接、游标、执行、提交四步即可完成绝大多数数据库操作，其核心是使用参数化查询确保安全。而 `sqlite3` 命令行工具则通过一系列 `.` 开头的点命令提供了强大的数据库管理和交互功能，从查看结构（`.schema`）、导出数据（`.dump`）到控制输出格式（`.mode`, `.headers`）等，是管理和调试 SQLite 数据库的必备工具[ref_1][ref_2][ref_3]。