How to pass custom prompt to create_sql_query_chain

[Shambhuraj11]

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_core.prompts import ChatPromptTemplate,MessagesPlaceholder,FewShotChatMessagePromptTemplate

# Few shot examples
examples = [
    {
        "input":"How many entries of records are present?",
        "query":"SELECT COUNT(*) from STUDENT;"
    }, {
        "input":"Tell me number of the students studying in all class?",
        "query":"SELECT count(*) FROM STUDENT;"
    }]

example_prompt_temp = ChatPromptTemplate.from_messages([
("human","{input}\nSQL Query:"),
("ai","{query}")
])

few_shot_prompt = FewShotChatMessagePromptTemplate(
example_prompt = example_prompt_temp,
examples = examples,
input_variables = ['input']

)

# Customized prompt

final_prompt = ChatPromptTemplate.from_messages(
[
("system", "You are a MySQL expert. Given an input question, create a syntactically correct MySQL query to run. Unless otherwise specificed.\n\nHere is the relevant table info: {table_info}\n\nBelow are a number of examples of questions and their corresponding SQL queries."),
few_shot_prompt,
("human", "{input}"),
]
)

# Chain for SQL query creation
generate_query = create_sql_query_chain(llm,db,final_prompt)

# invoking
generate_query.invoke(input={"question":"some query"})

Description

I am getting below error for my case. When i use default prompt it automatically fetch table info from my DB but when i am customizing it, i am getting error.

ValueError: Prompt must have input variables: 'input', 'top_k', 'table_info'

can anyone help me?

System Info

langchain 0.3.1
langchain-chroma 0.1.4
langchain-community 0.3.1
langchain-core 0.3.6

[dosubot[bot]]

Hey @Shambhuraj11! I'm here to help you out with any bugs, questions, or contributions you have. Let's squash those bugs together! 😉

To pass a custom prompt to create_sql_query_chain without encountering the error "ValueError: Prompt must have input variables: 'input', 'top_k', 'table_info'", you need to ensure that your custom prompt includes these input variables. Here is an example of how you can define a custom prompt that meets these requirements:

from langchain_core.prompts import PromptTemplate
from langchain.chains import create_sql_query_chain
from langchain_community.utilities import SQLDatabase
from langchain_openai import ChatOpenAI

# Define the custom prompt template
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}'''

# Create the PromptTemplate object
custom_prompt = PromptTemplate(
    input_variables=["input", "table_info", "top_k", "dialect"],
    template=template
)

# Initialize the SQLDatabase and language model
db = SQLDatabase.from_uri("sqlite:///Chinook.db")
llm = ChatOpenAI(model="gpt-3.5-turbo", temperature=0)

# Create the SQL query chain with the custom prompt
chain = create_sql_query_chain(llm, db, prompt=custom_prompt)

# Example usage
response = chain.invoke({"question": "How many employees are there"})
print(response)

This custom prompt includes the required input variables: input, top_k, table_info, and dialect. By ensuring these variables are present, you can avoid the ValueError and successfully create the SQL query chain ^[1][2][3].

To continue talking to Dosu, mention @dosu.

[Mourdhwaj]

Hi @Shambhuraj11 ,

I'm encountering the same issue when trying to create the chain using create_sql_query_chain. Have you managed to resolve this problem? If so, could you please share how you fixed it?