41-dify案例分享-基于database插件实现Text2sql的数据库查询图表工作流 ...

涛声依旧在  论坛元老 | 5 天前 | 显示全部楼层 | 阅读模式
打印 上一主题 下一主题
楼主

主题 1615|帖子 1615|积分 4845

1 媒介

Text2SQL（或称NL2SQL）是一种天然语言处理技能，旨在将天然语言（Natural Language）问题转化为关系型数据库中可实行的结构化查询语言（Structured Query Language，SQL），从而实现对数据库的查询和交互。这项技能的焦点目标是通过天然语言形貌，无需用户具备SQL语法知识，即可完成复杂的数据库查询使命
详细来说，Text2SQL的使命包括以下步调：

  • 输入分析：用户以天然语言形式输入问题，比方“查找平均工资高于团体平均工资的部门名称”。
  • 语义剖析：体系将输入的天然语言问题剖析为数据库中的结构化查询语句。
  • SQL生成：根据剖析效果生成对应的SQL语句，如“SELECT department_name FROM departments WHERE average_salary > (SELECT AVG(salary) FROM employees)”。
  • 实行与反馈：体系实行SQL查询并返回效果，同时可能对效果进行进一步的表明或分析。
Text2SQL的应用领域广泛，包括智能客服、数据分析、金融、医疗、教育等，能够明显提高用户与数据库交互的效率和便利性。别的，随着大型语言模型（LLMs）的发展，Text2SQL技能在处理复杂查询和多轮对话方面也取得了明显进展。
前期也给各人介绍过关于dify整合数据库实现图表生成的案例，dify 1.0 之后插件市场上出现类多类似数据库工具插件，本日就带各人实现基于Text2SQL dify工作流。话不多说我们看一下生产的效果：


工作流也给各人截图看一下

2.工作流的制作

本次工作流涉及到的流程节点比较多。这里每个节点我就一一介绍了。之前也给各人介绍过许多工作流的制作，本次重点是介绍一下工作流中的重点工具的使用以及实现思绪。
本次工作流是基于dify1.0+版本制作，有基于0.15 版本的小同伴发起各人升级，否则不能使用。先查抄一下自己dify的版本

本次工作流用到了几个工具。 时间工具、ECharts图表生成、database

关于上述3个组件安装，这里就不做详细展开。有小同伴需要纯内网使用的安装不了上述组件 可以参考我之前的文章Dify 实战：纯内网1.0+版本，攻克模型工具插件离线安装难题
开始

考虑到多轮对话我们这里使用chatflow 工作流。 点击预览按钮，右下角功能开启中 我们增长对话开场白

为了方便用户的使用我们做了2个引导对话的开场白，这里我们配置三个问题。

这样我们打开预览页面就会出现3个开场白对话，用户就不需要输入点击开场白 就直接对话了。

获取当前体系时间

这个地方重要是方便后面查询使用。

这里我们设置一下时区我们选择中国上海。

LLM

