ValueError: Prompt missing required variables: {'agent_scratchpad', 'tool_names', 'tools'}

[psathish10]

Checked other resources

• I added a very descriptive title to this issue.
• I searched the LangChain documentation with the integrated search.
• I used the GitHub search to find a similar question and didn't find it.
• I am sure that this is a bug in LangChain rather than my code.
• The bug is not resolved by updating to the latest stable version of LangChain (or the specific integration package).

Example Code

from langchain.prompts import SemanticSimilarityExampleSelector
from langchain.embeddings import HuggingFaceEmbeddings
from langchain.vectorstores import Chroma
from example_template import few_shots
from langchain.prompts import FewShotPromptTemplate
from langchain.chains.sql_database.prompt import PROMPT_SUFFIX,_mysql_prompt
from langchain.prompts.prompt import PromptTemplate
from langchain_google_genai import GoogleGenerativeAI
from langchain_community.utilities.sql_database import SQLDatabase
from langchain_community.agent_toolkits import create_sql_agent

import os

embeddings = HuggingFaceEmbeddings(model_name="sentence-transformers/all-MiniLM-L6-v2")
to_vector = ["".join(example.values())for example in few_shots]
vectorStore = Chroma.from_texts(to_vector,embeddings,metadatas=few_shots)
example_prompt =PromptTemplate(input_variables=["Question","SQLQuery","SQLResult","Answer"],template="\nQuestion: {Question}\nSQLQuery: {SQLQuery}\nSQLResult: {SQLResult}\nAnswer: {Answer}")

example_selector = SemanticSimilarityExampleSelector(vectorstore=vectorStore,k=2)

fewShot_Prompt_Template= FewShotPromptTemplate(
example_selector=example_selector,
example_prompt=example_prompt,
prefix=_mysql_prompt,
suffix=PROMPT_SUFFIX,
input_variables=["input","table_info","top_k",],

)

os.environ["MYSQL_HOST"] = "localhost"
os.environ["MYSQL_USER"] = "root"
os.environ["MYSQL_PASSWORD"] = ""
os.environ["MYSQL_DATABASE"] = "fhcjgjvkhkjk"

host = os.environ.get('MYSQL_HOST')
user = os.environ.get('MYSQL_USER')
password = os.environ.get('MYSQL_PASSWORD')
database = os.environ.get('MYSQL_DATABASE')

GEMINI_API_KEY = ''

llm = GoogleGenerativeAI(model="gemini-pro", google_api_key=GEMINI_API_KEY)

db = SQLDatabase.from_uri(f"mysql+mysqlconnector://{user}:{password}@{host}/{database}")

agent_executor = create_sql_agent(llm, db=db, verbose=True,prompt=fewShot_Prompt_Template)

Error Message and Stack Trace (if applicable)

ValueError Traceback (most recent call last)
Cell In[70], line 27
21 llm = GoogleGenerativeAI(model="gemini-pro", google_api_key=GEMINI_API_KEY)
24 db = SQLDatabase.from_uri(f"mysql+mysqlconnector://{user}:{password}@{host}/{database}")
---> 27 agent_executor = create_sql_agent(llm, db=db, verbose=True,prompt=fewShot_Prompt_Template)

File c:\Users\SATHISH\AppData\Local\Programs\Python\Python311\Lib\site-packages\langchain_community\agent_toolkits\sql\base.py:180, in create_sql_agent(llm, toolkit, agent_type, callback_manager, prefix, suffix, format_instructions, input_variables, top_k, max_iterations, max_execution_time, early_stopping_method, verbose, agent_executor_kwargs, extra_tools, db, prompt, **kwargs)
170 template = "\n\n".join(
171 [
172 react_prompt.PREFIX,
(...)
176 ]
177 )
178 prompt = PromptTemplate.from_template(template)
179 agent = RunnableAgent(
--> 180 runnable=create_react_agent(llm, tools, prompt),
181 input_keys_arg=["input"],
182 return_keys_arg=["output"],
183 **kwargs,
184 )
186 elif agent_type == AgentType.OPENAI_FUNCTIONS:
187 if prompt is None:

File c:\Users\SATHISH\AppData\Local\Programs\Python\Python311\Lib\site-packages\langchain\agents\react\agent.py:114, in create_react_agent(llm, tools, prompt, output_parser, tools_renderer, stop_sequence)
...
118 tool_names=", ".join([t.name for t in tools]),
119 )
120 if stop_sequence:

ValueError: Prompt missing required variables: {'agent_scratchpad', 'tool_names', 'tools'}

Description

