305 lines
8.6 KiB
Markdown
305 lines
8.6 KiB
Markdown
# MySQL Wrapper Project - New SQL (NSQL)
|
|
|
|
A lightweight, thread-safe Python wrapper for PyMySQL with enhanced features.
|
|
|
|
|
|
|
|
|
|
|
|
## Table of Contents
|
|
- [Advantages](#advantages)
|
|
- [Technical Principles](#technical-principles)
|
|
- [Features](#features)
|
|
- [API Reference](#api-reference)
|
|
- [Usage Examples](#usage-examples)
|
|
- [Limitations](#limitations)
|
|
- [Comparison](#comparison-with-other-projects)
|
|
|
|
## Advantages
|
|
|
|
### Compared to Other MySQL Wrappers
|
|
✔ **Thread Safety** - Built-in threading lock mechanism
|
|
✔ **SQL Injection Protection** - Strict identifier validation
|
|
✔ **Automatic Type Conversion** - Smart result type handling
|
|
✔ **Flexible Parameter Binding** - Supports both tuple and dict parameters
|
|
✔ **Connection Resilience** - Automatic reconnection handling
|
|
✔ **Debug Mode** - Detailed SQL logging for development
|
|
|
|
## Technical Principles
|
|
|
|
### Transaction Handling
|
|
- Uses PyMySQL's native transaction support
|
|
- Automatic `COMMIT` after each successful operation
|
|
- Manual transaction control available through raw connection
|
|
|
|
### Connection Pool
|
|
- Not a traditional connection pool
|
|
- Single persistent connection with thread locking
|
|
- Lightweight alternative for moderate workloads
|
|
- Suitable for long-running applications
|
|
|
|
## Features
|
|
|
|
### Core Features
|
|
- Parameterized query building
|
|
- Automatic FROM clause completion
|
|
- JSON data type support
|
|
- Binary data handling utilities
|
|
- Debug mode with SQL logging
|
|
- Dictionary-style result formatting
|
|
|
|
### Security Features
|
|
- SQL injection prevention
|
|
- Strict identifier validation
|
|
- Proper string escaping
|
|
- Parameter separation from queries
|
|
|
|
## API Reference
|
|
|
|
### Main Classes
|
|
|
|
#### `MySQL(host, port, charset="utf8", debug=False)`
|
|
Main wrapper class constructor.
|
|
|
|
#### `NewCursor(parent)`
|
|
Cursor class with enhanced methods.
|
|
|
|
### Core Methods
|
|
|
|
| Method | Description | Parameters |
|
|
|--------|-------------|------------|
|
|
| `use(db, Table=None)` | Switch database | `db`: database name |
|
|
| `select(_Table, FROM=None, WHERE=None, _limit=None)` | Basic SELECT | `_Table`: columns to select |
|
|
| `selectashead(_Table, FROM=None, WHERE=None, _limit=None)` | Dict-style results | Same as select |
|
|
| `insert(_Table, values=None, WHERE=None, **k)` | INSERT operation | Supports dict or kwargs |
|
|
| `update(WHERE, FROM=None, **k)` | UPDATE operation | WHERE clause required |
|
|
| `delete(FROM=None, WHERE=None)` | DELETE operation | |
|
|
| `istrue(FROM=None, WHERE=None)` | Existence check | Returns boolean |
|
|
|
|
### Helper Functions
|
|
|
|
| Function | Description | Example |
|
|
|----------|-------------|---------|
|
|
| `Func.NOW()` | Current timestamp | `Func.NOW()` |
|
|
| `Func.JSON_ARRAY()` | JSON array builder | `Func.JSON_ARRAY(1,2,3)` |
|
|
| `Func.insertbytes()` | Binary data handler | `Func.insertbytes(b'data')` |
|
|
|
|
## Usage Examples
|
|
|
|
### Basic Usage
|
|
```python
|
|
db = MySQL('localhost', 3306, debug=True)
|
|
db.__login__('user', 'password')
|
|
cursor = db.NewCursor()
|
|
|
|
# Select example
|
|
cursor.use('mydb', 'users')
|
|
results = cursor.select('*', WHERE=('age > %s', (18,)), _limit=10)
|
|
|
|
# Insert example
|
|
cursor.insert('users', {'name': 'John', 'age': 25})
|
|
|
|
# Transaction example
|
|
try:
|
|
cursor.update(WHERE=('id=%s', (1,)), FROM='users', balance=100)
|
|
cursor.update(WHERE=('id=%s', (2,)), FROM='users', balance=200)
|
|
except:
|
|
cursor.conn.rollback()
|
|
```
|
|
|
|
### Advanced Features
|
|
```python
|
|
# Binary data insertion
|
|
cursor.insert('files', {
|
|
'name': 'data.bin',
|
|
'content': Func.insertbytes(b'\x00\x01\x02')
|
|
})
|
|
|
|
# JSON data handling
|
|
cursor.insert('config', {
|
|
'settings': Func.JSON_ARRAY('item1', 'item2')
|
|
})
|
|
|
|
# Dictionary-style results
|
|
users = cursor.selectashead('*', FROM='users')
|
|
for user in users:
|
|
print(user['name'], user['age'])
|
|
```
|
|
|
|
## Limitations
|
|
|
|
### Not Recommended For
|
|
❌ High-concurrency applications (consider connection pool)
|
|
❌ Complex transaction scenarios
|
|
❌ ORM-like object mapping
|
|
❌ Asynchronous applications
|
|
|
|
### Performance Considerations
|
|
- Single connection model may bottleneck under heavy load
|
|
- Not optimized for bulk operations
|
|
- Type conversion adds minor overhead
|
|
|
|
## Comparison with Other Projects
|
|
|
|
| Feature | This Wrapper | PyMySQL | SQLAlchemy | Django ORM |
|
|
|---------|-------------|---------|------------|------------|
|
|
| Thread Safety | ✔ | ✖ | ✔ | ✔ |
|
|
| Connection Pool | ✖ | ✖ | ✔ | ✔ |
|
|
| ORM Features | ✖ | ✖ | ✔ | ✔ |
|
|
| SQL Building | ✔ | ✖ | ✔ | ✔ |
|
|
| Binary Support | ✔ | ✔ | ✔ | ✔ |
|
|
| Debug Mode | ✔ | ✖ | Partial | Partial |
|
|
|
|
---
|
|
|
|
# MySQL 封装项目 - New SQL (NSQL)
|
|
|
|
一个轻量级、线程安全的 PyMySQL Python 封装器，具有增强功能。
|
|
|
|
|
|
|
|
|
|
|
|
## 目录
|
|
- [优势](#优势)
|
|
- [技术原理](#技术原理)
|
|
- [功能特性](#功能特性)
|
|
- [API参考](#api参考)
|
|
- [使用示例](#使用示例)
|
|
- [局限性](#局限性)
|
|
- [对比](#与其他项目的对比)
|
|
|
|
## 优势
|
|
|
|
### 与其他MySQL封装器的比较
|
|
✔ **线程安全** - 内置线程锁机制
|
|
✔ **SQL注入防护** - 严格的标识符验证
|
|
✔ **自动类型转换** - 智能结果类型处理
|
|
✔ **灵活参数绑定** - 支持元组和字典参数
|
|
✔ **连接弹性** - 自动重连处理
|
|
✔ **调试模式** - 详细的SQL日志记录
|
|
|
|
## 技术原理
|
|
|
|
### 事务处理
|
|
- 使用PyMySQL原生事务支持
|
|
- 每次成功操作后自动提交`COMMIT`
|
|
- 可通过原始连接手动控制事务
|
|
|
|
### 连接池
|
|
- 非传统连接池
|
|
- 带线程锁的单一持久连接
|
|
- 适用于中等工作负载的轻量级方案
|
|
- 适合长期运行的应用程序
|
|
|
|
## 功能特性
|
|
|
|
### 核心功能
|
|
- 参数化查询构建
|
|
- 自动FROM子句补全
|
|
- JSON数据类型支持
|
|
- 二进制数据处理工具
|
|
- 带SQL日志记录的调试模式
|
|
- 字典式结果格式化
|
|
|
|
### 安全特性
|
|
- SQL注入预防
|
|
- 严格标识符验证
|
|
- 正确的字符串转义
|
|
- 查询与参数分离
|
|
|
|
## API参考
|
|
|
|
### 主要类
|
|
|
|
#### `MySQL(host, port, charset="utf8", debug=False)`
|
|
主封装类构造函数
|
|
|
|
#### `NewCursor(parent)`
|
|
带增强方法的游标类
|
|
|
|
### 核心方法
|
|
|
|
| 方法 | 描述 | 参数 |
|
|
|------|------|------|
|
|
| `use(db, Table=None)` | 切换数据库 | `db`: 数据库名 |
|
|
| `select(_Table, FROM=None, WHERE=None, _limit=None)` | 基础SELECT | `_Table`: 选择列 |
|
|
| `selectashead(_Table, FROM=None, WHERE=None, _limit=None)` | 字典式结果 | 同select |
|
|
| `insert(_Table, values=None, WHERE=None, **k)` | INSERT操作 | 支持字典或关键字参数 |
|
|
| `update(WHERE, FROM=None, **k)` | UPDATE操作 | 必须包含WHERE子句 |
|
|
| `delete(FROM=None, WHERE=None)` | DELETE操作 | |
|
|
| `istrue(FROM=None, WHERE=None)` | 存在性检查 | 返回布尔值 |
|
|
|
|
### 辅助函数
|
|
|
|
| 函数 | 描述 | 示例 |
|
|
|------|------|------|
|
|
| `Func.NOW()` | 当前时间戳 | `Func.NOW()` |
|
|
| `Func.JSON_ARRAY()` | JSON数组构建器 | `Func.JSON_ARRAY(1,2,3)` |
|
|
| `Func.insertbytes()` | 二进制数据处理 | `Func.insertbytes(b'data')` |
|
|
|
|
## 使用示例
|
|
|
|
### 基础用法
|
|
```python
|
|
db = MySQL('localhost', 3306, debug=True)
|
|
db.__login__('user', 'password')
|
|
cursor = db.NewCursor()
|
|
|
|
# 查询示例
|
|
cursor.use('mydb', 'users')
|
|
results = cursor.select('*', WHERE=('age > %s', (18,)), _limit=10)
|
|
|
|
# 插入示例
|
|
cursor.insert('users', {'name': '张三', 'age': 25})
|
|
|
|
# 事务示例
|
|
try:
|
|
cursor.update(WHERE=('id=%s', (1,)), FROM='users', balance=100)
|
|
cursor.update(WHERE=('id=%s', (2,)), FROM='users', balance=200)
|
|
except:
|
|
cursor.conn.rollback()
|
|
```
|
|
|
|
### 高级功能
|
|
```python
|
|
# 二进制数据插入
|
|
cursor.insert('files', {
|
|
'name': 'data.bin',
|
|
'content': Func.insertbytes(b'\x00\x01\x02')
|
|
})
|
|
|
|
# JSON数据处理
|
|
cursor.insert('config', {
|
|
'settings': Func.JSON_ARRAY('item1', 'item2')
|
|
})
|
|
|
|
# 字典式结果
|
|
users = cursor.selectashead('*', FROM='users')
|
|
for user in users:
|
|
print(user['name'], user['age'])
|
|
```
|
|
|
|
## 局限性
|
|
|
|
### 不推荐场景
|
|
❌ 高并发应用（考虑连接池方案）
|
|
❌ 复杂事务场景
|
|
❌ 类ORM的对象映射
|
|
❌ 异步应用
|
|
|
|
### 性能考量
|
|
- 单连接模型在重负载下可能成为瓶颈
|
|
- 未针对批量操作优化
|
|
- 类型转换会增加少量开销
|
|
|
|
## 与其他项目的对比
|
|
|
|
| 特性 | NSQL | PyMySQL | SQLAlchemy | Django ORM |
|
|
|------|---------|---------|------------|------------|
|
|
| 线程安全 | ✔ | ✖ | ✔ | ✔ |
|
|
| 连接池 | ✖ | ✖ | ✔ | ✔ |
|
|
| ORM功能 | ✖ | ✖ | ✔ | ✔ |
|
|
| SQL构建 | ✔ | ✖ | ✔ | ✔ |
|
|
| 二进制支持 | ✔ | ✔ | ✔ | ✔ |
|
|
| 调试模式 | ✔ | ✖ | 部分 | 部分 | |