Why does SQLDatabaseChain.invoke() return the SQL query instead of the final answer?

The behavior you’re describing typically indicates that the chain isn’t “completing” the full output structure as expected—that is, it’s only returning the generated SQL query (with the preceding “Question:” marker) rather than the final natural language answer. In other words, the LLM isn’t “post-processing” the SQL result into your final answer.

Below are a couple of things to check and try:

1. Output Format in the Prompt
   The SQLDatabaseChain expects that the LLM produces output in the format:

   Question: <your original question>
   SQLQuery: <generated SQL query>
   SQLResult: <result from DB>
   Answer: <natural language answer>
   

   If your prompt template does not clearly instruct the LLM to include the SQL result and generate the final “Answer:” section, the model may simply echo the query. Compare your prompt template with the one in the documentation and be sure that the “Answer:” part is explicitly included and that the model is instructed to use the SQL result to generate a natural language response.

2. Method Choice: .invoke() vs .run()
   In some examples the recommended method is to use the chain’s .run() method, which will automatically return the final answer (i.e. the value following “Answer:” in the generated output). If you’re using .invoke() without additional post‑processing, you might be seeing extra intermediate information (namely the full formatted prompt with “Question:” and “SQLQuery:” fields). Switching to:

   PYTHONCopied
   qns1 = db_chain.run("How many t-shirts do we have left for nike in extra small size and red color?")

   may directly return your desired final answer (e.g. “59”).

3. Return Direct Flag (Optional)
   There’s also a parameter return_direct=True that you can pass when initializing the chain. This bypasses any further LLM post‑processing of the SQL result. If you want the direct output of the SQL query to be used as the final answer, set that flag. Otherwise, you need to ensure the prompt does the final answer conversion:

   PYTHONCopied
   db_chain = SQLDatabaseChain.from_llm(llm, db, verbose=True, return_direct=True)

Summary:
It appears that your chain is correctly generating and executing the SQL query (returning the expected count of 59), but the prompt isn’t instructing the model to format the final answer correctly. Either adjust the prompt template so that the output includes the “Answer:” section based on the SQL result or use .run() (and/or set return_direct=True if that fits your use case) to get just the final answer. This should produce an output that contains simply “59” (or a natural language sentence with that count) in the final response.