I encountered a **ValueError** when trying to create an SQL agent using LangChain. The error message indicated that the prompt was missing required variables: agent_scratchpad, tool_names, and tools. Despite consulting various resources including Medium blogs, YouTube videos, GitHub references, and the LangChain documentation, I have not been able to find a solution.

System Info

"pip freeze | grep langchain"
platform windows
python version 12

[gideonphilip]

To solve this error you have 2 options:

1. Retrieve the prompt from the hub for reaction using the following code:
   prompt = hub.pull("hwchase17/react")
   When invoking, utilize this prompt for create_sql_agent.

from langchain_community.agent_toolkits.sql.base import create_sql_agent

llm = OpenAI()

prompt = hub.pull("hwchase17/react")

db = SQLDatabase.from_uri(f"mysql+mysqlconnector://{user}:{password}@{host}/{database}")

agent = create_sql_agent(llm,db=db,prompt=prompt)
your_prompt = PromptTemplate.from_template("""Query:{Query}""")
agent.invoke(your_prompt.format(query="question"))

By default, the create_sql_agent function utilizes ZERO_SHOT_REACT_DESCRIPTION, which relies on this code.

   # This code is from langchain_community.agent_toolkits.sql.base 
    if agent_type == AgentType.ZERO_SHOT_REACT_DESCRIPTION:
        if prompt is None:
            from langchain.agents.mrkl import prompt as react_prompt

            format_instructions = (
                format_instructions or react_prompt.FORMAT_INSTRUCTIONS
            )
            template = "\n\n".join(
                [
                    react_prompt.PREFIX,
                    "{tools}",
                    format_instructions,
                    react_prompt.SUFFIX,
                ]
            )
            prompt = PromptTemplate.from_template(template)
        agent = RunnableAgent(
            runnable=create_react_agent(llm, tools, prompt),
            input_keys_arg=["input"],
            return_keys_arg=["output"],
            **kwargs,
        )

2. You can try using different agent types that are supported:

• OPENAI_FUNCTIONS
• openai-tools

[psathish10]

#Does the above code work on the Google Gemini Pro model or the Google PaLM LLM?
`import streamlit as st
from langchain.llms import GooglePalm
from langchain_google_genai import GoogleGenerativeAI
from langchain.utilities import SQLDatabase
from langchain_experimental.sql import SQLDatabaseChain
from langchain.prompts import SemanticSimilarityExampleSelector
from langchain.embeddings import HuggingFaceEmbeddings
from langchain.vectorstores import Chroma
from langchain.prompts import FewShotPromptTemplate
from langchain.chains.sql_database.prompt import PROMPT_SUFFIX,MYSQL_PROMPT
from langchain import hub
from langchain.prompts.prompt import PromptTemplate
from langchain_community.agent_toolkits import create_sql_agent
from langchain_community.agent_toolkits.sql.base import create_sql_agent
from few_shots import few_shots
import os
from dotenv import load_dotenv

Load environment variables

load_dotenv()

def get_few_shot_db_chain(question):
# Database connection settings
os.environ["MYSQL_HOST"] = ""
os.environ["MYSQL_USER"] = ""
os.environ["MYSQL_PASSWORD"] = '"
os.environ["MYSQL_DATABASE"] = ""

host = os.environ.get('MYSQL_HOST')
user = os.environ.get('MYSQL_USER')
password = os.environ.get('MYSQL_PASSWORD')
database = os.environ.get('MYSQL_DATABASE')

db = SQLDatabase.from_uri(f"mysql+pymysql://{user}:{password}@{host}/{database}")

# Initialize the LLM
llm = GoogleGenerativeAI(model="gemini-pro", google_api_key=os.getenv("GOOGLE_API_KEY"))

# Initialize the embeddings
embeddings = HuggingFaceEmbeddings(model_name='sentence-transformers/all-MiniLM-L6-v2')
to_vectorize = [" ".join(example.values()) for example in few_shots]

# Ensure the collection is created or fetched properly
vectorstore = Chroma.from_texts(to_vectorize, embeddings,metadatas=few_shots )

# Example selector using the vectorstore
example_selector = SemanticSimilarityExampleSelector(
    vectorstore=vectorstore,
    k=2,
)

# Prompt for MySQL
mysql_prompt = """
You are a MySQL expert. Given an input question, first create a syntactically correct MySQL query to run, then look at the results of the query and return the answer to the input question.
Unless the user specifies in the question a specific number of examples to obtain, query for at most {top_k} results using the LIMIT clause as per MySQL. You can order the results to return the most informative data in the database.
Never query for all columns from a table. You must query only the columns that are needed to answer the question. Wrap each column name in backticks (`) to denote them as delimited identifiers.
Pay attention to use only the column names you can see in the tables below. Be careful to not query for columns that do not exist. Also, pay attention to which column is in which table.
Pay attention to use CURDATE() function to get the current date, if the question involves "today".

