search

langchain-ai / langchain

🦜🔗 Build context-aware reasoning applications
https://python.langchain.com
MIT License
92.27k stars 14.74k forks source link

In a Chatbot to chat with SQL using openai and langchain, how to integrate the chatbot to make simple conversations #15587

Closed jyoti194 closed 5 months ago

jyoti194 commented 8 months ago

System Info

python: 3.11 langchain: latest

Who can help?

No response

Information

Related Components

Reproduction

in my chatbot which interact with sql db, if im typing hi its giving me the output as the entity of 1st row and 1st column, instead of answering with nothing or invalid question. and some time the response is generated in ascending order and when re runed the query in descending order, how to validate the answer before its given out?

and how can i make my chatbot user friendly, like when user ask hi, it should greet back or when there is informal question it should just reply

import pandas as pd import sqlalchemy as sal import os, sys, openai import constants from langchain.sql_database import SQLDatabase from langchain.llms.openai import OpenAI from langchain_experimental.sql import SQLDatabaseChain from sqlalchemy import create_engine from langchain.prompts import ChatPromptTemplate

import ChatOpenAI

from langchain.memory import ConversationBufferMemory

import sql_cmd

from langchain.chat_models import ChatOpenAI from typing import List, Optional

from langchain.agents.agent_toolkits import SQLDatabaseToolkit from langchain.callbacks.manager import CallbackManagerForToolRun from langchain.chat_models import ChatOpenAI from langchain_experimental.plan_and_execute import ( PlanAndExecute, load_agent_executor, load_chat_planner, ) from langchain.sql_database import SQLDatabase from langchain.text_splitter import TokenTextSplitter from langchain.tools import BaseTool from langchain.tools.sql_database.tool import QuerySQLDataBaseTool from secret_key import openapi_key

from langchain import PromptTemplate

from langchain.prompts import PromptTemplate

from langchain.prompts.PromptTemplate import PromptTemplate

from langchain.models import ChatGPTClient

from langchain.utils import save_conversation

os.environ['OPENAI_API_KEY'] = openapi_key

from urllib.parse import quote_plus server_name = constants.server_name database_name = constants.database_name username = constants.username password = constants.password encoded_password = quote_plus(password) connection_uri = f"mssql+pyodbc://{username}:{encoded_password}@{server_name}/{database_name}?driver=ODBC+Driver+17+for+SQL+Server"

custom_suffix = """""

If the SQLResult is empty, the Answer should be "No results found". DO NOT hallucinate an answer if there is no result."""

engine = create_engine(connection_uri)

def chat(question,sql_format):

model_name="gpt-3.5-turbo-16k"
db = SQLDatabase(engine, view_support=True, include_tables=['EGV_emp_departments_ChatGPT'])
# db2 = SQLDatabase(engine, view_support=True, include_tables=['EGV_emp_departments_ChatGPT'])
# db = SQLDatabase(engine, view_support=True, include_tables=['EGV_emp_departments'])

llm = ChatOpenAI(temperature=0, verbose=False, model=model_name)

answer = None

if sql_format==False:

    db_chain = SQLDatabaseChain.from_llm(llm, db, verbose=True)

    answer = db_chain.run(question)

else:
    db_chain = SQLDatabaseChain.from_llm(llm, db, verbose=True , return_sql =True)

    sql_query = db_chain.run(question) 
    print("SQLQuery: "+str(sql_query))
    # result = engine.execute(sql_query)

    result_df = pd.read_sql(sql_query, engine)

    answer = result_df.to_dict()

from langchain.prompts import PromptTemplate 

PROMPT = """ 
Given an input question, first create a syntactically correct mssql query to run,  
then look at the results of the query and return the SQLResult as answer.  
The question: {db_chain.run}
"""
prompt_template = """" Use the following pieces of context to answer the question at the end. 
If you don't know the answer, please think rationally and answer from your own knowledge base. 
Don't consider the table which are not mentioned, if no result is matching with the keyword Please return the answer as invalid question 

{context}

Question: {questions}
"""
PROMPT = PromptTemplate(
    template=prompt_template, input_variables=["context", "questions"]
)

def split_text(text, chunk_size, chunk_overlap=0):
    text_splitter = TokenTextSplitter(
        chunk_size=chunk_size, chunk_overlap=chunk_overlap
    )
    yield from text_splitter.split_text(text)

class QuerySQLDatabaseTool2(QuerySQLDataBaseTool):
    def _run(
        self,
        query: str,
        run_manager: Optional[CallbackManagerForToolRun] = None,
    ) -> str:
        result = self.db.run_no_throw(query)

        return next(split_text(result, chunk_size=14_000))

class SQLDatabaseToolkit2(SQLDatabaseToolkit):
    def get_tools(self) -> List[BaseTool]:
        tools = super().get_tools()
        original_query_tool_description = tools[0].description
        new_query_tool = QuerySQLDatabaseTool2(
            db=self.db, description=original_query_tool_description
        )
        tools[0] = new_query_tool

        return tools

