Python中的SQLite数据库操作教程

SQLite 简介

SQLite 是一个轻量级的嵌入式数据库，它的设计目标是嵌入式，非常适合在资源有限的设备上使用，如移动设备、物联网设备等。SQLite 不需要一个单独的服务器进程，数据库是以文件形式存储在本地，这使得它在很多场景下使用起来非常方便。Python 内置了对 SQLite 的支持，通过 sqlite3 模块就可以方便地操作 SQLite 数据库。

SQLite 的特点

1. 轻量级：SQLite 库非常小，运行时开销也很低。它不需要像 MySQL 或 PostgreSQL 那样运行一个独立的数据库服务器进程，这使得它在资源受限的环境中表现出色。
2. 零配置：使用 SQLite 无需进行复杂的配置，只需要引入相应的库并操作数据库文件即可。
3. 事务支持：SQLite 支持完整的 ACID（原子性、一致性、隔离性、持久性）事务，确保数据的完整性和可靠性。
4. 跨平台：SQLite 可以在多种操作系统上运行，包括 Linux、Windows、macOS 等，并且其数据库文件格式在不同平台上是兼容的。

安装与引入 SQLite 模块

在 Python 中，sqlite3 模块是内置的，一般情况下无需额外安装。如果你使用的是标准的 Python 发行版，直接引入即可。

import sqlite3

创建数据库连接

要操作 SQLite 数据库，首先需要创建一个数据库连接。如果指定的数据库文件不存在，connect 方法会自动创建一个新的数据库文件。

import sqlite3

# 连接到 SQLite 数据库，文件名为 test.db
conn = sqlite3.connect('test.db')
print("成功连接到 SQLite 数据库")

在上述代码中，sqlite3.connect('test.db') 尝试连接到名为 test.db 的 SQLite 数据库文件。如果连接成功，conn 对象将代表这个数据库连接。

关闭数据库连接

使用完数据库后，应该关闭连接以释放资源。

conn.close()
print("成功关闭 SQLite 数据库连接")

创建表

连接到数据库后，可以执行 SQL 语句来创建表。SQLite 支持标准的 SQL 语法来创建表。以下是创建一个简单用户表的示例：

import sqlite3

conn = sqlite3.connect('test.db')
cursor = conn.cursor()

# 创建 users 表
create_table_sql = '''
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER
);
'''
cursor.execute(create_table_sql)
conn.commit()
print("成功创建 users 表")

conn.close()

在上述代码中：

1. conn.cursor() 创建一个游标对象 cursor，通过游标对象可以执行 SQL 语句。
2. CREATE TABLE users 语句定义了一个名为 users 的表，包含 id、name 和 age 三个列。id 列是主键，并且会自动递增；name 列是文本类型且不能为空；age 列是整数类型。
3. cursor.execute(create_table_sql) 执行创建表的 SQL 语句。
4. conn.commit() 提交事务，使创建表的操作生效。

插入数据

插入单条数据

向表中插入单条数据可以使用 INSERT INTO 语句。以下是向 users 表插入一条用户数据的示例：

import sqlite3

conn = sqlite3.connect('test.db')
cursor = conn.cursor()

# 插入单条数据
insert_sql = "INSERT INTO users (name, age) VALUES ('Alice', 25)"
cursor.execute(insert_sql)
conn.commit()
print("成功插入单条数据")

conn.close()

在上述代码中，INSERT INTO users (name, age) VALUES ('Alice', 25) 语句将一条姓名为 Alice，年龄为 25 的记录插入到 users 表中。

插入多条数据

如果要插入多条数据，可以使用 executemany 方法。以下是插入多条用户数据的示例：

import sqlite3

conn = sqlite3.connect('test.db')
cursor = conn.cursor()

# 多条数据
user_data = [
    ('Bob', 30),
    ('Charlie', 35),
    ('David', 40)
]

insert_sql = "INSERT INTO users (name, age) VALUES (?,?)"
cursor.executemany(insert_sql, user_data)
conn.commit()
print("成功插入多条数据")

conn.close()

在上述代码中：

1. user_data 是一个包含多个元组的列表，每个元组代表一条用户数据。
2. INSERT INTO users (name, age) VALUES (?,?) 中的 ? 是占位符，用于表示要插入的值。
3. cursor.executemany(insert_sql, user_data) 方法会循环执行 INSERT INTO 语句，将 user_data 中的每一条数据插入到表中。

查询数据

查询所有数据

要查询表中的所有数据，可以使用 SELECT * FROM 语句。以下是查询 users 表所有数据的示例：

import sqlite3

conn = sqlite3.connect('test.db')
cursor = conn.cursor()

# 查询所有数据
select_all_sql = "SELECT * FROM users"
cursor.execute(select_all_sql)
rows = cursor.fetchall()

for row in rows:
    print(row)

conn.close()

在上述代码中：

1. cursor.execute(select_all_sql) 执行查询所有数据的 SQL 语句。
2. cursor.fetchall() 获取查询结果集，返回一个包含所有行的列表，每一行是一个元组，元组中的元素按表中列的顺序排列。
3. 通过循环遍历 rows，打印出每一行的数据。

