Build Generative AI Chatbot

openai==0.27.0
langchain==0.0.152
sqlalchemy==1.4.41
pandas==1.4.3
gradio==2.7.5
python-dotenv==0.20.0

pip install -r requirements.txt

OPENAI_API_KEY=OPENAI-TOKEN-HERE
OPENAI_API_BASE=https://api.openai.com/v1

import os
import gradio as gr
from langchain_community.chat_models import ChatOpenAI
from langchain_community.agent_toolkits.sql.base import create_sql_agent
from langchain_community.utilities import SQLDatabase
from langchain_community.tools import Tool
from langchain.memory import ConversationBufferMemory
from dotenv import load_dotenv
from sqlalchemy import create_engine, inspect, text
from loader import create_demand_table

# Load environment variables
load_dotenv()

# Configure the language model with OpenAI directly
llm = ChatOpenAI(
    api_key=os.getenv("OPENAI_API_KEY"),
    model_name="gpt-3.5-turbo-0125",
    temperature=0.1,
)

# Initialize the database engine
engine = create_engine('sqlite:///db.sqlite3', echo=True)

# Function to process the uploaded file and create a table
def process_file(file):
    db = create_demand_table(engine, 'dynamic_table', file.name)
    return db

# Define a function to execute SQL queries
def run_sql_query(query):
    with engine.connect() as connection:
        result = connection.execute(text(query))
        return [dict(row) for row in result]

# Create a tool that executes SQL queries
sql_tool = Tool(
    name="SQLDatabaseTool",
    func=run_sql_query,
    description="Tool for executing SQL queries on the database."
)

# Configure the conversational agent's memory
memory = ConversationBufferMemory(memory_key="chat_history", return_messages=True)

# Function to handle user queries and process them with the SQL tool
def query_fn(input_text, file):
    db = process_file(file)
    conversational_agent = create_sql_agent(
        llm=llm,
        db=db,
        tools=[sql_tool],
        memory=memory,
        verbose=True,
        dialect='ansi',
        early_stopping_method="generate",
        handle_parsing_errors=True,
    )
    response = conversational_agent.run(input=input_text)

    # Check if the response is a list and contains valid data
    if isinstance(response, list) and len(response) > 0:
        # Convert each row of the result into a string
        result_text = ', '.join([str(row) for row in response])
    elif isinstance(response, str):
        # If the response is a string, use it directly
        result_text = response
    else:
        result_text = "No data found for the query."

    return {"result": result_text}

# Set up the user interface with Gradio
iface = gr.Interface(
    fn=query_fn,
    inputs=[gr.Textbox(label="Enter your query"), gr.File(label="Upload Excel file")],
    outputs=gr.JSON(label="Query Result"),
    title="Domain-specific chatbot"
)

# Launch the application
iface.launch(share=False, server_port=8080)

import pandas as pd
from sqlalchemy import create_engine, Table, Column, Integer, String, Date, MetaData
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from langchain_community.utilities import SQLDatabase

def create_demand_table(engine, table_name, excel_file):
    # Read the Excel file
    df = pd.read_excel(excel_file)

    # Create a base class for the table model
    Base = declarative_base()
    metadata = MetaData()

    # Map pandas data types to SQLAlchemy data types
    dtype_mapping = {
        'object': String,
        'int64': Integer,
        'float64': Integer,
        'datetime64[ns]': Date,
    }

    # Dynamically define the table structure
    columns = []
    if 'ID' not in df.columns:
        columns.append(Column('id', Integer, primary_key=True, autoincrement=True))

    for col_name, dtype in df.dtypes.items():
        col_type = dtype_mapping.get(str(dtype), String)
        columns.append(Column(col_name, col_type))

    # Dynamically create the table
    demand_table = Table(table_name, metadata, *columns)

    # Drop the existing table in the SQLite database, if it exists
    metadata.drop_all(engine, [demand_table])

    # Create the table in the SQLite database
    metadata.create_all(engine)

    # Create a session to interact with the database
    Session = sessionmaker(bind=engine)

    with Session() as session:
        # Insert data into the table
        df.to_sql(table_name, con=engine, if_exists='append', index=False)

    db = SQLDatabase(engine)
    return db

python app.py