在Python中使用SQLite数据库进行增删改查操作的代码示例_Python

一、核心前置知识

python 内置 sqlite3 模块，无需 pip install，直接导入即可使用。
操作流程：建立连接 → 创建游标 → 执行sql → 处理结果 → 提交事务（增删改）→ 关闭资源。
占位符：sqlite 使用 ? 作为参数占位符（避免 sql 注入，切勿字符串拼接）。

二、完整 crud 示例

步骤1：导入模块并建立连接

import sqlite3

# 1. 建立连接（关键）
# - 连接本地文件：test.db（不存在则自动创建）
# - 内存数据库：sqlite3.connect(":memory:")（程序退出后数据丢失）
conn = sqlite3.connect("test.db")

# 2. 创建游标（执行sql的工具）
cursor = conn.cursor()

步骤2：创建表（基础准备）

先定义数据表结构（以 users 表为例，包含 id、姓名、年龄、邮箱）：

# 创建users表（if not exists 避免重复创建）
create_table_sql = """
create table if not exists users (
    id integer primary key autoincrement,  -- 自增主键
    name text not null,                    -- 姓名（非空）
    age integer,                           -- 年龄
    email text unique                      -- 邮箱（唯一，避免重复）
);
"""
cursor.execute(create_table_sql)
conn.commit()  # 建表属于修改操作，需提交事务

步骤3：新增数据（create）

支持单条插入和批量插入，核心是用 ? 占位符传递参数：

# 方式1：单条插入
insert_single_sql = "insert into users (name, age, email) values (?, ?, ?);"
# 参数以元组形式传递，匹配占位符数量
cursor.execute(insert_single_sql, ("alice", 25, "alice@example.com"))

# 方式2：批量插入（效率更高，减少io）
insert_batch_sql = "insert into users (name, age, email) values (?, ?, ?);"
data_list = [
    ("bob", 30, "bob@example.com"),
    ("charlie", 28, "charlie@example.com"),
    ("david", 22, "david@example.com")
]
cursor.executemany(insert_batch_sql, data_list)

# 增删改操作必须提交事务，否则数据不生效
conn.commit()
print("插入数据成功，最后插入的id：", cursor.lastrowid)  # 获取最后插入的主键id

步骤4：查询数据（read）

查询无需提交事务，核心是通过 fetchone()/fetchall()/fetchmany(n) 获取结果：

# 方式1：查询单条数据（fetchone()）
select_single_sql = "select * from users where name = ?;"
cursor.execute(select_single_sql, ("alice",))
single_result = cursor.fetchone()  # 返回元组：(1, 'alice', 25, 'alice@example.com')
print("单条查询结果：", single_result)

# 方式2：查询多条数据（fetchall()）
select_all_sql = "select id, name, age from users where age > ?;"
cursor.execute(select_all_sql, (25,))
all_results = cursor.fetchall()  # 返回列表嵌套元组：[(2, 'bob', 30), (3, 'charlie', 28)]
print("\n多条查询结果：")
for row in all_results:
    print(f"id: {row[0]}, 姓名: {row[1]}, 年龄: {row[2]}")

# 方式3：指定条数查询（fetchmany(n)）
cursor.execute("select * from users;")
many_results = cursor.fetchmany(2)  # 仅获取前2条
print("\n指定条数查询结果：", many_results)

# 进阶：返回字典格式结果（更易读）
# 创建游标时指定 row_factory
conn.row_factory = sqlite3.row
cursor = conn.cursor()
cursor.execute("select * from users where id = ?;", (1,))
dict_result = cursor.fetchone()
print("\n字典格式结果：", dict_result["name"], dict_result["email"])  # 可通过键取值

步骤5：更新数据（update）

根据条件修改已有数据，注意加 where 子句（否则会更新全表）：

update_sql = "update users set age = ? where name = ?;"
cursor.execute(update_sql, (26, "alice"))  # 将alice的年龄改为26
conn.commit()

# 查看受影响的行数
print("更新影响的行数：", cursor.rowcount)  # 输出：1

步骤6：删除数据（delete）

同样需加 where 子句（否则删除全表数据）：

delete_sql = "delete from users where id = ?;"
cursor.execute(delete_sql, (4,))  # 删除id为4的记录
conn.commit()

print("删除影响的行数：", cursor.rowcount)  # 输出：1

步骤7：关闭资源（避免泄漏）

# 先关游标，再关连接
cursor.close()
conn.close()

三、最佳实践

1. 使用上下文管理器（with 语句）

自动关闭游标/连接，无需手动 close，更简洁安全：

import sqlite3

# 上下文管理器简化操作
with sqlite3.connect("test.db") as conn:
    cursor = conn.cursor()
    # 执行查询
    cursor.execute("select * from users;")
    print("上下文管理器查询结果：", cursor.fetchall())
    # 增删改无需手动commit，with块结束会自动提交（出错则回滚）
    cursor.execute("insert into users (name, age, email) values (?, ?, ?);", ("eve", 29, "eve@example.com"))

2. 防 sql 注入（关键）

严禁字符串拼接 sql，必须使用 ? 占位符：

# 错误示例（易被注入，如name传入 "alice'; drop table users; --"）
name = "alice"
sql = f"select * from users where name = '{name}';"  # 危险！

# 正确示例（占位符）
sql = "select * from users where name = ?;"
cursor.execute(sql, (name,))  # 安全

3. 处理异常（生产环境必备）

捕获 sqlite3.error 异常，避免程序崩溃：

import sqlite3

try:
    conn = sqlite3.connect("test.db")
    cursor = conn.cursor()
    # 执行可能出错的操作（如插入重复邮箱）
    cursor.execute("insert into users (name, age, email) values (?, ?, ?);", ("frank", 35, "alice@example.com"))
    conn.commit()
except sqlite3.integrityerror as e:
    # 捕获唯一键冲突异常
    print("插入失败：唯一键冲突", e)
    conn.rollback()  # 出错回滚事务
except sqlite3.error as e:
    print("数据库错误：", e)
    conn.rollback()
finally:
    if cursor:
        cursor.close()
    if conn:
        conn.close()