条件查询

可以使用 WHERE 子句进行条件查询。以下是查询年龄大于 30 岁的用户的示例：

import sqlite3

conn = sqlite3.connect('test.db')
cursor = conn.cursor()

# 条件查询
select_where_sql = "SELECT * FROM users WHERE age > 30"
cursor.execute(select_where_sql)
rows = cursor.fetchall()

for row in rows:
    print(row)

conn.close()

在上述代码中，SELECT * FROM users WHERE age > 30 语句只查询年龄大于 30 岁的用户数据。

排序查询

使用 ORDER BY 子句可以对查询结果进行排序。以下是按年龄升序查询用户数据的示例：

import sqlite3

conn = sqlite3.connect('test.db')
cursor = conn.cursor()

# 排序查询
select_order_sql = "SELECT * FROM users ORDER BY age ASC"
cursor.execute(select_order_sql)
rows = cursor.fetchall()

for row in rows:
    print(row)

conn.close()

在上述代码中，SELECT * FROM users ORDER BY age ASC 语句按年龄升序排列查询结果。如果要按降序排列，可以使用 DESC 关键字，即 SELECT * FROM users ORDER BY age DESC。

更新数据

使用 UPDATE 语句可以更新表中的数据。以下是将 users 表中名为 Bob 的用户年龄更新为 32 的示例：

import sqlite3

conn = sqlite3.connect('test.db')
cursor = conn.cursor()

# 更新数据
update_sql = "UPDATE users SET age = 32 WHERE name = 'Bob'"
cursor.execute(update_sql)
conn.commit()
print("成功更新数据")

conn.close()

在上述代码中，UPDATE users SET age = 32 WHERE name = 'Bob' 语句将 users 表中姓名为 Bob 的用户年龄更新为 32。

删除数据

使用 DELETE FROM 语句可以删除表中的数据。以下是删除 users 表中年龄大于 40 岁的用户数据的示例：

import sqlite3

conn = sqlite3.connect('test.db')
cursor = conn.cursor()

# 删除数据
delete_sql = "DELETE FROM users WHERE age > 40"
cursor.execute(delete_sql)
conn.commit()
print("成功删除数据")

conn.close()

在上述代码中，DELETE FROM users WHERE age > 40 语句删除 users 表中年龄大于 40 岁的用户数据。

事务处理

SQLite 支持事务处理，以确保一组操作要么全部成功，要么全部失败。在 Python 的 sqlite3 模块中，默认情况下，每次执行 execute 或 executemany 方法后都会自动提交事务。如果要手动控制事务，可以使用 conn.begin()、conn.commit() 和 conn.rollback() 方法。

以下是一个包含事务处理的示例，模拟从一个账户向另一个账户转账的操作：

import sqlite3

conn = sqlite3.connect('test.db')
cursor = conn.cursor()

try:
    # 开始事务
    conn.begin()

    # 从账户 A 减去 100
    update_a_sql = "UPDATE accounts SET balance = balance - 100 WHERE account_name = 'A'"
    cursor.execute(update_a_sql)

    # 向账户 B 增加 100
    update_b_sql = "UPDATE accounts SET balance = balance + 100 WHERE account_name = 'B'"
    cursor.execute(update_b_sql)

    # 提交事务
    conn.commit()
    print("转账成功")
except Exception as e:
    # 回滚事务
    conn.rollback()
    print(f"转账失败: {e}")
finally:
    conn.close()

在上述代码中：

1. conn.begin() 开始一个事务。
2. 执行两个 UPDATE 语句来模拟转账操作。
3. 如果在执行过程中没有发生异常，conn.commit() 提交事务，使操作生效。
4. 如果发生异常，conn.rollback() 回滚事务，撤销之前执行的所有操作。

使用上下文管理器

为了更方便地管理数据库连接和事务，可以使用 Python 的上下文管理器。sqlite3 模块中的 Connection 对象支持上下文管理器协议。

以下是使用上下文管理器的示例：

import sqlite3

with sqlite3.connect('test.db') as conn:
    cursor = conn.cursor()

    # 创建表
    create_table_sql = '''
    CREATE TABLE products (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        price REAL
    );
    '''
    cursor.execute(create_table_sql)

    # 插入数据
    insert_sql = "INSERT INTO products (name, price) VALUES ('Product A', 10.99)"
    cursor.execute(insert_sql)

    # 查询数据
    select_sql = "SELECT * FROM products"
    cursor.execute(select_sql)
    rows = cursor.fetchall()

    for row in rows:
        print(row)

在上述代码中，with sqlite3.connect('test.db') as conn 会自动管理数据库连接的创建和关闭。在 with 块内，如果没有发生异常，事务会自动提交；如果发生异常，事务会自动回滚。

SQLite 数据类型

SQLite 支持以下主要的数据类型：

