create_react_agent doesn't execute generated query on sql database

[susandong]

Checked other resources

• I added a very descriptive title to this question.
• I searched the LangChain documentation with the integrated search.
• I used the GitHub search to find a similar question and didn't find it.

Commit to Help

• I commit to help with one of those options 👆

Example Code

from langchain_community.agent_toolkits import SQLDatabaseToolkit
from langchain_core.messages import SystemMessage
from langchain_core.messages import HumanMessage
from langgraph.prebuilt import create_react_agent


toolkit = SQLDatabaseToolkit(db=db, llm=llm)

tools = toolkit.get_tools()


SQL_PREFIX = """You are an agent designed to interact with a SQL database.
Given an input question, create a syntactically correct SQLite query to run, then look at the results of the query and return the answer.
Unless the user specifies a specific number of examples they wish to obtain, always limit your query to at most 5 results.
You can order the results by a relevant column to return the most interesting examples in the database.
Never query for all the columns from a specific table, only ask for the relevant columns given the question.
You have access to tools for interacting with the database.
Only use the below tools. Only use the information returned by the below tools to construct your final answer.
You MUST double check your query before executing it. If you get an error while executing a query, rewrite the query and try again.

DO NOT make any DML statements (INSERT, UPDATE, DELETE, DROP etc.) to the database.

To start you should ALWAYS look at the tables in the database to see what you can query.
Do NOT skip this step.
Then you should query the schema of the most relevant tables."""

system_message = SystemMessage(content=SQL_PREFIX)

agent_executor = create_react_agent(llm, tools, messages_modifier=system_message)

for s in agent_executor.stream(
    {"messages": [HumanMessage(content="Which country's customers spent the most?")]}
):
    print(s)
    print("----")

Description

I am using the above code to create sql agent, the code runs, it generates reasonable sql queries, but the query results were all hallucinated, not the actual result based on the database. wondering how is the agent connected to db, since the agent arguments don't include db and why sql_db_query tool doesn't execute on the sql db.
@Duba

System Info

langchain==0.2.5
langchain-aws==0.1.6
langchain-chroma==0.1.0
langchain-community==0.2.5
langchain-core==0.2.7
langchain-experimental==0.0.51
langchain-text-splitters==0.2.1

[dosubot[bot]]

Hey there, @susandong! 👋 I'm a bot here to help you with bugs, questions, and becoming a contributor. Let's tackle this issue together!

The issue you're encountering is likely due to the create_react_agent function not being designed to directly handle SQL database interactions. Instead, you should use the create_sql_agent function, which is specifically designed for this purpose.

Here's how you can modify your code to use create_sql_agent:

1. Install Required Packages:
   Ensure you have the necessary packages installed:

   %pip install --upgrade --quiet langchain langchain-community langchain-openai

2. Set Up the Database:
   Make sure the SQLite database (Chinook.db) is correctly set up in the same directory as your notebook:

   from langchain_community.utilities import SQLDatabase
   
   db = SQLDatabase.from_uri("sqlite:///Chinook.db")
   print(db.dialect)
   print(db.get_usable_table_names())
   print(db.run("SELECT * FROM Artist LIMIT 10;"))

3. Create the SQL Agent:
   Create the SQL agent using the create_sql_agent function:

   from langchain_community.agent_toolkits import create_sql_agent
   from langchain_openai import ChatOpenAI
   
   llm = ChatOpenAI(model="gpt-3.5-turbo", temperature=0)
   agent_executor = create_sql_agent(llm, db=db, agent_type="openai-tools", verbose=True)

4. Invoke the Query:
   Invoke the query and print the result:

   agent_executor.invoke(
       "List the total sales per country. Which country's customers spent the most?"
   )

By following these steps, you can ensure that the agent is properly connected to the database and that the SQL queries are correctly executed ^[1][2].

To continue talking to Dosu, mention @dosu.

[susandong]

@dosu this is not correct, the above code is from the sql qa tutorial: https://python.langchain.com/v0.2/docs/tutorials/sql_qa/

