Get intermediate_steps with SQLDatabase Agent

[pnd-rdestratis]

When using the SQL Chain I can return the intermediate steps so that I can output the Query. For the SQL Agent this seems not to be an option without modifying the tool itself. How can I see the actual queries used (not only in the verbose, but save it in a variable e.g.)?

Alternatively is there a way to save the verbose output in a dict?

[cachatj]

Also curious about this?

[DishenWang2023]

Instead of using create_sql_agent, you can initialize the sql agent with initialize_agent and use the argument return_intermediate_steps=true to get the intermediate steps.

[cachatj]

So, I'm attempting to do this so I can dry_run BQ queries before doing full send.

I was able to get just the SQL query used by:

sqlchain = SQLDatabaseSequentialChain.from_llm(llm, db, verbose=True, return_intermediate_steps=True, callbacks=None, tags=None)

results = sqlchain("How many distinct tag values do we have for deviceid actors?")

print(results["intermediate_steps"][1])

-- You can display it with or without the print() in a jNB.

essentially, results["intermediate_steps"[1] is your SQL statement.

Also, I believe it still works like that with create_sql_agent as long as return_intermediate_steps=True is T.

[pnd-rdestratis]

Hmm I don't want to use a chain but the agent. I would like to display the thoughts, observations, queries and results separately in a web app. Initializing the agent did not work.

[vinayemmadi]

I working on a similar use case @pnd-rdestratis. Unable to get sql as a result

[AlejandroGil]

@pnd-rdestratis Did you get any progress on this? I'm facing the same issue

[nikitacorp]

@pnd-rdestratis @AlejandroGil @vinayemmadi I also would like to know, have you found any solution to your problem?

[ankitvays6657]

same issue, did anyone find a workaround?

[andrewhinh]

Using create_sql_agent(), specify agent_executor_kwargs like so: agent_executor_kwargs={"return_intermediate_steps": True}.

This works because in this file, it's apparent that AgentExecutor.from_agent_and_tools() doesn't use kwargs, but rather agent_executor_kwargs.

Then, make sure you don't use .run() when calling the agent, as it currently doesn't support intermediate steps. Use something else like __call__().

[GB-x2]

One quick fix can be prompt the LLM to attach SQL query to the response and apply some post processing

[jaxphuchanlmht1999]

Using create_sql_agent(), specify agent_executor_kwargs like so: agent_executor_kwargs={"return_intermediate_steps": True}.

This works because in this file, it's apparent that AgentExecutor.from_agent_and_tools() doesn't use kwargs, but rather agent_executor_kwargs.

Then, make sure you don't use .run() when calling the agent, as it currently doesn't support intermediate steps. Use something else like __call__().

I ran successfully, thank you so much.

[atulpareek31]

How can I stop execution of agent.call() right after query is generated?

[atulpareek31]

Using create_sql_agent(), specify agent_executor_kwargs like so: agent_executor_kwargs={"return_intermediate_steps": True}.

This works because in this file, it's apparent that AgentExecutor.from_agent_and_tools() doesn't use kwargs, but rather agent_executor_kwargs.

Then, make sure you don't use .run() when calling the agent, as it currently doesn't support intermediate steps. Use something else like __call__().

hi How can I stop execution of agent.call() right after query is generated? thanks

[madhubandru]

Using create_sql_agent(), specify agent_executor_kwargs like so: agent_executor_kwargs={"return_intermediate_steps": True}.

This works because in this file, it's apparent that AgentExecutor.from_agent_and_tools() doesn't use kwargs, but rather agent_executor_kwargs.

Then, make sure you don't use .run() when calling the agent, as it currently doesn't support intermediate steps. Use something else like __call__().

This helped, Thank you :)

[atulpareek31]

Using create_sql_agent(), specify agent_executor_kwargs like so: agent_executor_kwargs={"return_intermediate_steps": True}. This works because in this file, it's apparent that AgentExecutor.from_agent_and_tools() doesn't use kwargs, but rather agent_executor_kwargs. Then, make sure you don't use .run() when calling the agent, as it currently doesn't support intermediate steps. Use something else like __call__().

This helped, Thank you :)

Hi Madhu - Thanks for the reply. I am already setting up agent_executor_kwargs={"return_intermediate_steps": True} and getting result along with query after calling call() method. But what I am looking for is to stop execution after final query is built. Currently, langchain returns query result as well. As a workaround, I can extract only query from the returned dictionary.

Also, how can I provide additional metadata information about table using toolkit along with few-shots examples.

Thanks Atul

[mfernandezsidn]

Using create_sql_agent(), specify agent_executor_kwargs like so: agent_executor_kwargs={"return_intermediate_steps": True}.

This works because in this file, it's apparent that AgentExecutor.from_agent_and_tools() doesn't use kwargs, but rather agent_executor_kwargs.

Then, make sure you don't use .run() when calling the agent, as it currently doesn't support intermediate steps. Use something else like __call__().

I don't understand what I should do.

I have my agent:

agent_executor = create_sql_agent( llm=vertex_llm, db=db, verbose=True, top_k=1000, handle_parsing_errors=True, )

Then, I am executing call instead of the invoke function as I was doing.

test = agent_executor.__call__(prompt_4)

How do I recover the SQL query now?

[prashant268]

Using create_sql_agent(), specify agent_executor_kwargs like so: agent_executor_kwargs={"return_intermediate_steps": True}. This works because in this file, it's apparent that AgentExecutor.from_agent_and_tools() doesn't use kwargs, but rather agent_executor_kwargs. Then, make sure you don't use .run() when calling the agent, as it currently doesn't support intermediate steps. Use something else like __call__().

I don't understand what I should do.

I have my agent:

agent_executor = create_sql_agent( llm=vertex_llm, db=db, verbose=True, top_k=1000, handle_parsing_errors=True, )

Then, I am executing call instead of the invoke function as I was doing.

test = agent_executor.__call__(prompt_4)

How do I recover the SQL query now? agent_executor = create_sql_agent( llm=vertex_llm, agent_executor_kwargs={"return_intermediate_steps": True}. db=db, verbose=True, top_k=1000, handle_parsing_errors=True, ), it will give all steps and generated query you need to do post processing