I am building a chatbot application that connects to an SQL server, grabs custom data tables, turns them into pandas dataframes, and then uses them with a Langchain agent. Upon the page loading/sign in, the user’s email is used to determine the access level of sensitive information that is pulled from different SQL tables in the same database. These tables are stored in dataframes and passed to a pandas Langchain agent along with my llm. Whenever the user asks a question, it invokes the agent who will then respond. On my backend, I also check for if the user wants some form of visualization and create it using plotly, the dataframes, and custom code that I get the agent to create.

Locally, this works really well when I am testing it for one user and their email. However, I am not sure how to scale it for when numerous users are using the website at the same time. I am struggling to figure out how to ensure the dataframes, memory for the agent/llm and the agent itself can be intitialized for each individual and used in numerous requests.

Currently, this is my implementation for the start of the web app:

from flask import Flask, g, sessions, request, jsonify, render_template, session
from flask_session import Session
from sqlalchemy import create_engine, text
import pandas as pd
import os
import urllib.parse
from langchain_experimental.agents.agent_toolkits import create_pandas_dataframe_agent
from langchain_openai import AzureChatOpenAI
from langchain import prompts
from langchain.agents.agent_types import AgentType
from langchain.memory import ConversationBufferWindowMemory
import seaborn as sns
import plotly.express as px
import re
import plotly.io as pio
import logging
from sqlalchemy.engine import URL
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__, template_folder='templates')
secret_key = os.environ['FLASK_SECRET_KEY']
app.secret_key = secret_key

user = os.environ["USER_NAME"]
password = os.environ["PASSWORD"]
hostName = os.environ["HOSTNAME"]
port = os.environ["SQLPORT"]
db = os.environ["DATABASE"]
db_context = os.environ["CONTEXT"]

params = urllib.parse.quote_plus(
    "Driver={ODBC Driver 17 for SQL Server};"
    f"Server=tcp:{hostName},1433;"
    f"Database={db};"
    f"Uid={user};"
    f"Pwd={password};"
    "Encrypt=yes;"
    "TrustServerCertificate=yes;" #yes if running locally, no for production
    "Connection Timeout=240;"
)

connection_string = f"mssql+pyodbc:///?odbc_connect={params}"
app.config['SQLALCHEMY_DATABASE_URI'] = connection_string
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)

@app.route('/')
def home():
    global dataframes, memory, chat_prompt, context, userName, userImage, agent
    chat_prompt = chat_prompt_init()
    email = request.args.get('email')

    logging.info('email got')
    if not email:
        return render_template('no_email.html')

    dataframes = load_data_frames1(email)

    context = dataframes.get("context")
    contractDetails = dataframes.get("contract_details")
    projectDetails = dataframes.get("project_details")
    storeDetails = dataframes.get("store_details")
    userName = dataframes.get("name")
    userImage = dataframes.get("profile_pic")
    memory = ConversationBufferWindowMemory()

    client = AzureChatOpenAI(
        api_key=os.environ["AZURE_OPENAI_KEY"],  
        api_version="2023-12-01-preview",
        azure_endpoint=os.environ["AZURE_OPENAI_ENDPOINT"],
        deployment_name=os.environ["DEPLOYMENT_NAME"],
        temperature=0
    )

    agent = create_pandas_dataframe_agent(llm=client, df=[storeDetails, contractDetails, projectDetails], verbose=True, agent_type=AgentType.OPENAI_FUNCTIONS,  return_intermediate_steps=True)

    return render_template('Index.html')   

Here is my route for when the user presses the send button or clicks enter on their query/question:

@app.route('/process_query', methods=['POST'])
def process_query():
    if not agent:
        return jsonify({"error": "Agent not initialized"}), 500
    
    # Extract query from the POST request
    data = request.json
    query = data.get('query')
    
    if not query:
        return jsonify({"error": "No query provided"}), 400
    
    try:
        response = agent.invoke(chat_prompt.format_prompt(query=query, chat_history=memory.buffer_as_messages, context=context).to_messages())
        if response["intermediate_steps"]:
                queries = extract_queries(response['intermediate_steps'])
        else:
            queries = None
        
        pattern = r'```pythons(.*?)```'
        code_snippets = re.findall(pattern, response["output"], re.DOTALL)
        
        if code_snippets:
                cleaned_snippets = [snippet.replace("pythonn", "", 1) for snippet in code_snippets]
        else:
                cleaned_snippets = None
            
        graph = extract_graph_code(output_code=cleaned_snippets, queries=queries)
        
        clean_response = remove_code(response["output"])
        
        memory.chat_memory.add_user_message(query)
        memory.chat_memory.add_ai_message(clean_response)
        
        return jsonify({"response": clean_response,
                        "graph": graph})
    except Exception as e:
        return jsonify({"error": str(e)}), 500

Typically, if the tables were a simple read and the agent had no memory, then it would be very easy to code those objects into the process_query() function. However, I need to make sure the agent is not initialized each time the request to process a query is ran because I need persistent memory for each user. Furthermore, I can’t have the dataframes made with each request to process_query() because in order to grab the table and make the dataframes, it takes on average 27 seconds (I have not found a way to simplify the SQL queries to grab and make the custom tables based on user access level).

I need to find a way to have all my objects not as global variables (dataframes, agent, memory). I tried to make a dataframe a Json object and pass them into the session cookie, but just one dataframe was already too large. I have been trying to look online to find different ways I can do this but can’t seem to the find a good answer. I have seen people use Redis for in memory storage, but I do not have that and was hoping to find an alternative (if there isn’t one, please let me know). I am planning to host this application as an Azure Web App; not sure if that helps find a way around my issue.

I have considered creating a unique, randomly generated ID for each user and storing the agent, memory and data frames in global dictionaries with the ID as the key to keep them secure. Upon logout, I would delete the key value pairs to ensure the dictionaries don’t get too large, preventing issues with memory and space. I highly doubt this is an optimal choice in a production environment, but it is all I can think of currently. Please let me know what else I can do or try!