return answer

def chain1(question): text = chat(question,False)

return text

def chain2(question): query = chat(question,True)

return query

it uses "\Lib\site-packages\langchain_experimental\sql\base.py"

"\Lib\site-packages\langchain_experimental\sql\vector_sql.py" "\Lib\site-packages\langchain_experimental\sql\prompt.py"

Expected behavior

in my chatbot which interact with sql db, if im typing hi its giving me the output as the entity of 1st row and 1st column, instead of answering with nothing or invalid question. and some time the response is generated in ascending order and when re runed the query in descending order, how to validate the answer before its given out?

and how can i make my chatbot user friendly, like when user ask hi, it should greet back or when there is informal question it should just reply

dosubot[bot] commented 8 months ago

🤖

Hello @jyoti194,

Great to see you again! Hope you're having a productive coding day. Let's dive into your issue.

To make your chatbot more user-friendly and capable of handling informal queries like greetings, you can add a condition to check if the input question is a greeting and respond accordingly. Here's a simple example:

def chat(question, sql_format):
    greetings = ["hi", "hello", "hey"]  # Add more greetings as needed

    # Check if the input question is a greeting
    if question.lower() in greetings:
        return "Hello! How can I assist you today?"

    # If not a greeting, process the question normally
    # Rest of your code...

For validating SQL responses, you can add a check after the SQL query is executed and before the answer is returned. If the result is empty, you can return a user-friendly message like "No results found". Here's how you can do it:

def chat(question, sql_format):
    # Your code...

    result_df = pd.read_sql(sql_query, engine)

    # Check if the result is empty
    if result_df.empty:
        return "No results found"

    answer = result_df.to_dict()

    # Rest of your code...

Regarding the issue of the response being generated in ascending order and when re-run the query in descending order, it seems like the order of the results is determined by the SQL query itself. If you want to control the order of the results, you should modify the SQL query to include an ORDER BY clause.

Please note that these are basic implementations and might need to be adjusted based on your specific requirements and the structure of your code. If you need further assistance, please provide more details about the issues you're facing.

Sources

This response is meant to be useful and save you time. It is not meant to be a precise solution, but rather a starting point for your own research.

Help me be more useful! Please leave a 👍 if this is helpful and 👎 if it is irrelevant.

If you want to continue the conversation, start your reply with @dosu-bot.

jyoti194 commented 8 months ago

Hi @dosubot, Instead of creating a function to write the user friendly response, can we do that by prompt or message & content.

dosubot[bot] commented 8 months ago

🤖

To make your chatbot more user-friendly and capable of handling informal queries like greetings, you can modify the prompt_template in the PromptTemplate class. You can add a condition to check if the input query is a greeting and then respond accordingly. Here's how you can do it:

# flake8: noqa
from langchain.prompts import PromptTemplate

prompt_template = """
Use the following pieces of context to answer the question at the end. If you don't know the answer, just say that you don't know, don't try to make up an answer.

{context}

Question: {question}
Helpful Answer:"""

def handle_greetings(context, question):
    greetings = ["hi", "hello", "hey"]  # Add more greetings as needed
    if any(greeting in question.lower() for greeting in greetings):
        return "Hello! How can I assist you today?"
    else:
        return None

PROMPT = PromptTemplate(
    template=prompt_template, 
    input_variables=["context", "question"],
    preprocessor=handle_greetings
)

In the above code, I've added a function handle_greetings that checks if the question contains any greeting words. If it does, it returns a friendly greeting response. This function is then passed as a preprocessor to the PromptTemplate class. The preprocessor function is called before the template is rendered, allowing you to modify the input variables or return a response directly.

The PromptTemplate class in LangChain is used to define a template for generating prompts for the language model. It takes two parameters: template and input_variables.

The template is a string that defines the structure of the prompt. It can include placeholders, denoted by curly braces {}, which will be replaced by the values of the corresponding input_variables.

The input_variables is a list of strings that define the names of the variables that will be used to replace the placeholders in the template.

In the provided code, the PromptTemplate is used to create a prompt that instructs the language model to use the provided context to answer a question. The placeholders {context} and {question} in the template will be replaced by the actual context and question when the prompt is generated.

To customize the PromptTemplate to handle different types of user inputs, you can modify the template and input_variables parameters. For example, if you want to add a user's name to the prompt, you could modify the template to include a {user_name} placeholder and add "user_name" to the input_variables list.

Here's an example of how you might do this:

prompt_template = """Hello {user_name}, use the following pieces of context to answer the question at the end. If you don't know the answer, just say that you don't know, don't try to make up an answer.

{context}

Question: {question}
Helpful Answer:"""
PROMPT = PromptTemplate(
    template=prompt_template, input_variables=["user_name", "context", "question"]
)

In this example, when the prompt is generated, the {user_name}, {context}, and {question} placeholders in the template will be replaced by the actual user's name, context, and question.

