create_sql_agent and the memory

[RMDG2001]

I have been trying to add memory to the SQL agent and have ran to various problems, for what I could see in different discussion #6918 , #7546 and others have to a conclusion there is no exact way, at the moment, to have an agent that responds, querys from a database, and keeps the history and remembers the context to answer the next question and keep querying. At least in my case, and with the use of the SQLDatabaseToolkit just seemed to complicate things.

[dosubot[bot]]

🤖

Hello,

To add memory to the SQL agent in LangChain, you can use the save_context method of the ConversationBufferMemory class. This method allows you to save the context of a conversation, which can be used to respond to queries, retain history, and remember context for subsequent queries.

Here is how you can use this method:

# Create an instance of ConversationBufferMemory
memory = ConversationBufferMemory(memory_key="foo")

# Save context
memory.save_context({"input": "bar"}, {"output": "foo"})

The save_context method takes two dictionaries representing the input and output of a conversation. It saves these in the memory buffer, which can be accessed later to retrieve the context of the conversation.

In the context shared, MongoDB is used as a message store for the ConversationBufferMemory class. This allows you to save and retrieve messages from a MongoDB database. Here is how you can use MongoDB as a message store:

# setup MongoDB as a message store
message_history = MongoDBChatMessageHistory(
    connection_string=connection_string, session_id="test-session"
)
memory = ConversationBufferMemory(
    memory_key="baz", chat_memory=message_history, return_messages=True
)

# add some messages
memory.chat_memory.add_ai_message("This is me, the AI")
memory.chat_memory.add_user_message("This is me, the human")

Please note that the SQLDatabaseToolkit is not mentioned in the provided context, so it's unclear how it interacts with the ConversationBufferMemory class. If you need to integrate the SQLDatabaseToolkit with the memory management in LangChain, you might need to extend or modify the ConversationBufferMemory class or create a new class that uses both ConversationBufferMemory and SQLDatabaseToolkit.

For more information, you can refer to the following sources:

• libs/experimental/langchain_experimental/generative_agents/memory.py
• libs/langchain/tests/unit_tests/chains/test_conversation.py
• libs/langchain/tests/integration_tests/memory/test_mongodb.py

I hope this helps! If you have any other questions or need further clarification, feel free to ask.

Sources

• libs/experimental/langchain_experimental/generative_agents/memory.py
• libs/langchain/tests/unit_tests/chains/test_conversation.py
• libs/langchain/tests/integration_tests/memory/test_mongodb.py
• libs/langchain/langchain/memory/buffer.py
• libs/langchain/langchain/utilities/sql_database.py
• libs/langchain/tests/unit_tests/agents/test_sql.py

---

This response is meant to be useful and save you time. It is not meant to be a precise solution, but rather a starting point for your own research.

Help me be more useful! Please leave a 👍 if this is helpful and 👎 if it is irrelevant.

If you want to continue the conversation, start your reply with @dosu-bot.

[RMDG2001]

def initialize_bot():

os.environ["OPENAI_API_KEY"] = OPENAI_API_KEY
database_connection()
db = SQLDatabase.from_uri(uri)
llm_instance = OpenAI(temperature=0, model="davinci")
SQL_SUFFIX = """Begin!

history: {history}
question: {query}
Thought: I should look at the tables in the database to see what I can query. Then I should query the schema of the most relevant tables, and please return the output as a string.
{agent_scratchpad} """

agent_executor = create_sql_agent(   
    llm_instance,
    SQLDatabaseToolkit(db=db, llm=llm_instance),
    agent_executor_kwargs={
        "memory": ConversationBufferMemory(
            input_key="query", memory_key="history", return_messages= True

        )
    },
    
    suffix=SQL_SUFFIX,
    input_variables=["query", "history", "agent_scratchpad"],
    verbose= True
)   

return agent_executor

One of the different functions I've tried this one utilizing the agent_executor_kwargs, but my problem here is and OutputParserException: could not parse LLM Output:
I have various functions that I've tried using, the ones without memory work correctly, but as soon as I add memory in any way, it breaks

[aijazshekh0117]

Does anyone have right code snippet that is working with sqldatabasechain ? please provide the right input.

[simonrmonk]

Any update on this?

[wasifmasood]

Probably a bit too late but here is the right code:

memory = ConversationBufferMemory(
                                input_key="input", 
                                output_key='output',
                                memory_key="chat_history",
                                return_messages= True
                                  )
                                  

agent_executor = create_sql_agent(
    llm=llm,
    db=db,
    prompt=full_prompt,
    verbose=True,
    handle_parsing_errors=True,
    agent_type="openai-tools",
    # max_execution_time=10000,
    # max_iterations=500,
    agent_executor_kwargs={
        "memory": memory,
        "return_intermediate_steps": True
    }
)

