SQLite3 Python教程：内置SQLite数据库操作详解

SQLite与sqlite3模块简介

SQLite是一款轻量级关系型数据库管理系统，无需安装独立服务器，非常适合中小型项目开发。Python通过内置的sqlite3模块提供对SQLite的支持，让你无需安装额外库即可操作数据库。

sqlite3模块让数据库的创建和使用变得像操作普通文件一样简单。这一特性在原型开发、嵌入式系统、桌面应用以及教学场景中尤为实用。

安装与兼容性

sqlite3模块自Python 2.5起就包含在标准库中，无需额外安装。它完全兼容Windows、macOS和Linux操作系统，提供与SQLite 3版本交互的接口。

import sqlite3

连接数据库与创建连接

创建文件型数据库

conn = sqlite3.connect('my_database.db')

执行此命令后，如果指定文件不存在，系统会自动创建数据库。

创建内存数据库

conn = sqlite3.connect(':memory:')

这种方式特别适合测试和临时操作。

连接的其他参数

conn = sqlite3.connect('database.db', 
                      check_same_thread=False,
                      detect_types=sqlite3.PARSE_DECLTYPES)

创建表与数据模式操作

创建表

cursor = conn.cursor()
cursor.execute('''
    CREATE TABLE users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        age INTEGER,
        email TEXT UNIQUE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
''')
conn.commit()

SQLite数据类型

SQLite支持五种基本数据类型：

• INTEGER - 整数
• REAL - 浮点数
• TEXT - 文本字符串
• BLOB - 二进制数据
• NULL - 空值

SQLite采用动态类型系统，灵活性高，但操作数据时需格外小心。

CRUD操作（增、查、改、删）

插入数据

# 插入单条记录
cursor.execute("INSERT INTO users (name, age, email) VALUES (?, ?, ?)", 
               ("张三", 30, "zhangsan@example.com"))

# 插入多条记录
users_data = [
    ("李四", 25, "lisi@example.com"),
    ("王五", 35, "wangwu@example.com"),
    ("赵六", 28, "zhaoliu@example.com")
]
cursor.executemany("INSERT INTO users (name, age, email) VALUES (?, ?, ?)", users_data)
conn.commit()

查询数据

# 查询所有记录
cursor.execute("SELECT * FROM users")
all_users = cursor.fetchall()

# 带条件查询
cursor.execute("SELECT name, age FROM users WHERE age > ?", (25,))
adult_users = cursor.fetchall()

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

更新数据

cursor.execute("UPDATE users SET age = ? WHERE name = ?", (31, "张三"))
conn.commit()

# 获取受影响的行数
print(f"更新行数：{cursor.rowcount}")

删除数据

cursor.execute("DELETE FROM users WHERE name = ?", ("张三",))
conn.commit()
print(f"删除行数：{cursor.rowcount}")

事务处理与错误管理

sqlite3模块默认自动提交事务，但你可以通过conn.isolation_level控制事务行为。建议在批量操作中使用显式事务以提高性能。

try:
    conn.execute("BEGIN")
    # 执行多个操作
    cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("测试用户", 20))
    cursor.execute("UPDATE users SET age = age + 1 WHERE name = ?", ("测试用户",))
    conn.commit()
except Exception as e:
    conn.rollback()
    print(f"事务回滚：{e}")

性能优化技巧

• 使用executemany()批量插入数据
• 合理创建索引加速查询
• 使用PRAGMA指令调整SQLite设置
• 批量操作时使用显式事务

# 启用WAL模式提升并发性能
conn.execute("PRAGMA journal_mode=WAL")
# 设置缓存大小
conn.execute("PRAGMA cache_size=-8000")  # 8MB缓存

与Pandas集成

SQLite与Pandas配合使用，可以轻松实现数据分析和导出。

import pandas as pd

# 从SQLite读取数据到DataFrame
df = pd.read_sql_query("SELECT * FROM users", conn)

# 将DataFrame写入SQLite表
df.to_sql('users_backup', conn, if_exists='replace', index=False)

备份与恢复

# 备份数据库
import shutil
shutil.copy2('my_database.db', 'my_database_backup.db')

# 使用SQLite的备份API
def backup_db(src_conn, dst_conn):
    src_conn.backup(dst_conn)

常见问题与最佳实践

• 始终使用参数化查询（?占位符）防止SQL注入
• 操作完成后及时关闭连接
• 使用with语句管理资源
• 合理使用索引提升查询性能

with sqlite3.connect('my_database.db') as conn:
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users")
    data = cursor.fetchall()