create_sql_agent Error: table_names {"'<table name>'"} not found in database.

yernenip commented 8 months ago

System Info

LangChain version: 0.0.330 Windows 11 Python 3.11.3 SQLAlchemy version: 2.0.23

Who can help?

No response

Information

[ ] The official example notebooks/scripts
[X] My own modified scripts

Related Components

[ ] LLMs/Chat Models
[ ] Embedding Models
[ ] Prompts / Prompt Templates / Prompt Selectors
[ ] Output Parsers
[ ] Document Loaders
[ ] Vector Stores / Retrievers
[ ] Memory
[X] Agents / Agent Executors
[X] Tools / Toolkits
[ ] Chains
[ ] Callbacks/Tracing
[ ] Async

Reproduction

Steps to reproduce the behavior.

Follow the steps mentioned in https://python.langchain.com/docs/integrations/toolkits/sql_database
Replace connection string with a PostgreSQL connection string
Run the following code, make sure to update the connection string and the database/tables you are querying.

import langchain from langchain.llms import CTransformers from langchain.agents import create_sql_agent from langchain.agents.agent_toolkits import SQLDatabaseToolkit from langchain.sql_database import SQLDatabase from langchain.agents import AgentExecutor from langchain.agents.agent_types import AgentType

config = {'max_new_tokens': 256, 'repetition_penalty': 1.1, 'temperature': 0, 'context_length': 4096}

llm = CTransformers(model="TheBloke/CodeLlama-7B-Instruct-GGUF", model_file="codellama-7b-instruct.Q4_K_M.gguf",config=config, verbose=True)

CONNECTION_STRING = 'postgresql://postgresuser:password@localhost:5432/pharmadb' db = SQLDatabase.from_uri(CONNECTION_STRING)

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

agent_executor = create_sql_agent( llm=llm, toolkit=toolkit, verbose=True, agent_type=AgentType.ZERO_SHOT_REACT_DESCRIPTION, )

agent_executor.run("Describe the corporation table")

I get the following output in verbose mode..

Entering new AgentExecutor chain... Action: sql_db_list_tables Action Input: '' Observation: corporation, molecule, product_mat, productpack Thought: The corporation table is probably one of those tables. Action: sql_db_schema Action Input: 'corporation' Observation: Error: table_names {"'corporation'"} not found in database Thought: I must have misspelled the name of the table. Let me try again. Action: sql_db_schema Action Input: 'corporation' Observation: Error: table_names {"'corporation'"} not found in database Thought: I must have misspelled the name of the table. Let me try again. Action: sql_db_schema Action Input: 'corporation' Observation: Error: table_names {"'corporation'"} not found in database Thought: I must have misspelled the name of the table. Let me try again. Action: sql_db_schema Action Input: 'corporation' Observation: Error: table_names {"'corporation'"} not found in database Thought: I must have misspelled the name of the table. Let me try again. Action: sql_db_schema Action Input: 'corporation' Observation: Error: table_names {"'corporation'"} not found in database Thought: I must have misspelled the name of the table. Let me try again. Action: sql_db_schema Action Input: 'corporation' . . .

Finished chain. 'Agent stopped due to iteration limit or time limit.'

Expected behavior

Expected to correctly identify the table and its schema, run the query to retrieve top 3 records from the table and provide a description of the table.

dosubot[bot] commented 8 months ago

🤖

Hello @yernenip,

Nice to meet you! I'm Dosu, your friendly AI assistant here at LangChain. I'm here to help you troubleshoot bugs, answer any questions you may have and guide you on how to be a contributor. While we wait for a human maintainer to join us, rest assured I'll do my best to assist you.

I'm currently taking a look at your issue. Expect a more detailed response from me shortly.

dosubot[bot] commented 8 months ago

🤖

Based on the information you've provided and similar issues I found in the LangChain repository, it seems like the problem might be related to the way SQL commands are handled in LangChain. Specifically, the issue might be due to the SQL commands being surrounded by quotation marks, which can cause syntax errors.

There are two potential solutions to this problem:

After the SQL string is generated, perform a normalization that:
- Strips the SQL string
- Checks if quotation marks are at the beginning and the end and then removes them.

You can implement this solution by adding a normalization function to your code, like so:

def normalize_sql_cmd(sql_cmd):
    sql_cmd = sql_cmd.strip()
    if sql_cmd[0] == "'" and sql_cmd[-1] == "'":
        sql_cmd = sql_cmd[1:-1]
    return sql_cmd

Then, you can use this function to normalize the SQL commands before they are executed:

sql_cmd = normalize_sql_cmd(sql_cmd)

Modify the default prompt template for a database chain to only allow single quotes in SQL commands or to not ask for an SQL statement with quotes. This solution was suggested in this pull request.

If these solutions do not solve your problem, it might be helpful to provide more information about the exact error message you're seeing and the context in which it occurs. This could help identify other potential causes of the problem.

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.

fr3fou commented 6 months ago

Did you ever figure out a solution? @yernenip

yernenip commented 6 months ago

@fr3fou Not really, I moved on from LangChain to using function calling from OpenAI.

P.S:- I also changed my implementation from CodeLlama to OpenAI :-)

dijkstra-mose commented 3 months ago

I also encountered the same problem. After I upgrade the langchain, the problem is fixed. I think your langchain version is too early.

langchain-ai / langchain