3-rag-structured/rag-structured-data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# RAG for structured data\n",
    "\n",
    "Enabling querying over the structured data is qualitatively different from unstructured data.\n",
    "\n",
    "The approach for structured data (like SQL databases) is often for the LLM to write and execute queries in a DSL, such as SQL.\n",
    "\n",
    "At high-level it can be described as: \n",
    "\n",
    "1. Convert question into SQL query\n",
    "2. Execute query over the database\n",
    "3. Answer the question using the retrieved data\n",
    "\n",
    "**Notice**: every RAG pipeline for structured data will have one retrieval and one generation LLM calls, which can make your pipeline pretty slow and/or expensive.\n",
    "\n",
    "**SECURITY NOTE**\n",
    "\n",
    "Building Q&A systems of SQL databases requires executing model-generated SQL queries. There are inherent risks in doing this. Make sure that your database connection permissions are always scoped as narrowly as possible for your chain/agent's needs. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import modules\n",
    "from langchain_core.messages import HumanMessage\n",
    "from langchain_openai import AzureChatOpenAI\n",
    "\n",
    "from langchain.sql_database import SQLDatabase\n",
    "from langchain.agents.types import AgentType\n",
    "from langchain.agents import create_sql_agent\n",
    "from langchain.agents.agent_toolkits import SQLDatabaseToolkit\n",
    "\n",
    "import os\n",
    "from sqlalchemy import create_engine\n",
    "from dotenv import load_dotenv\n",
    "import pyodbc \n",
    "import urllib  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# load envs\n",
    "\n",
    "load_dotenv()\n",
    "\n",
    "# Azure SQL DB\n",
    "AZURE_SQL_SERVER_ADMIN = os.environ.get('AZURE_SQL_SERVER_ADMIN')\n",
    "AZURE_SQL_SERVER_PWD = os.environ.get('AZURE_SQL_SERVER_PWD')\n",
    "AZURE_SQL_SERVER = os.environ.get('AZURE_SQL_SERVER')\n",
    "AZURE_SQL_DATABASE = os.environ.get('AZURE_SQL_DATABASE')\n",
    "DRIVER = os.environ.get('DRIVER')\n",
    "\n",
    "# OpenAI\n",
    "AZURE_OPENAI_API_KEY = os.environ.get(\"AZURE_OPENAI_API_KEY\")\n",
    "AZURE_OPENAI_ENDPOINT = os.environ.get('AZURE_OPENAI_ENDPOINT')\n",
    "AZURE_OPENAI_VERSION = os.environ.get('AZURE_OPENAI_VERSION')\n",
    "AZURE_OPENAI_DEPLOYMENT_NAME = os.environ.get('AZURE_OPENAI_DEPLOYMENT_NAME')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# connect via pyodbc\n",
    "\n",
    "CONNECTION_STRING = f'DRIVER={DRIVER};SERVER=tcp:{AZURE_SQL_SERVER},1433;DATABASE={AZURE_SQL_DATABASE};UID={AZURE_SQL_SERVER_ADMIN};PWD={AZURE_SQL_SERVER_PWD}'\n",
    "cnxn = pyodbc.connect(CONNECTION_STRING)\n",
    "cursor = cnxn.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create engine\n",
    "\n",
    "ODBC_CONNECTION_STRING = f\"Driver={DRIVER};Server=tcp:{AZURE_SQL_SERVER},1433;Database={AZURE_SQL_DATABASE};Uid={AZURE_SQL_SERVER_ADMIN};Pwd={AZURE_SQL_SERVER_PWD};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;\"\n",
    "ODBC_CONNECTION_STRING = urllib.parse.quote_plus(ODBC_CONNECTION_STRING) \n",
    "ODBC_CONNECTION_STRING = \"mssql+pyodbc:///?odbc_connect=%s\" % ODBC_CONNECTION_STRING\n",
    "\n",
    "db_engine = create_engine(ODBC_CONNECTION_STRING)\n",
    "\n",
    "# initialize db for RAG\n",
    "db = SQLDatabase(db_engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"J'adore les données structurées yo !\""
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "oai = AzureChatOpenAI(\n",
    "    openai_api_version=AZURE_OPENAI_VERSION,\n",
    "    azure_deployment=AZURE_OPENAI_DEPLOYMENT_NAME,\n",
    ")\n",
    "\n",
    "# test\n",
    "message = HumanMessage(\n",
    "    content=\"Translate this sentence from English to French. I love structured data yo!.\"\n",
    ")\n",
    "oai.invoke([message]).content"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create sql toolkit for the agent\n",
    "sql_toolkit = SQLDatabaseToolkit(db=db, llm=oai)\n",
    "\n",
    "# create SQL agent\n",
    "agent_executor = create_sql_agent(\n",
    "    llm=oai,\n",
    "    toolkit=sql_toolkit,\n",
    "    verbose=True,\n",
    "    agent_type=AgentType.ZERO_SHOT_REACT_DESCRIPTION\n",
    ")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Initial Test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\u001b[1m> Entering new SQL Agent Executor chain...\u001b[0m\n",
      "\u001b[32;1m\u001b[1;3mTo answer this question, I need to access the database and find the records that match the criteria. I assume that there is a table that contains information about the items, including their speed, price per nos, and quantity. But first, I need to confirm the tables in the database.\n",
      "Action: sql_db_list_tables\n",
      "Action Input: \"\"\u001b[0m\u001b[38;5;200m\u001b[1;3mMedical Equipment Spare Part Inventories\u001b[0m\u001b[32;1m\u001b[1;3mThe database has one table which is \"Medical Equipment Spare Part Inventories\". I assume this table has the necessary information. I will need to look at the schema of this table to confirm if it has the fields I need to answer the question.\n",
      "Action: sql_db_schema\n",
      "Action Input: \"Medical Equipment Spare Part Inventories\"\u001b[0m\u001b[33;1m\u001b[1;3m\n",
      "CREATE TABLE [Medical Equipment Spare Part Inventories] (\n",
      "\t[Item_Code] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, \n",
      "\t[Item_Description] NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, \n",
      "\t[Part_No] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, \n",
      "\t[Part_Description] NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, \n",
      "\t[Model] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, \n",
      "\t[Unit_Of_Measurement] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, \n",
      "\t[Spare_Part_Type] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, \n",
      "\t[Location] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, \n",
      "\t[Specify] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, \n",
      "\t[Part_Category] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, \n",
      "\t[Is_Expiry_date_Required] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, \n",
      "\t[Min_Nos] TINYINT NULL, \n",
      "\t[Max_Nos] SMALLINT NULL, \n",
      "\t[Minimum_Price_Per_Nos_RM] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, \n",
      "\t[Maximum_Price_Per_Nos_RM] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, \n",
      "\t[Brand] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, \n",
      "\t[Status] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, \n",
      "\t[Expiry_Age_In_Month] TINYINT NULL, \n",
      "\t[Current_Stock_Level] SMALLINT NULL\n",
      ")\n",
      "\n",
      "/*\n",
      "3 rows from Medical Equipment Spare Part Inventories table:\n",
      "Item_Code\tItem_Description\tPart_No\tPart_Description\tModel\tUnit_Of_Measurement\tSpare_Part_Type\tLocation\tSpecify\tPart_Category\tIs_Expiry_date_Required\tMin_Nos\tMax_Nos\tMinimum_Price_Per_Nos_RM\tMaximum_Price_Per_Nos_RM\tBrand\tStatus\tExpiry_Age_In_Month\tCurrent_Stock_Level\n",
      "BP0018\tMotors/ Micromotors/ Carbon brushes\t01-0802-0503-085\tFlow motor\tNone\tUnit\tFast Moving Item\tCompany Site Office\tNone\tBiomedical\tYes\t1\t3\tMYR 500,00 \tMYR 800,00 \tCommon\tActive\t0\t0\n",
      "BP0025\tSensors/ Detectors\t01-0101-0101-044\tO2 Sensor OOM202\tNone\tUnit\tOne Off\tCentralized\tNone\tBiomedical\tYes\t1\t5\tMYR 280,00 \tMYR 380,00 \tCommon\tActive\t0\t0\n",
      "BP0005\tCasing/ Covers/ Brackets/ Latches/ Part Holders\t01-0802-0504-011\tDoor 22MM Bubble C.Holder 4008SV10\tFresenius 4008S NG\tUnit\tJust In Time\tCompany Site Office\tNone\tBiomedical\tNo\t1\t3\tMYR 800,00 \tMYR 826,80 \tFresenius\tActive\t0\t3\n",
      "*/\u001b[0m\u001b[32;1m\u001b[1;3mThe table \"Medical Equipment Spare Part Inventories\" has a column named \"Minimum_Price_Per_Nos_RM\" which I can use to filter items with a minimum price per nos of over 1000. It also has a column named \"Spare_Part_Type\" which should contain the information about whether an item is fast moving or not. Now I will write a query to find the count of such items.\n",
      "Action: sql_db_query_checker\n",
      "Action Input: \"SELECT COUNT(*) FROM [Medical Equipment Spare Part Inventories] WHERE Spare_Part_Type = 'Fast Moving Item' AND CAST(REPLACE(Minimum_Price_Per_Nos_RM, 'MYR ', '') AS FLOAT) > 1000\"\u001b[0m\u001b[36;1m\u001b[1;3mSELECT COUNT(*) FROM [Medical Equipment Spare Part Inventories] WHERE Spare_Part_Type = 'Fast Moving Item' AND CAST(REPLACE(Minimum_Price_Per_Nos_RM, 'MYR ', '') AS FLOAT) > 1000\u001b[0m\u001b[32;1m\u001b[1;3mThe query seems to be correct. It is designed to count the number of rows in the \"Medical Equipment Spare Part Inventories\" where the \"Spare_Part_Type\" is 'Fast Moving Item' and the \"Minimum_Price_Per_Nos_RM\" is over 1000. The \"Minimum_Price_Per_Nos_RM\" field is a string, so I'm replacing the 'MYR ' part and casting it to a float to perform the comparison. Now, I will execute this query.\n",
      "Action: sql_db_query\n",
      "Action Input: \"SELECT COUNT(*) FROM [Medical Equipment Spare Part Inventories] WHERE Spare_Part_Type = 'Fast Moving Item' AND CAST(REPLACE(Minimum_Price_Per_Nos_RM, 'MYR ', '') AS FLOAT) > 1000\"\u001b[0m\u001b[36;1m\u001b[1;3mError: (pyodbc.ProgrammingError) ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Error converting data type nvarchar to float. (8114) (SQLExecDirectW)')\n",
      "[SQL: SELECT COUNT(*) FROM [Medical Equipment Spare Part Inventories] WHERE Spare_Part_Type = 'Fast Moving Item' AND CAST(REPLACE(Minimum_Price_Per_Nos_RM, 'MYR ', '') AS FLOAT) > 1000]\n",
      "(Background on this error at: https://sqlalche.me/e/20/f405)\u001b[0m\u001b[32;1m\u001b[1;3mThe error suggests that the conversion from nvarchar to float is failing. This could be due to the presence of non-numeric characters in the \"Minimum_Price_Per_Nos_RM\" column other than 'MYR '. I need to inspect some records to identify the issue.\n",
      "Action: sql_db_query\n",
      "Action Input: \"SELECT TOP 10 Minimum_Price_Per_Nos_RM FROM [Medical Equipment Spare Part Inventories]\"\u001b[0m\u001b[36;1m\u001b[1;3m[('MYR 500,00 ',), ('MYR 280,00 ',), ('MYR 800,00 ',), ('MYR 650,00 ',), ('MYR 385,00 ',), ('MYR 450,00 ',), ('MYR 127,00 ',), ('MYR 1,85 ',), ('MYR 25,00 ',), ('MYR 85,00 ',)]\u001b[0m\u001b[32;1m\u001b[1;3mThe prices in the 'Minimum_Price_Per_Nos_RM' column are formatted with a comma as the decimal separator. This is why our conversion to float is failing, as float values use a dot as the decimal separator. I'll adjust the query to replace the comma with a dot before converting to float.\n",
      "Action: sql_db_query_checker\n",
      "Action Input: \"SELECT COUNT(*) FROM [Medical Equipment Spare Part Inventories] WHERE Spare_Part_Type = 'Fast Moving Item' AND CAST(REPLACE(REPLACE(Minimum_Price_Per_Nos_RM, 'MYR ', ''), ',', '.') AS FLOAT) > 1000\"\u001b[0m\u001b[36;1m\u001b[1;3mSELECT COUNT(*) FROM [Medical Equipment Spare Part Inventories] WHERE Spare_Part_Type = 'Fast Moving Item' AND CAST(REPLACE(REPLACE(Minimum_Price_Per_Nos_RM, 'MYR ', ''), ',', '.') AS FLOAT) > 1000\u001b[0m\u001b[32;1m\u001b[1;3mThe updated query appears to be correct as it now replaces both the 'MYR ' part and the comma in the \"Minimum_Price_Per_Nos_RM\" field before casting it to a float. Now, I will execute this query.\n",
      "Action: sql_db_query\n",
      "Action Input: \"SELECT COUNT(*) FROM [Medical Equipment Spare Part Inventories] WHERE Spare_Part_Type = 'Fast Moving Item' AND CAST(REPLACE(REPLACE(Minimum_Price_Per_Nos_RM, 'MYR ', ''), ',', '.') AS FLOAT) > 1000\"\u001b[0m\u001b[36;1m\u001b[1;3mError: (pyodbc.ProgrammingError) ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Error converting data type nvarchar to float. (8114) (SQLExecDirectW)')\n",
      "[SQL: SELECT COUNT(*) FROM [Medical Equipment Spare Part Inventories] WHERE Spare_Part_Type = 'Fast Moving Item' AND CAST(REPLACE(REPLACE(Minimum_Price_Per_Nos_RM, 'MYR ', ''), ',', '.') AS FLOAT) > 1000]\n",
      "(Background on this error at: https://sqlalche.me/e/20/f405)\u001b[0m\u001b[32;1m\u001b[1;3mThe error suggests that there is still an issue with the conversion from nvarchar to float. It's possible that there are other non-numeric characters in the \"Minimum_Price_Per_Nos_RM\" column that we haven't accounted for, or there could be some data quality issues. Let's take a deeper look at the data in \"Minimum_Price_Per_Nos_RM\" field.\n",
      "Action: sql_db_query\n",
      "Action Input: \"SELECT DISTINCT Minimum_Price_Per_Nos_RM FROM [Medical Equipment Spare Part Inventories] ORDER BY Minimum_Price_Per_Nos_RM\"\u001b[0m\u001b[36;1m\u001b[1;3m[('MYR 0,40 ',), ('MYR 1,00 ',), ('MYR 1,10 ',), ('MYR 1,30 ',), ('MYR 1,60 ',), ('MYR 1,85 ',), ('MYR 1.000,00 ',), ('MYR 1.090,00 ',), ('MYR 1.100,00 ',), ('MYR 1.125,00 ',), ('MYR 1.130,00 ',), ('MYR 1.163,00 ',), ('MYR 1.166,00 ',), ('MYR 1.175,00 ',), ('MYR 1.180,00 ',), ('MYR 1.200,00 ',), ('MYR 1.250,00 ',), ('MYR 1.267,00 ',), ('MYR 1.300,00 ',), ('MYR 1.305,00 ',), ('MYR 1.306,00 ',), ('MYR 1.330,00 ',), ('MYR 1.346,00 ',), ('MYR 1.350,00 ',), ('MYR 1.468,00 ',), ('MYR 1.495,00 ',), ('MYR 1.500,00 ',), ('MYR 1.520,00 ',), ('MYR 1.520,30 ',), ('MYR 1.571,00 ',), ('MYR 1.724,00 ',), ('MYR 1.750,00 ',), ('MYR 1.800,00 ',), ('MYR 1.850,00 ',), ('MYR 1.980,00 ',), ('MYR 10,00 ',), ('MYR 10.001,00 ',), ('MYR 10.500,00 ',), ('MYR 100,00 ',), ('MYR 104,38 ',), ('MYR 105,00 ',), ('MYR 11,00 ',), ('MYR 110,00 ',), ('MYR 118,00 ',), ('MYR 12,00 ',), ('MYR 12,50 ',), ('MYR 120,00 ',), ('MYR 122,00 ',), ('MYR 125,00 ',), ('MYR 127,00 ',), ('MYR 130,00 ',), ('MYR 132,00 ',), ('MYR 135,00 ',), ('MYR 138,00 ',), ('MYR 14,00 ',), ('MYR 140,00 ',), ('MYR 145,00 ',), ('MYR 15,00 ',), ('MYR 150,00 ',), ('MYR 155,00 ',), ('MYR 159,00 ',), ('MYR 159,85 ',), ('MYR 16,00 ',), ('MYR 16,50 ',), ('MYR 160,00 ',), ('MYR 163,00 ',), ('MYR 165,00 ',), ('MYR 166,90 ',), ('MYR 172,04 ',), ('MYR 175,00 ',), ('MYR 179,00 ',), ('MYR 18,00 ',), ('MYR 180,00 ',), ('MYR 182,00 ',), ('MYR 185,00 ',), ('MYR 19,50 ',), ('MYR 190,00 ',), ('MYR 198,00 ',), ('MYR 2,00 ',), ('MYR 2,40 ',), ('MYR 2,50 ',), ('MYR 2.000,00 ',), ('MYR 2.067,00 ',), ('MYR 2.080,00 ',), ('MYR 2.132,00 ',), ('MYR 2.180,25 ',), ('MYR 2.398,00 ',), ('MYR 2.700,00 ',), ('MYR 2.850,00 ',), ('MYR 20,00 ',), ('MYR 200,00 ',), ('MYR 203,00 ',), ('MYR 204,50 ',), ('MYR 205,00 ',), ('MYR 208,00 ',), ('MYR 21,00 ',), ('MYR 210,00 ',), ('MYR 215,00 ',), ('MYR 217,00 ',), ('MYR 225,00 ',), ('MYR 230,00 ',), ('MYR 235,00 ',), ('MYR 24,00 ',), ('MYR 240,00 ',), ('MYR 248,00 ',), ('MYR 25,00 ',), ('MYR 250,00 ',), ('MYR 252,00 ',), ('MYR 255,00 ',), ('MYR 257,15 ',), ('MYR 26,00 ',), ('MYR 260,00 ',), ('MYR 27,00 ',), ('MYR 27,50 ',), ('MYR 276,10 ',), ('MYR 277,00 ',), ('MYR 280,00 ',), ('MYR 288,00 ',), ('MYR 290,00 ',), ('MYR 298,80 ',), ('MYR 3,00 ',), ('MYR 3,50 ',), ('MYR 3,60 ',), ('MYR 3,70 ',), ('MYR 3.000,00 ',), ('MYR 3.100,00 ',), ('MYR 3.150,00 ',), ('MYR 3.400,00 ',), ('MYR 3.750,00 ',), ('MYR 3.857,69 ',), ('MYR 30,00 ',), ('MYR 300,00 ',), ('MYR 31,00 ',), ('MYR 310,00 ',), ('MYR 315,00 ',), ('MYR 32,00 ',), ('MYR 320,00 ',), ('MYR 321,00 ',), ('MYR 323,00 ',), ('MYR 328,00 ',), ('MYR 33,00 ',), ('MYR 336,00 ',), ('MYR 337,00 ',), ('MYR 340,00 ',), ('MYR 345,00 ',), ('MYR 348,11 ',), ('MYR 35,00 ',), ('MYR 350,00 ',), ('MYR 351,00 ',), ('MYR 352,00 ',), ('MYR 36,30 ',), ('MYR 360,00 ',), ('MYR 37,00 ',), ('MYR 370,00 ',), ('MYR 372,00 ',), ('MYR 375,00 ',), ('MYR 377,00 ',), ('MYR 378,00 ',), ('MYR 38,00 ',), ('MYR 380,00 ',), ('MYR 385,00 ',), ('MYR 386,00 ',), ('MYR 388,00 ',), ('MYR 390,00 ',), ('MYR 4,00 ',), ('MYR 4,10 ',), ('MYR 4.400,00 ',), ('MYR 4.500,00 ',), ('MYR 40,00 ',), ('MYR 400,00 ',), ('MYR 410,00 ',), ('MYR 42,00 ',), ('MYR 425,00 ',), ('MYR 440,00 ',), ('MYR 441,00 ',), ('MYR 45,50 ',), ('MYR 450,00 ',), ('MYR 46,00 ',), ('MYR 46,85 ',), ('MYR 460,00 ',), ('MYR 475,00 ',), ('MYR 48,00 ',), ('MYR 480,00 ',), ('MYR 490,00 ',), ('MYR 498,50 ',), ('MYR 499,00 ',), ('MYR 5,00 ',), ('MYR 5,30 ',), ('MYR 5.002,00 ',), ('MYR 5.341,00 ',), ('MYR 5.500,00 ',), ('MYR 5.945,40 ',), ('MYR 50,00 ',), ('MYR 500,00 ',), ('MYR 512,00 ',), ('MYR 52,00 ',), ('MYR 520,00 ',), ('MYR 525,00 ',), ('MYR 54,00 ',), ('MYR 55,00 ',), ('MYR 550,00 ',), ('MYR 560,00 ',), ('MYR 570,00 ',), ('MYR 58,40 ',), ('MYR 580,00 ',), ('MYR 59,70 ',), ('MYR 598,00 ',), ('MYR 6,25 ',), ('MYR 6,70 ',), ('MYR 6,90 ',), ('MYR 60,00 ',), ('MYR 600,00 ',), ('MYR 610,00 ',), ('MYR 62,00 ',), ('MYR 620,00 ',), ('MYR 63,60 ',), ('MYR 630,00 ',), ('MYR 645,00 ',), ('MYR 65,00 ',), ('MYR 650,00 ',), ('MYR 670,00 ',), ('MYR 671,00 ',), ('MYR 68,00 ',), ('MYR 680,00 ',), ('MYR 685,00 ',), ('MYR 699,00 ',), ('MYR 7,00 ',), ('MYR 7,50 ',), ('MYR 7,80 ',), ('MYR 70,00 ',), ('MYR 700,00 ',), ('MYR 712,00 ',), ('MYR 718,00 ',), ('MYR 720,00 ',), ('MYR 721,60 ',), ('MYR 730,00 ',), ('MYR 733,00 ',), ('MYR 740,00 ',), ('MYR 75,00 ',), ('MYR 76,00 ',), ('MYR 77,00 ',), ('MYR 789,00 ',), ('MYR 79,00 ',), ('MYR 8,00 ',), ('MYR 80,00 ',), ('MYR 800,00 ',), ('MYR 85,00 ',), ('MYR 854,00 ',), ('MYR 860,00 ',), ('MYR 9,50 ',), ('MYR 9.432,00 ',), ('MYR 90,00 ',), ('MYR 900,00 ',), ('MYR 920,00 ',), ('MYR 940,00 ',), ('MYR 943,00 ',), ('MYR 95,00 ',), ('MYR 950,00 ',), ('MYR 96,00 ',), ('MYR 974,00 ',), ('MYR 98,00 ',), ('MYR 990,00 ',)]\u001b[0m\u001b[32;1m\u001b[1;3mThe data in the 'Minimum_Price_Per_Nos_RM' column have both comma and dot as thousands separator. Some prices are in the format 'MYR 1.000,00 ' while others are in the format 'MYR 1,00 '. This is causing the conversion to float to fail. I will write a function that can handle this situation, converting the column to a numeric type regardless of whether it uses a dot or a comma as the thousands separator.\n",
      "Action: sql_db_query_checker\n",
      "Action Input: \"SELECT COUNT(*) FROM [Medical Equipment Spare Part Inventories] WHERE Spare_Part_Type = 'Fast Moving Item' AND CONVERT(FLOAT, REPLACE(REPLACE(REPLACE(Minimum_Price_Per_Nos_RM, 'MYR ', ''), '.', ''), ',', '.')) > 1000\"\u001b[0m\u001b[36;1m\u001b[1;3mSELECT COUNT(*) FROM [Medical Equipment Spare Part Inventories] WHERE Spare_Part_Type = 'Fast Moving Item' AND CONVERT(FLOAT, REPLACE(REPLACE(REPLACE(Minimum_Price_Per_Nos_RM, 'MYR ', ''), '.', ''), ',', '.')) > 1000\u001b[0m\u001b[32;1m\u001b[1;3mThe updated query appears to be correct as it now replaces the 'MYR ' part, the comma and the dot in the \"Minimum_Price_Per_Nos_RM\" field before casting it to a float. Now, I will execute this query.\n",
      "Action: sql_db_query\n",
      "Action Input: \"SELECT COUNT(*) FROM [Medical Equipment Spare Part Inventories] WHERE Spare_Part_Type = 'Fast Moving Item' AND CONVERT(FLOAT, REPLACE(REPLACE(REPLACE(Minimum_Price_Per_Nos_RM, 'MYR ', ''), '.', ''), ',', '.')) > 1000\"\u001b[0m\u001b[36;1m\u001b[1;3m[(17,)]\u001b[0m\u001b[32;1m\u001b[1;3mI now know the final answer. There are 17 fast moving items with a minimum price per nos of over 1000.\n",
      "Final Answer: There are 17 fast moving items with a minimum price per nos of over 1000.\u001b[0m\n",
      "\n",
      "\u001b[1m> Finished chain.\u001b[0m\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'There are 17 fast moving items with a minimum price per nos of over 1000.'"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# run agent executor\n",
    "\n",
    "question = \"How many fast moving items do we have with a minimum price per nos of over 1000? Any ideas?\"\n",
    "\n",
    "output = agent_executor.invoke(question)\n",
    "output['output']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Retrieval Enhancements"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Step 1: Improve the quality of a system prompt\n",
    "\n",
    "system_prompt = \"\"\" \n",
    "\n",
    "    You are the most powerful and skillfull expert in querying SQL Database to find answers to user's questions.\n",
    "\n",
    "    Your main task is to answer the USER QUERY. Utilize CONTEXT and COLUMN DESCRIPTIONS for better query understanding.\n",
    "\n",
    "    # CONTEXT\n",
    "    Table 'Medical Equipment Spare Part Inventories' contains spare parts information for maintaining a spare parts inventory management program\n",
    "\n",
    "    # COLUMN DESCRIPTIONS\n",
    "    - Item Code: A code that uniquely identifies each type of item or part in the inventory.\n",
    "    - Item Description: Provides a description of the item or part, which might include its general use or characteristics. This helps in identifying the item and understanding its application.\n",
    "    - Part No: The part number, which is a unique identifier assigned to a part for the purposes of inventory and ordering.\n",
    "    - Part Description: A brief description of the part, providing more details about its specifications or intended use in medical equipment.\n",
    "    - Unit Of Measurement: The unit in which the item is measured.\n",
    "    - Spare Part Type: Categorizes the part by its usage frequency or criticality, such as \"Fast Moving Item\" or \"One Off\".\n",
    "    - Location: Indicates where the part is stored or located, which can be crucial for logistics and inventory retrieval. For example, a \"Company Site Office\" or a specific warehouse location.\n",
    "    - Part Category: The category of the part, which helps in classifying parts into broader groups, such as \"Biomedical\" or \"Electrical\", aiding in management and reporting.\n",
    "    - Is Expiry date Required: Specifies whether an expiry date is required for the part, which is important for parts that are sensitive to age or environmental conditions.\n",
    "    - Min Nos: The minimum number of units that should be held in inventory for this part, which helps prevent understocking.\n",
    "    - Max Nos: The maximum number of units that should be held in inventory, which helps in preventing overstocking and managing storage space efficiently.\n",
    "    - Minimum Price Per Nos (RM): The minimum price per unit for the part, expressed in Malaysian Ringgit (RM). This could be used for budgeting and cost management.\n",
    "    - Maximum Price Per Nos (RM): The maximum price per unit for the part, also in Malaysian Ringgit. This might reflect different supplier costs or quality grades.\n",
    "    - Brand: The brand of the part, which can be a determinant of quality and preference for certain applications or compliance with specific equipment.\n",
    "    - Status: Indicates the current status of the part in the inventory (Active, Obsolete or Out of Stock)\n",
    "    - Expiry Age (In Month): If applicable, how many months until the part should be considered expired after receipt. This is particularly relevant for parts with critical performance characteristics that degrade over time.\n",
    "    - Current Stock Level: The current quantity of the part available in stock.\n",
    "\n",
    "    \n",
    "    # MANDATORY REQUIREMENTS\n",
    "    You never hallucinate.\n",
    "    You always answer precisely and constuctively.\n",
    "\n",
    "    # USER QUERY\n",
    "    {query}\n",
    "\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\u001b[1m> Entering new SQL Agent Executor chain...\u001b[0m\n",
      "\u001b[32;1m\u001b[1;3mTo answer this question, I need to find the count of items from the 'Medical Equipment Spare Part Inventories' table where the 'Spare Part Type' is 'Fast Moving Item'. \n",
      "\n",
      "Action: sql_db_list_tables\n",
      "Action Input: \"\"\u001b[0m\u001b[38;5;200m\u001b[1;3mMedical Equipment Spare Part Inventories\u001b[0m\u001b[32;1m\u001b[1;3mThe 'Medical Equipment Spare Part Inventories' table exists. Now I will write an SQL query to count the number of items where 'Spare Part Type' is 'Fast Moving Item'. \n",
      "\n",
      "Action: sql_db_query_checker\n",
      "Action Input: \"SELECT COUNT(*) FROM `Medical Equipment Spare Part Inventories` WHERE `Spare Part Type` = 'Fast Moving Item'\"\u001b[0m\u001b[36;1m\u001b[1;3mSELECT COUNT(*) FROM `Medical Equipment Spare Part Inventories` WHERE `Spare Part Type` = 'Fast Moving Item'\u001b[0m\u001b[32;1m\u001b[1;3mThe SQL query is correct. Now I will execute it to get the answer.\n",
      "\n",
      "Action: sql_db_query\n",
      "Action Input: \"SELECT COUNT(*) FROM `Medical Equipment Spare Part Inventories` WHERE `Spare Part Type` = 'Fast Moving Item'\"\u001b[0m\u001b[36;1m\u001b[1;3mError: (pyodbc.ProgrammingError) ('42000', \"[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near '`'. (102) (SQLExecDirectW)\")\n",
      "[SQL: SELECT COUNT(*) FROM `Medical Equipment Spare Part Inventories` WHERE `Spare Part Type` = 'Fast Moving Item']\n",
      "(Background on this error at: https://sqlalche.me/e/20/f405)\u001b[0m\u001b[32;1m\u001b[1;3mThe error seems to be due to the improper use of backticks around the table and column names. In SQL Server, we should use square brackets [] instead of backticks for this purpose. I will modify the query and check it again.\n",
      "\n",
      "Action: sql_db_query_checker\n",
      "Action Input: \"SELECT COUNT(*) FROM [Medical Equipment Spare Part Inventories] WHERE [Spare Part Type] = 'Fast Moving Item'\"\u001b[0m\u001b[36;1m\u001b[1;3mSELECT COUNT(*) FROM [Medical Equipment Spare Part Inventories] WHERE [Spare Part Type] = 'Fast Moving Item'\u001b[0m\u001b[32;1m\u001b[1;3mThe modified SQL query is correct. Now I will execute it to get the answer.\n",
      "\n",
      "Action: sql_db_query\n",
      "Action Input: \"SELECT COUNT(*) FROM [Medical Equipment Spare Part Inventories] WHERE [Spare Part Type] = 'Fast Moving Item'\"\u001b[0m\u001b[36;1m\u001b[1;3mError: (pyodbc.ProgrammingError) ('42S22', \"[42S22] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column name 'Spare Part Type'. (207) (SQLExecDirectW)\")\n",
      "[SQL: SELECT COUNT(*) FROM [Medical Equipment Spare Part Inventories] WHERE [Spare Part Type] = 'Fast Moving Item']\n",
      "(Background on this error at: https://sqlalche.me/e/20/f405)\u001b[0m\u001b[32;1m\u001b[1;3mIt seems like the column name 'Spare Part Type' doesn't exist in the table 'Medical Equipment Spare Part Inventories'. I need to check the schema of the table to verify the correct column names.\n",
      "\n",
      "Action: sql_db_schema\n",
      "Action Input: \"Medical Equipment Spare Part Inventories\"\u001b[0m\u001b[33;1m\u001b[1;3m\n",
      "CREATE TABLE [Medical Equipment Spare Part Inventories] (\n",
      "\t[Item_Code] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, \n",
      "\t[Item_Description] NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, \n",
      "\t[Part_No] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, \n",
      "\t[Part_Description] NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, \n",
      "\t[Model] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, \n",
      "\t[Unit_Of_Measurement] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, \n",
      "\t[Spare_Part_Type] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, \n",
      "\t[Location] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, \n",
      "\t[Specify] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, \n",
      "\t[Part_Category] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, \n",
      "\t[Is_Expiry_date_Required] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, \n",
      "\t[Min_Nos] TINYINT NULL, \n",
      "\t[Max_Nos] SMALLINT NULL, \n",
      "\t[Minimum_Price_Per_Nos_RM] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, \n",
      "\t[Maximum_Price_Per_Nos_RM] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, \n",
      "\t[Brand] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, \n",
      "\t[Status] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, \n",
      "\t[Expiry_Age_In_Month] TINYINT NULL, \n",
      "\t[Current_Stock_Level] SMALLINT NULL\n",
      ")\n",
      "\n",
      "/*\n",
      "3 rows from Medical Equipment Spare Part Inventories table:\n",
      "Item_Code\tItem_Description\tPart_No\tPart_Description\tModel\tUnit_Of_Measurement\tSpare_Part_Type\tLocation\tSpecify\tPart_Category\tIs_Expiry_date_Required\tMin_Nos\tMax_Nos\tMinimum_Price_Per_Nos_RM\tMaximum_Price_Per_Nos_RM\tBrand\tStatus\tExpiry_Age_In_Month\tCurrent_Stock_Level\n",
      "BP0018\tMotors/ Micromotors/ Carbon brushes\t01-0802-0503-085\tFlow motor\tNone\tUnit\tFast Moving Item\tCompany Site Office\tNone\tBiomedical\tYes\t1\t3\tMYR 500,00 \tMYR 800,00 \tCommon\tActive\t0\t0\n",
      "BP0025\tSensors/ Detectors\t01-0101-0101-044\tO2 Sensor OOM202\tNone\tUnit\tOne Off\tCentralized\tNone\tBiomedical\tYes\t1\t5\tMYR 280,00 \tMYR 380,00 \tCommon\tActive\t0\t0\n",
      "BP0005\tCasing/ Covers/ Brackets/ Latches/ Part Holders\t01-0802-0504-011\tDoor 22MM Bubble C.Holder 4008SV10\tFresenius 4008S NG\tUnit\tJust In Time\tCompany Site Office\tNone\tBiomedical\tNo\t1\t3\tMYR 800,00 \tMYR 826,80 \tFresenius\tActive\t0\t3\n",
      "*/\u001b[0m\u001b[32;1m\u001b[1;3mThe column name 'Spare Part Type' does exist in the table 'Medical Equipment Spare Part Inventories' and it matches exactly what I used in my query. The error could be due to the value 'Fast Moving Item' not being present in the 'Spare Part Type' column. I will modify the query to check how many unique values are present in the 'Spare Part Type' column to confirm my suspicion.\n",
      "\n",
      "Action: sql_db_query_checker\n",
      "Action Input: \"SELECT DISTINCT [Spare_Part_Type] FROM [Medical Equipment Spare Part Inventories]\"\u001b[0m\u001b[36;1m\u001b[1;3mSELECT DISTINCT [Spare_Part_Type] FROM [Medical Equipment Spare Part Inventories]\u001b[0m\u001b[32;1m\u001b[1;3mThe modified SQL query is correct. Now I will execute it to get the unique values in the 'Spare Part Type' column.\n",
      "\n",
      "Action: sql_db_query\n",
      "Action Input: \"SELECT DISTINCT [Spare_Part_Type] FROM [Medical Equipment Spare Part Inventories]\"\u001b[0m\u001b[36;1m\u001b[1;3m[('Fast Moving Item',), ('Just In Time',), ('One Off',)]\u001b[0m\u001b[32;1m\u001b[1;3mThe 'Spare Part Type' column does contain the value 'Fast Moving Item'. It seems like there was an issue with my initial query. I will try running the initial count query again to see if it works this time.\n",
      "\n",
      "Action: sql_db_query\n",
      "Action Input: \"SELECT COUNT(*) FROM [Medical Equipment Spare Part Inventories] WHERE [Spare Part Type] = 'Fast Moving Item'\"\u001b[0m\u001b[36;1m\u001b[1;3mError: (pyodbc.ProgrammingError) ('42S22', \"[42S22] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column name 'Spare Part Type'. (207) (SQLExecDirectW)\")\n",
      "[SQL: SELECT COUNT(*) FROM [Medical Equipment Spare Part Inventories] WHERE [Spare Part Type] = 'Fast Moving Item']\n",
      "(Background on this error at: https://sqlalche.me/e/20/f405)\u001b[0m\u001b[32;1m\u001b[1;3mThe same error occurred again. It seems like there is an issue with the SQL Server not recognizing the column name 'Spare Part Type'. The column names in the schema are shown with underscores instead of spaces. I should modify the query to use the column name 'Spare_Part_Type' instead of 'Spare Part Type'.\n",
      "\n",
      "Action: sql_db_query_checker\n",
      "Action Input: \"SELECT COUNT(*) FROM [Medical Equipment Spare Part Inventories] WHERE [Spare_Part_Type] = 'Fast Moving Item'\"\u001b[0m\u001b[36;1m\u001b[1;3mSELECT COUNT(*) FROM [Medical Equipment Spare Part Inventories] WHERE [Spare_Part_Type] = 'Fast Moving Item'\u001b[0m\u001b[32;1m\u001b[1;3mThe modified SQL query is correct. Now I will execute it to get the answer.\n",
      "\n",
      "Action: sql_db_query\n",
      "Action Input: \"SELECT COUNT(*) FROM [Medical Equipment Spare Part Inventories] WHERE [Spare_Part_Type] = 'Fast Moving Item'\"\u001b[0m\u001b[36;1m\u001b[1;3m[(235,)]\u001b[0m\u001b[32;1m\u001b[1;3mI now know the final answer.\n",
      "\n",
      "Final Answer: There are 235 fast moving items.\u001b[0m\n",
      "\n",
      "\u001b[1m> Finished chain.\u001b[0m\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'There are 235 fast moving items.'"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "question = \"How many fast moving items do we have?\"\n",
    "\n",
    "output = agent_executor.invoke(system_prompt.format(query=question))\n",
    "output['output']"
   ]
  }
 ],
 "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.11.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}