SQLDatabaseChain from experimental langchain cannot collect SQL query from model output (Syntax error)

[GameModes]

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_experimental.sql import SQLDatabaseChain

_DEFAULT_TEMPLATE = """Given an input question, first create a syntactically correct {dialect} query to run, then look at the results of the query and return the answer.
Use the following format:

Question: "Question here"
SQLQuery: "SQL Query to run"
SQLResult: "Result of the SQLQuery"
Answer: "Final answer here"

Only use the following tables:

{table_info}

Question: {input}"""

PROMPT = PromptTemplate(
    input_variables=["input", "table_info", "dialect"], template=_DEFAULT_TEMPLATE
)

sql_db_chain = SQLDatabaseChain.from_llm(llm = llm, db = db, verbose = True, use_query_checker=True, prompt=PROMPT)

sql_db_chain.run("What are the total amount of unique users?")

Description

• I'm trying to use the function SQLDatabaseChain, to retrieve information of a postgres SQL Database given a NL prompt
• I expect it to give a SQL query and then the query's output if ran on the database
• It shows the correct SQL query, however it somehow doesn't retrieve the query well from the model output to use it on the database and instead tries to run the SQL Query with the quotation marks: SyntaxError: syntax error at or near ""LINE 1: sql
  Making the output (using ' instead of ` to avoid making it a SQL markdown code)

'''sql
SELECT COUNT(DISTINCT id)
FROM user_table
'''

instead of pure SQL:

SELECT COUNT(DISTINCT id)
FROM user_table

System Info

langchain==0.3.12
langchain-community==0.3.12
langchain-core==0.3.25
langchain-experimental==0.3.3
langchain-groq==0.2.1
langchain-ollama==0.2.1
langchain-openai==0.2.12
langchain-text-splitters==0.3.3
windows platform
python 3.10

[feijoes]

@GameModes Could you please provide the error output from the terminal?

[GameModes]

sql_db_chain.run("What are the total amount of unique users?"). I get the error:
sql
SELECT COUNT(DISTINCT id) FROM user_table;

---

SyntaxError Traceback (most recent call last)
/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/base.py in _exec_single_context(self, dialect, context, statement, parameters)
1966 if not evt_handled:
-> 1967 self.dialect.do_execute(
1968 cursor, str_statement, effective_parameters, context

19 frames
SyntaxError: syntax error at or near "
"
LINE 1:
sql
^

The above exception was the direct cause of the following exception:

ProgrammingError Traceback (most recent call last)
/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/default.py in do_execute(self, cursor, statement, parameters, context)
939
940 def do_execute(self, cursor, statement, parameters, context=None):
--> 941 cursor.execute(statement, parameters)
942
943 def do_execute_no_params(self, cursor, statement, context=None):

ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near "
"
LINE 1:
sql
^

[SQL:
sql
SELECT COUNT(DISTINCT id) FROM user_table;
]
(Background on this error at: https://sqlalche.me/e/20/f405).

[feijoes]

I’ve never used SQLDatabaseChain before and didn’t notice anything unusual in the template or the provided code. You might want to try using the default prompt to see if it generates any errors. However, here are my recommendations:

1 Create a custom SQLDatabaseChain where it generates the query and executes it. You can try extracting the SQL from the string using something like:
.replace('"', "").replace("sql", "")

2 Consider using create_sql_agent. I think you can achieve the same result, and it’s easier to set up.

[GameModes]

Thank you for the quick reply. I will look into option 1 more.

I did use the create_sql_agent function lots of times before, however I quit because somehow it continously gave me error indicating it did not properly understand my prompt and my database. Although the create_sql_agent function that you provided seems to come from a different library (mine is from langchain_community.agent_toolkits and yours is from langchain_cohere.sql_agent.agent). Would that make a huge difference?

[feijoes]

I am not sure, you could try use the library and check if generates the same error

[GameModes]

so it seems the cohere create_sql_agent seems to be an older version of the community toolkit. Given that I want to use a local LLM (not open-ai, but rather something like LLama3.2), the cohere version says that it is missing important functions such as the bind function, so I cannot use the cohere create_sql agent.

However, assume you might have encountered this issue as well, I noticed that using the community create_sql_agent, outputs a lot of times: "Invalid Format: Missing 'Action:' after 'Thought:". Scrolling through the internet it should be either an incorrect prompt or a poorly made LLM for this task. It is odd, because 20% of the runs, it does work perfectly without given this Invalid format error....

You might know a good fix? or even a good prompt template for dealing with SQL queries given a NL prompt and executing the query and give the answer in a natural sentence?

[feijoes]

I don't recall seeing this specific error before (I usually work with GPT-4 or related LLMs). The issue is likely with the LLM itself, but if there's a problem with the prompt, I recommend checking and using the default prompt template from create_sql_agent as a base.

Here's an example of how to structure it:

from langchain.agents.mrkl import prompt as react_prompt
from langchain_community.agent_toolkits.sql.prompt import (
    SQL_FUNCTIONS_SUFFIX,
    SQL_PREFIX,
    SQL_SUFFIX,
)
toolkit =  SQLDatabaseToolkit(llm=llm, db=db)
prefix = prefix.format(dialect=toolkit.dialect, top_k=top_k) # top_k = 10

template = "\n\n".join(
                [
                    SQL_PREFIX,
                    "{tools}",
                    react_prompt.FORMAT_INSTRUCTIONS,
                    SQL_SUFFIX,
                ]
            )
prompt = PromptTemplate.from_template(template)