Use the following format:

Question: Question here
SQLQuery: Query to run with no pre-amble
SQLResult: Result of the SQLQuery
Answer: Final answer here

No pre-amble.
"""

# Example prompt template
example_prompt = PromptTemplate(
    input_variables=["Question", "SQLQuery", "SQLResult", "Answer"],
    template="\nQuestion: {Question}\nSQLQuery: {SQLQuery}\nSQLResult: {SQLResult}\nAnswer: {Answer}",
)

SUFFIX = """Begin!

Question: {input}
Thought:{agent_scratchpad}"""

pro= hub.pull("hwchase17/react")

# Few shot prompt template
few_shot_prompt = FewShotPromptTemplate(
    example_selector=example_selector,
    example_prompt=example_prompt,
    prefix=mysql_prompt,
    suffix=SUFFIX,
    input_variables=['agent_scratchpad',"input", "table_info", "top_k",'tool_names','tools'],  # These variables are used in the prefix and suffix
)

print(f"Few shot prompt: {few_shot_prompt}")

# Create the SQL agent
chain = create_sql_agent(llm=llm, db=db, verbose=True,prompt=few_shot_prompt)


# Return the response for the query
response = chain(question)
return response

Streamlit app

st.title("SQL Query Assistant")

user_question = st.text_input("Enter your question about the database:")

if st.button("Ask"):
if user_question:
result = get_few_shot_db_chain(user_question)
st.write(result)
else:
st.write("Please enter a question.")
`
I'm still facing issues. How can I resolve them? Is the above code correct for the Google Gemini Pro model or the Google PaLM LLM?

and I need to pass my own prompt template in vectorized formate

[psathish10]

I'm still facing issues. How can I resolve them? Is the above code correct for the Google Gemini Pro model or the Google PaLM LLM?

and I need to pass my own prompt template in vectorized formate

[gideonphilip]

The problem is not with what type of LLM you are using

   # This code is from langchain_community.agent_toolkits.sql.base 
    if agent_type == AgentType.ZERO_SHOT_REACT_DESCRIPTION:
        if the prompt is None:
            from langchain.agents.make import prompt as react_prompt

            format_instructions = (
                format_instructions or react_prompt.FORMAT_INSTRUCTIONS
            )
            template = "\n\n".join(
                [
                    react_prompt.PREFIX,
                    "{tools}",
                    format_instructions,
                    react_prompt.SUFFIX,
                ]
            )
            prompt = PromptTemplate.from_template(template)
        agent = RunnableAgent(
            runnable=create_react_agent(LLM, tools, prompt),
            input_keys_arg=["input"],
            return_keys_arg=["output"],
            **kwargs,
        )

The above is the internal code of langchain SQL agent, and if you check the default agent type is AgentType.ZERO_SHOT_REACT_DESCRIPTION:, which makes make prompt specific to the react agent if you are not passing, and if you are passing the prompt it should be aligned with the react agent structure. But you are trying to pass a few short prompts which will not work

    if agent_type == AgentType.ZERO_SHOT_REACT_DESCRIPTION:
        if prompt is None:
            from langchain.agents.mrkl import prompt as react_prompt

            format_instructions = (
                format_instructions or react_prompt.FORMAT_INSTRUCTIONS
            )
            template = "\n\n".join(
                [
                    react_prompt.PREFIX,
                    "{tools}",
                    format_instructions,
                    react_prompt.SUFFIX,
                ]
            )
            prompt = PromptTemplate.from_template(template)

To solve this problem you have two options:

• Avoid passing prompt to create_sql_agent(llm=llm, db=db, verbose=True,prompt=few_shot_prompt) and use like this

  agent = create_sql_agent(llm,db=db,prompt=prompt)
  few_shot_prompt = FewShotPromptTemplate(
    example_selector=example_selector,
    example_prompt=example_prompt,
    prefix=mysql_prompt,
    suffix=SUFFIX,
    input_variables=["input"],  # These variables are used in the prefix and suffix
)
  agent.invoke(few_shot_prompt.format(input="get me table"))

• Try to change agent_type to openai-tools reate_sql_agent(llm=llm, db=db, verbose=True,prompt=few_shot_prompt,agent_type="openai-tools")

Also I need more clarity on the reason for example_selector and the need for passing MySQL prompt, I can check and give you the working code