这个地方我们界说了一个LLM大语言，模型这里我们选择了火山引擎deepseek V3模型
体系提示词
  1. ### 角色
  2. 你是一个专业的SQL生成工具，需要根据用户生成标准的mysql库的SQL。   
  3. ### 任务
  4. 请根据以下问题生成 JSON 格式的 SQL 查询数组：
  5. 表名：student_scores（学生成绩信息表）
  6. 字段说明：
  7. - id: 主键
  8. - student_id: 学号
  9. - student_name: 学生姓名
  10. - class_name: 班级
  11. - subject: 科目
  12. - score: 分数
  13. - exam_date: 考试日期
  14. - semester: 学期
  15. - grade: 年级
  16. - created_at: 记录创建时间
  17. - updated_at: 记录更新时间
  18. ### 系统参数
  19. 当前时间：{{#1742350083195.text#}}
  21. ### 要求：
  22. 1. 根据用户提出的问题，生成 JSON 格式的 SQL 查询数组。
  23. 2. 每条 SQL 查询必须与问题直接相关，并从不同维度分析数据。
  24. 3. 生成的SQL最多10个。
  25. 4. 将所有生成的 SQL 查询封装到一个 JSON 数组中。
  26. 5. 确保 SQL 查询语法正确，并考虑性能优化。
  27. 6. 输出必须以 ```json 开始。
  28. 7. 如果问题涉及多个统计维度，请分别生成对应的子查询。
  29. 8. 涉及全量的应查询SQL以学期聚合。
复制代码
用户提示词 （重要给一下大模型一个用户提示词样例）
  1. 查询全校各科目平均分情况
复制代码
assistant 提示词 我们界说了一个上面开场白对应的SQL 语句
  1. ```json
  2.            [
  3.     {
  4.         "title": "统计全校各科目平均分",
  5.         "sql": "SELECT subject, ROUND(AVG(score), 2) AS avg_score FROM student_scores GROUP BY subject ORDER BY avg_score DESC;"
  6.     },
  7.     {
  8.         "title": "统计各科目及格率",
  9.         "sql": "SELECT subject, ROUND(COUNT(CASE WHEN score >= 60 THEN 1 END) * 100.0 / COUNT(*), 2) as pass_rate FROM student_scores GROUP BY subject ORDER BY pass_rate DESC;"
  10.     },
  11.     {
  12.         "title": "统计各科目成绩分布",
  13.         "sql": "SELECT subject, COUNT(CASE WHEN score >= 90 THEN 1 END) as excellent, COUNT(CASE WHEN score >= 75 AND score < 90 THEN 1 END) as good, COUNT(CASE WHEN score >= 60 AND score < 75 THEN 1 END) as pass, COUNT(CASE WHEN score < 60 THEN 1 END) as fail FROM student_scores GROUP BY subject;"
  14.     }
  15. ]
  16. ```
复制代码
整个llm配置截图如下


有的小同伴可能会问，这个SQL 我不会，怎么办。 可以把创建SQL 语句发给trae,让它帮我生成。


关于SQL 语句的创建 后面也会给各人用trae 来创建和生成。
SQL Execute

因为考虑到用实现text2SQL 所以我们免不了使用到 这个SQL Execute 工具。
我们可以在https://marketplace.dify.ai/plugins/hjlarry/database?language=zh-Hans 市场上找到这个项目

目前这个项目支持的数据库有mysql, postgresql, sqlite, sqlserver, oracle
  1. mysql+pymysql://root:123456@localhost:3306/test
  3. postgresql+psycopg2://postgres:123456@localhost:5432/test
  4. sqlite:///test.db
  5. mssql+pymssql://<username>:<password>@<freetds_name>/?charset=utf8
  6. oracle+oracledb://user:pass@hostname:port[/dbname][?service_name=<service>[&key=value&key=value...]]
复制代码
我们在插件市场把它安装好后，就需要对它配置。

我们用的是mysql参考上述链接字符串
  1. mysql+pymysql://root:123456@localhost:3306/test
复制代码
这里还有一个小本领，就是如果数据库密码是带有@符号的，我们需要转义一下。否自会出现错误。

​ 上述因为密码也带有特别符号“@” 和后面的数据链接符号@产生了歧义 这样程序毗连就会报错
怎样解决

若要借助转义的方式来处理包含特别字符 @ 的毗连字符串，在标准的数据库毗连字符串里，一样平常没有通用转义符号能直接用在字符串里。不过可以对特别字符 @ 进行 URL 编码，@ 对应的 URL 编码是 %40。
最后的酿成
  1. mysql+pymysql://root:zzz%40123@192.168.11.84:19030/test_db
复制代码
这样修改后在毗连就OK 了


看到已授权完成配置。
回到工作流我们设置一下 需要查询的SQL 语句（前面步调我们简化）

LLM汇总返回

这个地方也用到了LLM大语言模型，我们这里同样使用火山引擎提供的deepseek V3模型。
上下文中我们填写代码处理返回效果

体系提示词
  1. ### 角色
  2. 你是一个数据分析师，需要根据上个模型生成的 SQL 及其查询结果，优先回答用户问题，回答内容不要发散，并且将关联问题的结果进行分析并以 JSON 格式返回给用户。
  4. ### 参数
  5. - **SQL 模型生成**：{{#1742362677100.text#}}
  6. - **SQL 查询结果**：{{#context#}}
  8. ### 图片使用场景
  9. - 线性图 ：适用于展示趋势变化的数据，例如时间序列数据（如每月、每年的变化）。
  10. - 柱状图 ：适用于比较不同类别之间的数量或占比，例如各市的占比情况。
  11. - 饼状图 ：适用于展示整体的组成部分及其比例，通常用于单维度的比例分布。
  13. ### 要求：
  14. 1. 优先回答用户问题，回答内容不要发散。
  15. 2. 根据用户问题正确使用线性图/柱状图/饼状图。
  16. 3. 将返回内容放入到 JSON 中，格式如下：
  17. ```json
  18. {
  19.   "results": "用md格式先回复用户问题，其它维度数据简单概括，但是数据一定要展示出来",
  20.   "ECHarts": "1",  // 如果需要生成图表，则为 "1"；否则为 "0"
  21.   "chartType": "线性图/柱状图/饼状图",  // 图表类型（仅当 ECHarts 为 true 时提供）
  22.   "chartTitle": "图表标题",            // 图表标题（仅当 ECHarts 为 true 时提供）
  23.   "chartData": "图表的数据，多个用;隔开", // 图表数据（仅当 ECHarts 为 true 时提供）
  24.   "chartXAxis": "图表的X轴，多个用;隔开"   // 图表的X轴数据（仅当 ECHarts 为 true 时提供）
  25. }
  26. ```
  28. #### 注意事项：
  29. - 如果查询结果适合生成图表，则 ECHarts 设置为 "1"，并补充 chartType、chartTitle、chartData 和 chartXAxis 字段。
  30. - 如果查询结果不适合生成图表，则 ECHarts 设置为 "0"，并省略 chartType、chartTitle、chartData 和 chartXAxis 字段。
  31. - 咨询占比必须使用饼状图进行展示，饼状图chartData中应返回百分比。
复制代码

这个步调重要是对查询效果进行汇总分析，另外把查询的效果ECHarts 图表组装需要的需要的JSON格式数据。
代码实行生成echart

上面llm大语言模型处理的效果我们这里用代码实行生成echart,代码如下
  1. import re
  2. import json
  4. def main(arg1: str) -> dict:
  5.     # 默认返回值
  6.     default_output = {
  7.         "results": "",
  8.         "ECHarts": "0",
  9.         "chartType": "",
  10.         "chartTitle": "",
  11.         "chartData": "",
  12.         "chartXAxis": ""
  13.     }
  14.    
  15.     try:
  16.         # 使用正则表达式提取被 ```json 和 ```包裹的内容
  17.         match = re.search(r'```json\s*([\s\S]*?)\s*```', arg1)
  18.         if not match:
  19.             raise ValueError("输入字符串中未找到有效的 JSON 数据")
  20.         
  21.         # 提取 JSON 字符串
  22.         json_str = match.group(1).strip()
  23.         
  24.         # 将 JSON 字符串解析为 Python 字典
  25.         result_dict = json.loads(json_str)
  26.     except Exception as e:
  27.         # 如果解析失败，打印错误信息并返回默认输出
  28.         print(f"解析失败: {e}")
  29.         return default_output
  30.    
  31.     # 检查是否包含 ECHarts 字段
  32.     if "ECHarts" not in result_dict:
  33.         result_dict["ECHarts"] = "0"  # 默认设置为 "0"
  34.    
  35.     # 根据 ECHarts 的值动态检查图表相关字段
  36.     if result_dict["ECHarts"] == "1":
  37.         required_chart_fields = ["chartType", "chartTitle", "chartData", "chartXAxis"]
  38.         for field in required_chart_fields:
  39.             if field not in result_dict:
  40.                 result_dict[field] = ""  # 自动补全缺失字段为空字符串
  41.    
  42.     # 构造返回值
  43.     return {
  44.         "results": str(result_dict.get("results", "")),
  45.         "ECHarts": str(result_dict.get("ECHarts", "0")),
  46.         "chartType": str(result_dict.get("chartType", "")),
  47.         "chartTitle": str(result_dict.get("chartTitle", "")),
  48.         "chartData": str(result_dict.get("chartData", "")),
  49.         "chartXAxis": str(result_dict.get("chartXAxis", ""))
  50.     }
复制代码
条件分支

这个条件分支是考虑用户输入的信息 返回效果有线性图表、柱状图、饼图的输出，所以我们需要通过这个条件分支进行判定

ECharts图表

这个地方就是重要是使用ECharts图表对线性图表、柱状图、饼图的输出，这里输入的参数有3个分别是：
1.标题
2.数据
3.x 轴
其中 线性图表、柱状图一样的，饼图有点区别 它不是x 轴 而是换成分类，数据格式都是一样的。


直接输出

这个就是把效果返回。分别对应线性图表、柱状图、饼图。

以上步调我们就完成了工作流的制作。
3.验证及测试

我们点开工作流的预览按钮，点击体系给出的3个开场白。

体验地址https://difyhs.duckcloud.fun/chat/ukN4XhnVKLFN2wqw 备用地址（http://14.103.204.132/chat/ukN4XhnVKLFN2wqw）
相关资料和文档可以看我开源的项目 https://github.com/wwwzhouhui/dify-for-dsl
4.别的类资料

前面给各人介绍了工作流制作，其实这个工作流还是需要依靠数据库，所以我们需要把数据库和创建表创建完成才能使用。
有的小同伴可能问，这个SQL 语句建表语句和初始化数据我不会怎么办？不要紧 我也不会，我们发给trae帮我们搞定
建表语句生成

需求：

我们的问题
  1. 请根据内容创建一个MYSQL  student_scores 建表SQL 语句
复制代码

呵呵它帮我把数据表建好了。 建好后，我们发现我还要造一下数据，接下来我在让它帮我把初始化数据SQL 也搞定
初始化数据

我的问题
  1. 请这个表造100条数据，生成测试数据SQL 脚本，方便后面我们工作流图标展示使用
复制代码


这2个语句是分开的，我想让它一次搞定。 我接下来又提出问题。
我的问题
  1. 上面的SQL 语句和之前的 创建表的语句合并到一个SQL 语句并输出
复制代码

生成好的SQL 语句如下
student_scores.sql （ 这个数据库脚本支持MYSQL8+版本，这个要注意哦）
  1. -- 创建学生成绩表
  2. CREATE TABLE `student_scores` (
  3.   `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  4.   `student_id` varchar(20) NOT NULL COMMENT '学号',
  5.   `student_name` varchar(50) NOT NULL COMMENT '学生姓名',
  6.   `class_name` varchar(50) NOT NULL COMMENT '班级名称',
  7.   `subject` varchar(50) NOT NULL COMMENT '科目名称',
  8.   `score` decimal(5,2) NOT NULL COMMENT '分数',
  9.   `exam_date` date NOT NULL COMMENT '考试日期',
  10.   `semester` varchar(20) NOT NULL COMMENT '学期',
  11.   `grade` varchar(20) NOT NULL COMMENT '年级',
  12.   `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  13.   `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  14.   PRIMARY KEY (`id`),
  15.   KEY `idx_student_id` (`student_id`),
  16.   KEY `idx_exam_date` (`exam_date`),
  17.   KEY `idx_subject` (`subject`),
  18.   KEY `idx_class` (`class_name`)
  19. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='学生成绩信息表';
  21. -- 清空并重置表
  22. TRUNCATE TABLE student_scores;
  24. -- 插入基础测试数据
  25. INSERT INTO student_scores (student_id, student_name, class_name, subject, score, exam_date, semester, grade)
  26. WITH RECURSIVE numbers AS (
  27.     SELECT 1 AS n UNION ALL SELECT n + 1 FROM numbers WHERE n < 100
  28. ),
  29. random_data AS (
  30.     SELECT
  31.         n,
  32.         CONCAT('2023', LPAD(FLOOR(RAND() * 100), 3, '0')) as student_id,
  33.         ELT(FLOOR(RAND() * 10) + 1, '张三', '李四', '王五', '赵六', '钱七', '孙八', '周九', '吴十', '郑十一', '王十二') as student_name,
  34.         ELT(FLOOR(RAND() * 4) + 1, '高一(1)班', '高一(2)班', '高一(3)班', '高一(4)班') as class_name,
  35.         ELT(FLOOR(RAND() * 5) + 1, '语文', '数学', '英语', '物理', '化学') as subject,
  36.         ROUND(60 + RAND() * 40, 2) as score,
  37.         DATE_ADD('2023-12-01', INTERVAL FLOOR(RAND() * 30) DAY) as exam_date,
  38.         '2023-2024学年第一学期' as semester,
  39.         '高一' as grade
  40.     FROM numbers
  41. )
  42. SELECT
  43.     student_id,
  44.     student_name,
  45.     class_name,
  46.     subject,
  47.     score,
  48.     exam_date,
  49.     semester,
  50.     grade
  51. FROM random_data;
  53. -- 插入不及格成绩数据
  54. INSERT INTO student_scores (student_id, student_name, class_name, subject, score, exam_date, semester, grade)
  55. SELECT
  56.     CONCAT('2023', LPAD(FLOOR(RAND() * 100), 3, '0')),
  57.     ELT(FLOOR(RAND() * 10) + 1, '张三', '李四', '王五', '赵六', '钱七', '孙八', '周九', '吴十', '郑十一', '王十二'),
  58.     ELT(FLOOR(RAND() * 4) + 1, '高一(1)班', '高一(2)班', '高一(3)班', '高一(4)班'),
  59.     ELT(FLOOR(RAND() * 5) + 1, '语文', '数学', '英语', '物理', '化学'),
  60.     ROUND(40 + RAND() * 19, 2),
  61.     DATE_ADD('2023-12-01', INTERVAL FLOOR(RAND() * 30) DAY),
  62.     '2023-2024学年第一学期',
  63.     '高一'
  64. FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) n;
  66. -- 插入优秀成绩数据
  67. INSERT INTO student_scores (student_id, student_name, class_name, subject, score, exam_date, semester, grade)
  68. SELECT
  69.     CONCAT('2023', LPAD(FLOOR(RAND() * 100), 3, '0')),
  70.     ELT(FLOOR(RAND() * 10) + 1, '张三', '李四', '王五', '赵六', '钱七', '孙八', '周九', '吴十', '郑十一', '王十二'),
  71.     ELT(FLOOR(RAND() * 4) + 1, '高一(1)班', '高一(2)班', '高一(3)班', '高一(4)班'),
  72.     ELT(FLOOR(RAND() * 5) + 1, '语文', '数学', '英语', '物理', '化学'),
  73.     ROUND(90 + RAND() * 10, 2),
  74.     DATE_ADD('2023-12-01', INTERVAL FLOOR(RAND() * 30) DAY),
  75.     '2023-2024学年第一学期',
  76.     '高一'
  77. FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) n;
