SQLite是一款轻量级、零配置的开源嵌入式关系型数据库，以其单一文件存储、高性能和强可靠性著称。Python通过内置的sqlite3模块提供了对SQLite数据库的完整支持，使其成为Python开发者处理本地数据存储的理想选择。
核心特点
1、嵌入式架构：无需单独服务器进程，直接嵌入到应用程序中。
2、单一文件存储：整个数据库存储在单个磁盘文件中，便于迁移和备份。
3、零配置：无需安装或管理，开箱即用。
4、跨平台兼容：支持Windows、Linux、macOS等主流操作系统。
5、ACID事务：保证数据操作的原子性、一致性、隔离性和持久性。
Python使用SQLite
1. 连接数据库
import sqlite3
# 连接数据库（如果不存在则创建）
conn = sqlite3.connect('example.db')
# 创建游标对象
cursor = conn.cursor()
# 执行SQL语句
cursor.execute('''CREATE TABLE IF NOT EXISTS users
(id INTEGER PRIMARY KEY, name TEXT, age INTEGER)''')
# 提交更改
conn.commit()
# 关闭连接
conn.close()
2. 插入数据
def insert_user(name, age):
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
# 使用参数化查询防止SQL注入
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", (name, age))
conn.commit()
conn.close()
# 插入多条数据
users = [('Alice', 25), ('Bob', 30), ('Charlie', 35)]
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
cursor.executemany("INSERT INTO users (name, age) VALUES (?, ?)", users)
conn.commit()
conn.close()
3. 查询数据
def get_all_users():
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
conn.close()
return rows
def get_user_by_name(name):
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
cursor.execute("SELECT * FROM users WHERE name=?", (name,))
user = cursor.fetchone()
conn.close()
return user
4. 更新和删除数据
def update_user_age(name, new_age):
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
cursor.execute("UPDATE users SET age=? WHERE name=?", (new_age, name))
conn.commit()
conn.close()
def delete_user(name):
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
cursor.execute("DELETE FROM users WHERE name=?", (name,))
conn.commit()
conn.close()
功能示例
1. 使用事务
def transfer_funds(from_id, to_id, amount):
conn = sqlite3.connect('bank.db')
cursor = conn.cursor()
try:
# 开始事务
cursor.execute("BEGIN TRANSACTION")
# 扣除转出账户金额
cursor.execute("UPDATE accounts SET balance = balance - ? WHERE id = ?", (amount, from_id))
# 增加转入账户金额
cursor.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?", (amount, to_id))
# 提交事务
conn.commit()
print("转账成功")
except sqlite3.Error as e:
# 发生错误时回滚
conn.rollback()
print(f"转账失败: {e}")
finally:
conn.close()
2. 使用上下文管理器
from contextlib import closing
def get_user_count():
with closing(sqlite3.connect('example.db')) as conn:
cursor = conn.cursor()
cursor.execute("SELECT COUNT(*) FROM users")
count = cursor.fetchone()
return count
3. 使用Row工厂获取字典式结果
conn = sqlite3.connect('example.db')
conn.row_factory = sqlite3.Row # 设置行工厂
cursor = conn.cursor()
cursor.execute("SELECT * FROM users WHERE age > ?", (30,))
for row in cursor:
print(f"ID: {row['id']}, Name: {row['name']}, Age: {row['age']}")
conn.close()
性能优化
1、批量操作：使用 executemany() 进行批量插入。
2、合理使用索引：为常用查询字段创建索引。
3、PRAGMA设置：调整SQLite的PRAGMA参数优化性能。
4、定期维护：使用VACUUM命令回收未使用空间。
5、WAL模式：对于高并发读场景，使用 PRAGMA journal_mode=WAL 。
# 性能优化示例
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
# 设置WAL模式提高并发读取性能
cursor.execute("PRAGMA journal_mode=WAL")
# 为常用查询字段创建索引
cursor.execute("CREATE INDEX IF NOT EXISTS idx_user_age ON users(age)")
conn.commit()
conn.close()
适用场景
适用场景：
移动应用和嵌入式系统
桌面应用程序
小型到中型网站
开发和测试环境
数据分析和缓存
局限性：
不适合高并发写入场景
缺乏网络接口，不适合客户端-服务器架构
用户权限管理功能有限
SQLite与Python的结合为开发者提供了一个简单而强大的本地数据存储解决方案。内置的sqlite3模块提供了强大而便捷的数据库操作能力，使得SQLite成为开发中小型应用的理想选择。通过合理使用其特性，可以在各种应用场景中实现高效、可靠的数据管理。理解其优势和局限，合理运用在各种场景中，将极大提升开发效率和应用程序质量。