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

3.7 KiB

SQL操作接口

基本配置

字段 值 说明
type 1 SQL操作
action 3 查询(SELECT)
hasPage 0/1 是否分页
dbLinkId "0" 主库,可指定业务库

示例:基础查询

# 创建按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"})

示例:带系统变量

-- 查询当前用户信息
SELECT * FROM base_user WHERE f_id = {@userId}

-- 查询当前组织及子组织
SELECT * FROM base_organize WHERE f_id IN ({@organizeId})

示例:模糊查询

-- 关键词搜索
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)

# 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:当前用户查询

SELECT f_real_name, f_organize_id
FROM base_user
WHERE f_id = {@userId}

示例2:行政区划(树形)

SELECT f_id, f_full_name
FROM base_province
WHERE f_parent_id = {parentId}
  • 参数: parentId (varchar)
  • 用于:省市区三级联动