SQL query execution is not working with QuerySQLDataBaseTool

[kj4483]

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

import os, getpass
from dotenv import load_dotenv
from langchain_community.utilities import SQLDatabase

load_dotenv()

db_url = f"mysql+mysqlconnector://{os.getenv('DB_USER')}:{os.getenv('DB_PASSWORD')}@{os.getenv('DB_HOST')}/{os.getenv('DB_NAME')}"
db = SQLDatabase.from_uri(db_url)
print(db.dialect)
print(db.get_usable_table_names())
db.run("SELECT `CODE` FROM `CompanyOverview` WHERE `CMP_NM_KOR` = '삼성전자' LIMIT 1;")

from langchain_openai import ChatOpenAI
llm = ChatOpenAI(model="gpt-4o")

from langchain_community.tools.sql_database.tool import QuerySQLDataBaseTool

execute_query = QuerySQLDataBaseTool(db=db)
write_query = create_sql_query_chain(llm, db)
chain = write_query | execute_query
chain.invoke({"question": "What is CODE for '삼성전자' stock?"})

Description

I trying to execute SQL query generated by LLM with LangChain document Build a Question/Answering system over SQL data.

However I encountered following error:
"Error: (mysql.connector.errors.ProgrammingError) 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sql\nSELECT `CODE` \nFROM `CompanyOverview` \nWHERE `CMP_NM_KOR` = '삼성전자' at line 1\n[SQL: sql\nSELECT CODE \nFROM CompanyOverview \nWHERE CMP_NM_KOR = '삼성전자'\nLIMIT 1;\n```]\n(Background on this error at: https://sqlalche.me/e/20/f405)"

How should I do to correctly execute the query?

System Info

$ pip freeze | grep langchain
langchain==0.1.20
langchain-cohere==0.1.5
langchain-community==0.0.38
langchain-core==0.1.52
langchain-groq==0.1.5
langchain-openai==0.1.7
langchain-text-splitters==0.0.2
langchainhub==0.1.20

python version: 3.10.14

[dosubot[bot]]

Hello, @kj4483! I'm here to assist you with any bugs, questions, or contributions. Let's tackle this issue together!

The error you're encountering is due to the SQL query being wrapped in triple backticks, which is not valid SQL syntax. To resolve this issue, you need to ensure that the query generated by the LLM does not include these backticks. Here’s how you can modify your code to handle this:

1. Modify the QuerySQLDataBaseTool class to clean up the query:
   You can create a custom tool that extends QuerySQLDataBaseTool and overrides the _run method to clean up the query before execution.

from langchain_community.tools.sql_database.tool import QuerySQLDataBaseTool

class CustomQuerySQLDataBaseTool(QuerySQLDataBaseTool):
    def _run(self, query: str, run_manager: Optional[CallbackManagerForToolRun] = None) -> Union[str, Sequence[Dict[str, Any]], Result]:
        # Clean up the query by removing triple backticks
        cleaned_query = query.strip("```sql\n").strip("\n```")
        return self.db.run_no_throw(cleaned_query)

2. Use the custom tool in your chain:
   Replace the QuerySQLDataBaseTool with your custom tool in the chain.

import os
from dotenv import load_dotenv
from langchain_community.utilities import SQLDatabase
from langchain_openai import ChatOpenAI
from langchain.chains import create_sql_query_chain

load_dotenv()

db_url = f"mysql+mysqlconnector://{os.getenv('DB_USER')}:{os.getenv('DB_PASSWORD')}@{os.getenv('DB_HOST')}/{os.getenv('DB_NAME')}"
db = SQLDatabase.from_uri(db_url)

llm = ChatOpenAI(model="gpt-4o")

# Use the custom tool
execute_query = CustomQuerySQLDataBaseTool(db=db)
write_query = create_sql_query_chain(llm, db)
chain = write_query | execute_query

response = chain.invoke({"question": "What is CODE for '삼성전자' stock?"})
print(response)

This should ensure that the SQL query generated by the LLM is correctly formatted and executable by the MySQL database.

Additionally, make sure you are using the correct configurations and settings for MySQL databases to avoid syntax errors. Refer to the MYSQL_PROMPT template and other configurations mentioned in the LangChain documentation ^[1][2].

