目录
数据库表结构:
数据库查询的http服务搭建:
流程引擎搭建:
开始,
HTTP查询数据库,
LLM数据分析,
直接回复,
效果测试:
下载链接:
数据库表结构:
数据库poetry中包含4张表,分别是poems,poems_author,poetry,poetry_author。
poems:
poems_author:
Poetry:
poetry_author:
通过执行desc指令,分别获取到4张表的表结构
desc poems;
+-----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| author_id | int | YES | | 0 | |
| title | varchar(255) | NO | | NULL | |
| content | text | NO | | NULL | |
| author | varchar(255) | NO | | NULL | |
+-----------+--------------+------+-----+---------+----------------+desc poems_author;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | NULL | |
| intro_l | text | YES | | NULL | |
| intro_s | text | YES | | NULL | |
+---------+--------------+------+-----+---------+----------------+desc poetry;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| author_id | int | YES | | 0 | |
| title | varchar(255) | NO | | NULL | |
| content | text | NO | | NULL | |
| yunlv_rule | text | YES | | NULL | |
| author | varchar(255) | NO | | NULL | |
| dynasty | char(1) | NO | | NULL | |
+------------+--------------+------+-----+---------+----------------+desc poetry_author;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | NULL | |
| intro | text | YES | | NULL | |
| dynasty | char(1) | NO | | NULL | |
+---------+--------------+------+-----+---------+----------------+
数据库查询的http服务搭建:
通过搭建一个支持post请求的http服务,基于该服务实现SQL的查询结果输出。
from fastapi import FastAPI, HTTPException
from pydantic import BaseModel
import pymysql
import uvicorn
from contextlib import contextmanagerapp = FastAPI()class SQLQuery(BaseModel):sql_query: str@contextmanager
def get_db_connection(config):# 数据库连接的上下文管理器conn = Nonetry:conn = pymysql.connect(**config)yield connfinally:if conn:conn.close()@app.post("/execute_query")
async def execute_query(query: SQLQuery
):# 处理POST请求以执行SQL查询try:sql_queries = query.sql_query.strip()if not sql_queries:raise HTTPException(status_code=400, detail="Missing sql_query parameter")with get_db_connection(app.db_config) as conn:results = []with conn.cursor(pymysql.cursors.DictCursor) as cursor:for sql_query in sql_queries.split(';'):if sql_query.strip():cursor.execute(sql_query)result = cursor.fetchall()if result:results.extend(result)conn.commit()return resultsexcept pymysql.Error as e:raise HTTPException(status_code=500, detail=f"数据库错误: {str(e)}")except Exception as e:raise HTTPException(status_code=500, detail=f"服务器错误: {str(e)}")if __name__ == '__main__':# 数据库配置app.db_config = {"host": "172.86.222.28","user": "root","password": "123456","database": "poetry","port": 3306,"charset": 'utf8mb4'}uvicorn.run(app, host='0.0.0.0', port=35005)
流程引擎搭建:
开始-->LLM-->Http请求-->LLM-->直接回复
开始,
LLM生成SQL,
第一个LLM实现将用户提问转化为SQL,模型选择qwen2.5-14b模型,
System里面输入数据库中表的结构,
# 你是数据分析专家,精通MySQL,能够根据用户的问题生成高效的SQL查询。## 数据库表结构### 1. poetry(唐诗宋诗表);
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| author_id | int(11) | YES | | 0 | |
| title | varchar(255) | NO | | NULL | |
| content | text | NO | | NULL | |
| yunlv_rule | text | YES | | NULL | |
| author | varchar(255) | NO | | NULL | |
| dynasty | char(1) | NO | | NULL | |
+------------+--------------+------+-----+---------+----------------+### 2. poetry_author(唐诗宋诗作者表);
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | NULL | |
| intro | text | YES | | NULL | |
| dynasty | char(1) | NO | | NULL | |
+---------+--------------+------+-----+---------+----------------+### 3. poems(宋词表);
+-----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| author_id | int(11) | YES | | 0 | |
| title | varchar(255) | NO | | NULL | |
| content | text | NO | | NULL | |
| author | varchar(255) | NO | | NULL | |
+-----------+--------------+------+-----+---------+----------------+### 4. poems_author(宋词作者表);
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | NULL | |
| intro_l | text | YES | | NULL | |
| intro_s | text | YES | | NULL | |
+---------+--------------+------+-----+---------+----------------+注意:dynasty字段存储的是缩写:S代表宋,T代表唐USER中输入问答要求,
问题:开始/{x}sys.query
请严格按照以下要求回答:
1. 仅使用提供的表和字段
2. 输出内容直接给完整SQL,不要任何Markdown格式,不要有注释,不要有换行
HTTP查询数据库,
API链接http://10.1.12.10:35005/execute_query,请求模式为POST,
HEADERS设置Content-Type为application/json
BODY选择JSON
Json格式为,
{"sql_query": "LLM-生成SQL/{x}text"}
失败重试次数为3
LLM数据分析,
该模型选用qwen2.5-7b,
SYSTEM中设置如下,
# 数据分析专家工作指南## 角色定位专业的SQL数据分析专家,负责解读MySQL poetry数据库的查询结果:## 核心规则1. 直接分析已提供数据,默认数据已满足查询条件2. 接受数据原貌,不质疑数据有效性3. 无需二次筛选或验证数据范围4. 空数据集统一回复"没有查询到相关数据"5. 避免使用提示性语言6. 分析结果以markdown格式输出7. 整理sql查询结果,以markdown表格格式输出放置输出开头8. 整理sql查询结果, 以echarts图表格式输出放最后,图表配置需要尽量简洁,不要有太多冗余的配置项输出格式如下:```echarts
{
"title": {
"text": "示例图表",
"subtext": "ECharts 示例"
},
"tooltip": {
"trigger": "item",
"formatter": "{a} <br/>{b}: {c} ({d}%)"
},
"legend": {
"orient": "vertical",
"left": "left",
"data": ["A", "B", "C", "D"]
},
"series": [
{
"name": "示例数据",
"type": "pie",
"radius": "50%",
"data": [
{ "value": 335, "name": "A" },
{ "value": 310, "name": "B" },
{ "value": 234, "name": "C" },
{ "value": 135, "name": "D" }
],
"emphasis": {
"itemStyle": {
"shadowBlur": 10,
"shadowOffsetX": 0,
"shadowColor": "rgba(0, 0, 0, 0.5)"
}
}
}
]
}
```9. 注意:如果sql查询结果为标量或者仅有一个结果,就取消Echarts图表。另外,根据结果自行决定使用不同的ECharts类型,eg: 柱状图、饼图、折线图、雷达图等。## 分析报告规范### 数据处理原则1. 严格基于JSON数据集2. 数据已预筛选,直接进行统计分析3. 不进行数据条件的二次确认### 报告结构要求1. 数据概览2. 详细分析3. 结论部分
USER中填写,
数据分析
直接回复,
输出最终LLM的结果{x}text
效果测试:
可以基于数据库进行多表的查询汇总,输出结果,并以表格、柱状图等形式展示。
下载链接:
包括数据库、dify的DSL文件、数据库服务脚本https://download.csdn.net/download/qq_14845119/90482856