Dify知识库之二 SQL查询智能体

最新推荐文章于 2025-04-03 20:39:49 发布

赤胜骄阳

最新推荐文章于 2025-04-03 20:39:49 发布

阅读量923

点赞数 21

分类专栏：大模型知识库文章标签： sql 数据库

本文链接：https://blog.youkuaiyun.com/qiufo/article/details/146226635

版权

大模型知识库专栏收录该内容

25 篇文章

订阅专栏

Dify知识库之二 SQL查询智能体

Mysql数据查询

Mysql数据查询

1、创建表结构，插入数据

drop table if exists employee_kpi ;
CREATE TABLE employee_kpi (
    id SERIAL PRIMARY KEY,
    year INT NOT NULL, -- 年份
    depart_name VARCHAR(255) NOT NULL, -- 部门名称
    user_name VARCHAR(255) NOT NULL, -- 员工姓名
    quarter1 FLOAT, -- 第一季度指标
    quarter2 FLOAT, -- 第二季度指标
    quarter3 FLOAT, -- 第三季度指标
    quarter4 FLOAT, -- 第四季度指标
    status VARCHAR(50), -- 状态
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 创建时间
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- 更新时间
);

ALTER TABLE employee_kpi COMMENT = '员工每年KPI指标表';
ALTER TABLE employee_kpi MODIFY COLUMN id INT COMMENT '主键ID';
ALTER TABLE employee_kpi MODIFY COLUMN year INT COMMENT '年份';
ALTER TABLE employee_kpi MODIFY COLUMN depart_name VARCHAR(255) COMMENT '部门名称';
ALTER TABLE employee_kpi MODIFY COLUMN user_name VARCHAR(255) COMMENT '员工姓名';
ALTER TABLE employee_kpi MODIFY COLUMN quarter1 DECIMAL(10,2) COMMENT '第一季度指标';
ALTER TABLE employee_kpi MODIFY COLUMN quarter2 DECIMAL(10,2) COMMENT '第二季度指标';
ALTER TABLE employee_kpi MODIFY COLUMN quarter3 DECIMAL(10,2) COMMENT '第三季度指标';
ALTER TABLE employee_kpi MODIFY COLUMN quarter4 DECIMAL(10,2) COMMENT '第四季度指标';
ALTER TABLE employee_kpi MODIFY COLUMN status VARCHAR(50) COMMENT '状态';
ALTER TABLE employee_kpi MODIFY COLUMN created_at DATETIME COMMENT '创建时间';
ALTER TABLE employee_kpi MODIFY COLUMN updated_at DATETIME COMMENT '更新时间';

构建测试数据