[dosubot[bot]]

To customize the prompt used by the create_sql_agent function, you can create a custom prompt template and pass it to the create_sql_agent function. Here is an example of how to do this:

1. Create a FewShotPromptTemplate:

   from langchain_core.prompts import (
       ChatPromptTemplate,
       FewShotPromptTemplate,
       MessagesPlaceholder,
       PromptTemplate,
       SystemMessagePromptTemplate,
   )
   
   system_prefix = """You are an agent designed to interact with a SQL database.
   Given an input question, create a syntactically correct {dialect} query to run, then look at the results of the query and return the answer.
   Unless the user specifies a specific number of examples they wish to obtain, always limit your query to at most {top_k} results.
   You can order the results by a relevant column to return the most interesting examples in the database.
   Never query for all the columns from a specific table, only ask for the relevant columns given the question.
   You have access to tools for interacting with the database.
   Only use the given tools. Only use the information returned by the tools to construct your final answer.
   You MUST double check your query before executing it. If you get an error while executing a query, rewrite the query and try again.
   
   DO NOT make any DML statements (INSERT, UPDATE, DELETE, DROP etc.) to the database.
   
   If the question does not seem related to the database, just return "I don't know" as the answer.
   
   Here are some examples of user inputs and their corresponding SQL queries:"""
   
   few_shot_prompt = FewShotPromptTemplate(
       example_selector=example_selector,
       example_prompt=PromptTemplate.from_template(
           "User input: {input}\nSQL query: {query}"
       ),
       input_variables=["input", "dialect", "top_k"],
       prefix=system_prefix,
       suffix="",
   )

2. Create a Full Chat Prompt Template:

   full_prompt = ChatPromptTemplate.from_messages(
       [
           SystemMessagePromptTemplate(prompt=few_shot_prompt),
           ("human", "{input}"),
           MessagesPlaceholder("agent_scratchpad"),
       ]
   )

3. Create the Agent with the Custom Prompt:

   agent = create_sql_agent(
       llm=llm,
       db=db,
       prompt=full_prompt,
       verbose=True,
       agent_type="openai-tools",
   )

4. Invoke the Agent:

   agent.invoke({"input": "How many artists are there?"})

Alternatively, you can directly pass a custom prompt to the create_sql_agent function using the prompt parameter. Here is an example:

from langchain_core.prompts import ChatPromptTemplate, SystemMessagePromptTemplate, HumanMessagePromptTemplate, MessagesPlaceholder

custom_prompt = ChatPromptTemplate.from_messages(
    [
        SystemMessagePromptTemplate.from_template("You are an SQL agent. {dialect}"),
        HumanMessagePromptTemplate.from_template("{input}"),
        MessagesPlaceholder(variable_name="agent_scratchpad"),
    ]
)

agent = create_sql_agent(
    llm=llm,
    db=db,
    prompt=custom_prompt,
    verbose=True,
    agent_type="tool-calling",
)

This setup allows you to customize the prompt used by the create_sql_agent function by defining a custom FewShotPromptTemplate or directly passing a ChatPromptTemplate to the agent creation function ^[1][2][3][4].

To continue talking to Dosu, mention @dosu.

[dolanp83]

Same issue. The bot's suggestion is using a different method completely than the tutorial.

[ahammi]

I am getting the same error, the agent is getting stuck during the processing and reasoning step, and it's not progressing beyond the initial internal thought or action. Here is the feedback I am getting when I ask it to fetch data "To find the number of approved opportunities for the period 2024P05, we need to query the ApprovedOpportunities table for that specific period. Let's first check the schema of the ApprovedOpportunities table to understand its structure. I'll start by retrieving the schema of the ApprovedOpportunities table."
And that's all , it doesn't go into further steps, any idea ?

Thanks a lot,
Regards

[felipemg1991]

Hello everyone. Has create_sql_agent been removed from the project? I can't find it in the latest versions of Langchain experimental

[Dominicod]

Also running into this issue with a local model using Ollama, it seems to not even be attempting to use the tools at all.