Is it possible to use create_sql_agent with ollama models?

[cboettig]

The docs describe how to create an SQL agent using OpenAI as an example but implying that the approach is generic.

I am able to use create_sql_query_chain just fine against either an OpenAI LLM or an Ollama LLM (examples below). However, when I try to use create_sql_agent on the Ollama LLM, the agent throws an error on any agent.run() call, while the identical setup works fine with the OpenAI agent.

I have seen the ollama-sql template, but this seems to create a 'chain' (a la create_sql_query_chain?) and not an agent? I don't quite see how to use it. I'd like to use create_sql_agent() with a ollama agent -- the documentation seems to imply this should be possible.

Setup:

## My database connection
from sqlalchemy import create_engine
from langchain.sql_database import SQLDatabase
db_uri = "duckdb:///database.duckdb"
engine = create_engine(db_uri)
db = SQLDatabase(engine, view_support=True)

create_sql_query_chain works just fine:

from langchain_community.llms import Ollama
llm = Ollama(model="duckdb-nsql", temperature=0)


## A SQL Chain
from langchain.chains import create_sql_query_chain
chain = create_sql_query_chain(llm, db)

# we can do stuff with the chain
chain.get_prompts()[0].pretty_print()
response = chain.invoke({"question": "How many total rows are in the fee table?"})
response

But creating an agent does not:

# An agent -- does not translate to non OpenAI??
from langchain_community.agent_toolkits import create_sql_agent
agent = create_sql_agent(llm, db=db, verbose=True)
agent.run("How many total rows are in the fee table?")

ERROR:

ValueError: An output parsing error occurred. In order to pass this error back to the agent and have it try again, pass `handle_parsing_errors=True` to the AgentExecutor. This is the error: Could not parse LLM output: ` SELECT COUNT(*) FROM fee;`

The same code executes fine with an ChatOpenAI LLM

## ChatGPT Connection
import os
import streamlit as st
from langchain_openai import ChatOpenAI
os.environ["OPENAI_API_KEY"] = st.secrets["OPENAI_API_KEY"]
chatgpt = ChatOpenAI(model="gpt-3.5-turbo", temperature=0)

# An agent can execute queries
from langchain_community.agent_toolkits import create_sql_agent
chatgpt_agent = create_sql_agent(chatgpt, db=db, agent_type="openai-tools", verbose=True)
chatgpt_agent.run("How many total rows are in the fee table?")

[agenthunt007]

were you able to make it work? I am running into similar issue

[maximeperrindev]

Hi,

