Python连接SQLite数据库:使用内置sqlite3模块实现增删改查

南乔
南乔
发布时间:2026-11-17 | 5人浏览过
原创

本文详细介绍如何使用Python标准库内置的sqlite3模块连接SQLite数据库,并完整实现数据表创建及增删改查(CRUD)操作,包含参数化查询、事务管理与常见避坑指南。

在开发轻量级应用、原型系统或本地离线程序时,部署独立的关系型数据库服务(如 MySQL 或 PostgreSQL)往往会引入额外的配置与维护成本。Python 自带的 sqlite3 标准库无需安装任何第三方依赖,直接将数据库引擎内嵌在应用程序中,数据库实体仅为一个紧凑的单文件。本文将以最精炼、可直接运行的方式,讲解如何使用 sqlite3 模块完成数据库连接、创建数据表,并完整实现插入(Create)、查询(Read)、更新(Update)与删除(Delete)操作。

一、核心机制与核心对象关系

SQLite 属于无服务器(Serverless)架构的自包含关系型数据库引擎。在 Python 中操作 SQLite,核心依赖两个对象:连接对象(Connection)与游标对象(Cursor)。

  • Connection:代表与特定数据库文件的会话通道,负责控制事务范围(提交与回滚)、字符编码及底层资源关闭。
  • Cursor:在连接基础上创建的游标指针,负责向引擎发送 SQL 语句、维护执行状态并读取返回的行数据集。
Python连接SQLite数据库:使用内置sqlite3模块实现增删改查
图 1:Python sqlite3 模块的核心对象与工作架构

二、标准操作生命周期与准备工作

一次规范的 SQLite 交互包含建立连接、生成游标、执行 DDL/DML 指令、事务提交及最后关闭资源的完整生命周期。在编码中,推荐遵循清晰的执行步骤或使用 Python 上下文管理器来确保连接正确关闭。

Python连接SQLite数据库:使用内置sqlite3模块实现增删改查
图 2:SQLite 数据交互的标准 5 步生命周期流程

连接数据库并创建数据表的示例代码如下。我们在示例中使用 users.db 文件,如果该文件在当前工作目录中不存在,connect() 会自动创建它。

import sqlite3

def init_database(db_path: str = "users.db"):
    # 步骤 1:连接到 SQLite 数据库文件
    conn = sqlite3.connect(db_path)
    
    # 步骤 2:创建游标对象
    cursor = conn.cursor()
    
    # 步骤 3:编写并执行建表 DDL
    create_table_sql = """
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE NOT NULL,
        age INTEGER,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );
    """
    cursor.execute(create_table_sql)
    
    # 步骤 4:DDL 执行后提交变更
    conn.commit()
    
    # 步骤 5:关闭资源
    cursor.close()
    conn.close()
    print("数据库及 users 表初始化成功。")

if __name__ == "__main__":
    init_database()
内存数据库模式

如果在自动化单元测试或临时缓存场景中不需要持久化存储,可将路径指定为 sqlite3.connect(":memory:")。该模式数据驻留在内存中,连接关闭时自动销毁,读写速度极快。

三、实现数据的增删改查(CRUD)

增删改查是数据库交互的基石。在编写具体逻辑前,必须树立核心安全意识:绝不要使用 Python 的 %、str.format() 或 f-string 来拼接 SQL 语句中的变量,这会导致严重的 SQL 注入漏洞;必须使用 ? 占位符进行参数化查询。

Python连接SQLite数据库:使用内置sqlite3模块实现增删改查
图 3:SQL 注入成因与 sqlite3 参数化查询防护机理对比

1. 插入数据(Create)

插入操作使用 INSERT INTO 语句。对于单条数据,使用 cursor.execute();对于大批量数据插入,推荐使用 cursor.executemany(),后者在底层执行优化,吞吐效率显著高于循环单条插入。

import sqlite3

def add_single_user(name: str, email: str, age: int):
    conn = sqlite3.connect("users.db")
    cursor = conn.cursor()
    
    sql = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)"
    # 注意:参数必须以元组或列表传递
    cursor.execute(sql, (name, email, age))
    
    conn.commit()
    inserted_id = cursor.lastrowid
    print(f"成功插入单条数据,自增 ID 为:{inserted_id}")
    
    cursor.close()
    conn.close()

def add_multiple_users(user_list: list):
    conn = sqlite3.connect("users.db")
    cursor = conn.cursor()
    
    sql = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)"
    # 批量插入:传入包含多个元组的列表
    cursor.executemany(sql, user_list)
    
    conn.commit()
    print(f"成功批量插入 {cursor.rowcount} 条数据。")
    
    cursor.close()
    conn.close()

# 示例调用
if __name__ == "__main__":
    add_single_user("张三", "zhangsan@example.com", 25)
    
    batch_data = [
        ("李四", "lisi@example.com", 28),
        ("王五", "wangwu@example.com", 32),
        ("赵六", "zhaoliu@example.com", 22)
    ]
    add_multiple_users(batch_data)

2. 查询数据(Read)

执行 SELECT 查询后,数据暂存在游标缓冲区中。sqlite3 提供了三个读取方法:

  • fetchone():获取查询结果集的第一行,返回一个元组,没有更多行时返回 None。
  • fetchall():读取全部剩余行,返回一个由元组构成的列表。
  • fetchmany(size):按指定批次大小分段读取,避免大数据量一次性加载耗尽内存。