INSERT INTO employee_kpi (id, year, depart_name, user_name, quarter1, quarter2, quarter3, quarter4, status, created_at, updated_at) VALUES
(1, 2023, '销售部', '张三', 85.5, 90.0, 88.0, 92.0, '正常', '2023-01-01 10:00:00', '2023-01-01 10:00:00'),
(2, 2023, '市场部', '李四', 78.0, 82.5, 80.0, 85.0, '正常', '2023-01-01 10:00:00', '2023-01-01 10:00:00'),
(3, 2023, '研发部', '王五', 92.0, 95.0, 93.5, 96.0, '正常', '2023-01-01 10:00:00', '2023-01-01 10:00:00'),
(4, 2023, '财务部', '赵六', 88.0, 90.5, 89.0, 91.0, '正常', '2023-01-01 10:00:00', '2023-01-01 10:00:00'),
(5, 2023, '人事部', '孙七', 80.0, 85.0, 82.0, 87.0, '正常', '2023-01-01 10:00:00', '2023-01-01 10:00:00'),
(6, 2023, '销售部', '周八', 85.0, 88.0, 86.0, 90.0, '正常', '2023-01-01 10:00:00', '2023-01-01 10:00:00'),
(7, 2023, '市场部', '吴九', 78.5, 81.0, 79.0, 83.0, '正常', '2023-01-01 10:00:00', '2023-01-01 10:00:00'),
(8, 2023, '研发部', '郑十', 91.0, 94.0, 92.0, 95.0, '正常', '2023-01-01 10:00:00', '2023-01-01 10:00:00'),
(9, 2023, '财务部', '王十一', 87.0, 89.5, 88.0, 90.0, '正常', '2023-01-01 10:00:00', '2023-01-01 10:00:00'),
(10, 2023, '人事部', '李十二', 79.0, 84.0, 81.0, 86.0, '正常', '2023-01-01 10:00:00', '2023-01-01 10:00:00'),
(11, 2023, '销售部', '张十三', 84.0, 87.0, 85.0, 89.0, '正常', '2023-01-01 10:00:00', '2023-01-01 10:00:00'),
(12, 2023, '市场部', '赵十四', 77.0, 80.5, 78.0, 82.0, '正常', '2023-01-01 10:00:00', '2023-01-01 10:00:00'),
(13, 2023, '研发部', '孙十五', 90.0, 93.0, 91.5, 94.0, '正常', '2023-01-01 10:00:00', '2023-01-01 10:00:00'),
(14, 2023, '财务部', '周十六', 86.0, 88.5, 87.0, 89.0, '正常', '2023-01-01 10:00:00', '2023-01-01 10:00:00'),
(15, 2023, '人事部', '吴十七', 78.0, 83.0, 80.0, 85.0, '正常', '2023-01-01 10:00:00', '2023-01-01 10:00:00'),
(16, 2023, '销售部', '郑十八', 83.0, 86.0, 84.0, 88.0, '正常', '2023-01-01 10:00:00', '2023-01-01 10:00:00'),
(17, 2023, '市场部', '王十九', 76.5, 79.0, 77.0, 81.0, '正常', '2023-01-01 10:00:00', '2023-01-01 10:00:00'),
(18, 2023, '研发部', '李二十', 89.0, 92.0, 90.0, 93.0, '正常', '2023-01-01 10:00:00', '2023-01-01 10:00:00'),
(19, 2023, '财务部', '张二十一', 85.0, 87.5, 86.0, 88.0, '正常', '2023-01-01 10:00:00', '2023-01-01 10:00:00'),
(20, 2023, '人事部', '赵二十二', 77.0, 82.0, 79.0, 84.0, '正常', '2023-01-01 10:00:00', '2023-01-01 10:00:00');

2、导入知识库

表结构描述:

表名称: employee_kpi 员工每年KPI指标表

字段列表:

id , 主键ID, 整型
year , 年份, 整型
depart_name , 部门名称, 字符串
user_name , 员工姓名, 字符串
quarter1 , 第一季度指标, 浮点型
quarter2 , 第二季度指标, 浮点型
quarter3 , 第三季度指标, 浮点型
quarter4 , 第四季度指标, 浮点型
status , 状态, 字符串
created_at , 创建时间, 时间戳
updated_at , 更新时间, 时间戳

3、配置dify查询sql工作流

方案一自建api提供数据库查询：

设置sql输入参数变量在这里插入图片描述
新增代码执行节点，接收入参，编写python语句，执行sql查询并输出查询数据代码如下：

import json
import mysql.connector
from mysql.connector import Error
from typing import Dict, Any
import datetime

