22-SQLite 程序中 SQLite 操作
📘 SQLite 操作
目标：用 SQLite 替代 JSON 文件，构建一个持久化、可查询、可扩展的多会话聊天记忆系统。
一、SQLite 是什么？
1.1 定义
SQLite 是一个 轻量级、无服务器、零配置 的关系型数据库引擎。
- 数据存储在 单个
.db文件 中（如chat.db） - 不需要启动数据库服务（对比 MySQL/PostgreSQL）
- 支持标准 SQL 语法（SELECT, INSERT, CREATE TABLE 等）
| 需求 | SQLite 是否满足 |
|---|---|
| 本地持久化聊天记录 | ✅ 单文件存储 |
| 多会话隔离（session_id） | ✅ 可建索引快速查询 |
| 中文支持 | ✅ 原生 UTF-8 |
| 无需运维 | ✅ 无后台进程 |
| Python 原生支持 | ✅ 标准库 sqlite3 |
1.2 核心概念
| 概念 | 说明 |
|---|---|
| 数据库文件 | 如 memory.db，包含所有表和数据 |
| 表（Table） | 结构化数据容器（如 chat_history） |
| 行（Row） | 一条记录（如一条用户消息） |
| 列（Column） | 字段（如 session_id, content） |
| 主键（Primary Key） | 唯一标识一行（可组合：session_id + message_index） |
1.3 执行方法
- sqlite3 命令行工具，
- Python 的 sqlite3 模块
二、实践篇：从零构建聊天记忆数据库
步骤 1：安装数据库SQLite，进入&退出
# apt install sqlite3 (如果已经安装，忽略)
sqlite3 --version # 查阅版本
进入
sqlite3 chat.db
# SQLite version 3.45.1 2024-01-30 16:01:20
# Enter ".help" for usage hints.
# sqlite>
退出
sqlite> .quit
步骤 2：设计表结构
创建一张表存储所有会话消息：
CREATE TABLE chat_history (
session_id TEXT NOT NULL,
message_index INTEGER NOT NULL,
role TEXT NOT NULL CHECK(role IN ('human', 'ai')),
content TEXT NOT NULL,
timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (session_id, message_index)
);
| 字段名（Column） | 数据类型（Type） | 约束（Constraints） | 说明（Purpose & Design Rationale） |
|---|---|---|---|
| session_id | TEXT | NOT NULL | 标识会话所属用户或上下文（如 "alice"、"user_123"）。实现多会话隔离，是分组查询的关键。 |
| message_index | INTEGER | NOT NULL | 同一会话内消息的顺序编号，确保对话顺序正确。 |
| 主键（Primary Key） | / | PRIMARY KEY (session_id, message_index) | 复合主键：确保同一会话内消息索引唯一，同时允许多个会话独立使用相同索引（如 alice.index=0 和 bob.index=0）。 |
| role | TEXT | NOT NULL + CHECK(role IN ('human', 'ai')) | 消息发送者角色：'human' 表示用户输入，'ai' 表示 AI 回复。LangChain 规定。 |
| content | TEXT | NOT NULL | 消息的实际文本内容，存储用户提问或 AI 回答的完整字符串。 |
| timestamp | DATETIME | DEFAULT CURRENT_TIMESTAMP | 消息创建时间，自动记录插入时刻，用于时间窗口管理（如“最近30分钟对话”）。 |
3. 📥 增（Insert） — 添加一条消息
-- 插入用户消息（human）
INSERT INTO chat_history (session_id, message_index, role, content)
VALUES ('user123', 1, 'human', '你好！');
-- 插入 AI 回复（ai）
INSERT INTO chat_history (session_id, message_index, role, content)
VALUES ('user123', 2, 'ai', '你好！有什么我可以帮你的吗？');
4. 🔍 查（Select） — 查询历史
查某一会话的全部消息（按顺序）
SELECT * FROM chat_history;
SELECT role, content, timestamp
FROM chat_history
WHERE session_id = 'user123'
ORDER BY message_index;
查所有会话的最新一条消息
SELECT session_id, role, content, timestamp
FROM chat_history ch1
WHERE message_index = (
SELECT MAX(message_index)
FROM chat_history ch2
WHERE ch2.session_id = ch1.session_id
);
5. ✏️ 改（Update） — 修改某条消息内容（谨慎使用）
⚠️ 通常聊天记录是不可变的，但技术上可以改：
-- 修改 user123 的第 1 条消息内容
UPDATE chat_history
SET content = '你好啊！',
timestamp = CURRENT_TIMESTAMP -- 可选：更新时间戳
WHERE session_id = 'user123' AND message_index = 1;
6. 💾 “保存”是什么意思？
在 SQLite 中：
- 每条
INSERT/UPDATE/DELETE默认自动提交（autocommit），立即写入文件； - 所以不需要额外“保存”命令；
- 数据库文件（如
chat.db）就是持久化存储。
✅ 只要你用的是
sqlite3命令行或 Python 的sqlite3模块（默认 autocommit），数据写入即永久保存。
7. 🗑️ 删（Delete） — 删除记录（慎重）
删除某一会话的全部记录
DELETE FROM chat_history
WHERE session_id = 'user123';
删除某一条特定消息
DELETE FROM chat_history
WHERE session_id = 'user123' AND message_index = 2;
清空整个表（保留结构）
DELETE FROM chat_history;
-- 或更高效（重置自增等，但 SQLite 无自增主键问题）
-- TRUNCATE 不被 SQLite 支持，用 DELETE 即可
🔧 在 sqlite3 命令行中完整操作示例
$ sqlite3 chat.db
-- 创建表（如果还没建）
CREATE TABLE IF NOT EXISTS chat_history (...); -- ← 你的建表语句
-- 插入数据
INSERT INTO chat_history (session_id, message_index, role, content)
VALUES ('alice', 1, 'human', '今天天气怎么样？');
INSERT INTO chat_history (session_id, message_index, role, content)
VALUES ('alice', 2, 'ai', '今天晴朗，适合出门！');
-- 查询
SELECT * FROM chat_history WHERE session_id = 'alice';
-- 删除
DELETE FROM chat_history WHERE session_id = 'alice' AND message_index = 2;
-- 退出
.quit
此时，chat.db 文件已包含所有未删除的数据，重启终端或电脑后依然存在。
🐍 在 Python 中使用（配合 LangChain）
如果你要用这个表配合 SQLChatMessageHistory，LangChain 会自动处理增、查操作：
# 2_fixed.py
from langchain_community.chat_message_histories import SQLChatMessageHistory
from sqlalchemy import create_engine
# 创建 SQLite 数据库引擎（自动创建 chat.db 文件）
engine = create_engine("sqlite:///chat.db")
# 使用 `connection` 参数（不再是 connection_string！）
history = SQLChatMessageHistory(
session_id="alice",
connection=engine, # 👈 关键修改：用 connection 代替 connection_string
)
# 查看历史
print("当前历史:", history.messages)
# 添加消息
history.add_user_message("明天会下雨吗？")
history.add_ai_message("据预报，明天多云。")
# 再次查看
print("更新后历史:", history.messages)
sqlite3 chat.db
# 在 SQLite 中，.schema 是一个 元命令（meta-command），
# 用于 显示数据库中表（或整个数据库）的创建语句（DDL）。
sqlite> .schema
CREATE TABLE message_store (
id INTEGER NOT NULL,
session_id TEXT,
message TEXT,
PRIMARY KEY (id)
);
sqlite> select * from message_store;
# ✅ 此时，chat.db 中已包含：
# session_id | message_index | role | content | timestamp
# -----------|---------------|-------|------------------------|------------
# alice | 1 | human | 你好 | ...
# alice | 2 | ai | 你好！ | ...
# alice | 3 | human | 明天会下雨吗？ | ...
# alice | 4 | ai | 据预报，明天多云。 | ...
#
# 🔒 所有操作默认自动提交（autocommit=True），无需手动 save() 或 commit()
浙公网安备 33010602011771号