To continue talking to Dosu, mention @dosu.

[dosubot[bot]]

You can leverage existing tools without creating a custom class by using the VectorSQLDatabaseChain class, which handles SQL query generation and execution. Here’s how you can do it:

1. Set up the SQL database chain:

   import os
   from dotenv import load_dotenv
   from langchain_community.utilities import SQLDatabase
   from langchain_openai import ChatOpenAI
   from langchain_experimental.sql import VectorSQLDatabaseChain
   from langchain_core.embeddings import OpenAIEmbeddings
   from langchain_experimental.sql.vector_sql import VectorSQLOutputParser
   
   load_dotenv()
   
   db_url = f"mysql+mysqlconnector://{os.getenv('DB_USER')}:{os.getenv('DB_PASSWORD')}@{os.getenv('DB_HOST')}/{os.getenv('DB_NAME')}"
   db = SQLDatabase.from_uri(db_url)
   llm = ChatOpenAI(model="gpt-4o")
   embeddings = OpenAIEmbeddings()
   sql_cmd_parser = VectorSQLOutputParser.from_embeddings(embeddings)
   db_chain = VectorSQLDatabaseChain.from_llm(llm, db, sql_cmd_parser=sql_cmd_parser)

2. Invoke the chain with your question:

   response = db_chain({"query": "What is CODE for '삼성전자' stock?"})
   print(response)

This setup allows you to generate and execute SQL queries without needing to create a custom class for query cleanup. The VectorSQLDatabaseChain class handles the generation and execution of the SQL query, ensuring that the query is correctly formatted and executable by the database ^[1][2][3][4].

To continue talking to Dosu, mention @dosu.

[Pipasgonzalez]

@dosu from which library do I have to import the "Result" class you mentioned?

[kj4483]

@dosu from which library do I have to import the "Result" class you mentioned?

Try this:
# from sqlalchemy.engine import Result

[dosubot[bot]]

To resolve the syntax error caused by the SQL query being wrapped in triple backticks, you can leverage existing tools without creating a custom class. Here’s how you can do it using the SQLDatabaseChain class, which handles SQL query generation and execution:

1. Set up the SQL database chain:

   import os
   from dotenv import load_dotenv
   from langchain_community.utilities import SQLDatabase
   from langchain_openai import ChatOpenAI
   from langchain.chains import SQLDatabaseChain
   
   load_dotenv()
   
   db_url = f"mysql+mysqlconnector://{os.getenv('DB_USER')}:{os.getenv('DB_PASSWORD')}@{os.getenv('DB_HOST')}/{os.getenv('DB_NAME')}"
   db = SQLDatabase.from_uri(db_url)
   llm = ChatOpenAI(model="gpt-4o")
   
   # Use the SQLDatabaseChain directly
   db_chain = SQLDatabaseChain.from_llm(llm, db)

2. Invoke the chain with your question:

   response = db_chain.run("What is CODE for '삼성전자' stock?")
   print(response)

This approach leverages the existing SQLDatabaseChain class, which should handle the query generation and execution without needing to create a custom class. This should resolve the SQL syntax error you're encountering ^[1].

To continue talking to Dosu, mention @dosu.

[mdbellini]

@dosu can you share your requeriments.txt for this example?

[dominicdev]

any solution for this? tried using custom but still the same error

[BnBear123]

Hi I'm also trying to make chatbot based sql. I wish it help you.

import os, getpass
from dotenv import load_dotenv
from langchain_community.utilities import SQLDatabase

load_dotenv()

db_url = f"mysql+mysqlconnector://{os.getenv('DB_USER')}:{os.getenv('DB_PASSWORD')}@{os.getenv('DB_HOST')}/{os.getenv('DB_NAME')}"
db = SQLDatabase.from_uri(db_url)
print(db.dialect)
print(db.get_usable_table_names())
db.run("SELECT `CODE` FROM `CompanyOverview` WHERE `CMP_NM_KOR` = '삼성전자' LIMIT 1;")

from langchain_openai import ChatOpenAI
llm = ChatOpenAI(model="gpt-4o")

db_chain = SQLDatabaseChain.from_llm(llm, chat_db)

db_chain.run("What is CODE for '삼성전자' stock?")

[Pipasgonzalez]

I dont get it. What did you change?