Dify 实现 Text2SQL 并以图表展示结果_dify text to sql

Dify 实现 Text2SQL 并以图表展示结果

• 1. 建表和登录数据
• 2. 创建 Chatflow
• 3. 测试

1. 建表和登录数据

下面是 Oracle 数据的建表和插入数据的SQL语句。

-- 创建学生成绩表CREATE TABLE student_scores ( id NUMBER(19) NOT NULL, student_id VARCHAR2(20) NOT NULL, student_name VARCHAR2(50) NOT NULL, class_name VARCHAR2(50) NOT NULL, subject VARCHAR2(50) NOT NULL, score NUMBER(5,2) NOT NULL, exam_date DATE NOT NULL, semester VARCHAR2(30) NOT NULL, grade VARCHAR2(20) NOT NULL, created_at TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL, updated_at TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL, CONSTRAINT pk_student_scores PRIMARY KEY (id));-- 添加注释COMMENT ON TABLE student_scores IS \'学生成绩信息表\';COMMENT ON COLUMN student_scores.id IS \'主键ID\';COMMENT ON COLUMN student_scores.student_id IS \'学号\';COMMENT ON COLUMN student_scores.student_name IS \'学生姓名\';COMMENT ON COLUMN student_scores.class_name IS \'班级名称\';COMMENT ON COLUMN student_scores.subject IS \'科目名称\';COMMENT ON COLUMN student_scores.score IS \'分数\';COMMENT ON COLUMN student_scores.exam_date IS \'考试日期\';COMMENT ON COLUMN student_scores.semester IS \'学期\';COMMENT ON COLUMN student_scores.grade IS \'年级\';COMMENT ON COLUMN student_scores.created_at IS \'创建时间\';COMMENT ON COLUMN student_scores.updated_at IS \'更新时间\';-- 创建索引CREATE INDEX idx_student_id ON student_scores(student_id);CREATE INDEX idx_exam_date ON student_scores(exam_date);CREATE INDEX idx_subject ON student_scores(subject);CREATE INDEX idx_class ON student_scores(class_name);-- 创建序列用于自增IDCREATE SEQUENCE student_scores_seq START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;-- 创建触发器实现自增IDCREATE OR REPLACE TRIGGER student_scores_biBEFORE INSERT ON student_scoresFOR EACH ROWBEGIN SELECT student_scores_seq.NEXTVAL INTO :NEW.id FROM dual; :NEW.created_at := SYSTIMESTAMP; :NEW.updated_at := SYSTIMESTAMP;END;/-- 创建触发器实现更新时间自动更新CREATE OR REPLACE TRIGGER student_scores_buBEFORE UPDATE ON student_scoresFOR EACH ROWBEGIN :NEW.updated_at := SYSTIMESTAMP;END;/-- 清空表TRUNCATE TABLE student_scores;-- 插入基础测试数据-- Oracle 使用 CONNECT BY LEVEL 代替 MySQL 的 WITH RECURSIVEINSERT INTO student_scores (student_id, student_name, class_name, subject, score, exam_date, semester, grade)SELECT \'2023\' || LPAD(FLOOR(DBMS_RANDOM.VALUE(0, 100)), 3, \'0\') as student_id, CASE FLOOR(DBMS_RANDOM.VALUE(1, 11)) WHEN 1 THEN \'张三\' WHEN 2 THEN \'李四\' WHEN 3 THEN \'王五\' WHEN 4 THEN \'赵六\' WHEN 5 THEN \'钱七\' WHEN 6 THEN \'孙八\' WHEN 7 THEN \'周九\' WHEN 8 THEN \'吴十\' WHEN 9 THEN \'郑十一\' ELSE \'王十二\' END as student_name, CASE FLOOR(DBMS_RANDOM.VALUE(1, 5)) WHEN 1 THEN \'高一(1)班\' WHEN 2 THEN \'高一(2)班\' WHEN 3 THEN \'高一(3)班\' ELSE \'高一(4)班\' END as class_name, CASE FLOOR(DBMS_RANDOM.VALUE(1, 6)) WHEN 1 THEN \'语文\' WHEN 2 THEN \'数学\' WHEN 3 THEN \'英语\' WHEN 4 THEN \'物理\' ELSE \'化学\' END as subject, ROUND(60 + DBMS_RANDOM.VALUE(0, 40), 2) as score, TO_DATE(\'2023-12-01\', \'YYYY-MM-DD\') + FLOOR(DBMS_RANDOM.VALUE(0, 30)) as exam_date, \'2023-2024学年第一学期\' as semester, \'高一\' as gradeFROM DUALCONNECT BY LEVEL <= 100;-- 插入不及格成绩数据INSERT INTO student_scores (student_id, student_name, class_name, subject, score, exam_date, semester, grade)SELECT \'2023\' || LPAD(FLOOR(DBMS_RANDOM.VALUE(0, 100)), 3, \'0\') as student_id, CASE FLOOR(DBMS_RANDOM.VALUE(1, 11)) WHEN 1 THEN \'张三\' WHEN 2 THEN \'李四\' WHEN 3 THEN \'王五\' WHEN 4 THEN \'赵六\' WHEN 5 THEN \'钱七\' WHEN 6 THEN \'孙八\' WHEN 7 THEN \'周九\' WHEN 8 THEN \'吴十\' WHEN 9 THEN \'郑十一\' ELSE \'王十二\' END as student_name, CASE FLOOR(DBMS_RANDOM.VALUE(1, 5)) WHEN 1 THEN \'高一(1)班\' WHEN 2 THEN \'高一(2)班\' WHEN 3 THEN \'高一(3)班\' ELSE \'高一(4)班\' END as class_name, CASE FLOOR(DBMS_RANDOM.VALUE(1, 6)) WHEN 1 THEN \'语文\' WHEN 2 THEN \'数学\' WHEN 3 THEN \'英语\' WHEN 4 THEN \'物理\' ELSE \'化学\' END as subject, ROUND(40 + DBMS_RANDOM.VALUE(0, 19), 2) as score, TO_DATE(\'2023-12-01\', \'YYYY-MM-DD\') + FLOOR(DBMS_RANDOM.VALUE(0, 30)) as exam_date, \'2023-2024学年第一学期\' as semester, \'高一\' as gradeFROM DUALCONNECT BY LEVEL <= 5;-- 插入优秀成绩数据INSERT INTO student_scores (student_id, student_name, class_name, subject, score, exam_date, semester, grade)SELECT \'2023\' || LPAD(FLOOR(DBMS_RANDOM.VALUE(0, 100)), 3, \'0\') as student_id, CASE FLOOR(DBMS_RANDOM.VALUE(1, 11)) WHEN 1 THEN \'张三\' WHEN 2 THEN \'李四\' WHEN 3 THEN \'王五\' WHEN 4 THEN \'赵六\' WHEN 5 THEN \'钱七\' WHEN 6 THEN \'孙八\' WHEN 7 THEN \'周九\' WHEN 8 THEN \'吴十\' WHEN 9 THEN \'郑十一\' ELSE \'王十二\' END as student_name, CASE FLOOR(DBMS_RANDOM.VALUE(1, 5)) WHEN 1 THEN \'高一(1)班\' WHEN 2 THEN \'高一(2)班\' WHEN 3 THEN \'高一(3)班\' ELSE \'高一(4)班\' END as class_name, CASE FLOOR(DBMS_RANDOM.VALUE(1, 6)) WHEN 1 THEN \'语文\' WHEN 2 THEN \'数学\' WHEN 3 THEN \'英语\' WHEN 4 THEN \'物理\' ELSE \'化学\' END as subject, ROUND(90 + DBMS_RANDOM.VALUE(0, 10), 2) as score, TO_DATE(\'2023-12-01\', \'YYYY-MM-DD\') + FLOOR(DBMS_RANDOM.VALUE(0, 30)) as exam_date, \'2023-2024学年第一学期\' as semester, \'高一\' as gradeFROM DUALCONNECT BY LEVEL <= 5;

2. 创建 Chatflow

详细操作请参考 dify案例分享-基于database插件实现Text2sql的数据库查询图表工作流这篇文章。

这里我使用的是 Oracle 数据库，Database 连接示例，

oracle+oracledb://admin:your_password@xxx.xxx.xxx.xxx:1521?service_name=freepdb1

3. 测试

完结！