223 lines
6.1 KiB
Markdown
223 lines
6.1 KiB
Markdown
# 数据库系统使用文档
|
||
|
||
本文档详细介绍了本项目中使用的异步数据库系统，包括其架构设计、使用方法和最佳实践。
|
||
|
||
## 系统概述
|
||
|
||
本项目的数据库系统基于 `aiosqlite` 库构建，提供了异步的 SQLite 数据库访问接口。系统主要特性包括：
|
||
|
||
1. **异步操作**：完全支持异步/await模式，适配NoneBot2框架
|
||
2. **连接池**：内置连接池机制，提高数据库访问性能
|
||
3. **参数化查询**：支持安全的参数化查询，防止SQL注入
|
||
4. **SQL文件支持**：可以直接执行SQL文件中的脚本
|
||
5. **类型支持**：支持 `pathlib.Path` 和 `str` 类型的路径参数
|
||
|
||
## 核心类和方法
|
||
|
||
### DatabaseManager 类
|
||
|
||
`DatabaseManager` 是数据库操作的核心类，提供了以下主要方法：
|
||
|
||
#### 初始化
|
||
```python
|
||
from konabot.common.database import DatabaseManager
|
||
from pathlib import Path
|
||
|
||
# 使用默认数据库路径
|
||
db = DatabaseManager()
|
||
|
||
# 指定了义数据库路径
|
||
db = DatabaseManager("./data/myapp.db")
|
||
db = DatabaseManager(Path("./data/myapp.db"))
|
||
```
|
||
|
||
#### 查询操作
|
||
```python
|
||
# 执行查询语句并返回结果
|
||
results = await db.query("SELECT * FROM users WHERE age > ?", (18,))
|
||
|
||
# 从SQL文件执行查询
|
||
results = await db.query_by_sql_file("./sql/get_users.sql", (18,))
|
||
```
|
||
|
||
#### 执行操作
|
||
```python
|
||
# 执行非查询语句
|
||
await db.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("张三", "zhangsan@example.com"))
|
||
|
||
# 执行SQL脚本（不带参数）
|
||
await db.execute_script("""
|
||
CREATE TABLE IF NOT EXISTS users (
|
||
id INTEGER PRIMARY KEY,
|
||
name TEXT NOT NULL,
|
||
email TEXT UNIQUE
|
||
);
|
||
INSERT INTO users (name, email) VALUES ('测试用户', 'test@example.com');
|
||
""")
|
||
|
||
# 从SQL文件执行非查询语句
|
||
await db.execute_by_sql_file("./sql/create_tables.sql")
|
||
|
||
# 带参数执行SQL文件
|
||
await db.execute_by_sql_file("./sql/insert_user.sql", ("张三", "zhangsan@example.com"))
|
||
|
||
# 执行多条语句（每条语句使用相同参数）
|
||
await db.execute_many("INSERT INTO users (name, email) VALUES (?, ?)", [
|
||
("张三", "zhangsan@example.com"),
|
||
("李四", "lisi@example.com"),
|
||
("王五", "wangwu@example.com")
|
||
])
|
||
|
||
# 从SQL文件执行多条语句（每条语句使用相同参数）
|
||
await db.execute_many_values_by_sql_file("./sql/batch_insert.sql", [
|
||
("张三", "zhangsan@example.com"),
|
||
("李四", "lisi@example.com")
|
||
])
|
||
```
|
||
|
||
## SQL文件处理机制
|
||
|
||
### 单语句SQL文件
|
||
```sql
|
||
-- insert_user.sql
|
||
INSERT INTO users (name, email) VALUES (?, ?);
|
||
```
|
||
|
||
```python
|
||
# 使用方式
|
||
await db.execute_by_sql_file("./sql/insert_user.sql", ("张三", "zhangsan@example.com"))
|
||
```
|
||
|
||
### 多语句SQL文件
|
||
```sql
|
||
-- setup.sql
|
||
CREATE TABLE IF NOT EXISTS users (
|
||
id INTEGER PRIMARY KEY,
|
||
name TEXT NOT NULL,
|
||
email TEXT UNIQUE
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS profiles (
|
||
user_id INTEGER,
|
||
age INTEGER,
|
||
FOREIGN KEY (user_id) REFERENCES users(id)
|
||
);
|
||
```
|
||
|
||
```python
|
||
# 使用方式
|
||
await db.execute_by_sql_file("./sql/setup.sql")
|
||
```
|
||
|
||
### 多语句带不同参数的SQL文件
|
||
```sql
|
||
-- batch_operations.sql
|
||
INSERT INTO users (name, email) VALUES (?, ?);
|
||
INSERT INTO profiles (user_id, age) VALUES (?, ?);
|
||
```
|
||
|
||
```python
|
||
# 使用方式
|
||
await db.execute_by_sql_file("./sql/batch_operations.sql", [
|
||
("张三", "zhangsan@example.com"), # 第一条语句的参数
|
||
(1, 25) # 第二条语句的参数
|
||
])
|
||
```
|
||
|
||
## 最佳实践
|
||
|
||
### 1. 数据库表设计
|
||
```sql
|
||
-- 推荐的表设计实践
|
||
CREATE TABLE IF NOT EXISTS example_table (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
name TEXT NOT NULL,
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
```
|
||
|
||
### 2. SQL文件组织
|
||
建议按照功能模块组织SQL文件：
|
||
```
|
||
plugin/
|
||
├── sql/
|
||
│ ├── create_tables.sql
|
||
│ ├── insert_data.sql
|
||
│ ├── update_data.sql
|
||
│ └── query_data.sql
|
||
└── __init__.py
|
||
```
|
||
|
||
### 3. 错误处理
|
||
```python
|
||
try:
|
||
results = await db.query("SELECT * FROM users WHERE id = ?", (user_id,))
|
||
except Exception as e:
|
||
logger.error(f"数据库查询失败: {e}")
|
||
# 处理错误情况
|
||
```
|
||
|
||
### 4. 连接管理
|
||
```python
|
||
# 在应用启动时初始化
|
||
db_manager = DatabaseManager()
|
||
|
||
# 在应用关闭时清理连接
|
||
async def shutdown():
|
||
await db_manager.close_all_connections()
|
||
```
|
||
|
||
## 高级特性
|
||
|
||
### 连接池配置
|
||
```python
|
||
class DatabaseManager:
|
||
def __init__(self, db_path: Optional[Union[str, Path]] = None):
|
||
# 连接池大小配置
|
||
self._pool_size = 5 # 可根据需要调整
|
||
```
|
||
|
||
### 事务支持
|
||
```python
|
||
# 通过execute方法的自动提交机制支持事务
|
||
await db.execute("BEGIN TRANSACTION")
|
||
try:
|
||
await db.execute("INSERT INTO users (name) VALUES (?)", ("张三",))
|
||
await db.execute("INSERT INTO profiles (user_id, age) VALUES (?, ?)", (1, 25))
|
||
await db.execute("COMMIT")
|
||
except Exception:
|
||
await db.execute("ROLLBACK")
|
||
raise
|
||
```
|
||
|
||
## 注意事项
|
||
|
||
1. **异步环境**：所有数据库操作都必须在异步环境中执行
|
||
2. **参数安全**：始终使用参数化查询，避免SQL注入
|
||
3. **资源管理**：确保在应用关闭时调用 `close_all_connections()`
|
||
4. **SQL解析**：使用 `sqlparse` 库准确解析SQL语句，正确处理包含分号的字符串和注释
|
||
5. **错误处理**：适当处理数据库操作可能抛出的异常
|
||
|
||
## 常见问题
|
||
|
||
### Q: 如何处理数据库约束错误？
|
||
A: 确保SQL语句中的字段名正确引用，特别是保留字需要使用双引号包围：
|
||
```sql
|
||
CREATE TABLE air_conditioner (
|
||
id VARCHAR(128) PRIMARY KEY,
|
||
"on" BOOLEAN NOT NULL, -- 使用双引号包围保留字
|
||
temperature REAL NOT NULL
|
||
);
|
||
```
|
||
|
||
### Q: 如何处理多个语句和参数的匹配？
|
||
A: 当SQL文件包含多个语句时，参数应该是参数列表，每个语句对应一个参数元组：
|
||
```python
|
||
await db.execute_by_sql_file("./sql/batch.sql", [
|
||
("参数1", "参数2"), # 第一个语句的参数
|
||
("参数3", "参数4") # 第二个语句的参数
|
||
])
|
||
```
|
||
|
||
通过遵循这些指南和最佳实践，您可以充分利用本项目的异步数据库系统，构建高性能、安全的数据库应用。 |