277 lines
7.4 KiB
Markdown
277 lines
7.4 KiB
Markdown
# 技术文档：Excel/JSON 模型转 DDL 脚本工具
|
||
|
||
## 简介
|
||
|
||
本工具是一个基于 Python 的命令行程序，用于将 Excel（`.xlsx`）或 JSON 格式的数据模型文件转换为指定数据库类型的 SQL DDL（数据定义语言）建表语句。支持多种主流数据库，包括：
|
||
|
||
- SQL Server
|
||
- MySQL
|
||
- Oracle
|
||
- PostgreSQL
|
||
|
||
此外，工具还支持根据数据生成 `INSERT` 语句，便于初始化测试数据。
|
||
|
||
---
|
||
|
||
## 功能特性
|
||
|
||
| 特性 | 描述 |
|
||
|------|------|
|
||
| 多格式输入 | 支持 `.xlsx` 和 `.json` 文件作为输入源 |
|
||
| 多数据库输出 | 支持生成四种数据库的 DDL 语法 |
|
||
| 模板驱动 | 使用模板引擎自定义 DDL 输出格式 |
|
||
| 自动生成插入语句 | 若模型中包含数据，自动附加 `INSERT INTO` 语句 |
|
||
| 批量处理 | 可遍历整个目录下的所有模型文件并合并输出 |
|
||
|
||
---
|
||
|
||
## 目录结构与依赖说明
|
||
|
||
### 项目依赖模块
|
||
|
||
```python
|
||
import io
|
||
import sys
|
||
from traceback import print_exc
|
||
from xlsxData import CRUDData, xlsxFactory
|
||
import codecs
|
||
import json
|
||
from sqlor.ddl_template_sqlserver import sqlserver_ddl_tmpl
|
||
from sqlor.ddl_template_mysql import mysql_ddl_tmpl
|
||
from sqlor.ddl_template_oracle import oracle_ddl_tmpl
|
||
from sqlor.ddl_template_postgresql import postgresql_ddl_tmpl
|
||
from appPublic.myTE import MyTemplateEngine
|
||
from appPublic.folderUtils import listFile
|
||
```
|
||
|
||
> **注意**：
|
||
> - `xlsxData`：负责读取 `.xlsx` 文件并解析成结构化数据。
|
||
> - `sqlor.*_ddl_tmpl`：各数据库对应的 DDL 模板字符串。
|
||
> - `appPublic.myTE.MyTemplateEngine`：轻量级模板渲染引擎。
|
||
> - `appPublic.folderUtils.listFile`：递归列出指定后缀的文件。
|
||
|
||
---
|
||
|
||
## 核心函数说明
|
||
|
||
### 1. `xls2ddl(xlsfile: str, dbtype: str) -> str`
|
||
|
||
将单个 `.xlsx` 或 `.json` 文件转换为对应数据库的 DDL 脚本。
|
||
|
||
#### 参数
|
||
|
||
| 参数名 | 类型 | 说明 |
|
||
|-------|------|------|
|
||
| `xlsfile` | `str` | 输入文件路径（支持 `.xlsx` 或 `.json`） |
|
||
| `dbtype` | `str` | 目标数据库类型，如 `"mysql"`、`"oracle"` 等（不区分大小写） |
|
||
|
||
#### 返回值
|
||
|
||
- 成功时返回完整的 DDL 字符串（含可选的 `INSERT` 语句）
|
||
- 失败时打印错误信息并返回 `None`
|
||
|
||
#### 工作流程
|
||
|
||
1. 判断文件是否为 JSON：
|
||
- 是 → 使用 `codecs.open` 以 UTF-8 编码加载 JSON 数据
|
||
- 否 → 使用 `xlsxFactory` 创建解析器对象读取 XLSX
|
||
2. 获取数据内容 (`data = d.get_data()`)
|
||
3. 查找对应数据库的 DDL 模板
|
||
4. 使用 `MyTemplateEngine` 渲染 DDL 模板
|
||
5. 如果存在数据（`data.data`），调用 `gen_insert()` 生成插入语句并追加
|
||
6. 返回最终 SQL 脚本
|
||
|
||
---
|
||
|
||
### 2. `gen_insert(xls: CRUDData) -> str`
|
||
|
||
根据模型中的数据生成多条 `INSERT INTO ... VALUES ...` 语句。
|
||
|
||
#### 参数
|
||
|
||
| 参数名 | 类型 | 说明 |
|
||
|--------|------|------|
|
||
| `xls` | `CRUDData` 实例 | 包含表名和数据行的对象 |
|
||
|
||
#### 返回值
|
||
|
||
- 返回拼接后的多条插入语句字符串，每条以分号结尾，换行分隔
|
||
|
||
#### 处理逻辑
|
||
|
||
- 提取第一张表的名称：`tbl = xls.summary[0].name`
|
||
- 遍历每一行数据：
|
||
- 键作为字段名
|
||
- 值如果是字符串则加单引号，否则转为字符串
|
||
- 组合为标准 `INSERT` 语句
|
||
|
||
> 示例输出：
|
||
```sql
|
||
insert into users (id, name, age) values (1, 'Alice', 25);
|
||
insert into users (id, name, age) values (2, 'Bob', 30);
|
||
```
|
||
|
||
---
|
||
|
||
### 3. `model2ddl(folder: str, dbtype: str) -> str`
|
||
|
||
批量处理一个目录下所有 `.xlsx` 和 `.json` 文件，生成统一的 DDL 脚本。
|
||
|
||
#### 参数
|
||
|
||
| 参数名 | 类型 | 说明 |
|
||
|--------|------|------|
|
||
| `folder` | `str` | 模型文件所在目录路径 |
|
||
| `dbtype` | `str` | 目标数据库类型 |
|
||
|
||
#### 返回值
|
||
|
||
- 合并后的完整 DDL 字符串，每个文件前添加注释 `-- 文件路径`
|
||
|
||
#### 异常处理
|
||
|
||
- 对每个文件进行 `try...except` 包裹
|
||
- 出错时输出异常信息及堆栈跟踪，继续处理其他文件
|
||
|
||
---
|
||
|
||
## 模板系统设计
|
||
|
||
### 模板映射表：`tmpls`
|
||
|
||
```python
|
||
tmpls = {
|
||
"sqlserver": sqlserver_ddl_tmpl,
|
||
"mysql": mysql_ddl_tmpl,
|
||
"oracle": oracle_ddl_tmpl,
|
||
"postgresql": postgresql_ddl_tmpl
|
||
}
|
||
```
|
||
|
||
> 所有模板均为字符串形式，内部使用占位符（由 `MyTemplateEngine` 解析），例如：
|
||
>
|
||
> ```sql
|
||
> CREATE TABLE {{table_name}} (
|
||
> {% for col in columns %}
|
||
> {{col.name}} {{col.type}}{% if col.nullable == False %} NOT NULL{% endif %},
|
||
> {% endfor %}
|
||
> );
|
||
> ```
|
||
|
||
---
|
||
|
||
## 主程序入口（命令行运行）
|
||
|
||
### 运行方式
|
||
|
||
```bash
|
||
python your_script.py <dbtype> <folder_or_file>
|
||
```
|
||
|
||
#### 示例
|
||
|
||
```bash
|
||
python model2ddl.py mysql ./models/
|
||
```
|
||
|
||
#### 参数说明
|
||
|
||
| 参数位置 | 含义 |
|
||
|---------|------|
|
||
| `sys.argv[1]` | 数据库类型（如 `mysql`） |
|
||
| `sys.argv[2]` | 模型文件夹路径 |
|
||
|
||
#### 中文输出兼容性修复
|
||
|
||
为防止 Windows 控制台因编码问题报错（如 `'gbk' codec can't encode \u200b`），设置 stdout 编码为 UTF-8：
|
||
|
||
```python
|
||
sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding='utf8')
|
||
```
|
||
|
||
---
|
||
|
||
## 使用示例
|
||
|
||
### 输入文件示例（`user.xlsx`）
|
||
|
||
| Field | Type | Null | Default | Comment |
|
||
|-----------|-------------|------|---------|---------------|
|
||
| id | int | NO | | 主键 |
|
||
| username | varchar(32) | NO | | 用户名 |
|
||
| email | varchar(64) | YES | NULL | 邮箱地址 |
|
||
|
||
数据页（可选）：
|
||
|
||
| id | username | email |
|
||
|----|----------|------------------|
|
||
| 1 | alice | alice@exam.com |
|
||
| 2 | bob | bob@exam.com |
|
||
|
||
---
|
||
|
||
### 输出示例（MySQL）
|
||
|
||
```sql
|
||
-- ./models/user.xlsx
|
||
|
||
CREATE TABLE user (
|
||
id INT NOT NULL COMMENT '主键',
|
||
username VARCHAR(32) NOT NULL COMMENT '用户名',
|
||
email VARCHAR(64) NULL DEFAULT NULL COMMENT '邮箱地址'
|
||
);
|
||
|
||
insert into user (id, username, email) values (1, 'alice', 'alice@exam.com');
|
||
insert into user (id, username, email) values (2, 'bob', 'bob@exam.com');
|
||
```
|
||
|
||
---
|
||
|
||
## 注意事项
|
||
|
||
1. **文件编码要求**
|
||
- JSON 文件必须为 UTF-8 编码
|
||
- 推荐所有文件路径不含中文或特殊字符
|
||
|
||
2. **数据模型格式**
|
||
- Excel 文件需符合 `xlsxData` 模块定义的结构规范
|
||
- 必须包含字段元信息表（列名、类型、约束等）
|
||
- 可选数据表用于生成 `INSERT` 语句
|
||
|
||
3. **数据库模板扩展**
|
||
- 新增数据库支持只需实现新的模板并加入 `tmpls` 映射即可
|
||
|
||
4. **性能建议**
|
||
- 不适用于超大数据集（因全部加载至内存）
|
||
- 建议单个模型文件控制在千行以内
|
||
|
||
---
|
||
|
||
## 错误处理
|
||
|
||
| 错误类型 | 表现 | 解决方法 |
|
||
|--------|------|--------|
|
||
| 文件无法读取 | 输出 `can not read data` | 检查文件格式或权限 |
|
||
| 数据为空 | 输出 `not data return from XLSX file` | 检查文件内容是否正确 |
|
||
| 数据库类型不支持 | 抛出异常 `%s database not implemented` | 检查拼写或确认已实现该模板 |
|
||
| 模板渲染失败 | 抛出模板引擎异常 | 检查模板语法或数据结构匹配性 |
|
||
|
||
---
|
||
|
||
## 扩展建议
|
||
|
||
- 添加配置文件支持不同导出选项（如是否生成 INSERT）
|
||
- 增加对 SQLite 等更多数据库的支持
|
||
- 支持导出到文件而非仅打印到终端
|
||
- 提供 GUI 界面或 Web 接口版本
|
||
|
||
---
|
||
|
||
## 许可与版权
|
||
|
||
© 2025 Your Company. All rights reserved.
|
||
开源许可证：MIT（如有）
|
||
|
||
---
|
||
|
||
📌 **提示**：此工具适用于开发阶段快速搭建数据库 schema 和测试数据，生产环境请结合审核流程使用。 |