08-SQLDB_QA.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "66ab3cc5-aee4-415a-9391-1e5d37ccaf1d",
   "metadata": {},
   "source": [
    "# Skill 3: Q&A against a SQL Database (Azure SQL, Azure Fabric, Synapse, SQL Managed Instance, etc)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "306fc0a9-4044-441d-9ba7-f54f32e6ea9f",
   "metadata": {},
   "source": [
    "Now that we know (from the prior Notebook) how to query tabular data on a CSV file and how to perform data analysis with Python, let's try now to keep the data at is source and ask questions directly to a SQL Database.\n",
    "The goal of this notebook is to demonstrate how a LLM can understand a human question and translate that into a SQL query to get the answer. \n",
    "\n",
    "We will be using the Azure SQL Server that you created on the initial deployment. However the same code below works with any SQL database like Synapse for example.\n",
    "\n",
    "Let's begin.."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "c1fb79a3-4856-4721-988c-112813690a90",
   "metadata": {
    "metadata": {}
   },
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import pyodbc\n",
    "from langchain_openai import AzureChatOpenAI\n",
    "from langchain_community.agent_toolkits import create_sql_agent, SQLDatabaseToolkit\n",
    "from langchain_community.utilities.sql_database import SQLDatabase\n",
    "\n",
    "from langchain.agents import AgentExecutor\n",
    "from langchain.callbacks.manager import CallbackManager\n",
    "\n",
    "from common.prompts import MSSQL_AGENT_PREFIX\n",
    "\n",
    "from IPython.display import Markdown, HTML, display  \n",
    "\n",
    "from dotenv import load_dotenv\n",
    "load_dotenv(\"credentials.env\")\n",
    "\n",
    "def printmd(string):\n",
    "    display(Markdown(string))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "258a6e99-2d4f-4147-b8ee-c64c85296181",
   "metadata": {
    "metadata": {}
   },
   "outputs": [],
   "source": [
    "# Set the ENV variables that Langchain needs to connect to Azure OpenAI\n",
    "os.environ[\"OPENAI_API_VERSION\"] = os.environ[\"AZURE_OPENAI_API_VERSION\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1e8e0b32-a6b5-4b1c-943d-e57b737213fa",
   "metadata": {},
   "source": [
    "# Install MS SQL DB driver in your machine\n",
    "\n",
    "Use `lsb_release -a` to verify OS version details"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "9a353df6-0966-4e43-a914-6a2856eb140a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "No LSB modules are available.\n",
      "Distributor ID:\tUbuntu\n",
      "Description:\tUbuntu 20.04.6 LTS\n",
      "Release:\t20.04\n",
      "Codename:\tfocal\n"
     ]
    }
   ],
   "source": [
    "!lsb_release -a"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8112882c",
   "metadata": {},
   "source": [
    "## Using AML Instance"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3f6b661e",
   "metadata": {},
   "source": [
    "\n",
    "You might need the driver installed in order to talk to the SQL DB, so run the below cell once. Then restart the kernel and continue<br>\n",
    "[Microsoft Learn Reference](https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver16&tabs=ubuntu18-install%2Calpine17-install%2Cdebian8-install%2Credhat7-13-install%2Crhel7-offline)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "65fbffc7-e149-4eb3-a4db-9f114b06f205",
   "metadata": {},
   "outputs": [],
   "source": [
    "# !sudo ./download_odbc_driver.sh"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "357bca72",
   "metadata": {},
   "source": [
    "## Using Dev Container\n",
    "\n",
    "You might need the driver installed in order to talk to the SQL DB, so run the below cell once. Then restart the kernel and continue<br>\n",
    "[Microsoft Learn Reference](https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver16&tabs=ubuntu18-install%2Cdebian17-install%2Cdebian8-install%2Credhat7-13-install%2Crhel7-offline#17)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "59c04434",
   "metadata": {
    "metadata": {}
   },
   "outputs": [],
   "source": [
    "# !chmod +x ./download_odbc_driver_dev_container.sh\n",
    "# !./download_odbc_driver_dev_container.sh"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "35e30fa1-877d-4d3b-80b0-e17459c1e4f4",
   "metadata": {},
   "source": [
    "# Load Azure SQL DB with the Covid Tracking CSV Data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b4352dca-7159-4e41-983d-2c6951cf18db",
   "metadata": {},
   "source": [
    "The Azure SQL Database is currently empty, so we need to fill it up with data. Let's use the same data on the Covid CSV filed we used on the prior Notebook, that way we can compare results and methods. \n",
    "For this, you will need to type below the credentials you used at creation time."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "26739d89-e075-4098-ab38-92cccf9f9425",
   "metadata": {
    "metadata": {}
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Connection successful!\n",
      "('Microsoft SQL Azure (RTM) - 12.0.2000.8 \\n\\tJun 19 2024 16:01:48 \\n\\tCopyright (C) 2022 Microsoft Corporation\\n',)\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import create_engine, text\n",
    "from sqlalchemy.engine import URL\n",
    "import os\n",
    "\n",
    "# Configuration for the database connection\n",
    "db_config = {\n",
    "    'drivername': 'mssql+pyodbc',\n",
    "    'username': os.environ[\"SQL_SERVER_USERNAME\"] + '@' + os.environ[\"SQL_SERVER_NAME\"],\n",
    "    'password': os.environ[\"SQL_SERVER_PASSWORD\"],\n",
    "    'host': os.environ[\"SQL_SERVER_NAME\"],\n",
    "    'port': 1433,\n",
    "    'database': os.environ[\"SQL_SERVER_DATABASE\"],\n",
    "    'query': {'driver': 'ODBC Driver 17 for SQL Server'},\n",
    "}\n",
    "\n",
    "# Create a URL object for connecting to the database\n",
    "db_url = URL.create(**db_config)\n",
    "\n",
    "# Connect to the Azure SQL Database using the URL string\n",
    "engine = create_engine(db_url)\n",
    "\n",
    "# Test the connection using the SQLAlchemy 2.0 execution style\n",
    "with engine.connect() as conn:\n",
    "    try:\n",
    "        # Use the text() construct for safer SQL execution\n",
    "        result = conn.execute(text(\"SELECT @@VERSION\"))\n",
    "        version = result.fetchone()\n",
    "        print(\"Connection successful!\")\n",
    "        print(version)\n",
    "    except Exception as e:\n",
    "        print(e)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "acaf202c-33a1-4105-b506-c26f2080c1d8",
   "metadata": {
    "metadata": {}
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(pyodbc.ProgrammingError) ('42S01', \"[42S01] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]There is already an object named 'covidtracking' in the database. (2714) (SQLExecDirectW)\")\n",
      "[SQL: CREATE TABLE covidtracking (date VARCHAR(MAX), state VARCHAR(MAX), death FLOAT, deathConfirmed FLOAT, deathIncrease INT, deathProbable FLOAT, hospitalized FLOAT, hospitalizedCumulative FLOAT, hospitalizedCurrently FLOAT, hospitalizedIncrease INT, inIcuCumulative FLOAT, inIcuCurrently FLOAT, negative FLOAT, negativeIncrease INT, negativeTestsAntibody FLOAT, negativeTestsPeopleAntibody FLOAT, negativeTestsViral FLOAT, onVentilatorCumulative FLOAT, onVentilatorCurrently FLOAT, positive FLOAT, positiveCasesViral FLOAT, positiveIncrease INT, positiveScore INT, positiveTestsAntibody FLOAT, positiveTestsAntigen FLOAT, positiveTestsPeopleAntibody FLOAT, positiveTestsPeopleAntigen FLOAT, positiveTestsViral FLOAT, recovered FLOAT, totalTestEncountersViral FLOAT, totalTestEncountersViralIncrease INT, totalTestResults FLOAT, totalTestResultsIncrease INT, totalTestsAntibody FLOAT, totalTestsAntigen FLOAT, totalTestsPeopleAntibody FLOAT, totalTestsPeopleAntigen FLOAT, totalTestsPeopleViral FLOAT, totalTestsPeopleViralIncrease INT, totalTestsViral FLOAT, totalTestsViralIncrease INT)]\n",
      "(Background on this error at: https://sqlalche.me/e/20/f405)\n"
     ]
    }
   ],
   "source": [
    "# Read CSV file into a pandas dataframe\n",
    "csv_path = \"./data/all-states-history.csv\"\n",
    "df = pd.read_csv(csv_path).fillna(value = 0)\n",
    "\n",
    "# Infer column names and data types\n",
    "column_names = df.columns.tolist()\n",
    "column_types = df.dtypes.to_dict()\n",
    "\n",
    "# Generate SQL statement to create table\n",
    "table_name = 'covidtracking'\n",
    "\n",
    "create_table_sql = f\"CREATE TABLE {table_name} (\"\n",
    "for name, dtype in column_types.items():\n",
    "    if dtype == 'object':\n",
    "        create_table_sql += f\"{name} VARCHAR(MAX), \"\n",
    "    elif dtype == 'int64':\n",
    "        create_table_sql += f\"{name} INT, \"\n",
    "    elif dtype == 'float64':\n",
    "        create_table_sql += f\"{name} FLOAT, \"\n",
    "    elif dtype == 'bool':\n",
    "        create_table_sql += f\"{name} BIT, \"\n",
    "    elif dtype == 'datetime64[ns]':\n",
    "        create_table_sql += f\"{name} DATETIME, \"\n",
    "create_table_sql = create_table_sql[:-2] + \")\"\n",
    "\n",
    "try:\n",
    "    #Createse the table in Azure SQL\n",
    "    with engine.connect() as conn:\n",
    "        # Execute the create table SQL statement\n",
    "        conn.execute(text(create_table_sql))\n",
    "        print(\"Table\", table_name, \"successfully created\")\n",
    "    # Insert data into SQL Database\n",
    "    lower = 0\n",
    "    upper = 1000\n",
    "    limit = df.shape[0]\n",
    "\n",
    "    while lower < limit:\n",
    "        df[lower:upper].to_sql(table_name, con=engine, if_exists='append', index=False)\n",
    "        print(\"rows:\", lower, \"-\", upper, \"inserted\")\n",
    "        lower = upper\n",
    "        upper = min(upper + 1000, limit)\n",
    "\n",
    "except Exception as e:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "33ad46af-11a4-41a6-94af-15509fd9e16c",
   "metadata": {},
   "source": [
    "# Query with LLM"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ea2ef524-565a-4f28-9955-fce0d01bbe21",
   "metadata": {},
   "source": [
    "**Note**: We are here using Azure SQL, however the same code will work with Synapse, SQL Managed instance, or any other SQL engine. You just need to provide the right values for the ENV variables and it will connect succesfully."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "7faef3c0-8166-4f3b-a5e3-d30acfd65fd3",
   "metadata": {},
   "outputs": [],
   "source": [
    "llm = AzureChatOpenAI(deployment_name=os.environ[\"GPT4o_DEPLOYMENT_NAME\"], temperature=0.5, max_tokens=2000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "6cbe650c-9e0a-4209-9595-de13f2f1ee0a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Let's create the db object\n",
    "db = SQLDatabase.from_uri(db_url)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "ae80c022-415e-40d1-b205-1744a3164d70",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Natural Language question (query)\n",
    "QUESTION = \"\"\"\n",
    "How may patients were hospitalized during July 2020 in Texas. \n",
    "And nationwide as the total of all states? \n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "95052aba-d0c5-4883-a0b6-70c20e236b6a",
   "metadata": {},
   "source": [
    "### SQL Agent"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eb8b1352-d6d7-4319-a0b8-ae7b9c2fd234",
   "metadata": {},
   "source": [
    "LangChain has a SQL Agent which provides a more flexible way of interacting with SQL Databases than a chain. The main advantages of using the SQL Agent are:\n",
    "\n",
    "    It can answer questions based on the databases’ schema as well as on the databases’ content (like describing a specific table).\n",
    "    It can recover from errors by running a generated query, catching the traceback and regenerating it correctly.\n",
    "    It can query the database as many times as needed to answer the user question.\n",
    "    It will save tokens by only retrieving the schema from relevant tables.\n",
    "\n",
    "To initialize the agent we’ll use the `create_sql_agent` constructor. This agent uses the SQLDatabaseToolkit which contains tools to:\n",
    "\n",
    "    Create and execute queries\n",
    "    Check query syntax\n",
    "    Retrieve table descriptions\n",
    "    … and more"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "2b51fb36-68b5-4770-b5f1-c042a08e0a0f",
   "metadata": {},
   "outputs": [],
   "source": [
    "toolkit = SQLDatabaseToolkit(db=db, llm=llm)\n",
    "\n",
    "agent_executor = create_sql_agent(\n",
    "    prefix=MSSQL_AGENT_PREFIX,\n",
    "    llm=llm,\n",
    "    toolkit=toolkit,\n",
    "    top_k=30,\n",
    "    agent_type=\"openai-tools\",\n",
    "    verbose=True,\n",
    "\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "21c6c6f5-4a14-403f-a1d0-fe6b0c34a563",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[QuerySQLDataBaseTool(description=\"Input to this tool is a detailed and correct SQL query, output is a result from the database. If the query is not correct, an error message will be returned. If an error is returned, rewrite the query, check the query, and try again. If you encounter an issue with Unknown column 'xxxx' in 'field list', use sql_db_schema to query the correct table fields.\", db=<langchain_community.utilities.sql_database.SQLDatabase object at 0x7f392caeb910>),\n",
       " InfoSQLDatabaseTool(description='Input to this tool is a comma-separated list of tables, output is the schema and sample rows for those tables. Be sure that the tables actually exist by calling sql_db_list_tables first! Example Input: table1, table2, table3', db=<langchain_community.utilities.sql_database.SQLDatabase object at 0x7f392caeb910>),\n",
       " ListSQLDatabaseTool(db=<langchain_community.utilities.sql_database.SQLDatabase object at 0x7f392caeb910>),\n",
       " QuerySQLCheckerTool(description='Use this tool to double check if your query is correct before executing it. Always use this tool before executing a query with sql_db_query!', db=<langchain_community.utilities.sql_database.SQLDatabase object at 0x7f392caeb910>, llm=AzureChatOpenAI(client=<openai.resources.chat.completions.Completions object at 0x7f392cae97e0>, async_client=<openai.resources.chat.completions.AsyncCompletions object at 0x7f392cae8ca0>, temperature=0.5, openai_api_key=SecretStr('**********'), openai_proxy='', max_tokens=2000, azure_endpoint='https://gios-openai-northcentral.openai.azure.com', deployment_name='gpt-4o', openai_api_version='2024-05-01-preview', openai_api_type='azure'), llm_chain=LLMChain(prompt=PromptTemplate(input_variables=['dialect', 'query'], template='\\n{query}\\nDouble check the {dialect} query above for common mistakes, including:\\n- Using NOT IN with NULL values\\n- Using UNION when UNION ALL should have been used\\n- Using BETWEEN for exclusive ranges\\n- Data type mismatch in predicates\\n- Properly quoting identifiers\\n- Using the correct number of arguments for functions\\n- Casting to the correct data type\\n- Using the proper columns for joins\\n\\nIf there are any of the above mistakes, rewrite the query. If there are no mistakes, just reproduce the original query.\\n\\nOutput the final SQL query only.\\n\\nSQL Query: '), llm=AzureChatOpenAI(client=<openai.resources.chat.completions.Completions object at 0x7f392cae97e0>, async_client=<openai.resources.chat.completions.AsyncCompletions object at 0x7f392cae8ca0>, temperature=0.5, openai_api_key=SecretStr('**********'), openai_proxy='', max_tokens=2000, azure_endpoint='https://gios-openai-northcentral.openai.azure.com', deployment_name='gpt-4o', openai_api_version='2024-05-01-preview', openai_api_type='azure')))]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# As we know by now, Agents use expert/tools. Let's see which are the tools for this SQL Agent\n",
    "agent_executor.tools"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "6d7bb8cf-8661-4174-8185-c64b4b20670d",
   "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;3m\n",
      "Invoking: `sql_db_list_tables` with `{}`\n",
      "\n",
      "\n",
      "\u001b[0m\u001b[38;5;200m\u001b[1;3mcheckpoints, covidtracking, message_store\u001b[0m\u001b[32;1m\u001b[1;3m\n",
      "Invoking: `sql_db_schema` with `{'table_names': 'covidtracking'}`\n",
      "\n",
      "\n",
      "\u001b[0m\u001b[33;1m\u001b[1;3m\n",
      "CREATE TABLE covidtracking (\n",
      "\tdate VARCHAR(max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, \n",
      "\tstate VARCHAR(max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, \n",
      "\tdeath FLOAT(53) NULL, \n",
      "\t[deathConfirmed] FLOAT(53) NULL, \n",
      "\t[deathIncrease] BIGINT NULL, \n",
      "\t[deathProbable] FLOAT(53) NULL, \n",
      "\thospitalized FLOAT(53) NULL, \n",
      "\t[hospitalizedCumulative] FLOAT(53) NULL, \n",
      "\t[hospitalizedCurrently] FLOAT(53) NULL, \n",
      "\t[hospitalizedIncrease] BIGINT NULL, \n",
      "\t[inIcuCumulative] FLOAT(53) NULL, \n",
      "\t[inIcuCurrently] FLOAT(53) NULL, \n",
      "\tnegative FLOAT(53) NULL, \n",
      "\t[negativeIncrease] BIGINT NULL, \n",
      "\t[negativeTestsAntibody] FLOAT(53) NULL, \n",
      "\t[negativeTestsPeopleAntibody] FLOAT(53) NULL, \n",
      "\t[negativeTestsViral] FLOAT(53) NULL, \n",
      "\t[onVentilatorCumulative] FLOAT(53) NULL, \n",
      "\t[onVentilatorCurrently] FLOAT(53) NULL, \n",
      "\tpositive FLOAT(53) NULL, \n",
      "\t[positiveCasesViral] FLOAT(53) NULL, \n",
      "\t[positiveIncrease] BIGINT NULL, \n",
      "\t[positiveScore] BIGINT NULL, \n",
      "\t[positiveTestsAntibody] FLOAT(53) NULL, \n",
      "\t[positiveTestsAntigen] FLOAT(53) NULL, \n",
      "\t[positiveTestsPeopleAntibody] FLOAT(53) NULL, \n",
      "\t[positiveTestsPeopleAntigen] FLOAT(53) NULL, \n",
      "\t[positiveTestsViral] FLOAT(53) NULL, \n",
      "\trecovered FLOAT(53) NULL, \n",
      "\t[totalTestEncountersViral] FLOAT(53) NULL, \n",
      "\t[totalTestEncountersViralIncrease] BIGINT NULL, \n",
      "\t[totalTestResults] FLOAT(53) NULL, \n",
      "\t[totalTestResultsIncrease] BIGINT NULL, \n",
      "\t[totalTestsAntibody] FLOAT(53) NULL, \n",
      "\t[totalTestsAntigen] FLOAT(53) NULL, \n",
      "\t[totalTestsPeopleAntibody] FLOAT(53) NULL, \n",
      "\t[totalTestsPeopleAntigen] FLOAT(53) NULL, \n",
      "\t[totalTestsPeopleViral] FLOAT(53) NULL, \n",
      "\t[totalTestsPeopleViralIncrease] BIGINT NULL, \n",
      "\t[totalTestsViral] FLOAT(53) NULL, \n",
      "\t[totalTestsViralIncrease] BIGINT NULL\n",
      ")\n",
      "\n",
      "/*\n",
      "3 rows from covidtracking table:\n",
      "date\tstate\tdeath\tdeathConfirmed\tdeathIncrease\tdeathProbable\thospitalized\thospitalizedCumulative\thospitalizedCurrently\thospitalizedIncrease\tinIcuCumulative\tinIcuCurrently\tnegative\tnegativeIncrease\tnegativeTestsAntibody\tnegativeTestsPeopleAntibody\tnegativeTestsViral\tonVentilatorCumulative\tonVentilatorCurrently\tpositive\tpositiveCasesViral\tpositiveIncrease\tpositiveScore\tpositiveTestsAntibody\tpositiveTestsAntigen\tpositiveTestsPeopleAntibody\tpositiveTestsPeopleAntigen\tpositiveTestsViral\trecovered\ttotalTestEncountersViral\ttotalTestEncountersViralIncrease\ttotalTestResults\ttotalTestResultsIncrease\ttotalTestsAntibody\ttotalTestsAntigen\ttotalTestsPeopleAntibody\ttotalTestsPeopleAntigen\ttotalTestsPeopleViral\ttotalTestsPeopleViralIncrease\ttotalTestsViral\ttotalTestsViralIncrease\n",
      "2021-03-07\tAK\t305.0\t0.0\t0\t0.0\t1293.0\t1293.0\t33.0\t0\t0.0\t0.0\t0.0\t0\t0.0\t0.0\t1660758.0\t0.0\t2.0\t56886.0\t0.0\t0\t0\t0.0\t0.0\t0.0\t0.0\t68693.0\t0.0\t0.0\t0\t1731628.0\t0\t0.0\t0.0\t0.0\t0.0\t0.0\t0\t1731628.0\t0\n",
      "2021-03-07\tAL\t10148.0\t7963.0\t-1\t2185.0\t45976.0\t45976.0\t494.0\t0\t2676.0\t0.0\t1931711.0\t2087\t0.0\t0.0\t0.0\t1515.0\t0.0\t499819.0\t392077.0\t408\t0\t0.0\t0.0\t0.0\t0.0\t0.0\t295690.0\t0.0\t0\t2323788.0\t2347\t0.0\t0.0\t119757.0\t0.0\t2323788.0\t2347\t0.0\t0\n",
      "2021-03-07\tAR\t5319.0\t4308.0\t22\t1011.0\t14926.0\t14926.0\t335.0\t11\t0.0\t141.0\t2480716.0\t3267\t0.0\t0.0\t2480716.0\t1533.0\t65.0\t324818.0\t255726.0\t165\t0\t0.0\t0.0\t0.0\t81803.0\t0.0\t315517.0\t0.0\t0\t2736442.0\t3380\t0.0\t0.0\t0.0\t481311.0\t0.0\t0\t2736442.0\t3380\n",
      "*/\u001b[0m\u001b[32;1m\u001b[1;3m\n",
      "Invoking: `sql_db_query_checker` with `{'query': \"SELECT SUM(hospitalizedIncrease) AS total_hospitalized_texas FROM covidtracking WHERE state = 'TX' AND date LIKE '2020-07%'\"}`\n",
      "\n",
      "\n",
      "\u001b[0m\u001b[36;1m\u001b[1;3m```sql\n",
      "SELECT SUM(hospitalizedIncrease) AS total_hospitalized_texas \n",
      "FROM covidtracking \n",
      "WHERE state = 'TX' \n",
      "AND date LIKE '2020-07%'\n",
      "```\u001b[0m\u001b[32;1m\u001b[1;3m\n",
      "Invoking: `sql_db_query_checker` with `{'query': \"SELECT SUM(hospitalizedIncrease) AS total_hospitalized_nationwide FROM covidtracking WHERE date LIKE '2020-07%'\"}`\n",
      "\n",
      "\n",
      "\u001b[0m\u001b[36;1m\u001b[1;3m```sql\n",
      "SELECT SUM(hospitalizedIncrease) AS total_hospitalized_nationwide \n",
      "FROM covidtracking \n",
      "WHERE date LIKE '2020-07%'\n",
      "```\u001b[0m\u001b[32;1m\u001b[1;3m\n",
      "Invoking: `sql_db_query` with `{'query': \"SELECT SUM(hospitalizedIncrease) AS total_hospitalized_texas FROM covidtracking WHERE state = 'TX' AND date LIKE '2020-07%'\"}`\n",
      "\n",
      "\n",
      "\u001b[0m\u001b[36;1m\u001b[1;3m[(0,)]\u001b[0m\u001b[32;1m\u001b[1;3m\n",
      "Invoking: `sql_db_query` with `{'query': \"SELECT SUM(hospitalizedIncrease) AS total_hospitalized_nationwide FROM covidtracking WHERE date LIKE '2020-07%'\"}`\n",
      "\n",
      "\n",
      "\u001b[0m\u001b[36;1m\u001b[1;3m[(63105,)]\u001b[0m\u001b[32;1m\u001b[1;3mFinal Answer: During July 2020, there were no hospitalized patients reported in Texas. Nationwide, the total number of hospitalized patients was 63,105.\n",
      "\n",
      "Explanation:\n",
      "To determine the number of hospitalized patients during July 2020 in both Texas and nationwide, I queried the `covidtracking` table. The specific SQL queries used were:\n",
      "\n",
      "For Texas:\n",
      "```sql\n",
      "SELECT SUM(hospitalizedIncrease) AS total_hospitalized_texas \n",
      "FROM covidtracking \n",
      "WHERE state = 'TX' \n",
      "AND date LIKE '2020-07%'\n",
      "```\n",
      "\n",
      "For Nationwide:\n",
      "```sql\n",
      "SELECT SUM(hospitalizedIncrease) AS total_hospitalized_nationwide \n",
      "FROM covidtracking \n",
      "WHERE date LIKE '2020-07%'\n",
      "```\n",
      "\n",
      "The results showed that Texas had no hospitalized patients reported during that period, while the nationwide total was 63,105.\u001b[0m\n",
      "\n",
      "\u001b[1m> Finished chain.\u001b[0m\n"
     ]
    }
   ],
   "source": [
    "try:\n",
    "    response = agent_executor.invoke(QUESTION) \n",
    "except Exception as e:\n",
    "    response = str(e)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "f23d2135-2199-474e-ae83-455aefc9b93b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/markdown": [
       "Final Answer: During July 2020, there were no hospitalized patients reported in Texas. Nationwide, the total number of hospitalized patients was 63,105.\n",
       "\n",
       "Explanation:\n",
       "To determine the number of hospitalized patients during July 2020 in both Texas and nationwide, I queried the `covidtracking` table. The specific SQL queries used were:\n",
       "\n",
       "For Texas:\n",
       "```sql\n",
       "SELECT SUM(hospitalizedIncrease) AS total_hospitalized_texas \n",
       "FROM covidtracking \n",
       "WHERE state = 'TX' \n",
       "AND date LIKE '2020-07%'\n",
       "```\n",
       "\n",
       "For Nationwide:\n",
       "```sql\n",
       "SELECT SUM(hospitalizedIncrease) AS total_hospitalized_nationwide \n",
       "FROM covidtracking \n",
       "WHERE date LIKE '2020-07%'\n",
       "```\n",
       "\n",
       "The results showed that Texas had no hospitalized patients reported during that period, while the nationwide total was 63,105."
      ],
      "text/plain": [
       "<IPython.core.display.Markdown object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "printmd(response[\"output\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cfef208f-321c-490e-a50e-e92602daf125",
   "metadata": {},
   "source": [
    "**IMPORTANT NOTE**: If you don't specify the column name on the question, runing the above cell multiple times will yield diferent results some times. <br>\n",
    "The reason is:\n",
    "The column names are ambiguous, hence it is hard even for Humans to discern what are the right columns to use"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "56cbc405-26e2-471e-9626-2a0df07f5ddc",
   "metadata": {},
   "source": [
    "# Summary"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7381ea5f-7269-4e1f-8b0c-1e2c04bd84c0",
   "metadata": {},
   "source": [
    "In this notebook, we achieved our goal of Asking a Question in natural language to a dataset located on a SQL Database.  We did this by using purely prompt engineering (Langchain does it for us) and the cognitive power of GPT models.\n",
    "\n",
    "This process shows why it is NOT necessary to move the data from its original source as long as the source has an API and a common language we can use to interface with. LLMs have been trained on the whole public Github corpus, so it can pretty much understand most of the coding and database query languages that exists out there. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "02073623-91b4-40d6-8eaf-cb6d9c6a7a9a",
   "metadata": {},
   "source": [
    "# NEXT\n",
    "\n",
    "The Next Notebook will show you how to create a custom agent that connects to the internet using BING SEARCH API to answer questions grounded on search results with citations. Basically a clone of Bing Chat."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "84c169d9-ae2e-4188-b500-869d14b2579c",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.10 - SDK v2",
   "language": "python",
   "name": "python310-sdkv2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}