def main(query: str) -> Dict[str, Any]:
    """
    连接到 MySQL 数据库，执行 SQL 查询并返回结果
    :param query: 要执行的 SQL 查询语句
    :return: 包含查询结果和状态信息的字典
    """
    # 数据库连接配置
    conn_config = {
        "database": "dbname",
        "user": "user",
        "password": "password",
        "host": "host",
        "port": 3306  # MySQL 默认端口
    }
    # 初始化结果字典
    result: Dict[str, Any] = {
        "success": False,
        "data": [],
        "error": None
    }
    connection = None
    cursor = None

    try:
        # 建立数据库连接
        connection = mysql.connector.connect(**conn_config)
        # 创建游标对象，用于执行 SQL 语句
        cursor = connection.cursor()
        # 执行 SQL 查询
        cursor.execute(query)
        # 处理空结果集
        if cursor.description is None:
            return {"result": "[]"}  # 返回空数组的JSON字符串

        columns = [desc[0] for desc in cursor.description]
        result = [
            dict(zip(columns, (v.isoformat() if hasattr(v, 'isoformat') else v for v in row)))
            for row in cursor.fetchall()
        ]

        # 双重保证：结果始终包含在字典中
        return {
            "result": json.dumps(result, ensure_ascii=False)
        }
    except (Exception, Error) as e:
        # 查询失败，记录错误信息
        return {"error": json.dumps({"message": f"连接失败: {str(e)}"})}
    finally:
        # 无论是否发生异常，都要确保关闭游标和连接
        if cursor:
            cursor.close()
        if connection:
            connection.close()
            print("数据库连接已关闭")

点击运行，若是报错
在这里插入图片描述
分析：在 Docker 中运行的环境中遇到 ModuleNotFoundError: No module named ‘mysql’ 错误，通常是因为 Docker 容器中缺少所需的 Python 库。以下是解决此问题的步骤：

解决方案

更新 Dockerfile ：
- 如果您使用 Dockerfile 构建镜像，请确保在 Dockerfile 中安装了 mysql-connector-python 库。可以通过在 Dockerfile 中添加以下行来安装：
```
RUN pip install mysql-connector-python
```
重建 Docker 镜像：
- 在更新 Dockerfile 后，您需要重新构建 Docker 镜像。可以使用以下命令：
```
docker build -t your_image_name .
```
重新启动容器：
- 使用新的镜像重新启动容器：
```
docker run -d your_image_name
```
检查 Python 环境：
- 确保在 Docker 容器中运行的 Python 环境中， mysql-connector-python 已正确安装。可以进入容器并运行 pip list 检查已安装的库。

配置navicate等访问pgsql
在这里插入图片描述

结束节点接收输出变量

方案二安装database插件

安装
授权
在这里插入图片描述
官方插件说是支持以下数据库

mysql+pymysql://root:123456@localhost:3306/test
postgresql+psycopg2://postgres:123456@localhost:5432/test
sqlite:///test.db
mssql+pymssql://<username>:<password>@<freetds_name>/?charset=utf8
oracle+oracledb://user:pass@hostname:port[/dbname][?service_name=<service>[&key=value&key=value...]]

实测postgresql和sqlite是ok的， mysql报错，其他几个未测试。故以pgsql为例，说明如何制作。

创建工作流，默认添加“开始”节点。
配置如下：添加入参：question
添加LLm节点，取名“编写查询语句”，配置模型和system提示词

你是投资FA行业数据分析专家，精通PostgreSQL。根据用户问题生成PostgreSQL查询。
 
核心规则：
1. 仅使用用户提到的表和字段
2. 确保SQL兼容PostgreSQL
3. 只用简体中文，禁用繁体中文
4. 只输出一个完整SQL语句，确保输出是一个可直接执行的SQL文本,不含注释等其它任何多余信息
- 不需要数据思考过程及文案，仅输出可执行的sql，这很重要
- 不需要额外输出一个“sql”的字符串说明
关键技巧：
1. WHERE子句：
- 对于字符串和长文本字段用LIKE操作，而不是等于操作，例如：WHERE 产品型号 LIKE N'%关键词%'，而不是WHERE 产品型号='关键词'
- 日期用DATEDIFF：WHERE DATEDIFF(day, 生产完成时间, GETDATE()) = 0
2. 除法处理：
必须用模板：
CASE WHEN 【除数】 = 0 THEN 0
ELSE CAST(【被除数】 AS FLOAT) / 【除数】
END AS 【结果列名】
注意：
- 检查表名和字段名
- 字符字段用LIKE N'%关键词%'
- 所有除法用指定模板

