【Langchain系列五】DbGPT——Langchain+PG构建结构化数据库智能问答系统

Langchain二次开发专栏

• 【Langchain系列一】常用大模型的key获取与连接方式
• 【Langchain系列二】LangChain+Prompt +LLM智能问答入门
• 【Langchain系列三】GraphGPT——LangChain+NebulaGraph+llm构建智能图数据库问答系统
• 【Langchain系列四】RAG——基于非结构化数据库的智能问答
• 【Langchain系列五】DbGPT——Langchain+PG构建结构化数据库智能问答系统
• 【Langchain系列六】Langchain+Gradio界面化
• 【Langchain系列七】Langchain+FastAPI（字符串输出与OpenAI规范流式输出）+FastGPT

1. 官方资料

• 构建一个基于SQL数据的问答系统
• 构建基于SQL数据的问答系统

2. 安装依赖

pip install langchain langchain_community pyjwt langgraph psycopg2

3. 完整代码

import os
import re
import time
from operator import itemgetter

from langchain.chains.sql_database.query import create_sql_query_chain
from langchain_community.agent_toolkits import SQLDatabaseToolkit
from langchain_community.chat_models.zhipuai import ChatZhipuAI
from langchain_community.tools import QuerySQLDataBaseTool
from langchain_community.utilities import SQLDatabase
from langchain_core.messages import AIMessage, SystemMessage, HumanMessage
from langchain_core.output_parsers import StrOutputParser
from langchain_core.prompts import PromptTemplate, FewShotPromptTemplate, ChatPromptTemplate, MessagesPlaceholder
from langchain_core.runnables import RunnablePassthrough
from langchain_ollama import ChatOllama
from langgraph.prebuilt import create_react_agent

# 设置OpenAI API密钥(GLM-4-Flash是免费模型，其它模型会消耗token，智谱的是目前免费模型最好的)
os.environ["ZHIPUAI_API_KEY"] = "你的key"
# llm = ChatZhipuAI(model="GLM-4-Flash", temperature=0.5)
# 本地模型也可以，但是glm4(9b)的效果明显优于qwen2.5:7b,和线上GLM-4-Flash的差不多
# llm=ChatOllama(base_url="http://localhost:11434", model="qwen2.5:7b", temperature=0.5)
llm = ChatOllama(base_url="http://localhost:11434", model="glm4:latest", temperature=0.5)
# qwen 效果有点差,罗里吧嗦还不对
# os.environ["DASHSCOPE_API_KEY"] = "密钥"
# llm = ChatTongyi(model="qwen-turbo", verbose=True)
# 讯飞星火（貌似不支持数据库的智能问答，会报错找不到model kwords
# llm = ChatSparkLLM(spark_app_id='应用id',spark_api_key='应用key',spark_api_secret='应用密钥',spark_llm_domain='generalv3.5')


ai_db = SQLDatabase.from_uri("postgresql://postgres:123456@localhost:5433/ai_data",
                             include_tables=['engineering_defect_detail'])


def query_with_agent(q: str):
    """
    基于代理的agent+提示词的查询优化(给定提示词后，可以解决枚举问题，但是给出的答案不是基于sql真实执行的结果）
    :return:
    """
    prompt = '''Given an input question, first create a syntactically correct {dialect} query to run, then look at the results of the query and return the answer.
                    Use the following format strictly:
                    Question: "Question here"
                    SQLQuery: "SQL Query to run"
                    SQLResult: "The actual result after executing the SQLQuery in the database"
                    Answer: "Final answer here"
                    Only use the following tables:
                    ['engineering_defect_detail(pk_defect_id, defect_component_id (部件), defect_component_type_id (部件种类), defect_position_id (部位), defect_description_id (缺陷描述), message (缺陷描述), detail (详情), levl ('1'一般，'2'严重，'3'危急), type ('0'可见光,'1'红外线), tower_name (杆塔名称), contri_company_name (出资单位名称), property_company_name (资产单位名称), maintain_company_name (运维单位名称), start_tower_no (起始杆号), end_tower_code (终止杆号), search_defect_name (查缺发现人), search_defect_num (查缺发现人人资编码), polling_time (