How to customise the prompt template from the sql_query_agent ?

[marcusRB]

Hello, I tried many times to customise the prompt template and I cannot to achieve my prompt template works as well.

My first part of code retrieve the toolkit and If I using the chain, the prompt needs other inputs and if I use the agent, the query spent much time to get the answer or directly, fails.

1. Configuration:

# Create` DB Toolkit
toolkit = SQLDatabaseToolkit(db=db,
                             llm=llm)
tools = toolkit.get_tools()                             
agent = create_sql_agent(
    llm=llm,
    toolkit=toolkit,
    verbose=True,
    agent_type=AgentType.ZERO_SHOT_REACT_DESCRIPTION,
    top_k=5,
    prompt=prompt,
    handle_parsing_errors=True,
    early_stopping_method="force",
    max_iterations=30
)

The errors come from prompt template, input_tools or tools_name

in the prompt I'm using:

text of prompt followed by:

**Use the following format**:

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

User input: {input}
SQL query: {query}

**Tools available for the task:**
{tool_names}

the agent.run....and fails.

I tried to use the same example on LangChain, about FewShotPromptTemplate, and either works.

toolkit = toolkit or SQLDatabaseToolkit(llm=llm, db=db)
tools = toolkit.get_tools()
tool_names = ", ".join([tool.name for tool in tools])  # Collect tool names for prompt

# Define the prompt template with all necessary variables
prompt = FewShotPromptTemplate(
    examples=examples,
    example_prompt=answer_prompt,
    prefix="You are a PostgreSQL expert. Here is the relevant table info:\n\nBelow are examples of questions and SQL queries.",
    suffix="User input: {input}\nSQL query: ",
    input_variables=["input", "tools", "agent_scratchpad", "tool_names"]
).partial(
    tools=tool_names,             # Pass the tool names here
    agent_scratchpad=""            # Initialize agent_scratchpad as empty
)

then chain.invoke and fails.

How do I achieve the best results with customised prompts with my examples of queries?

the library used are:

langchain                                0.3.0
langchain-community                      0.3.0
langchain-core                           0.3.0
langchain-experimental                   0.3.0
langchain-nomic                          0.1.3
langchain-ollama                         0.2.0
langchain-openai                         0.2.0
langchain-qdrant                         0.1.4
langchain-text-splitters                 0.3.0
langgraph                                0.2.39
langgraph-checkpoint                     2.0.1
langgraph-sdk                            0.1.33
langsmith                                0.1.135

Thanks

[dosubot[bot]]

Hello @marcusRB! I'm here to assist you with any bugs, questions, or contributions you might have. Let's tackle this challenge together!

To customize the prompt template for the SQL query agent and achieve better results, you can follow these steps:

1. Modify the Prompt Template: Ensure your prompt template is correctly structured and includes all necessary variables. The template should guide the agent in generating the correct SQL queries. Here's an example of how you might structure your prompt:

   from langchain_core.prompts.prompt import PromptTemplate
   
   prompt = PromptTemplate(
       input_variables=["input", "tools", "agent_scratchpad", "tool_names"],
       template="""
       You are a PostgreSQL expert. Here is the relevant table info:
   
       Below are examples of questions and SQL queries.
   
       Use the following format:
   
       Question: Question here
       SQLQuery: SQL Query to run
       SQLResult: Result of the SQLQuery
       Answer: Final answer here
   
       User input: {input}
       SQL query: {query}
   
       Tools available for the task:
       {tool_names}
       """
   )

2. Ensure Correct Input Variables: Make sure that all the input variables required by the prompt are correctly passed. This includes input, tools, agent_scratchpad, and tool_names.

3. Use Few-Shot Learning: Incorporate examples into your prompt using the FewShotPromptTemplate. This helps the model understand the expected input-output relationship. Ensure that your examples are relevant and well-structured.

4. Optimize Agent Configuration: Adjust the agent's configuration to improve performance. This includes setting appropriate values for top_k, max_iterations, and other parameters that control the agent's behavior.

5. Debugging and Testing: Test your setup with different queries to identify any issues. Use debugging tools to trace the flow of data and understand where the process might be failing.

By following these steps and ensuring that your prompt template is well-structured and includes relevant examples, you should be able to achieve better results with your customized prompts.

To continue talking to Dosu, mention @dosu.