KeyError: 'Input to ChatPromptTemplate is missing variables {\'"status"\'}.

[quangnguyen1135]

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

# Define SQL chain
def get_sql_chain(db):
    template = """
    You are a data analyst at a company. You are interacting with a user who is asking you questions about the company's database.
   
    <SCHEMA>{schema}</SCHEMA>
    Conversation History: {chat_history}

   
    For example:
    Question: trạng thái của đơn hàng
    SQL Query: 
    ""
    SELECT 
        ti.tracking_number, 
        c.name AS carrier_name, 
        JSON_UNQUOTE(JSON_EXTRACT(ti.shipments, '$[*].time')) AS shipment_times,
        JSON_UNQUOTE(JSON_EXTRACT(ti.shipments, '$[*].status')) AS shipment_statuses,
        JSON_UNQUOTE(JSON_EXTRACT(ti.shipments, '$[*].description')) AS shipment_descriptions,
        JSON_UNQUOTE(JSON_EXTRACT(ti.shipments, '$[*].location')) AS shipment_locations,
        ti.created_at, 
        ti.updated_at
    FROM 
        tracking_info ti
    JOIN 
        carriers c ON ti.carrier_id = c.id
    WHERE 
        ti.tracking_number NOT IN (
        SELECT tracking_number 
        FROM tracking_info 
        WHERE JSON_UNQUOTE(JSON_EXTRACT(shipments, '$[*].status')) = 'Delivered'
    )
    ORDER BY 
        ti.updated_at DESC;
    ""

    Kiểm tra các đơn hàng còn chưa Delivered:
    '''
    SELECT count(*)
    FROM tracking_info
    WHERE NOT JSON_CONTAINS(shipments, '{"status": "Delivered"}');
    '''
    
    Your turn:
    
    Question: {question}
    SQL Query:
    """
    prompt = ChatPromptTemplate.from_template(template)
    llm = ChatGoogleGenerativeAI(model="gemini-1.0-pro", temperature=0.3)
    
    def get_schema(_):
        return db.get_table_info()
    
    return (
        RunnablePassthrough.assign(schema=get_schema)
        | prompt
        | llm
        | StrOutputParser()
    )

# Define response chain
def get_response(user_query: str, chat_history: list):
    db = init_database()
    sql_chain = get_sql_chain(db)
    
    template = """
    You are a data analyst at a company. You are interacting with a user who is asking you questions about the company's database.
    Based on the table schema below, question, sql query, and sql response, write a natural language response in the specified format if the question is related to the database.

    <SCHEMA>{schema}</SCHEMA>

    Conversation History: {chat_history}
    SQL Query: <SQL>{query}</SQL>
    User question: {question}
    SQL Response: {response}
    Your turn:
    """
    
    prompt = ChatPromptTemplate.from_template(template)
    llm = ChatGoogleGenerativeAI(model="gemini-1.0-pro", temperature=0.3)

    chain = (
        RunnablePassthrough.assign(query=sql_chain).assign(
            schema=lambda _: db.get_table_info(),
            response=lambda vars: db.run(vars["query"]) if "query" in vars else None,
        )
        | prompt
        | llm
        | StrOutputParser()
    )