参考示例及业务逻辑说明：{{#context#}}

配置User提示词

表结构描述:
表名称: employee_kpi 员工每年KPI指标表
字段列表:
- id , 主键ID, 整型
- year , 年份, 整型
- depart_name , 部门名称, 字符串
- user_name , 员工姓名, 字符串
- quarter1 , 第一季度指标, 浮点型
- quarter2 , 第二季度指标, 浮点型
- quarter3 , 第三季度指标, 浮点型
- quarter4 , 第四季度指标, 浮点型
- status , 状态, 字符串
- created_at , 创建时间, 时间戳
- updated_at , 更新时间, 时间戳

问题：{{#1741837636496.question#}}
注意：务必认真检查输出结果，确保输出的 SQL 文本是可直接执行的，不包含注释、换行符或其他多余信息。

在这里插入图片描述
3. 添加“执行”节点，命名为“执行查询”，定义入参、处理函数、输出变量

import re

def main(arg1):
   
    # 去掉 <think> 标签及其之间的内容
    cleaned = re.sub(r'<think>.*?</think>', '', arg1, flags=re.DOTALL)
    # 去掉 <details> 标签及其之间的内容
    cleaned = re.sub(r'<details>.*?</details>', '', cleaned, flags=re.DOTALL)
    # 去掉换行符和回车符
    cleaned = cleaned.replace('sql\n', '').replace('\n', ' ').replace('\r', ' ').replace('```', ' ')
    return {
        'result': cleaned
    }

在这里插入图片描述
4. 添加“sql execute”

## 4、输入prompt 模板
#角色：你是一位精通SQL语言的数据库专家，精通MySQL，同时擅长解读和分析数据
 
#任务：你的任务是理解用户的输入和上下文内容，编写SQL查询，并调用工具查询获得结果，结合用户的提问，对查询结果进行呈现、解读和分析

#关键步骤：
 
1、对用户输入的内容进行识别和判断，如果内容涉及政治、时事、社会问题以及违背道德和法律法规的情形，一律输出：”您提出的问题超出我应当回答的范围，请询问与公司业务相关的问题，否则我无法作出回答
 
2、根据用户输入的内容和上下文信息，从知识库“数据结构描述”中检索“ods.fpf_index”表结构信息

如果无法输出数据，请输出提示：为确保查询获得准确信息，请再把你的需求描述细致一些
 
3、根据用户输入的内容和上下文信息，形成一个符合用户意图的完整问题，以此作为输入在知识库“sql示例”中检索SQL语句参考示例
 
4、基于对上下文和对用户提问的理解，按照检索到的数据表结构信息，以及SQL参考示例，编写SQL查询语句。注意，若内容分类与参考示例中的分类不符时，则忽略这个示例。另外，不是所有情况下都有示例参考，没有示例时请按照自己的理解和掌握的知识编写SQL语句
 
5、去除SQL语句中多余的注释、换行符等无用信息，输出一个纯净的、可直接执行的SQL语句
 
6、执行SQL查询，获取结果
 
7、阅读查询结果，结合历史对话内容，对查询结果进行呈现、解读和分析
 
#编写SQL时的注意事项：
 
1. 务必根据上下文提供的数据表结构描述来编写SQL语句，确保仅使用数据表结构描述中提到的表名和字段名，并参考对字段的解释
 
2. 确保SQL兼容MysqlSQL
 
3. 只输出一个完整SQL语句，无注释，确保可直接执行并获得预期的结果
 
#其他注意事项
 
1、不要输出中间的思考过程，只输出最终的结果

完整的dsl文件如下, 其中有ollama和pgsql的配置需要自行修改

app:
  description: ''
  icon: 🤖
  icon_background: '#FFEAD5'
  mode: workflow
  name: pgsql查询
  use_icon_as_answer_icon: false
dependencies:
- current_identifier: null
  type: marketplace
  value:
    marketplace_plugin_unique_identifier: hjlarry/database:0.0.4@3a0b78c887a9321a78fca56f4c68ca85434a298032d34964d92b61e322977938
kind: app
version: 0.1.5
workflow:
  conversation_variables: []
  environment_variables: []
  features:
    file_upload:
      allowed_file_extensions:
      - .JPG
      - .JPEG
      - .PNG
      - .GIF
      - .WEBP
      - .SVG
      allowed_file_types:
      - image
      allowed_file_upload_methods:
      - local_file
      - remote_url
      enabled: false
      fileUploadConfig:
        audio_file_size_limit: 50
        batch_count_limit: 5
        file_size_limit: 15
        image_file_size_limit: 10
        video_file_size_limit: 100
        workflow_file_upload_limit: 10
      image:
        enabled: false
        number_limits: 3
        transfer_methods:
        - local_file
        - remote_url
      number_limits: 3
    opening_statement: ''
    retriever_resource:
      enabled: true
    sensitive_word_avoidance:
      enabled: false
    speech_to_text:
      enabled: false
    suggested_questions: []
    suggested_questions_after_answer:
      enabled: false
    text_to_speech:
      enabled: false
      language: ''
      voice: ''
  graph:
    edges:
    - data:
        isInIteration: false
        isInLoop: false
        sourceType: start
        targetType: code
      id: 1741835490412-source-1741923972064-target
      source: '1741835490412'
      sourceHandle: source
      target: '1741923972064'
      targetHandle: target
      type: custom
      zIndex: 0
    - data:
        isInIteration: false
        isInLoop: false
        sourceType: code
        targetType: tool
      id: 1741923972064-source-1743411938553-target
      source: '1741923972064'
      sourceHandle: source
      target: '1743411938553'
      targetHandle: target
      type: custom
      zIndex: 0
    - data:
        isInLoop: false
        sourceType: tool
        targetType: end
      id: 1743411938553-source-1741835503564-target
      source: '1743411938553'
      sourceHandle: source
      target: '1741835503564'
      targetHandle: target
      type: custom
      zIndex: 0
    nodes:
    - data:
        desc: ''
        selected: false
        title: 开始
        type: start
        variables:
        - label: sql
          max_length: 2000
          options: []
          required: true
          type: paragraph
          variable: sql
      height: 90
      id: '1741835490412'
      position:
        x: -161
        y: 213
      positionAbsolute:
        x: -161
        y: 213
      selected: false
      sourcePosition: right
      targetPosition: left
      type: custom
      width: 244
    - data:
        desc: ''
        outputs:
        - value_selector:
          - '1743411938553'
          - json
          variable: result
        selected: false
        title: 结束
        type: end
      height: 90
      id: '1741835503564'
      position:
        x: 688
        y: 282
      positionAbsolute:
        x: 688
        y: 282
      selected: false
      sourcePosition: right
      targetPosition: left
      type: custom
      width: 244
    - data:
        code: "import re\n\ndef main(arg1):\n\n\n\n    \n    # 去掉 <think> 标签及其之间的内容\n\
          \    cleaned = re.sub(r'<think>.*?</think>', '', arg1, flags=re.DOTALL)\n\
          \    # 去掉 <details> 标签及其之间的内容\n    cleaned = re.sub(r'<details>.*?</details>',\
          \ '', cleaned, flags=re.DOTALL)\n    # 去掉换行符和回车符\n    cleaned = cleaned.replace('sql\\\
          n', '').replace('\\n', ' ').replace('\\r', ' ').replace('```', ' ')\n  \
          \  return {\n        'result': cleaned\n    }"
        code_language: python3
        desc: ''
        outputs:
          result:
            children: null
            type: string
        selected: true
        title: 代码执行
        type: code
        variables:
        - value_selector:
          - '1741835490412'
          - sql
          variable: arg1
      height: 54
      id: '1741923972064'
      position:
        x: 122
        y: 231
      positionAbsolute:
        x: 122
        y: 231
      selected: true
      sourcePosition: right
      targetPosition: left
      type: custom
      width: 244
    - data:
        desc: ''
        is_team_authorization: true
        output_schema: null
        paramSchemas:
        - auto_generate: null
          default: null
          form: llm
          human_description:
            en_US: The SQL query string.
            ja_JP: The SQL query string.
            pt_BR: The SQL query string.
            zh_Hans: SQL 查询语句。
          label:
            en_US: SQL Query
            ja_JP: SQL Query
            pt_BR: SQL Query
            zh_Hans: SQL 查询语句
          llm_description: The SQL query string.
          max: null
          min: null
          name: query
          options: []
          placeholder: null
          precision: null
          required: true
          scope: null
          template: null
          type: string
        - auto_generate: null
          default: json
          form: form
          human_description:
            en_US: Choose the output format.
            ja_JP: Choose the output format.
            pt_BR: Choose the output format.
            zh_Hans: 选择输出格式。
          label:
            en_US: Output format
            ja_JP: Output format
            pt_BR: Output format
            zh_Hans: 输出格式
          llm_description: ''
          max: null
          min: null
          name: format
          options:
          - label:
              en_US: JSON
              ja_JP: JSON
              pt_BR: JSON
              zh_Hans: JSON
            value: json
          - label:
              en_US: CSV
              ja_JP: CSV
              pt_BR: CSV
              zh_Hans: CSV
            value: csv
          - label:
              en_US: YAML
              ja_JP: YAML
              pt_BR: YAML
              zh_Hans: YAML
            value: yaml
          - label:
              en_US: Markdown
              ja_JP: Markdown
              pt_BR: Markdown
              zh_Hans: Markdown
            value: md
          - label:
              en_US: Excel
              ja_JP: Excel
              pt_BR: Excel
              zh_Hans: Excel
            value: xlsx
          - label:
              en_US: HTML
              ja_JP: HTML
              pt_BR: HTML
              zh_Hans: HTML
            value: html
          placeholder: null
          precision: null
          required: false
          scope: null
          template: null
          type: select
        - auto_generate: null
          default: null
          form: llm
          human_description:
            en_US: Optional, Filling in this field will overwrite the database connection
              entered during authorization.
            ja_JP: Optional, Filling in this field will overwrite the database connection
              entered during authorization.
            pt_BR: Optional, Filling in this field will overwrite the database connection
              entered during authorization.
            zh_Hans: 选填，填写后将覆盖授权时填写的数据库连接。
          label:
            en_US: DB URI
            ja_JP: DB URI
            pt_BR: DB URI
            zh_Hans: DB URI
          llm_description: ''
          max: null
          min: null
          name: db_uri
          options: []
          placeholder: null
          precision: null
          required: false
          scope: null
          template: null
          type: string
        params:
          db_uri: ''
          format: ''
          query: ''
        provider_id: hjlarry/database/database
        provider_name: hjlarry/database/database
        provider_type: builtin
        selected: false
        title: SQL Execute
        tool_configurations:
          format: json
        tool_label: SQL Execute
        tool_name: sql_execute
        tool_parameters:
          db_uri:
            type: mixed
            value: postgresql://XXX:XXX@192.168.0.5:5432/poetry
          query:
            type: mixed
            value: '{{#1741923972064.result#}}'
        type: tool
      height: 90
      id: '1743411938553'
      position:
        x: 426
        y: 231
      positionAbsolute:
        x: 426
        y: 231
      selected: false
      sourcePosition: right
      targetPosition: left
      type: custom
      width: 244
    viewport:
      x: 287.3973253717469
      y: 181.2008889368034
      zoom: 0.8705505632961241