I see that the chat history is maintained when I run agent_executor.invoke(input).

[Asim-Shah-2004]

please help me here i am not getting the correct output my code dosent remember the previous context

here is my code

import os
from langchain_community.utilities.sql_database import SQLDatabase
from langchain_community.agent_toolkits import create_sql_agent
from langchain_openai import ChatOpenAI
from langchain_community.agent_toolkits import SQLDatabaseToolkit
from langchain_core.messages import AIMessage, SystemMessage
from langchain.memory import ConversationBufferMemory
from langchain_core.prompts.chat import (
ChatPromptTemplate,
HumanMessagePromptTemplate,
MessagesPlaceholder,
)

def main():

   db_name = input("Enter the database name: ")
print("Connecting to database...")
try:
    db = SQLDatabase.from_uri(
        f"mysql+pymysql://{db_user}:{db_password}@{db_host}/{db_name}"
    )
    print("Connection established successfully.")
except Exception as e:
    print("An error occurred:", e)

dialect = input("Enter the dialect you want the database to be in: ")

schema_query = f"""SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_KEY
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = '{db_name}'  
ORDER BY TABLE_NAME, ORDINAL_POSITION;"""

print("Fetching database schema...")

try:
    schema = db._execute(schema_query)
    print("Schema fetched successfully.")
except Exception as e:
    print("An error occurred:", e)


relationship_query = """SELECT 

TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME,
REFERENCED_TABLE_SCHEMA,
REFERENCED_TABLE_NAME,
REFERENCED_COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
TABLE_SCHEMA = SCHEMA()
AND REFERENCED_TABLE_NAME IS NOT NULL;"""

print("Fetching relationships")

try:
    relationships = db._execute(relationship_query)
    print("relationships fetched successfully.")
except Exception as e:
    print("An error occurred:", e)

print("Generating system prompt...")

system_prefix = f"""You are an agent designed to interact with a SQL database.

Given an input question, create a syntactically correct {dialect} query.
Only generate queries, don't execute them. The schema of the database is

{schema}

The relationships in the database are

{relationships}

"""

system_prompt_chunks = [
    system_prefix[i : i + 4096] for i in range(0, len(system_prefix), 4096)
]


system_messages = []
for chunk in system_prompt_chunks:
    system_messages.append(SystemMessage(content=chunk))


full_prompt = ChatPromptTemplate.from_messages(
    system_messages
    + [
        HumanMessagePromptTemplate.from_template("{input}"),
        MessagesPlaceholder("agent_scratchpad"),
    ]
)

llm = ChatOpenAI(model="gpt-3.5-turbo", temperature=0)
memory = ConversationBufferMemory(input_key="input",output_key="output",memory_key="chat_history",return_messages=True)
agent_executor = create_sql_agent(
    llm=llm,
    db=db,
    prompt=full_prompt,
    verbose=True,
    agent_type="openai-tools",
    agent_executor_kwargs={
        "memory":memory,
        "return_intermediate_steps":True
    }
)

while True:
    query = input("Enter your query: ")
    response = agent_executor.invoke({"input": query})
    memory.save_context(input_str=query, output_str=response)
    choice = input("Do you want to quit? (y/n): ")
    if choice == "y":
        break

if name == "main":
main()

[AIOnGraph]

Probably a bit too late but here is the right code:

memory = ConversationBufferMemory(
                                input_key="input", 
                                output_key='output',
                                memory_key="chat_history",
                                return_messages= True
                                  )
                                  

agent_executor = create_sql_agent(
    llm=llm,
    db=db,
    prompt=full_prompt,
    verbose=True,
    handle_parsing_errors=True,
    agent_type="openai-tools",
    # max_execution_time=10000,
    # max_iterations=500,
    agent_executor_kwargs={
        "memory": memory,
        "return_intermediate_steps": True
    }
)

I see that the chat history is maintained when I run agent_executor.invoke(input).

In verbose I am getting chathistory ,but i am not getting the correct output my code dosent remember the previous context.

did you check with this condition?

[m0han22]

@wasifmasood , I have implemented the similar code. It is maintaining the chat history but not retrieving it when the user asks any question. Is there any other alternative? I have tested with RunnableWithMessageHistory. With that, I am getting an error "Agent stopped due to max iterations.". 

[ENUMERA8OR]

Try using a different model, sometimes the open source models don't work well. Your best bet are closed source frontier models. Try Openai or Claude. They are in my opinion some of the best option out there.