'''

Description

I just want the bot to check how many undelivered orders there are in the database
but the bot understands it as a variable, I just put that sentence in the prompt so the bot can use it to query

data in mysql like:
'''
INSERT INTO tracking_info (id, tracking_number, carrier_id, shipments, created_at, updated_at)
VALUES(280578,'420070169261290339726900366546',1615,'[{"time": "2024-02-03 11:10:00", "status": "OutForDelivery", "carrier": {"code": "21051", "name": "USPS", "carrier_id": 1615, "provider_id": 1}, "location": "CRANFORD, NJ 07016", "description": "Out for Delivery, Expected Delivery by 9:00pm -> Your item is out for delivery on February 3, 2024 at 6:10 am in CRANFORD, NJ 07016."}, {"time": "2024-02-02 17:43:00", "status": "Arrived at Post Office", "carrier": {"code": "21051", "name": "USPS", "carrier_id": 1615, "provider_id": 1}, "location": "CRANFORD, NJ 07016", "description": "Arrived at Post Office"}]','2024-02-03 03:28:08','2024-02-03 17:20:03')
'''

System Info

langchain_core: 0.2.1
langchain: 0.2.1
langchain_community: 0.2.1
langsmith: 0.1.63
langchain_google_genai: 1.0.5
langchain_openai: 0.1.7
langchain_text_splitters: 0.2.0

[dosubot[bot]]

Hey there, @quangnguyen1135! I'm here to help you out with any bugs, questions, or contributions you have in mind. Let's get to work!

To fix the KeyError: 'Input to ChatPromptTemplate is missing variables {"status"}' error, you need to ensure that the variable status is included in the input to the ChatPromptTemplate. This error indicates that the ChatPromptTemplate expects a variable named status but it is not being provided.

Here is an example of how you can include the status variable in the input:

from langchain_core.prompts import ChatPromptTemplate

# Assuming you have a ChatPromptTemplate instance
chat_prompt_template = ChatPromptTemplate.from_messages([
    ("system", "You are checking the status of orders."),
    ("human", "What is the status of the orders?"),
    ("ai", "The status of the orders is {status}.")
])

# Provide the required 'status' variable in the input
input_variables = {
    "status": "undelivered"  # or whatever value is appropriate
}

# Use the template with the input variables
result = chat_prompt_template.format(**input_variables)
print(result)

Make sure that wherever you are calling the ChatPromptTemplate, you include the status variable in the dictionary passed to it. This ensures that all required variables are provided, preventing the KeyError ^[1][2].

To continue talking to Dosu, mention @dosu.

[quangnguyen1135]

"status" is in the database and is not an input parameter. I just want to be Just put it in the prompt so the bot can understand the query command

'''
SELECT count(*)
FROM tracking_info
WHERE NOT JSON_CONTAINS(shipments, '{"status": "Delivered"}');
'''

[dosubot[bot]]

To avoid the KeyError and correctly include the "status" from the database in the prompt, you need to ensure that "status" is included in the input_variables list of the PromptTemplate and referenced in the template string.

Here is how you can modify the code:

1. Add "status" to the input_variables list.
2. Include {status} in the template string where you want the status to appear.

For example, if you want to modify the _DEFAULT_TEMPLATE to include "status", you can do it as follows:

_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. Unless the user specifies in his question a specific number of examples he wishes to obtain, always limit your query to at most {top_k} results. You can order the results by a relevant column to return the most interesting examples in the database.

Never query for all the columns from a specific table, only ask for a the few relevant columns given the question.

Pay attention to use only the column names that you can see in the schema description. Be careful to not query for columns that do not exist. Also, pay attention to which column is in which table.

Use the following format:

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

Status: {status}
"""

PROMPT = PromptTemplate(
    input_variables=["input", "table_info", "dialect", "top_k", "status"],
    template=_DEFAULT_TEMPLATE + PROMPT_SUFFIX,
)

This ensures that the "status" variable is included in the prompt and avoids the KeyError ^[1].

To continue talking to Dosu, mention @dosu.

[maximeperrindev]

Hi @quangnguyen1135,

You should use double brackets if it's not identified as a prompt variable. {"status": "Delivered"} become {{"status": "Delivered"}}

# Define SQL chain
def get_sql_chain(db):
    template = """
    You are a data analyst at a company. You are interacting with a user who is asking you questions about the company's database.
   
    <SCHEMA>{schema}</SCHEMA>
    Conversation History: {chat_history}

   
    For example:
    Question: trạng thái của đơn hàng
    SQL Query: 
    ""
    SELECT 
        ti.tracking_number, 
        c.name AS carrier_name, 
        JSON_UNQUOTE(JSON_EXTRACT(ti.shipments, '$[*].time')) AS shipment_times,
        JSON_UNQUOTE(JSON_EXTRACT(ti.shipments, '$[*].status')) AS shipment_statuses,
        JSON_UNQUOTE(JSON_EXTRACT(ti.shipments, '$[*].description')) AS shipment_descriptions,
        JSON_UNQUOTE(JSON_EXTRACT(ti.shipments, '$[*].location')) AS shipment_locations,
        ti.created_at, 
        ti.updated_at
    FROM 
        tracking_info ti
    JOIN 
        carriers c ON ti.carrier_id = c.id
    WHERE 
        ti.tracking_number NOT IN (
        SELECT tracking_number 
        FROM tracking_info 
        WHERE JSON_UNQUOTE(JSON_EXTRACT(shipments, '$[*].status')) = 'Delivered'
    )
    ORDER BY 
        ti.updated_at DESC;
    ""

    Kiểm tra các đơn hàng còn chưa Delivered:
    '''
    SELECT count(*)
    FROM tracking_info
    WHERE NOT JSON_CONTAINS(shipments, '{{"status": "Delivered"}}');
    '''
    
    Your turn:
    
    Question: {question}
    SQL Query:
    """
    prompt = ChatPromptTemplate.from_template(template)
    llm = ChatGoogleGenerativeAI(model="gemini-1.0-pro", temperature=0.3)
    
    def get_schema(_):
        return db.get_table_info()
    
    return (
        RunnablePassthrough.assign(schema=get_schema)
        | prompt
        | llm
        | StrOutputParser()
    )

# Define response chain
def get_response(user_query: str, chat_history: list):
    db = init_database()
    sql_chain = get_sql_chain(db)
    
    template = """
    You are a data analyst at a company. You are interacting with a user who is asking you questions about the company's database.
    Based on the table schema below, question, sql query, and sql response, write a natural language response in the specified format if the question is related to the database.

    <SCHEMA>{schema}</SCHEMA>

    Conversation History: {chat_history}
    SQL Query: <SQL>{query}</SQL>
    User question: {question}
    SQL Response: {response}
    Your turn:
    """
    
    prompt = ChatPromptTemplate.from_template(template)
    llm = ChatGoogleGenerativeAI(model="gemini-1.0-pro", temperature=0.3)

    chain = (
        RunnablePassthrough.assign(query=sql_chain).assign(
            schema=lambda _: db.get_table_info(),
            response=lambda vars: db.run(vars["query"]) if "query" in vars else None,
        )
        | prompt
        | llm
        | StrOutputParser()
    )
'''