SQL Agent table name error while taking actions

[TheLancelot]

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 create_sql_agent
from langchain.agents.agent_types import AgentType
agent_executor = create_sql_agent(llm=llm,db=db,verbose=True)

agent_executor.invoke(
    {
        "input": "describe the pollution_data table"
    }
)

Description

I am using the Langchain SQL agent to answer queries about an sql database. The database has only one table called as pollution_data and in the question I am asking the agent to describe the table.

On running, the agent stops due to iteration limit.

As seen in the verbose, highlighted in the backticks below

Action: sql_db_list_tables
Action Input:
Observationpollution_data Now that I know the table exists, I can use sql_db_schema to get the schema and sample data

Action Input: pollution_data
ObservationError: table_names {'pollution_data\nObservation'} not found in database I need to check if I have the correct spelling and table name by querying the database directly
Action: sql_db_query

this error keeps repeating until iteration limit, the problem obviously is it is considering \nObservation to be a part of the database tablename even if it knows the actual table name in the previous iteration

How do i fix this?

System Info

Package Information

langchain_core: 0.1.25
langchain: 0.1.8
langchain_community: 0.0.21
langsmith: 0.1.5
langchain_experimental: 0.0.52

[dosubot[bot]]

🤖

Hey there @TheLancelot! 😄 Great to see you diving into the depths of LangChain again. How's everything going on your end?

Based on the information you've provided and the similar issues I found in the LangChain repository, it seems like the problem might be related to how the table names are being processed.

In a similar issue #7457, the problem was that the single quotes around table names in the Example Input of InfoSQLDatabaseTool were misleading the language model. The solution was to customize the tool's description in the toolkits to remove the single quotes around table names. Here's an example of how to create a custom toolkit to address the issue:

from langchain.agents.agent_toolkits.sql.toolkit import SQLDatabaseToolkit
from langchain.tools import BaseTool
from langchain.tools.sql_database.tool import (
    InfoSQLDatabaseTool,
    ListSQLDatabaseTool,
    QuerySQLCheckerTool,
    QuerySQLDataBaseTool,
)

class CustomSQLDatabaseToolkit(SQLDatabaseToolkit):
    def get_tools(self) -> list[BaseTool]:
        list_tables_tool = ListSQLDatabaseTool(db=self.db)
        table_schema_tool_description = (
            "Use this tool to get the schema of specific tables. "
            "Input to this tool is a comma-separated list of tables, output is the "
            "schema and sample rows for those tables. "
            "Be sure that the tables actually exist by calling "
            f"{list_tables_tool.name} first! "
            "Example Input: table1, table2, table3"  # NOTE: removed the single quotes here
        )
        table_schema_tool = InfoSQLDatabaseTool(
            db=self.db,
            description=table_schema_tool_description,
        )
        # ... rest of the toolkit setup ...
        return [
            list_tables_tool,
            table_schema_tool,
            # ... other tools ...
        ]

In another similar issue #12458, the solution was to modify the get_schema function to replace the incorrect table names with the correct ones. Here's a code snippet that demonstrates this:

def get_schema(_):
    table_info = db.get_table_info()
    for table in table_info:
        if table['name'] == 'table_products':
            table['name'] = 'products'
        elif table['name'] == 'table_cocktails':
            table['name'] = 'cocktails'
    return table_info

Please try these solutions and let me know if they solve your problem. If not, could you provide more information about your database schema and the exact error message you're getting? This will help me better understand the issue and find a solution.

Sources

• The single quote in Example Input of SQLDatabaseToolkit will mislead LLM
• langchain.agents.create_sql_agent / SQLDatabaseToolkit - Agent never gets DB schema and tries to query nonexistent table names.
• libs/community/langchain_community/agent_toolkits/sql/base.py

About Dosu

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.

[kbatsuren]

@TheLancelot, did you fix this issue?

[kbatsuren]

It looks like the table name is corrupted with this suffix "\nObservation".

[skumarmallampeta]

Hi @kbatsuren were you able to get past this issue? I am running into a similar issue and not able to query data from the tables

[pritipanna]

Hi @kbatsuren were you able to get past this issue? I am running into a similar issue and not able to query data from the tables

for me, the issue was it was taking default dbo schema. my tables were in a different schema. when i specified the schema, the issue persisted, because while writing the query it was not specifying the schema. instead of 'schema.table_name' it was taking 'table_name'. which is still creating an issue for me. however, i wrote a prompt template where i specified the machine to use the schema everytime while querying. gave it some examples too, that resolved my issue

[kbatsuren]

Hi @skumarmallampeta and @pritipanna, I passed this issue by changing the LLM models.

In my case, I changed the model, Mistral-7b to gpt4 and llama-70b, but this issue should be resolved in smaller 7b LLMs.

[skumarmallampeta]

thank you for the info @kbatsuren , my issue resolved after switching to another LLM model.

[texasdave2]

Has anyone found a solution for this that works on 8b models? To have the model dictate a new line issue or single quote issue seems like an enormous amount of overhead to parse a small piece of code:

Action: sql_db_schema
Action Input: healthcare
ObservationError: table_names {'healthcare\nObservation'} not found in databaseIt seems there is an error in the schema output. Let me try listing the tables again.

The Observation portion of the system prompt has a newline in it possibly and is confusing both Meta/Llama3 8b and Hermes/Llama 8b. It shouldn't take a 70b model to do this right?