02_langchain_sql_agents_OpenAI_GPT.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Langchain Agents\n",
    "\n",
    "A simple notebook that uses the Langchain agents to query the SQLite database \"sql_lite_database.db\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\u001b[1m> Entering new AgentExecutor chain...\u001b[0m\n",
      "\u001b[32;1m\u001b[1;3mAction: sql_db_list_tables\n",
      "Action Input: \u001b[0m\n",
      "Observation: \u001b[38;5;200m\u001b[1;3mAGENTS, CUSTOMER, ORDERS\u001b[0m\n",
      "Thought:\u001b[32;1m\u001b[1;3m I should query the CUSTOMER table to get the number of customers.\n",
      "Action: sql_db_schema\n",
      "Action Input: CUSTOMER\u001b[0m\n",
      "Observation: \u001b[33;1m\u001b[1;3m\n",
      "CREATE TABLE \"CUSTOMER\" (\n",
      "\t\"CUST_CODE\" TEXT(6) NOT NULL, \n",
      "\t\"CUST_NAME\" TEXT(40) NOT NULL, \n",
      "\t\"CUST_CITY\" CHAR(35), \n",
      "\t\"WORKING_AREA\" TEXT(35) NOT NULL, \n",
      "\t\"CUST_COUNTRY\" TEXT(20) NOT NULL, \n",
      "\t\"GRADE\" NUMERIC, \n",
      "\t\"OPENING_AMT\" NUMERIC(12, 2) NOT NULL, \n",
      "\t\"RECEIVE_AMT\" NUMERIC(12, 2) NOT NULL, \n",
      "\t\"PAYMENT_AMT\" NUMERIC(12, 2) NOT NULL, \n",
      "\t\"OUTSTANDING_AMT\" NUMERIC(12, 2) NOT NULL, \n",
      "\t\"PHONE_NO\" TEXT(17) NOT NULL, \n",
      "\t\"AGENT_CODE\" CHAR(6) NOT NULL, \n",
      "\tPRIMARY KEY (\"CUST_CODE\"), \n",
      "\tFOREIGN KEY(\"AGENT_CODE\") REFERENCES \"AGENTS\" (\"AGENT_CODE\")\n",
      ")\n",
      "\n",
      "/*\n",
      "3 rows from CUSTOMER table:\n",
      "CUST_CODE\tCUST_NAME\tCUST_CITY\tWORKING_AREA\tCUST_COUNTRY\tGRADE\tOPENING_AMT\tRECEIVE_AMT\tPAYMENT_AMT\tOUTSTANDING_AMT\tPHONE_NO\tAGENT_CODE\n",
      "C00013\tHolmes\tLondon\tLondon\tUK\t2.0000000000\t6000.00\t5000.00\t7000.00\t4000.00\tBBBBBBB\tA003\n",
      "C00001\tMicheal\tNew York\tNew York\tUSA\t2.0000000000\t3000.00\t5000.00\t2000.00\t6000.00\tCCCCCCC\tA008\n",
      "C00020\tAlbert\tNew York\tNew York\tUSA\t3.0000000000\t5000.00\t7000.00\t6000.00\t6000.00\tBBBBSBB\tA008\n",
      "*/\u001b[0m\n",
      "Thought:\u001b[32;1m\u001b[1;3m I should use the COUNT function to get the number of customers.\n",
      "Action: sql_db_query\n",
      "Action Input: SELECT COUNT(*) FROM CUSTOMER\u001b[0m\n",
      "Observation: \u001b[36;1m\u001b[1;3m[(25,)]\u001b[0m\n",
      "Thought:\u001b[32;1m\u001b[1;3m I now know the final answer\n",
      "Final Answer: 25\u001b[0m\n",
      "\n",
      "\u001b[1m> Finished chain.\u001b[0m\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "{'input': 'How many customers do we have in our database?', 'output': '25'}"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from langchain.utilities import SQLDatabase\n",
    "from langchain.llms import OpenAI\n",
    "from langchain.agents.agent_types import AgentType\n",
    "from langchain.agents.agent_toolkits import SQLDatabaseToolkit \n",
    "from langchain.utilities import SQLDatabase\n",
    "from langchain_openai import OpenAI\n",
    "from langchain.agents import create_sql_agent \n",
    "import os\n",
    "from dotenv import load_dotenv\n",
    "load_dotenv()\n",
    "\n",
    "# define the database we want to use for our test\n",
    "db = SQLDatabase.from_uri('sqlite:///sql_lite_database.db')\n",
    "\n",
    "# choose llm model, in this case the default OpenAI model\n",
    "llm = OpenAI(\n",
    "            temperature=0, \n",
    "            verbose=True,\n",
    "            openai_api_key=os.getenv(\"OPENAI_API_KEY\"),\n",
    "            )\n",
    "\n",
    "# setup agent\n",
    "toolkit = SQLDatabaseToolkit(db=db, llm=llm)\n",
    "agent_executor = create_sql_agent(\n",
    "    llm=llm,\n",
    "    toolkit=toolkit,\n",
    "    verbose=True,\n",
    "    agent_type=AgentType.ZERO_SHOT_REACT_DESCRIPTION,\n",
    ")\n",
    "\n",
    "# define the user's question\n",
    "question = \"How many customers do we have in our database?\"\n",
    "agent_executor.invoke(question)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'agent_executor' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[5], line 3\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[38;5;66;03m# define the user's question\u001b[39;00m\n\u001b[0;32m      2\u001b[0m question \u001b[38;5;241m=\u001b[39m \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mList all customers from the US\u001b[39m\u001b[38;5;124m\"\u001b[39m\n\u001b[1;32m----> 3\u001b[0m \u001b[43magent_executor\u001b[49m\u001b[38;5;241m.\u001b[39minvoke(question)\n",
      "\u001b[1;31mNameError\u001b[0m: name 'agent_executor' is not defined"
     ]
    }
   ],
   "source": [
    "# define the user's question\n",
    "question = \"List all customers from the US\"\n",
    "agent_executor.invoke(question)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'agent_executor' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[4], line 3\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[38;5;66;03m# define the user's question\u001b[39;00m\n\u001b[0;32m      2\u001b[0m question \u001b[38;5;241m=\u001b[39m \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mWhat is the phone number of the customer Albert?\u001b[39m\u001b[38;5;124m\"\u001b[39m\n\u001b[1;32m----> 3\u001b[0m \u001b[43magent_executor\u001b[49m\u001b[38;5;241m.\u001b[39minvoke(question)\n",
      "\u001b[1;31mNameError\u001b[0m: name 'agent_executor' is not defined"
     ]
    }
   ],
   "source": [
    "# define the user's question\n",
    "question = \"What is the phone number of the customer Albert?\"\n",
    "agent_executor.invoke(question)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".venv",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}