Section_3-16-Working-with-SQL-data-RAG-app.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "087275c3",
   "metadata": {},
   "source": [
    "# Q&A chain and Agent over SQL Database"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "12eae6c0",
   "metadata": {},
   "source": [
    "## Architecture\n",
    "At a high level, the steps of any SQL chain and agent are:\n",
    "- Convert question to SQL query using LLM model.\n",
    "- Execute the SQL query\n",
    "- Getting the model to respond to user input using the query results."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "bc466eab",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "from dotenv import load_dotenv\n",
    "import openai\n",
    "from langchain_openai import ChatOpenAI"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "c8ea3c2e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Setup model\n",
    "load_dotenv()\n",
    "api_key = os.getenv('OPENAI_API_KEY')\n",
    "openai.api_key = api_key"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "83a08d65",
   "metadata": {},
   "outputs": [],
   "source": [
    "llm = ChatOpenAI(temperature=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "c1892780",
   "metadata": {},
   "outputs": [],
   "source": [
    "from langchain.utilities import SQLDatabase\n",
    "from langchain_experimental.sql import SQLDatabaseChain\n",
    "from langchain.prompts import PromptTemplate, HumanMessagePromptTemplate\n",
    "from langchain.schema import HumanMessage, SystemMessage\n",
    "from langchain.chains import create_sql_query_chain"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "04585328",
   "metadata": {},
   "outputs": [],
   "source": [
    "host = \"193.206.183.37\"\n",
    "port = \"3306\"\n",
    "username = \"root\"\n",
    "password = \"root\"\n",
    "database_schema = \"analytics\"\n",
    "mysql_uri = f\"mysql+pymysql://{username}:{password}@{host}:{port}/{database_schema}\"\n",
    "#print(mysql_uri)\n",
    "db = SQLDatabase.from_uri(mysql_uri,sample_rows_in_table_info = 5)\n",
    "chain = create_sql_query_chain(llm,db)\n",
    "#print(chain)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "af617937",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "mysql\n",
      "['customer', 'sale']\n"
     ]
    }
   ],
   "source": [
    "print(db.dialect)\n",
    "print(db.get_usable_table_names())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "0894e3b2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'[(1001,)]'"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.run(\"Select count(*) from customer LIMIT 10;\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "73b11b50",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'SELECT COUNT(`customerid`) AS total_customers FROM customer;'"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "response = chain.invoke({\"question\":\"How many customer are there?\"})\n",
    "response"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "e9ebab01",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'[(1001,)]'"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.run(response)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "60b76664",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"SELECT COUNT(*) AS total_customers\\nFROM customer\\nWHERE city = 'Bronx';\""
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "response = chain.invoke({\"question\":\"How many customers from bronx?\"})\n",
    "response"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "f6e89964",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'[(2,)]'"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.run(response)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "391795c9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT `firstname`, `lastname`, `phone`\n",
      "FROM `customer`\n",
      "WHERE `city` = 'Bronx'\n",
      "LIMIT 5;\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "\"[('Cecile', 'Clergeau', '(718) 405-2868'), ('Guoqing', 'Chen', '(718) 829-2436')]\""
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "response = chain.invoke({\"question\":\"How many customers from bronx and list their name and phone?\"})\n",
    "print(response)\n",
    "db.run(response)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "c72ee06e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT `state`, COUNT(`state`) AS state_count\n",
      "FROM `customer`\n",
      "GROUP BY `state`\n",
      "ORDER BY state_count DESC\n",
      "LIMIT 1;\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "\"[('NY', 79)]\""
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "response = chain.invoke({\"question\":\"Which state are customers most from?\"})\n",
    "print(response)\n",
    "db.run(response)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "1d1cc680",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT c.customerid, c.firstname, c.lastname, c.phone, s.shipping\n",
      "FROM customer c\n",
      "JOIN sale s ON c.customerid = s.customerid\n",
      "ORDER BY s.shipping DESC\n",
      "LIMIT 5;\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "\"[('customerid', 'firstname', 'lastname', 'phone', 'shipping'), ('3286', 'Cyrus', 'Gallagher', '(408) 626-8013', '9.95'), ('1376', 'Richard', 'Magner', '(205) 921-9237', '9.95'), ('1827', 'Kirill', 'Yurov', '(718) 434-6188', '9.95'), ('2609', 'Rajiv', 'Banker', '(812) 546-4257', '9.95')]\""
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "response = chain.invoke({\"question\":\"Give me the top 5 customers who paid the highest shipping charges\"})\n",
    "print(response)\n",
    "db.run(response)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}