Files
2026-05-18 09:28:15 +08:00

151 lines
3.7 KiB
Markdown
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
# SQL操作接口
## 基本配置
| 字段 | 值 | 说明 |
|------|-----|------|
| type | 1 | SQL操作 |
| action | 3 | 查询(SELECT) |
| hasPage | 0/1 | 是否分页 |
| dbLinkId | "0" | 主库,可指定业务库 |
## 示例:基础查询
```python
# 创建按ID查询用户的接口
payload = DataInterfaceAPI.build_payload(
full_name="按ID查询用户",
en_code="query_user_by_id",
sql="SELECT * FROM base_user WHERE f_id = {userId}",
parameters=[{
"field": "userId",
"fieldName": "用户ID",
"dataType": "varchar",
"required": 1,
"sourceType": 1
}],
category="分类ID",
action=3
)
di.create(payload)
result = di.execute(di_id, {"userId": "xxx"})
```
## 示例:带系统变量
```sql
-- 查询当前用户信息
SELECT * FROM base_user WHERE f_id = {@userId}
-- 查询当前组织及子组织
SELECT * FROM base_organize WHERE f_id IN ({@organizeId})
```
## 示例:模糊查询
```sql
-- 关键词搜索
SELECT * FROM base_user WHERE f_real_name LIKE CONCAT('%', {@keyword}, '%')
-- 多字段搜索
SELECT * FROM base_user
WHERE f_real_name LIKE CONCAT('%', {@keyword}, '%')
OR f_account LIKE CONCAT('%', {@keyword}, '%')
```
## 执行增删改 (action=1/2/4)
```python
# action=1 增加, action=2 修改, action=4 删除
# 增删改不返回数据,只返回成功/失败
```
> **注意**:action 值不可信!部分 DI 配置为 action=1 但实际执行的是 SELECT 查询。判断 DI 类型应以 SQL 首关键词(SELECT/INSERT/UPDATE/DELETE)为准。
payload = DataInterfaceAPI.build_payload(
full_name="删除用户",
en_code="delete_user",
sql="DELETE FROM base_user WHERE f_id = {userId}",
parameters=[...],
category="分类ID",
action=4 # 删除
)
```
## 参考示例
| ID | 名称 | SQL特征 |
|-----|------|---------|
| 83ae1f5e5c26447b8c4849ead352b048 | 当前用户 | `WHERE f_id = {@userId}` |
| a0c3186292df4fe2842f66dee0ea7729 | 查询当前组织 | 多表JOIN |
| ebcc44be142e43b795c0d769abd6d25a | 用户列表 | 多表JOIN+条件 |
| 420587863533428357 | 行政区划 | `WHERE f_parent_id = {parentId}` |
| 469125798494792965 | SQL真分页 | `LIMIT` 分页 |
## AIBridge 快速构建增删改查
```python
di = DataInterfaceAPI(client)
# INSERT 新增
di_id = di.create_insert_interface(
full_name="新增用户",
en_code="insert_user",
table_name="base_user",
fields=["f_real_name", "f_account"],
parameters=[
{"field": "real_name", "fieldName": "姓名", "dataType": "varchar", "required": 1, "sourceType": 1},
{"field": "account", "fieldName": "账号", "dataType": "varchar", "required": 1, "sourceType": 1},
],
category="分类ID"
)
# UPDATE 修改
di_id = di.create_update_interface(
full_name="修改用户",
en_code="update_user",
table_name="base_user",
set_fields=["f_real_name"],
where_field="f_id",
parameters=[...],
category="分类ID"
)
# DELETE 删除
di_id = di.create_delete_interface(
full_name="删除用户",
en_code="delete_user",
table_name="base_user",
where_field="f_id",
parameters=[...],
category="分类ID"
)
# SELECT 查询(支持分页)
di_id = di.create_select_interface(
full_name="查询用户",
en_code="select_user",
sql="SELECT * FROM base_user LIMIT {offset}, {size}",
parameters=[...],
category="分类ID",
has_page=1
)
```
### 示例1:当前用户查询
```sql
SELECT f_real_name, f_organize_id
FROM base_user
WHERE f_id = {@userId}
```
### 示例2:行政区划(树形)
```sql
SELECT f_id, f_full_name
FROM base_province
WHERE f_parent_id = {parentId}
```
- 参数: parentId (varchar)
- 用于:省市区三级联动