create_sql_agent uses tools under the hood to communicate with your database (https://python.langchain.com/v0.1/docs/use_cases/sql/agents/). That means the LLM model should support tool calling (e.g being fine tuned to use tools and correct format).
Ollama models do not support tool calling natively.

You can check this answer : #21907 (comment)

[IP050]

Since some models available within ollama now allow toolcalling you can indeed use an ollama model such as llama3-groq-tool-use, either with create_sql_agent or as post above stated using the tools that it uses under the hood directly.

If you want to use create_sql_agent, you must specify the toolkit over db, it uses get_tools() itself so no need for that:

from langchain_core.messages import HumanMessage
from langchain_ollama import ChatOllama
from langchain_community.agent_toolkits import SQLDatabaseToolkit
from langchain_community.agent_toolkits import create_sql_agent

llm = ChatOllama(model="llama3-groq-tool-use:latest", base_url="{url}") 
toolkit = SQLDatabaseToolkit(db=db, llm=llm) 
agent_executor = create_sql_agent(llm, toolkit=toolkit, agent_type="tool-calling")
agent_executor.invoke([HumanMessage(content="A question about the database?")])

Should help you on the way.

I do highly recommend looking under the hood and using either the langgraph.prebuilt create_react_agent or stepping away from usage of prebuilt/community components for a while and diving deeper in it's core to gain more understanding of both langchain/langgraph, it can save you lots of time and makes you more flexible versus changes within langchain, langgraph, models or stuff like streamlit etc.

[GameModes]

I have been doing research on this topic for some time now. Open-source local models seems to be very sensitive to their prompts. To try to avoid hallucinating, you must mention (depending on the model) about 3 times within the prompt, it can only use tables that were mentioned within the toolkit/given database. Only then you have a small chance it won't hallucinate.

However, I do believe there exist a certain prompt/parameter setup that will work perfectly for every Ollama model. But it seems the internet has not find it yet. Will keep you updated if I happen to find this "perfect" prompt. But looking at the pay-to-use models, the LLM should support tool-calling, which really helps out the accuracy

[IP050]

This is from a long time ago but what @GameModes states is correct, except the execution of it or finding the perfect prompt/parameters, in my opinion.

Finding the perfect flow is what matters, even with openai models. Toolcalling allows for dividing of tasks and thus should prevent repeating prompts.

@GameModes To put you in the right direction: try adding a 'judge' or 'check' agent that judges/checks the inputs and outputs of the agent(s) and try if you can get it correct within 3-5 iterations. This should give you an idea of how you can put a certain agent on the sideline/checking whatever it is you are trying to do. Inputs can also be given by a certain agent/tool or you can make the toolcalling agent call a tool that contains the prompt which would also prevent giving it as its input multiple times. Furthermore, for persistence the seed and temperature are important.

Hope this helps.

Edit: This also lowers the difference between using different models, paid, open source, etc. Doesn't matter. If you build a correct flow, toolkit, etc. Your application can and will become multi-modal without too many model specific tweaks.

[IP050]

To give a concrete example how I would build a tool-calling agent and a toolkit, that outperforms create_sql_agent in my opinion, please try something like this:

from langchain.agents import AgentExecutor, create_tool_calling_agent, tool

@tool
def fetch_and_summarize_news_tool(input) -> str:
    """
    Tool definition is important and can save you prompt context.
    Provide a search term (query) to search on news.com.
    Returns a summarized text of up to 10 articles. 
    """
    return summarize(input)

tools = [fetch_and_summarize_news_tool, analyze_data_tool, visualize_data_tool, get_detailed_article_info_tool]

manager_agent = create_tool_calling_agent(llm, tools, prompt)
agent_executor = AgentExecutor(agent=manager_agent, tools=tools, verbose=True, memory=memory)
runnable_with_history = RunnableWithMessageHistory(
    manager_agent,
    your_history_function(),
        input_messages_key="",
    history_messages_key=""
)

[GameModes]

So you suggest 1 sql agent and one (judge) tool-calling agent? Or 2 tool calling agent? If the latter, then how would I be able to connect the Database with the Language model? Using the sqldatabasetoolkit?

[IP050]

@GameModes

Not exactly. In the above example there is one tool-calling agent, maybe if I go a bit more in depth on the above example it will clear some things up:

from langchain.agents import AgentExecutor, create_tool_calling_agent
from langchain_openai import ChatOpenAI
from utils import fetch_articles # imagine (perhaps even with an llm) a scraper that scrapes articles their short-summaries and urls

model_one = "gpt-3.5-turbo-1106"
model_two= "gpt-4o-mini-2024-07-18"

summarize_llm = ChatOpenAI(temperature=0, model_name=model_one, openai_api_key=OPENAI_AI_KEY)
tool_calling_llm = ChatOpenAI(openai_api_key=OPENAI_AI_KEY, model=model_two, streaming=True)
data_analyze_agent = AnotherLlm(vars, seperate_prompt) 
excel_exporting_agent = AndAnotherOne(vars, seperate_prompt)

from langchain.chains.summarize import load_summarize_chain
from langchain_community.document_loaders import WebBaseLoader
from langchain_core.prompts import PromptTemplate

def summarize_example(url, llm, PROMPT):
    loader = WebBaseLoader(web_paths=url)
    docs = loader.load()
    prompt_template = PROMPT
    prompt = PromptTemplate.from_template(prompt_template)
    chain = load_summarize_chain(llm, chain_type="stuff", prompt=prompt) # I suggest making chain_type variable as well. 
    result = chain.invoke(docs)
    return result["output_text"]

from langchain.agents import tool 

SUMMARIZE_PROMPT = """You are an expert on topicOne, TopicTwo and en Topicthree .You have access to the following summarized news items:
{text}
Take them and describe them in a way that you would discuss with a colleague.
"""

@tool
def fetch_and_summarize_news_tool(query: str) -> str:
    """
    Provide a search term (query) to search on example_news_website.com
    Returns a summarized  text of up to 10 articles.
    """
    news_items = fetch_articles(query, limit=10)
        
    if not news_items:
        return f"No articles found for  '{query}'."
    try:
        summary = summarize_example(
            llm,
            url=example_news_website.com, 
            PROMPT=PROMPT
        )
        return summary
    except Exception as e:
        return f"Error at summary: {str(e)}"

@tool
def analyze_data_tool(input: str):
    """Use this tool to analyze data."""
    response = data_analyze_agent .invoke(input)
    return response

@tool
def visualize_data_tool(input: str):
    """Use this tool to generate visualizations."""
    response = data_analyze_agent .invoke(input)
    return (response)

@tool 
def export_to_excel_tool(input: pd.Dataframe)
    """
    Exports the given dataframe to Excel.
   """

   return excel_exporting_agent(input)


manager_prompt = """
You are a versatile assistant designed to manage a variety of tasks efficiently.
You have access to a range of specialized tools to assist you in providing accurate and detailed responses.
Here’s how you should operate:
   - For general questions, conversations, or instructions that don't require complex data analysis, respond directly using your knowledge and reasoning capabilities. 
   - If asked concrete questions about data regarding topic X, always use the data analysis and/or data visualisation tool. 
   - When asked about news always use the fetch_and_summarize_news_tool.
   - If asked for a excel always use the export_to_excel_tool. 
"""


tools = [analyze_data_tool, visualize_data_tool,export_to_excel_tool, fetch_and_summarize_news_tool]
manager_agent = create_tool_calling_agent(tool_calling_llm , tools, manager_prompt)
executing_agent= AgentExecutor(agent=manager_agent, tools=tools, verbose=True, memory=memory)

# the executing_agent is a runnable so now you can build you chat(loop) in whatever way you prefer, example; async for chunk in x.astream(input,config)

I hope this clears my method somewhat up to you. I would recommend building a simple application that has one tool-calling agent and a couple simple agents that you put in the tool-calling agents' toolkit. If you do this, you should be able to implement that same logic on a database, csv files, text, etc. Make sure to put the agentexecutor verbose param as true so you can see in the logs when it makes a toolcall and to which tool. After this, you can build and put a judge-agent on your flow, since then you are aware of what it should be judging ;)

Also: I suggest looking under the hood of create_sql_agent and building your own.

I'd also be happy to help by looking at your code but the above should help you a bit to understand why an llm starts to hallucinate. Please note that this is how I work with tool-calling-agents and I by no means state that this is best practice. Good luck.