Python操作Sqlite3

一、基本操作

#!/usr/bin/env python
# -*- coding:utf-8 -*-
# __author__ =

import sqlite3

# Python自2.5版本开始内置sqlite3模块，无需额外安装
print(sqlite3.version)  # 输出模块版本
print(sqlite3.sqlite_version)  # 输出SQLite引擎版本

# 创建/连接数据库文件
conn = sqlite3.connect('mydatabase.db')

# 也可以使用内存数据库（临时存储）
# conn = sqlite3.connect(':memory:')

# 创建游标对象
cursor = conn.cursor()

# 创建表
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    email TEXT UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
''')

# 单条插入
cursor.execute('''
INSERT INTO users (username, email)
VALUES (?, ?)
''', ('john_doe', 'john@example.com'))

# 批量插入
users = [
    ('jane_smith', 'jane@example.com'),
    ('bob_jones', 'bob@example.com')
]
cursor.executemany('''
INSERT INTO users (username, email)
VALUES (?, ?)
''', users)

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

# 遍历结果
for row in rows:
    print(row)

# 分页查询
cursor.execute('SELECT * FROM users LIMIT 10 OFFSET 0')

# 更新数据
cursor.execute('''
UPDATE users
SET email = ?
WHERE username = ?
''', ('new_email@example.com', 'john_doe'))

# 删除数据
cursor.execute('''
DELETE FROM users
WHERE username = ?
''', ('john_doe',))

# 事务处理
try:
    # 执行多个操作
    cursor.execute("INSERT ...")
    cursor.execute("UPDATE ...")
    conn.commit()  # 提交事务
except sqlite3.Error as e:
    conn.rollback()  # 回滚事务
    print(f"事务处理失败: {e}")

# 关闭资源
cursor.close()
conn.close()

 二、高级操作
1、使用上下文管理器

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

2、处理BLOB数据

# 插入图片
with open('image.jpg', 'rb') as f:
    img_data = f.read()

cursor.execute('''
INSERT INTO images (name, data)
VALUES (?, ?)
''', ('profile_pic', sqlite3.Binary(img_data)))

# 查询图片
cursor.execute('SELECT data FROM images WHERE name = ?', ('profile_pic',))
image_data = cursor.fetchone()[0]

3、使用自定义函数

# 注册自定义函数
def reverse_text(s):
    return s[::-1]

conn.create_function("reverse", 1, reverse_text)

# 使用自定义函数
cursor.execute("SELECT reverse(username) FROM users")

4、开启外键约束，默认情况下SQLite外键约束是关闭的，需要手动开启：

cursor.execute("PRAGMA foreign_keys = ON;")

5、常见错误处理

错误类型	原因	解决方法
OperationalError	表不存在	检查表名拼写
IntegrityError	唯一约束冲突	确保数据唯一性
ProgrammingError	SQL语法错误	检查SQL语句
DatabaseError	数据库文件损坏	尝试修复或恢复备份

6、性能优化建议
使用executemany()批量插入数据
避免频繁打开/关闭连接
使用索引优化查询
适当使用事务处理
定期进行数据库分析和优化

# 分析数据库
cursor.execute("ANALYZE;")
# 优化数据库
cursor.execute("VACUUM;")

7、推荐实践
使用参数化查询防止SQL注入
显式关闭游标和连接
对敏感数据进行加密存储
定期备份数据库文件
使用类型提示增强代码可读性

from typing import List, Tuple

def get_users() -> List[Tuple]:
    with sqlite3.connect('mydatabase.db') as conn:
        with conn.cursor() as cursor:
            cursor.execute("SELECT * FROM users")
            return cursor.fetchall()

8、扩展应用
与ORM框架集成（如SQLAlchemy）
构建GUI应用（PyQt/PySide）
移动应用后端（Kivy）
桌面工具开发（Tkinter）

9、sqlite-web管理工具安装

$ pip install sqlite-web
$ sqlite_web /path/to/database.db

参考链接：
    https://www.toutiao.com/article/7488374613488058892/
    https://www.toutiao.com/article/7471615185578213924/     # Faker库构建测试数据
    https://www.cnblogs.com/paleDream/p/16626311.html       # Faker库构建测试数据
    https://github.com/lk-geimfari/mimesis       # mimesis库构建测试数据