You can find more information about the PromptTemplate class in the LangChain repository at libs/langchain/langchain/chains/retrieval_qa/prompt.py and libs/langchain/langchain/chains/question_answering/stuff_prompt.py.

Sources

This response is meant to be useful and save you time. It is not meant to be a precise solution, but rather a starting point for your own research.

Help me be more useful! Please leave a 👍 if this is helpful and 👎 if it is irrelevant.

If you want to continue the conversation, start your reply with @dosu-bot.

jyoti194 commented 8 months ago

Hi @dosubot, how to create the data visualisation like bar plot or pia charts using SQLDatabaseChain in the same above code

dosubot[bot] commented 8 months ago

🤖

To create data visualizations from the SQL query results in your LangChain application, you can use data visualization libraries like matplotlib or seaborn. Here's how you can modify your chat function to create a bar plot using matplotlib:

First, you need to install matplotlib. You can do this outside your script using pip:

pip install matplotlib

Then, you can modify your chat function as follows:

import matplotlib.pyplot as plt

def chat(question,sql_format):
    model_name="gpt-3.5-turbo-16k"
    db = SQLDatabase(engine, view_support=True, include_tables=['EGV_emp_departments_ChatGPT'])
    llm = ChatOpenAI(temperature=0, verbose=False, model=model_name)
    answer = None
    if sql_format==False:
        db_chain = SQLDatabaseChain.from_llm(llm, db, verbose=True)
        answer = db_chain.run(question)
    else:
        db_chain = SQLDatabaseChain.from_llm(llm, db, verbose=True , return_sql =True)
        sql_query = db_chain.run(question) 
        print("SQLQuery: "+str(sql_query))
        result_df = pd.read_sql(sql_query, engine)
        answer = result_df.to_dict()

        # Create a bar plot
        result_df.plot(kind='bar')
        plt.show()

    return answer

In this code, result_df.plot(kind='bar') creates a bar plot from the DataFrame result_df, and plt.show() displays the plot.

Please note that this is a basic example and you might need to adjust the code according to your specific needs, for example, by specifying the columns to use for the x and y axes in the plot method.

As for your question about whether LangChain supports the integration of data visualization libraries, I wasn't able to find a specific mention of this in the LangChain repository. However, since LangChain is a Python framework, you should be able to use any Python library, including data visualization libraries, as long as they are compatible with the Python version you are using.

I hope this helps! If you have any further questions, feel free to ask.

Sources

This response is meant to be useful and save you time. It is not meant to be a precise solution, but rather a starting point for your own research.

Help me be more useful! Please leave a 👍 if this is helpful and 👎 if it is irrelevant.

If you want to continue the conversation, start your reply with @dosu-bot.

jyoti194 commented 8 months ago

Hi @dosubot, thanks for the response

But not for the SQL result in general if ask a question like " give me a bar plot of how many departments are present in a the view " or just give a plot of new joiner in 2023.

dosubot[bot] commented 8 months ago

🤖

To enable your chatbot to generate data visualizations like bar plots or pie charts in response to user queries interacting with an SQL database, you would need to integrate a data visualization library such as matplotlib or seaborn into your LangChain application.

Here's an example of how you might modify your existing code to generate a bar plot in response to a user query:

import matplotlib.pyplot as plt

def chat(question,sql_format):
    model_name="gpt-3.5-turbo-16k"
    db = SQLDatabase(engine, view_support=True, include_tables=['EGV_emp_departments_ChatGPT'])
    llm = ChatOpenAI(temperature=0, verbose=False, model=model_name)
    answer = None
    if sql_format==False:
        db_chain = SQLDatabaseChain.from_llm(llm, db, verbose=True)
        answer = db_chain.run(question)
    else:
        db_chain = SQLDatabaseChain.from_llm(llm, db, verbose=True , return_sql =True)
        sql_query = db_chain.run(question) 
        print("SQLQuery: "+str(sql_query))
        result_df = pd.read_sql(sql_query, engine)
        answer = result_df.to_dict()
        # Assuming the result is a DataFrame with two columns: 'category' and 'count'
        result_df.plot(kind='bar', x='category', y='count')
        plt.show()
    return answer

This code will generate a bar plot showing the count per category, based on the result of the SQL query. Note that this is a simple example and you might need to adjust the code based on the specifics of your application and the structure of your SQL query results.

Please note that this code assumes that the run method of the SQLDatabaseChain returns the result of the SQL query as a string. If this is not the case, you will need to adjust the code to correctly extract the data from the query result.

For more information on how to use matplotlib to generate different types of plots, you can refer to the matplotlib documentation.

For more information on how to use pandas to manipulate data, you can refer to the pandas documentation.

For more information on how to use LangChain with SQL databases, you can refer to the LangChain SQL database documentation.

I hope this helps! If you have any further questions, feel free to ask.

Sources

This response is meant to be useful and save you time. It is not meant to be a precise solution, but rather a starting point for your own research.

Help me be more useful! Please leave a 👍 if this is helpful and 👎 if it is irrelevant.

If you want to continue the conversation, start your reply with @dosu-bot.