1. NULL：表示空值。
2. INTEGER：有符号整数，根据值的大小可以存储为 1、2、3、4、6 或 8 字节。
3. REAL：8 字节浮点值。
4. TEXT：文本字符串，使用数据库编码（UTF - 8、UTF - 16BE 或 UTF - 16LE）存储。
5. BLOB：二进制大对象，可以存储任何数据，如图片、音频等。

在定义表结构时，需要根据数据的实际类型选择合适的数据类型。例如，对于年龄可以使用 INTEGER 类型，对于产品价格可以使用 REAL 类型，对于产品名称可以使用 TEXT 类型等。

SQLite 高级特性

索引

索引可以提高查询性能。在 SQLite 中，可以使用 CREATE INDEX 语句创建索引。以下是为 users 表的 name 列创建索引的示例：

import sqlite3

conn = sqlite3.connect('test.db')
cursor = conn.cursor()

# 创建索引
create_index_sql = "CREATE INDEX idx_name ON users (name)"
cursor.execute(create_index_sql)
conn.commit()
print("成功创建索引")

conn.close()

在上述代码中，CREATE INDEX idx_name ON users (name) 语句为 users 表的 name 列创建了一个名为 idx_name 的索引。当查询涉及到 name 列时，这个索引可以加快查询速度。

外键约束

外键用于建立表与表之间的关系，确保数据的一致性。要在 SQLite 中启用外键约束，需要在连接数据库后执行 PRAGMA foreign_keys = ON。以下是一个使用外键的示例：

import sqlite3

conn = sqlite3.connect('test.db')
cursor = conn.cursor()

# 启用外键约束
cursor.execute('PRAGMA foreign_keys = ON')

# 创建 orders 表
create_orders_table_sql = '''
CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER,
    order_date TEXT,
    FOREIGN KEY (user_id) REFERENCES users (id)
);
'''
cursor.execute(create_orders_table_sql)
conn.commit()
print("成功创建 orders 表")

conn.close()

在上述代码中：

1. PRAGMA foreign_keys = ON 启用外键约束。
2. CREATE TABLE orders 语句创建了一个 orders 表，其中 user_id 列是外键，引用了 users 表的 id 列。这意味着 orders 表中的 user_id 值必须存在于 users 表的 id 列中，从而保证了数据的一致性。

视图

视图是一个虚拟表，它是基于 SQL 查询的结果集。视图可以简化复杂的查询，并且可以提供额外的数据安全性。以下是创建一个视图的示例：

import sqlite3

conn = sqlite3.connect('test.db')
cursor = conn.cursor()

# 创建视图
create_view_sql = '''
CREATE VIEW adult_users AS
SELECT * FROM users WHERE age >= 18;
'''
cursor.execute(create_view_sql)
conn.commit()
print("成功创建视图")

conn.close()

在上述代码中，CREATE VIEW adult_users AS SELECT * FROM users WHERE age >= 18 语句创建了一个名为 adult_users 的视图，该视图只包含年龄大于等于 18 岁的用户数据。之后可以像查询表一样查询这个视图：

import sqlite3

conn = sqlite3.connect('test.db')
cursor = conn.cursor()

# 查询视图
select_view_sql = "SELECT * FROM adult_users"
cursor.execute(select_view_sql)
rows = cursor.fetchall()

for row in rows:
    print(row)

conn.close()

性能优化

1. 合理使用索引：根据查询条件，为经常用于 WHERE 子句、JOIN 子句等的列创建索引，可以显著提高查询性能。但索引也会增加插入、更新和删除操作的开销，因此要权衡使用。
2. 批量操作：尽量使用 executemany 方法进行批量插入、更新等操作，而不是逐条执行，这样可以减少数据库的交互次数，提高效率。
3. 事务管理：合理使用事务，将相关的操作放在一个事务中，减少不必要的事务提交次数。同时，在事务内避免执行耗时较长的操作，以免锁定数据库资源。
4. 避免全表扫描：编写查询语句时，尽量避免全表扫描。通过合理的索引和查询条件，可以让数据库快速定位到所需的数据。
5. 数据库文件管理：定期清理不再使用的数据库文件，对于大型数据库，可以考虑进行数据库文件的碎片化整理，以提高数据库的访问性能。

常见问题及解决方法

1. 数据库文件损坏：如果数据库文件损坏，可能会导致无法连接或操作失败。可以使用 PRAGMA integrity_check 语句检查数据库的完整性。如果发现问题，可以尝试使用备份文件恢复，或者使用 SQLite 的修复工具（如 sqlite3_analyzer）进行修复。
2. 并发访问问题：虽然 SQLite 支持一定程度的并发访问，但在高并发场景下可能会出现锁争用等问题。可以通过合理设置事务隔离级别、优化事务操作等方式来减少并发问题的影响。
3. 内存使用问题：在处理大量数据时，可能会出现内存使用过高的情况。可以通过分批处理数据、及时关闭游标和连接等方式来控制内存使用。

通过以上对 SQLite 数据库在 Python 中的操作教程，你应该已经掌握了基本的数据库创建、表操作、数据增删改查以及事务处理等知识。在实际应用中，可以根据具体需求进一步优化和扩展这些操作，以满足不同的业务场景。