复制代码
创建数据库导入SQL脚本

这个地方我们需要增长创建数据库，把SQl语句导入。
我们使用数据库工具 dbeaver.exe

创建一个test数据库

创建好后，如下

然后导入我们student_scores.sql 脚本。 当然你也可以使用Navicat Premium Lite 这种数据库 导入脚本。


导入完成后我们看到创建好的表和SQL

以上数据都是AI帮我们生成的，看起来还挺真实的，省了我不少时间造数据。
5.总结

本日重要带各人实现了基于 Text2SQL 的 Dify 工作流，借助 Dify 平台实现天然语言到 SQL 查询的转换，并进行数据库查询与图表生成。详细介绍了整个工作流的实现步调，包括工作流的制作，如添加对话开场白、获取当前体系时间、配置 LLM 大语言模型以及使用 SQL Execute 工具实行 SQL 查询等。本次工作流涉及到 Dify 1.0+ 版本的使用、相关工具（时间工具、ECharts 图表生成、database）的安装与配置，以及 SQL 语句的生成与实行等知识。虽然步调较多，但只要按照文章的指引逐步操作，信任各人都能够把握。感爱好的小同伴可以关注支持，本日的分享就到这里竣事了，我们下个文章见。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

本帖子中包含更多资源

您需要 登录 才可以下载或查看，没有账号？立即注册

x
回复

使用道具 举报

0 个回复

倒序浏览
返回列表

快速回复

高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 or 立即注册

本版积分规则

涛声依旧在

论坛元老
这个人很懒什么都没写!

楼主热帖

标签云

AI 运维 CIO 存储 服务器
微信订阅号
微信服务号
微信客服
小程序
H5
关于我们 商务合作 网站地图
©Copyright 2022-2024 杭州祥升科技有限公司 版权所有 浙ICP备20004199号
快速回复 返回顶部 返回列表