Python基础教程（八十七）访问数据库之SQLite：指尖上的数据库，深度解析Python与SQLite的零配置高效协作（附实战代码）-优快云博客

一、SQLite核心优势：轻量嵌入，即开即用

相较于MySQL/PostgreSQL等数据库，SQLite的核心差异如下：

特性	SQLite	传统数据库
架构	嵌入式，无独立服务器	客户端-服务器模式
部署	零配置，单文件存储	需安装配置服务
并发写入	库级锁，适合低并发场景	行级/表级锁，高并发
适用场景	移动端、桌面应用、小型Web	高并发Web、企业应用

二、Python操作SQLite全流程详解

1. 基础操作四步法

import sqlite3

# 1. 连接数据库（自动创建）
conn = sqlite3.connect('user_db.db')  

# 2. 创建游标
cursor = conn.cursor()  

# 3. 执行SQL（创建用户表）
cursor.execute('''CREATE TABLE IF NOT EXISTS users (
                id INTEGER PRIMARY KEY,
                name TEXT NOT NULL,
                email TEXT UNIQUE,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)''')

# 4. 提交并关闭
conn.commit()  
conn.close()

2. 高效CRUD与防注入

# 安全插入（参数化查询）
new_user = ('李雷', 'lilei@example.com')
cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", new_user)

# 批量插入加速
users_data = [('韩梅梅', 'hanmeimei@test.com'), ('小明', 'xiaoming@data.org')]
cursor.executemany("INSERT INTO users (name, email) VALUES (?, ?)", users_data)

# 查询与结果遍历
cursor.execute("SELECT * FROM users WHERE email LIKE ?", ('%@test.com',))
for row in cursor.fetchall():
    print(f"ID:{row[0]}, 姓名:{row[1]}, 邮箱:{row[2]}")

三、进阶实战技巧

1. 内存数据库：极速测试

mem_conn = sqlite3.connect(':memory:')  # 内存中运行

2. 连接池管理（提升Web性能）

import sqlite3
from contextlib import closing

def get_user(user_id):
    with closing(sqlite3.connect('app.db')) as conn:  # 自动关闭连接
        cursor = conn.cursor()
        cursor.execute("SELECT name FROM users WHERE id=?", (user_id,))
        return cursor.fetchone()[0]

3. JSON扩展支持（v3.38+）

# 启用JSON1扩展
conn = sqlite3.connect('data.db')
conn.enable_load_extension(True)
conn.load_extension("./sqlite3-json.so")  # 加载扩展

# 直接查询JSON字段
cursor.execute("SELECT json_extract(profile, '$.age') FROM users")

四、完整示例：用户管理系统

import sqlite3
from datetime import datetime

def init_db():
    conn = sqlite3.connect('user_system.db')
    cursor = conn.cursor()
    cursor.execute('''CREATE TABLE IF NOT EXISTS users (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    username TEXT UNIQUE NOT NULL,
                    password_hash TEXT NOT NULL,
                    last_login TIMESTAMP)''')
    conn.commit()
    return conn

def add_user(conn, username, password):
    try:
        cursor = conn.cursor()
        cursor.execute("INSERT INTO users (username, password_hash) VALUES (?, ?)", 
                      (username, password))
        conn.commit()
        print(f"用户 {username} 创建成功！")
    except sqlite3.IntegrityError:
        print("错误：用户名已存在！")

# 调用示例
db_conn = init_db()
add_user(db_conn, "tech_writer", "secure_hash_123")
db_conn.close()

最佳实践建议：