Langchain_NL2SQL_2024.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": []
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/PradipNichite/Youtube-Tutorials/blob/main/Langchain_NL2SQL_2024.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "https://www.aidemos.com/\n",
        "\n",
        "[Discover Best AI Tools with Video Demos](https://www.aidemos.com/)\n"
      ],
      "metadata": {
        "id": "l4gH0BcahHO6"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "\n",
        "1. Building a basic NL2SQL model\n",
        "2. Adding few-shot examples\n",
        "3. Dynamic few-shot example selection\n",
        "4. Dynamic relevant table selection (Large DB Isssue)\n",
        "5. Customizing prompts\n",
        "6. Adding memory to the chatbot so that it answers follow-up questions related to the database."
      ],
      "metadata": {
        "id": "up5XHcGdVon2"
      }
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "HUShN3Sm0Lr0",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "collapsed": true,
        "outputId": "09597eab-734e-4c3a-c383-4463dfcbaa18"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\u001b[?25l     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m0.0/67.3 kB\u001b[0m \u001b[31m?\u001b[0m eta \u001b[36m-:--:--\u001b[0m\r\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m67.3/67.3 kB\u001b[0m \u001b[31m2.8 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[?25h  Installing build dependencies ... \u001b[?25l\u001b[?25hdone\n",
            "  Getting requirements to build wheel ... \u001b[?25l\u001b[?25hdone\n",
            "  Preparing metadata (pyproject.toml) ... \u001b[?25l\u001b[?25hdone\n",
            "\u001b[2K   \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m50.4/50.4 kB\u001b[0m \u001b[31m4.6 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K   \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m2.4/2.4 MB\u001b[0m \u001b[31m28.5 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K   \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m45.0/45.0 kB\u001b[0m \u001b[31m3.0 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K   \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m615.5/615.5 kB\u001b[0m \u001b[31m38.6 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K   \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m2.4/2.4 MB\u001b[0m \u001b[31m80.0 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K   \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m273.8/273.8 kB\u001b[0m \u001b[31m23.4 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K   \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m94.9/94.9 kB\u001b[0m \u001b[31m9.1 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K   \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m1.9/1.9 MB\u001b[0m \u001b[31m70.8 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K   \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m93.2/93.2 kB\u001b[0m \u001b[31m8.7 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K   \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m13.3/13.3 MB\u001b[0m \u001b[31m94.3 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K   \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m55.8/55.8 kB\u001b[0m \u001b[31m4.6 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K   \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m54.4/54.4 kB\u001b[0m \u001b[31m4.8 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K   \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m3.1/3.1 MB\u001b[0m \u001b[31m83.3 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K   \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m1.2/1.2 MB\u001b[0m \u001b[31m59.9 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K   \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m63.7/63.7 kB\u001b[0m \u001b[31m5.8 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K   \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m442.1/442.1 kB\u001b[0m \u001b[31m30.1 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K   \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m49.5/49.5 kB\u001b[0m \u001b[31m4.6 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K   \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m316.6/316.6 kB\u001b[0m \u001b[31m25.5 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K   \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m73.3/73.3 kB\u001b[0m \u001b[31m6.5 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K   \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m3.8/3.8 MB\u001b[0m \u001b[31m91.0 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K   \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m425.7/425.7 kB\u001b[0m \u001b[31m31.1 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K   \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m168.2/168.2 kB\u001b[0m \u001b[31m15.1 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K   \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m46.0/46.0 kB\u001b[0m \u001b[31m3.5 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K   \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m86.8/86.8 kB\u001b[0m \u001b[31m3.0 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[?25h  Building wheel for pypika (pyproject.toml) ... \u001b[?25l\u001b[?25hdone\n",
            "\u001b[31mERROR: pip's dependency resolver does not currently take into account all the packages that are installed. This behaviour is the source of the following dependency conflicts.\n",
            "tensorflow 2.17.1 requires protobuf!=4.21.0,!=4.21.1,!=4.21.2,!=4.21.3,!=4.21.4,!=4.21.5,<5.0.0dev,>=3.20.3, but you have protobuf 5.28.3 which is incompatible.\n",
            "tensorflow-metadata 1.13.1 requires protobuf<5,>=3.20.3, but you have protobuf 5.28.3 which is incompatible.\u001b[0m\u001b[31m\n",
            "\u001b[0m"
          ]
        }
      ],
      "source": [
        "!pip install langchain==0.3.7 langchain_openai==0.2.8 langchain_community==0.3.7 langchain-chroma==0.1.4 langsmith==0.1.142 pymysql==1.1.1 chromadb==0.5.18 -q"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [],
      "metadata": {
        "id": "OXdNuzAdWTdv"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "https://www.mysqltutorial.org/getting-started-with-mysql/mysql-sample-database/"
      ],
      "metadata": {
        "id": "zROkmBChhBWy"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "db_user = \"\"\n",
        "db_password = \"\"\n",
        "db_host = \"\"\n",
        "db_name = \"classicmodels\""
      ],
      "metadata": {
        "id": "c91p9Ep90S9j"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "###Building a basic NL2SQL model"
      ],
      "metadata": {
        "id": "OQJ4HJ-6WYjk"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "from langchain_community.utilities.sql_database import SQLDatabase\n",
        "# db = SQLDatabase.from_uri(f\"mysql+pymysql://{db_user}:{db_password}@{db_host}/{db_name}\",sample_rows_in_table_info=1,include_tables=['customers','orders'],custom_table_info={'customers':\"customer\"})\n",
        "db = SQLDatabase.from_uri(f\"mysql+pymysql://{db_user}:{db_password}@{db_host}/{db_name}\")"
      ],
      "metadata": {
        "id": "jiNjFg1i0U3Z"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "print(db.dialect)\n",
        "print(db.get_usable_table_names())\n",
        "print(db.table_info)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "4KJLTIDG0YJ5",
        "outputId": "60c4ca6a-19d2-4511-cd72-1b32aa358cf2"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "mysql\n",
            "['application_logs', 'customers', 'employees', 'offices', 'orderdetails', 'orders', 'payments', 'productlines', 'products']\n",
            "\n",
            "CREATE TABLE application_logs (\n",
            "\tlog_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, \n",
            "\tsession_id VARCHAR(255) NOT NULL, \n",
            "\tuser_question TEXT NOT NULL, \n",
            "\tgpt_response TEXT NOT NULL, \n",
            "\tgenerated_query TEXT, \n",
            "\tmodel VARCHAR(255), \n",
            "\tgpt_response_time DECIMAL(10, 3), \n",
            "\tcreated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, \n",
            "\tPRIMARY KEY (log_id)\n",
            ")DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB COLLATE utf8mb4_0900_ai_ci\n",
            "\n",
            "/*\n",
            "3 rows from application_logs table:\n",
            "log_id\tsession_id\tuser_question\tgpt_response\tgenerated_query\tmodel\tgpt_response_time\tcreated_at\n",
            "1\t7ff5df52-84cd-46b7-8da2-67aa28f98a1c\tHow many records are there in the customers table?\tThere are 122 records in the customers table.\tSELECT COUNT(*) AS total_records FROM customers\tgpt-3.5-turbo\t13.717\t2024-10-04 07:09:37\n",
            "2\tddad0e6c-2c80-49ca-badb-004cdf5c8044\tHow many records are there in the customers table?\tThere are 122 records in the customers table.\tSELECT COUNT(*) AS total_records FROM customers\tgpt-3.5-turbo\t13.424\t2024-10-04 07:19:20\n",
            "3\t59e4be0a-6a18-403f-9010-61f7af07b956\tHow many records are there in the customers table?\tThere are 122 records in the customers table.\tSELECT COUNT(*) AS total_records FROM customers;\tgpt-3.5-turbo\t14.026\t2024-10-04 08:26:03\n",
            "*/\n",
            "\n",
            "\n",
            "CREATE TABLE customers (\n",
            "\t`customerNumber` INTEGER NOT NULL, \n",
            "\t`customerName` VARCHAR(50) NOT NULL, \n",
            "\t`contactLastName` VARCHAR(50) NOT NULL, \n",
            "\t`contactFirstName` VARCHAR(50) NOT NULL, \n",
            "\tphone VARCHAR(50) NOT NULL, \n",
            "\t`addressLine1` VARCHAR(50) NOT NULL, \n",
            "\t`addressLine2` VARCHAR(50), \n",
            "\tcity VARCHAR(50) NOT NULL, \n",
            "\tstate VARCHAR(50), \n",
            "\t`postalCode` VARCHAR(15), \n",
            "\tcountry VARCHAR(50) NOT NULL, \n",
            "\t`salesRepEmployeeNumber` INTEGER, \n",
            "\t`creditLimit` DECIMAL(10, 2), \n",
            "\tPRIMARY KEY (`customerNumber`), \n",
            "\tCONSTRAINT customers_ibfk_1 FOREIGN KEY(`salesRepEmployeeNumber`) REFERENCES employees (`employeeNumber`)\n",
            ")DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB COLLATE utf8mb4_0900_ai_ci\n",
            "\n",
            "/*\n",
            "3 rows from customers table:\n",
            "customerNumber\tcustomerName\tcontactLastName\tcontactFirstName\tphone\taddressLine1\taddressLine2\tcity\tstate\tpostalCode\tcountry\tsalesRepEmployeeNumber\tcreditLimit\n",
            "103\tAtelier graphique\tSchmitt\tCarine \t40.32.2555\t54, rue Royale\tNone\tNantes\tNone\t44000\tFrance\t1370\t21000.00\n",
            "112\tSignal Gift Stores\tKing\tJean\t7025551838\t8489 Strong St.\tNone\tLas Vegas\tNV\t83030\tUSA\t1166\t71800.00\n",
            "114\tAustralian Collectors, Co.\tFerguson\tPeter\t03 9520 4555\t636 St Kilda Road\tLevel 3\tMelbourne\tVictoria\t3004\tAustralia\t1611\t117300.00\n",
            "*/\n",
            "\n",
            "\n",
            "CREATE TABLE employees (\n",
            "\t`employeeNumber` INTEGER NOT NULL, \n",
            "\t`lastName` VARCHAR(50) NOT NULL, \n",
            "\t`firstName` VARCHAR(50) NOT NULL, \n",
            "\textension VARCHAR(10) NOT NULL, \n",
            "\temail VARCHAR(100) NOT NULL, \n",
            "\t`officeCode` VARCHAR(10) NOT NULL, \n",
            "\t`reportsTo` INTEGER, \n",
            "\t`jobTitle` VARCHAR(50) NOT NULL, \n",
            "\tPRIMARY KEY (`employeeNumber`), \n",
            "\tCONSTRAINT employees_ibfk_1 FOREIGN KEY(`reportsTo`) REFERENCES employees (`employeeNumber`), \n",
            "\tCONSTRAINT employees_ibfk_2 FOREIGN KEY(`officeCode`) REFERENCES offices (`officeCode`)\n",
            ")DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB COLLATE utf8mb4_0900_ai_ci\n",
            "\n",
            "/*\n",
            "3 rows from employees table:\n",
            "employeeNumber\tlastName\tfirstName\textension\temail\tofficeCode\treportsTo\tjobTitle\n",
            "1002\tMurphy\tDiane\tx5800\tdmurphy@classicmodelcars.com\t1\tNone\tPresident\n",
            "1056\tPatterson\tMary\tx4611\tmpatterso@classicmodelcars.com\t1\t1002\tVP Sales\n",
            "1076\tFirrelli\tJeff\tx9273\tjfirrelli@classicmodelcars.com\t1\t1002\tVP Marketing\n",
            "*/\n",
            "\n",
            "\n",
            "CREATE TABLE offices (\n",
            "\t`officeCode` VARCHAR(10) NOT NULL, \n",
            "\tcity VARCHAR(50) NOT NULL, \n",
            "\tphone VARCHAR(50) NOT NULL, \n",
            "\t`addressLine1` VARCHAR(50) NOT NULL, \n",
            "\t`addressLine2` VARCHAR(50), \n",
            "\tstate VARCHAR(50), \n",
            "\tcountry VARCHAR(50) NOT NULL, \n",
            "\t`postalCode` VARCHAR(15) NOT NULL, \n",
            "\tterritory VARCHAR(10) NOT NULL, \n",
            "\tPRIMARY KEY (`officeCode`)\n",
            ")DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB COLLATE utf8mb4_0900_ai_ci\n",
            "\n",
            "/*\n",
            "3 rows from offices table:\n",
            "officeCode\tcity\tphone\taddressLine1\taddressLine2\tstate\tcountry\tpostalCode\tterritory\n",
            "1\tSan Francisco\t+1 650 219 4782\t100 Market Street\tSuite 300\tCA\tUSA\t94080\tNA\n",
            "2\tBoston\t+1 215 837 0825\t1550 Court Place\tSuite 102\tMA\tUSA\t02107\tNA\n",
            "3\tNYC\t+1 212 555 3000\t523 East 53rd Street\tapt. 5A\tNY\tUSA\t10022\tNA\n",
            "*/\n",
            "\n",
            "\n",
            "CREATE TABLE orderdetails (\n",
            "\t`orderNumber` INTEGER NOT NULL, \n",
            "\t`productCode` VARCHAR(15) NOT NULL, \n",
            "\t`quantityOrdered` INTEGER NOT NULL, \n",
            "\t`priceEach` DECIMAL(10, 2) NOT NULL, \n",
            "\t`orderLineNumber` SMALLINT NOT NULL, \n",
            "\tPRIMARY KEY (`orderNumber`, `productCode`), \n",
            "\tCONSTRAINT orderdetails_ibfk_1 FOREIGN KEY(`orderNumber`) REFERENCES orders (`orderNumber`), \n",
            "\tCONSTRAINT orderdetails_ibfk_2 FOREIGN KEY(`productCode`) REFERENCES products (`productCode`)\n",
            ")DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB COLLATE utf8mb4_0900_ai_ci\n",
            "\n",
            "/*\n",
            "3 rows from orderdetails table:\n",
            "orderNumber\tproductCode\tquantityOrdered\tpriceEach\torderLineNumber\n",
            "10100\tS18_1749\t30\t136.00\t3\n",
            "10100\tS18_2248\t50\t55.09\t2\n",
            "10100\tS18_4409\t22\t75.46\t4\n",
            "*/\n",
            "\n",
            "\n",
            "CREATE TABLE orders (\n",
            "\t`orderNumber` INTEGER NOT NULL, \n",
            "\t`orderDate` DATE NOT NULL, \n",
            "\t`requiredDate` DATE NOT NULL, \n",
            "\t`shippedDate` DATE, \n",
            "\tstatus VARCHAR(15) NOT NULL, \n",
            "\tcomments TEXT, \n",
            "\t`customerNumber` INTEGER NOT NULL, \n",
            "\tPRIMARY KEY (`orderNumber`), \n",
            "\tCONSTRAINT orders_ibfk_1 FOREIGN KEY(`customerNumber`) REFERENCES customers (`customerNumber`)\n",
            ")DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB COLLATE utf8mb4_0900_ai_ci\n",
            "\n",
            "/*\n",
            "3 rows from orders table:\n",
            "orderNumber\torderDate\trequiredDate\tshippedDate\tstatus\tcomments\tcustomerNumber\n",
            "10100\t2003-01-06\t2003-01-13\t2003-01-10\tShipped\tNone\t363\n",
            "10101\t2003-01-09\t2003-01-18\t2003-01-11\tShipped\tCheck on availability.\t128\n",
            "10102\t2003-01-10\t2003-01-18\t2003-01-14\tShipped\tNone\t181\n",
            "*/\n",
            "\n",
            "\n",
            "CREATE TABLE payments (\n",
            "\t`customerNumber` INTEGER NOT NULL, \n",
            "\t`checkNumber` VARCHAR(50) NOT NULL, \n",
            "\t`paymentDate` DATE NOT NULL, \n",
            "\tamount DECIMAL(10, 2) NOT NULL, \n",
            "\tPRIMARY KEY (`customerNumber`, `checkNumber`), \n",
            "\tCONSTRAINT payments_ibfk_1 FOREIGN KEY(`customerNumber`) REFERENCES customers (`customerNumber`)\n",
            ")DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB COLLATE utf8mb4_0900_ai_ci\n",
            "\n",
            "/*\n",
            "3 rows from payments table:\n",
            "customerNumber\tcheckNumber\tpaymentDate\tamount\n",
            "103\tHQ336336\t2004-10-19\t6066.78\n",
            "103\tJM555205\t2003-06-05\t14571.44\n",
            "103\tOM314933\t2004-12-18\t1676.14\n",
            "*/\n",
            "\n",
            "\n",
            "CREATE TABLE productlines (\n",
            "\t`productLine` VARCHAR(50) NOT NULL, \n",
            "\t`textDescription` VARCHAR(4000), \n",
            "\t`htmlDescription` MEDIUMTEXT, \n",
            "\timage MEDIUMBLOB, \n",
            "\tPRIMARY KEY (`productLine`)\n",
            ")DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB COLLATE utf8mb4_0900_ai_ci\n",
            "\n",
            "/*\n",
            "3 rows from productlines table:\n",
            "productLine\ttextDescription\thtmlDescription\timage\n",
            "Classic Cars\tAttention car enthusiasts: Make your wildest car ownership dreams come true. Whether you are looking\tNone\tNone\n",
            "Motorcycles\tOur motorcycles are state of the art replicas of classic as well as contemporary motorcycle legends \tNone\tNone\n",
            "Planes\tUnique, diecast airplane and helicopter replicas suitable for collections, as well as home, office o\tNone\tNone\n",
            "*/\n",
            "\n",
            "\n",
            "CREATE TABLE products (\n",
            "\t`productCode` VARCHAR(15) NOT NULL, \n",
            "\t`productName` VARCHAR(70) NOT NULL, \n",
            "\t`productLine` VARCHAR(50) NOT NULL, \n",
            "\t`productScale` VARCHAR(10) NOT NULL, \n",
            "\t`productVendor` VARCHAR(50) NOT NULL, \n",
            "\t`productDescription` TEXT NOT NULL, \n",
            "\t`quantityInStock` SMALLINT NOT NULL, \n",
            "\t`buyPrice` DECIMAL(10, 2) NOT NULL, \n",
            "\t`MSRP` DECIMAL(10, 2) NOT NULL, \n",
            "\tPRIMARY KEY (`productCode`), \n",
            "\tCONSTRAINT products_ibfk_1 FOREIGN KEY(`productLine`) REFERENCES productlines (`productLine`)\n",
            ")DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB COLLATE utf8mb4_0900_ai_ci\n",
            "\n",
            "/*\n",
            "3 rows from products table:\n",
            "productCode\tproductName\tproductLine\tproductScale\tproductVendor\tproductDescription\tquantityInStock\tbuyPrice\tMSRP\n",
            "S10_1678\t1969 Harley Davidson Ultimate Chopper\tMotorcycles\t1:10\tMin Lin Diecast\tThis replica features working kickstand, front suspension, gear-shift lever, footbrake lever, drive \t7933\t48.81\t95.70\n",
            "S10_1949\t1952 Alpine Renault 1300\tClassic Cars\t1:10\tClassic Metal Creations\tTurnable front wheels; steering function; detailed interior; detailed engine; opening hood; opening \t7305\t98.58\t214.30\n",
            "S10_2016\t1996 Moto Guzzi 1100i\tMotorcycles\t1:10\tHighway 66 Mini Classics\tOfficial Moto Guzzi logos and insignias, saddle bags located on side of motorcycle, detailed engine,\t6625\t68.99\t118.94\n",
            "*/\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import os\n",
        "os.environ[\"OPENAI_API_KEY\"] = \"\"\n",
        "os.environ[\"LANGCHAIN_TRACING_V2\"] = \"true\"\n",
        "os.environ[\"LANGCHAIN_PROJECT\"] = \"\"\n",
        "os.environ[\"LANGCHAIN_API_KEY\"] = \"\""
      ],
      "metadata": {
        "id": "Z6IfK8MV0wXy"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "from langchain.chains import create_sql_query_chain\n",
        "from langchain_openai import ChatOpenAI\n",
        "\n",
        "llm = ChatOpenAI(model=\"gpt-4o\", temperature=0)\n",
        "generate_query = create_sql_query_chain(llm, db)\n",
        "query = generate_query.invoke({\"question\": \"what is price of `1968 Ford Mustang`\"})\n",
        "# \"what is price of `1968 Ford Mustang`\"\n",
        "print(query)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "RiCC2ZNA006l",
        "outputId": "07825658-a25a-44e0-c955-dfda99036dae"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "```sql\n",
            "SELECT `buyPrice` \n",
            "FROM `products` \n",
            "WHERE `productName` = '1968 Ford Mustang' \n",
            "LIMIT 5;\n",
            "```\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import re\n",
        "\n",
        "def clean_sql_query(text: str) -> str:\n",
        "    \"\"\"\n",
        "    Clean SQL query by removing code block syntax, various SQL tags, backticks,\n",
        "    prefixes, and unnecessary whitespace while preserving the core SQL query.\n",
        "\n",
        "    Args:\n",
        "        text (str): Raw SQL query text that may contain code blocks, tags, and backticks\n",
        "\n",
        "    Returns:\n",
        "        str: Cleaned SQL query\n",
        "    \"\"\"\n",
        "    # Step 1: Remove code block syntax and any SQL-related tags\n",
        "    # This handles variations like ```sql, ```SQL, ```SQLQuery, etc.\n",
        "    block_pattern = r\"```(?:sql|SQL|SQLQuery|mysql|postgresql)?\\s*(.*?)\\s*```\"\n",
        "    text = re.sub(block_pattern, r\"\\1\", text, flags=re.DOTALL)\n",
        "\n",
        "    # Step 2: Handle \"SQLQuery:\" prefix and similar variations\n",
        "    # This will match patterns like \"SQLQuery:\", \"SQL Query:\", \"MySQL:\", etc.\n",
        "    prefix_pattern = r\"^(?:SQL\\s*Query|SQLQuery|MySQL|PostgreSQL|SQL)\\s*:\\s*\"\n",
        "    text = re.sub(prefix_pattern, \"\", text, flags=re.IGNORECASE)\n",
        "\n",
        "    # Step 3: Extract the first SQL statement if there's random text after it\n",
        "    # Look for a complete SQL statement ending with semicolon\n",
        "    sql_statement_pattern = r\"(SELECT.*?;)\"\n",
        "    sql_match = re.search(sql_statement_pattern, text, flags=re.IGNORECASE | re.DOTALL)\n",
        "    if sql_match:\n",
        "        text = sql_match.group(1)\n",
        "\n",
        "    # Step 4: Remove backticks around identifiers\n",
        "    text = re.sub(r'`([^`]*)`', r'\\1', text)\n",
        "\n",
        "    # Step 5: Normalize whitespace\n",
        "    # Replace multiple spaces with single space\n",
        "    text = re.sub(r'\\s+', ' ', text)\n",
        "\n",
        "    # Step 6: Preserve newlines for main SQL keywords to maintain readability\n",
        "    keywords = ['SELECT', 'FROM', 'WHERE', 'GROUP BY', 'HAVING', 'ORDER BY',\n",
        "               'LIMIT', 'JOIN', 'LEFT JOIN', 'RIGHT JOIN', 'INNER JOIN',\n",
        "               'OUTER JOIN', 'UNION', 'VALUES', 'INSERT', 'UPDATE', 'DELETE']\n",
        "\n",
        "    # Case-insensitive replacement for keywords\n",
        "    pattern = '|'.join(r'\\b{}\\b'.format(k) for k in keywords)\n",
        "    text = re.sub(f'({pattern})', r'\\n\\1', text, flags=re.IGNORECASE)\n",
        "\n",
        "    # Step 7: Final cleanup\n",
        "    # Remove leading/trailing whitespace and extra newlines\n",
        "    text = text.strip()\n",
        "    text = re.sub(r'\\n\\s*\\n', '\\n', text)\n",
        "\n",
        "    return text\n"
      ],
      "metadata": {
        "id": "c3mGf_Kz0AG-"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "query = clean_sql_query(query)\n",
        "print(query)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "YwJWFQSkrsyt",
        "outputId": "40070d6f-4aac-41d9-a833-02a99266fd23"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "SELECT buyPrice \n",
            "FROM products \n",
            "WHERE productName = '1968 Ford Mustang' \n",
            "LIMIT 5;\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "from langchain_community.tools.sql_database.tool import QuerySQLDataBaseTool\n",
        "execute_query = QuerySQLDataBaseTool(db=db)\n",
        "execute_query.invoke(query)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 36
        },
        "id": "_BxL9qfW1el6",
        "outputId": "b991c2d3-addb-48ce-ce43-8e3f6111d55e"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "\"[(Decimal('95.34'),)]\""
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            }
          },
          "metadata": {},
          "execution_count": 10
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "from langchain_core.runnables import RunnablePassthrough, RunnableLambda\n",
        "\n",
        "chain = generate_query | RunnableLambda(clean_sql_query) | execute_query\n",
        "\n",
        "chain.invoke({\"question\": \"How many orders are there\"})"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 36
        },
        "id": "Kf2TzUe203vc",
        "outputId": "bdcd4d94-1e87-4fa2-f4e8-87faab975aba"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "'[(326,)]'"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            }
          },
          "metadata": {},
          "execution_count": 12
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "chain.get_prompts()[0].pretty_print()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "WkyqNw9f1JE3",
        "outputId": "aebbb98d-992f-4043-894e-4130d111648a"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "You are a MySQL expert. Given an input question, first create a syntactically correct MySQL query to run, then look at the results of the query and return the answer to the input question.\n",
            "Unless the user specifies in the question a specific number of examples to obtain, query for at most 5 results using the LIMIT clause as per MySQL. You can order the results to return the most informative data in the database.\n",
            "Never query for all columns from a table. You must query only the columns that are needed to answer the question. Wrap each column name in backticks (`) to denote them as delimited identifiers.\n",
            "Pay attention to use only the column names you can see in the tables below. Be careful to not query for columns that do not exist. Also, pay attention to which column is in which table.\n",
            "Pay attention to use CURDATE() function to get the current date, if the question involves \"today\".\n",
            "\n",
            "Use the following format:\n",
            "\n",
            "Question: Question here\n",
            "SQLQuery: SQL Query to run\n",
            "SQLResult: Result of the SQLQuery\n",
            "Answer: Final answer here\n",
            "\n",
            "Only use the following tables:\n",
            "\u001b[33;1m\u001b[1;3m{table_info}\u001b[0m\n",
            "\n",
            "Question: \u001b[33;1m\u001b[1;3m{input}\u001b[0m\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "from operator import itemgetter\n",
        "\n",
        "from langchain_core.output_parsers import StrOutputParser\n",
        "from langchain_core.prompts import PromptTemplate\n",
        "from langchain_core.runnables import RunnablePassthrough, RunnableLambda\n",
        "\n",
        "answer_prompt = PromptTemplate.from_template(\n",
        "    \"\"\"Given the following user question, corresponding SQL query, and SQL result, answer the user question.\n",
        "\n",
        "Question: {question}\n",
        "SQL Query: {query}\n",
        "SQL Result: {result}\n",
        "Answer: \"\"\"\n",
        ")\n",
        "\n",
        "rephrase_answer = answer_prompt | llm | StrOutputParser()\n",
        "\n",
        "chain = (\n",
        "    RunnablePassthrough.assign(query=generate_query | RunnableLambda(clean_sql_query)).assign(\n",
        "        result=itemgetter(\"query\") | execute_query\n",
        "    )\n",
        "    | rephrase_answer\n",
        ")\n",
        "\n",
        "chain.invoke({\"question\": \"How many orders are there\"})"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 36
        },
        "id": "KVCYCPpU2s1d",
        "outputId": "77d5b84d-6608-4cd9-cda3-3f621abead67"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "'There are 326 orders.'"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            }
          },
          "metadata": {},
          "execution_count": 14
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "###Adding few-shot examples"
      ],
      "metadata": {
        "id": "uPkoDITyWfxG"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "examples = [\n",
        "    {\n",
        "        \"input\": \"List all customers in France with a credit limit over 20,000.\",\n",
        "        \"query\": \"SELECT * FROM customers WHERE country = 'France' AND creditLimit > 20000;\"\n",
        "    },\n",
        "    {\n",
        "        \"input\": \"Get the highest payment amount made by any customer.\",\n",
        "        \"query\": \"SELECT MAX(amount) FROM payments;\"\n",
        "    },\n",
        "    {\n",
        "        \"input\": \"Show product details for products in the 'Motorcycles' product line.\",\n",
        "        \"query\": \"SELECT * FROM products WHERE productLine = 'Motorcycles';\"\n",
        "    },\n",
        "    {\n",
        "        \"input\": \"Retrieve the names of employees who report to employee number 1002.\",\n",
        "        \"query\": \"SELECT firstName, lastName FROM employees WHERE reportsTo = 1002;\"\n",
        "    },\n",
        "    {\n",
        "        \"input\": \"List all products with a stock quantity less than 7000.\",\n",
        "        \"query\": \"SELECT productName, quantityInStock FROM products WHERE quantityInStock < 7000;\"\n",
        "    },\n",
        "    {\n",
        "     'input':\"what is price of `1968 Ford Mustang`\",\n",
        "     \"query\": \"SELECT `buyPrice`, `MSRP` FROM products  WHERE `productName` = '1968 Ford Mustang' LIMIT 1;\"\n",
        "    }\n",
        "]"
      ],
      "metadata": {
        "id": "qJZ8vYwD3I4m"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "from langchain_core.prompts import ChatPromptTemplate, MessagesPlaceholder,FewShotChatMessagePromptTemplate,PromptTemplate\n",
        "\n",
        "example_prompt = ChatPromptTemplate.from_messages(\n",
        "    [\n",
        "        (\"human\", \"{input}\\nSQLQuery:\"),\n",
        "        (\"ai\", \"{query}\"),\n",
        "    ]\n",
        ")\n",
        "few_shot_prompt = FewShotChatMessagePromptTemplate(\n",
        "    example_prompt=example_prompt,\n",
        "    examples=examples,\n",
        "    # input_variables=[\"input\",\"top_k\"],\n",
        "    input_variables=[\"input\"],\n",
        ")\n",
        "print(few_shot_prompt.format(input1=\"How many products are there?\"))"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "H3sLLayg3WV6",
        "outputId": "0abd9ee6-dab0-487d-e0c9-fd0f07e7a266"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Human: List all customers in France with a credit limit over 20,000.\n",
            "SQLQuery:\n",
            "AI: SELECT * FROM customers WHERE country = 'France' AND creditLimit > 20000;\n",
            "Human: Get the highest payment amount made by any customer.\n",
            "SQLQuery:\n",
            "AI: SELECT MAX(amount) FROM payments;\n",
            "Human: Show product details for products in the 'Motorcycles' product line.\n",
            "SQLQuery:\n",
            "AI: SELECT * FROM products WHERE productLine = 'Motorcycles';\n",
            "Human: Retrieve the names of employees who report to employee number 1002.\n",
            "SQLQuery:\n",
            "AI: SELECT firstName, lastName FROM employees WHERE reportsTo = 1002;\n",
            "Human: List all products with a stock quantity less than 7000.\n",
            "SQLQuery:\n",
            "AI: SELECT productName, quantityInStock FROM products WHERE quantityInStock < 7000;\n",
            "Human: what is price of `1968 Ford Mustang`\n",
            "SQLQuery:\n",
            "AI: SELECT `buyPrice`, `MSRP` FROM products  WHERE `productName` = '1968 Ford Mustang' LIMIT 1;\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "###Dynamic few-shot example selection"
      ],
      "metadata": {
        "id": "JRjiZ1O-WkX5"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "from langchain_chroma import Chroma\n",
        "from langchain_core.example_selectors import SemanticSimilarityExampleSelector\n",
        "from langchain_openai import OpenAIEmbeddings\n",
        "\n",
        "vectorstore = Chroma()\n",
        "vectorstore.delete_collection()\n",
        "example_selector = SemanticSimilarityExampleSelector.from_examples(\n",
        "    examples,\n",
        "    OpenAIEmbeddings(),\n",
        "    vectorstore,\n",
        "    k=2,\n",
        "    input_keys=[\"input\"],\n",
        ")\n",
        "example_selector.select_examples({\"input\": \"how many employees we have?\"})\n",
        "# example_selector.select_examples({\"input\": \"How many employees?\"})"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "FLLY5GkD4FaJ",
        "outputId": "619ea43f-c483-4fc0-e3f8-f1f0db1c49f0"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[{'input': 'Retrieve the names of employees who report to employee number 1002.',\n",
              "  'query': 'SELECT firstName, lastName FROM employees WHERE reportsTo = 1002;'},\n",
              " {'input': 'List all customers in France with a credit limit over 20,000.',\n",
              "  'query': \"SELECT * FROM customers WHERE country = 'France' AND creditLimit > 20000;\"}]"
            ]
          },
          "metadata": {},
          "execution_count": 17
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "few_shot_prompt = FewShotChatMessagePromptTemplate(\n",
        "    example_prompt=example_prompt,\n",
        "    example_selector=example_selector,\n",
        "    input_variables=[\"input\",\"top_k\"],\n",
        ")\n",
        "print(few_shot_prompt.format(input=\"How many products are there?\"))"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "4rID0Q0d4jHj",
        "outputId": "ab272db8-d97a-4d8b-e424-7eec496198da"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Human: List all products with a stock quantity less than 7000.\n",
            "SQLQuery:\n",
            "AI: SELECT productName, quantityInStock FROM products WHERE quantityInStock < 7000;\n",
            "Human: Show product details for products in the 'Motorcycles' product line.\n",
            "SQLQuery:\n",
            "AI: SELECT * FROM products WHERE productLine = 'Motorcycles';\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "Customizing prompts"
      ],
      "metadata": {
        "id": "72ZSFWBeW_tE"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "final_prompt = ChatPromptTemplate.from_messages(\n",
        "    [\n",
        "        (\"system\", \"You are a MySQL expert. Given an input question, create a syntactically correct MySQL query to run. Unless otherwise specificed.\\n\\nHere is the relevant table info: {table_info}\\n\\nBelow are a number of examples of questions and their corresponding SQL queries.\"),\n",
        "        few_shot_prompt,\n",
        "        (\"human\", \"{input}\"),\n",
        "    ]\n",
        ")\n",
        "print(final_prompt.format(input=\"How many products are there?\",table_info=\"some table info\"))"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "PCnlv2pd469x",
        "outputId": "cc107925-e90d-4d4b-c936-a95c7a5fcb3b"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "System: You are a MySQL expert. Given an input question, create a syntactically correct MySQL query to run. Unless otherwise specificed.\n",
            "\n",
            "Here is the relevant table info: some table info\n",
            "\n",
            "Below are a number of examples of questions and their corresponding SQL queries.\n",
            "Human: List all products with a stock quantity less than 7000.\n",
            "SQLQuery:\n",
            "AI: SELECT productName, quantityInStock FROM products WHERE quantityInStock < 7000;\n",
            "Human: Show product details for products in the 'Motorcycles' product line.\n",
            "SQLQuery:\n",
            "AI: SELECT * FROM products WHERE productLine = 'Motorcycles';\n",
            "Human: How many products are there?\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "generate_query = create_sql_query_chain(llm, db,final_prompt)\n",
        "\n",
        "chain = (\n",
        "    RunnablePassthrough.assign(query=generate_query | RunnableLambda(clean_sql_query)).assign(\n",
        "        result=itemgetter(\"query\") | execute_query\n",
        "    )\n",
        "    | rephrase_answer\n",
        ")\n",
        "\n",
        "chain.invoke({\"question\": \"How many csutomers with credit limit more than 50000\"})"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 36
        },
        "id": "eN7UFbYs5ULO",
        "outputId": "b4c8ced0-dd39-4df2-fa3d-c2a000a9d64f"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "'There are 85 customers with a credit limit more than 50,000.'"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            }
          },
          "metadata": {},
          "execution_count": 20
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "###Dynamic relevant table selection"
      ],
      "metadata": {
        "id": "VzLDjfdJWxQ0"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "from operator import itemgetter\n",
        "#from langchain.chains.openai_tools import create_extraction_chain_pydantic\n",
        "from pydantic import BaseModel, Field\n",
        "from typing import List\n",
        "import pandas as pd\n",
        "\n",
        "def get_table_details():\n",
        "    # Read the CSV file into a DataFrame\n",
        "    table_description = pd.read_csv(\"/content/database_table_descriptions.csv\")\n",
        "    table_docs = []\n",
        "\n",
        "    # Iterate over the DataFrame rows to create Document objects\n",
        "    table_details = \"\"\n",
        "    for index, row in table_description.iterrows():\n",
        "        table_details = table_details + \"Table Name:\" + row['table_name'] + \"\\n\" + \"Table Description:\" + row['description'] + \"\\n\\n\"\n",
        "\n",
        "    return table_details\n",
        "\n",
        "\n",
        "class Table(BaseModel):\n",
        "    \"\"\"Table in SQL database.\"\"\"\n",
        "\n",
        "    name: List[str] = Field(description=\"List of Name of tables in SQL database.\")\n",
        "\n",
        "# table_names = \"\\n\".join(db.get_usable_table_names())\n",
        "table_details = get_table_details()\n",
        "print(table_details)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "fuTfq0e-6Mgi",
        "outputId": "c6e8ffe0-e249-4df2-9c16-1290890aae71"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Table Name:productlines\n",
            "Table Description:Stores information about the different product lines offered by the company, including a unique name, textual description, HTML description, and image. Categorizes products into different lines.\n",
            "\n",
            "Table Name:products\n",
            "Table Description:Contains details of each product sold by the company, including code, name, product line, scale, vendor, description, stock quantity, buy price, and MSRP. Linked to the productlines table.\n",
            "\n",
            "Table Name:offices\n",
            "Table Description:Holds data on the company's sales offices, including office code, city, phone number, address, state, country, postal code, and territory. Each office is uniquely identified by its office code.\n",
            "\n",
            "Table Name:employees\n",
            "Table Description:Stores information about employees, including number, last name, first name, job title, contact info, and office code. Links to offices and maps organizational structure through the reportsTo attribute.\n",
            "\n",
            "Table Name:customers\n",
            "Table Description:Captures data on customers, including customer number, name, contact details, address, assigned sales rep, and credit limit. Central to managing customer relationships and sales processes.\n",
            "\n",
            "Table Name:payments\n",
            "Table Description:Records payments made by customers, tracking the customer number, check number, payment date, and amount. Linked to the customers table for financial tracking and account management.\n",
            "\n",
            "Table Name:orders\n",
            "Table Description:Details each sales order placed by customers, including order number, dates, status, comments, and customer number. Linked to the customers table, tracking sales transactions.\n",
            "\n",
            "Table Name:orderdetails\n",
            "Table Description:Describes individual line items for each sales order, including order number, product code, quantity, price, and order line number. Links orders to products, detailing the items sold.\n",
            "\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "table_details_prompt = ChatPromptTemplate.from_messages(\n",
        "        [\n",
        "            (\"system\", \"\"\"Return the names of ALL the SQL tables that MIGHT be relevant to the user question.\n",
        "                          The tables are:\n",
        "\n",
        "                          {table_details}\n",
        "\n",
        "                          Remember to include ALL POTENTIALLY RELEVANT tables, even if you're not sure that they're needed.\"\"\"),\n",
        "            (\"human\", \"{question}\")\n",
        "        ]\n",
        "    )\n",
        "\n",
        "structured_llm = llm.with_structured_output(Table)\n",
        "\n",
        "table_chain = table_details_prompt | structured_llm\n",
        "tables = table_chain.invoke({\"question\": \"give me details of customer and their order count\", \"table_details\":table_details})\n",
        "tables"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "GA1TjUgm7EST",
        "outputId": "bd85b094-8fa1-4915-fea2-a246f3ece42b"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "Table(name=['customers', 'orders'])"
            ]
          },
          "metadata": {},
          "execution_count": 27
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "def get_tables(table_response: Table) -> List[str]:\n",
        "    \"\"\"\n",
        "    Extracts the list of table names from a Table object.\n",
        "\n",
        "    Args:\n",
        "        table_response (Table): A Pydantic Table object containing table names.\n",
        "\n",
        "    Returns:\n",
        "        List[str]: A list of table names.\n",
        "    \"\"\"\n",
        "    return table_response.name\n",
        "\n",
        "select_table = {\"question\": itemgetter(\"question\"), \"table_details\": itemgetter(\"table_details\")} | table_chain | get_tables\n",
        "select_table.invoke({\"question\": \"give me details of customer and their order count\", \"table_details\":table_details})"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "rTf1SphE69Xf",
        "outputId": "b4abd7c9-fa49-4c64-bd46-ac55287867ed"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "['customers', 'orders']"
            ]
          },
          "metadata": {},
          "execution_count": 33
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "chain = (\n",
        "RunnablePassthrough.assign(table_names_to_use=select_table) |\n",
        "RunnablePassthrough.assign(query=generate_query | RunnableLambda(clean_sql_query)).assign(\n",
        "    result=itemgetter(\"query\") | execute_query\n",
        ")\n",
        "| rephrase_answer\n",
        ")\n",
        "chain.invoke({\"question\": \"How many cutomers with order count more than 5\", \"table_details\":table_details})"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 36
        },
        "id": "6DyhowwN71ts",
        "outputId": "c4292053-dcd6-4dd7-8e8c-038174e54de8"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "'There are 2 customers with an order count of more than 5.'"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            }
          },
          "metadata": {},
          "execution_count": 35
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "chain.invoke({\"question\": \"Can you list their names?\", \"table_details\":table_details})"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 53
        },
        "id": "2GGHwgnI8T7i",
        "outputId": "78ad0dc3-ed2e-4f96-c0f8-363d760ad031"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "\"The names of the customers are:\\n\\n1. Atelier graphique\\n2. La Rochelle Gifts\\n3. Saveley & Henriot, Co.\\n4. Daedalus Designs Imports\\n5. La Corne D'abondance, Co.\\n6. Mini Caravy\\n7. Alpha Cognac\\n8. Lyon Souveniers\\n9. Auto Associés & Cie.\\n10. Marseille Mini Autos\\n11. Reims Collectables\\n12. Auto Canal+ Petit\""
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            }
          },
          "metadata": {},
          "execution_count": 36
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "###Adding memory to the chatbot so that it answers follow-up questions related to the database.\n",
        "\n",
        "\n",
        "\n",
        "\n"
      ],
      "metadata": {
        "id": "dvy70bh0W28Q"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "final_prompt = ChatPromptTemplate.from_messages(\n",
        "    [\n",
        "        (\"system\", \"You are a MySQL expert. Given an input question, create a syntactically correct MySQL query to run. Unless otherwise specificed.\\n\\nHere is the relevant table info: {table_info}\\n\\nBelow are a number of examples of questions and their corresponding SQL queries. Those examples are just for referecne and hsould be considered while answering follow up questions\"),\n",
        "        few_shot_prompt,\n",
        "        MessagesPlaceholder(variable_name=\"messages\"),\n",
        "        (\"human\", \"{input}\"),\n",
        "    ]\n",
        ")\n",
        "print(final_prompt.format(input=\"How many products are there?\",table_info=\"some table info\",messages=[]))"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "StZ_P_5_87L6",
        "outputId": "7f7d8aae-2acd-4663-db5f-c1953b5ce68e"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "System: You are a MySQL expert. Given an input question, create a syntactically correct MySQL query to run. Unless otherwise specificed.\n",
            "\n",
            "Here is the relevant table info: some table info\n",
            "\n",
            "Below are a number of examples of questions and their corresponding SQL queries. Those examples are just for referecne and hsould be considered while answering follow up questions\n",
            "Human: List all products with a stock quantity less than 7000.\n",
            "SQLQuery:\n",
            "AI: SELECT productName, quantityInStock FROM products WHERE quantityInStock < 7000;\n",
            "Human: Show product details for products in the 'Motorcycles' product line.\n",
            "SQLQuery:\n",
            "AI: SELECT * FROM products WHERE productLine = 'Motorcycles';\n",
            "Human: How many products are there?\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "from langchain.memory import ChatMessageHistory\n",
        "history = ChatMessageHistory()\n",
        "\n",
        "generate_query = create_sql_query_chain(llm, db,final_prompt)\n",
        "\n",
        "chain = (\n",
        "RunnablePassthrough.assign(table_names_to_use=select_table) |\n",
        "RunnablePassthrough.assign(query=generate_query | RunnableLambda(clean_sql_query)).assign(\n",
        "    result=itemgetter(\"query\") | execute_query\n",
        ")\n",
        "| rephrase_answer\n",
        ")\n"
      ],
      "metadata": {
        "id": "OmXe-uWJ8lvt"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "question = \"How many cutomers with order count more than 5\"\n",
        "response = chain.invoke({\"question\": question,\"messages\":history.messages, \"table_details\":table_details})\n",
        "response"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 36
        },
        "id": "6x-mjNpo9lc8",
        "outputId": "64a854e9-71f1-4614-8f21-f89d2a2bedde"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "'There are 2 customers with an order count of more than 5.'"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            }
          },
          "metadata": {},
          "execution_count": 39
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "history.add_user_message(question)\n",
        "history.add_ai_message(response)\n"
      ],
      "metadata": {
        "id": "k98Hmj2Y9mvk"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "history.messages"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "x9DLodzOonza",
        "outputId": "3e1906be-ad02-478c-e6dd-6762f3390ba0"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[HumanMessage(content='How many cutomers with order count more than 5', additional_kwargs={}, response_metadata={}),\n",
              " AIMessage(content='There are 2 customers with an order count of more than 5.', additional_kwargs={}, response_metadata={})]"
            ]
          },
          "metadata": {},
          "execution_count": 41
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "response = chain.invoke({\"question\": \"Can you list there names?\",\"messages\":history.messages, \"table_details\":table_details})\n",
        "response"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 36
        },
        "id": "ptN_RIoI-CZX",
        "outputId": "424b3d03-3995-4560-fed9-4a1b0fc7c818"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "'The names of the customers are Mini Gifts Distributors Ltd. and Euro+ Shopping Channel.'"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            }
          },
          "metadata": {},
          "execution_count": 43
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "HVhNmBZcotjH"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}