默认情况下,每行数据以普通元组形式返回,只能通过数字下标访问(如 row[0])。若想通过字段名访问,可以设置 conn.row_factory = sqlite3.Row。

import sqlite3

def query_users(min_age: int = 20):
    conn = sqlite3.connect("users.db")
    # 启用 sqlite3.Row,支持通过字段名访问列
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    sql = "SELECT id, name, email, age, created_at FROM users WHERE age >= ? ORDER BY id ASC"
    cursor.execute(sql, (min_age,))
    
    rows = cursor.fetchall()
    print(f"符合条件的用户数:{len(rows)}")
    for row in rows:
        # 可以像字典一样使用字段名
        print(f"ID: {row['id']} | 姓名: {row['name']} | 邮箱: {row['email']} | 年龄: {row['age']}")
        
    cursor.close()
    conn.close()

if __name__ == "__main__":
    query_users(24)

输出示例(示例展示):

符合条件的用户数:3
ID: 1 | 姓名: 张三 | 邮箱: zhangsan@example.com | 年龄: 25
ID: 2 | 姓名: 李四 | 邮箱: lisi@example.com | 年龄: 28
ID: 3 | 姓名: 王五 | 邮箱: wangwu@example.com | 年龄: 32

3. 更新数据(Update)

更新数据使用 UPDATE ... SET ... WHERE ... 语句。执行后可以通过 cursor.rowcount 属性检查实际受到影响并被修改的行数;如果 rowcount == 0,通常意味着 WHERE 条件没有匹配到记录。

import sqlite3

def update_user_email(user_id: int, new_email: str):
    conn = sqlite3.connect("users.db")
    cursor = conn.cursor()
    
    sql = "UPDATE users SET email = ? WHERE id = ?"
    cursor.execute(sql, (new_email, user_id))
    
    conn.commit()
    affected = cursor.rowcount
    if affected > 0:
        print(f"用户 ID {user_id} 邮箱已成功更新为:{new_email}")
    else:
        print(f"未找到 ID 为 {user_id} 的用户,未发生修改。")
        
    cursor.close()
    conn.close()

if __name__ == "__main__":
    update_user_email(1, "zhangsan_updated@example.com")

4. 删除数据(Delete)

删除操作使用 DELETE FROM ... WHERE ... 语句。执行删除时务必严格核对 WHERE 条件,防止全表数据被无意清空。

import sqlite3

def delete_user(user_id: int):
    conn = sqlite3.connect("users.db")
    cursor = conn.cursor()
    
    sql = "DELETE FROM users WHERE id = ?"
    cursor.execute(sql, (user_id,))
    
    conn.commit()
    affected = cursor.rowcount
    if affected > 0:
        print(f"已成功删除 ID 为 {user_id} 的用户。")
    else:
        print(f"目标用户 ID {user_id} 不存在。")
        
    cursor.close()
    conn.close()

if __name__ == "__main__":
    delete_user(4)

四、异常处理与优雅上下文管理

在实际生产编码中,若在 execute() 执行中抛出异常,后续代码可能跳过 commit() 与 close()。Python 的 sqlite3.Connection 原生支持作为上下文管理器(with conn:),但需要注意其原生行为特性:

  • with conn: 负责事务管理:代码块正常结束自动执行 commit();若代码块抛出异常,则自动执行 rollback()。
  • with conn: 并不负责关闭连接。因此,最稳妥的做法是在外层使用 contextlib.closing,或者在外层加上 try...finally 显式调用 conn.close()。
import sqlite3
from contextlib import closing

def safe_batch_transfer(source_id: int, target_id: int):
    # 使用 closing 确保在退出代码块时必然执行 conn.close()
    with closing(sqlite3.connect("users.db")) as conn:
        try:
            # with conn 自动管理事务提交与异常回滚
            with conn:
                cursor = conn.cursor()
                # 扣减逻辑或联合变更
                cursor.execute("UPDATE users SET age = age + 1 WHERE id = ?", (source_id,))
                cursor.execute("UPDATE users SET age = age - 1 WHERE id = ?", (target_id,))
            print("事务已顺利提交。")
        except sqlite3.Error as err:
            print(f"执行数据库操作失败,事务已自动回滚。原因:{err}")

五、核心 API 与排错速查总结

为了帮助读者在日常开发中快速选用方法并排查问题,下表及图 4 总结了 sqlite3 模块的关键接口职责与常见排错对照。

方法 / 属性 所属对象 常见用途 关键注意事项
connect(path) sqlite3 顶层模块 打开或创建数据库文件 多线程共享时需关注 check_same_thread 参数
cursor() Connection 创建命令执行游标 完成操作后建议显式关闭
execute(sql, params) Cursor / Connection 执行单条参数化 SQL 参数必须是元组或列表,单参数必须写成 (val,)
executemany(sql, seq) Cursor 执行参数列表的批量写入 效率显著高于循环 execute
commit() Connection 持久化提交当前未结事务 写操作未提交则数据在程序关闭后丢失
rollback() Connection 回滚当前未结事务中的变更 配合 try...except sqlite3.Error 保障数据一致性
row_factory = sqlite3.Row Connection 指定返回行映射方式 允许按键名访问字段(如 row['name'])
Python连接SQLite数据库:使用内置sqlite3模块实现增删改查
图 4:sqlite3 模块开发避坑与诊断速查参考
相关专题:

本站声明

本文内容仅供参考,不代表本站观点。转载或引用请注明出处。如发现内容问题,请通过联系我们页面反馈。

Python 相关产品和应用

全部下载

专题列表