07-SQLDB_QA.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "66ab3cc5-aee4-415a-9391-1e5d37ccaf1d",
   "metadata": {},
   "source": [
    "# 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, 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 so advanced as GPT-4 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. The server should be created on the Resource Group where the Azure Cognitive Search service is located.\n",
    "\n",
    "Let's begin.."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "c1fb79a3-4856-4721-988c-112813690a90",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import pyodbc\n",
    "from langchain.chat_models import AzureChatOpenAI\n",
    "from langchain.agents import create_sql_agent\n",
    "from langchain.agents.agent_toolkits import SQLDatabaseToolkit\n",
    "from langchain.sql_database import SQLDatabase\n",
    "from langchain.agents import AgentExecutor\n",
    "from langchain.callbacks.manager import CallbackManager\n",
    "\n",
    "from common.prompts import MSSQL_PROMPT, MSSQL_AGENT_PREFIX, MSSQL_AGENT_FORMAT_INSTRUCTIONS\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": 3,
   "id": "258a6e99-2d4f-4147-b8ee-c64c85296181",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Set the ENV variables that Langchain needs to connect to Azure OpenAI\n",
    "os.environ[\"OPENAI_API_BASE\"] = os.environ[\"AZURE_OPENAI_ENDPOINT\"]\n",
    "os.environ[\"OPENAI_API_KEY\"] = os.environ[\"AZURE_OPENAI_API_KEY\"]\n",
    "os.environ[\"OPENAI_API_VERSION\"] = os.environ[\"AZURE_OPENAI_API_VERSION\"]\n",
    "os.environ[\"OPENAI_API_TYPE\"] = \"azure\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1e8e0b32-a6b5-4b1c-943d-e57b737213fa",
   "metadata": {},
   "source": [
    "# Install MS SQL DB driver in your machine"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9a353df6-0966-4e43-a914-6a2856eb140a",
   "metadata": {},
   "source": [
    "We need the driver installed on this compute in order to talk to the SQL DB, so run the below cell once<br>\n",
    "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": "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": 5,
   "id": "26739d89-e075-4098-ab38-92cccf9f9425",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Connection successful!\n",
      "('Microsoft SQL Azure (RTM) - 12.0.2000.8 \\n\\tJul  8 2023 12:00:47 \\n\\tCopyright (C) 2022 Microsoft Corporation\\n',)\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_902797/2845560204.py:27: RemovedIn20Warning: Deprecated API features detected! These feature(s) are not compatible with SQLAlchemy 2.0. To prevent incompatible upgrades prior to updating applications, ensure requirements files are pinned to \"sqlalchemy<2.0\". Set environment variable SQLALCHEMY_WARN_20=1 to show all deprecation warnings.  Set environment variable SQLALCHEMY_SILENCE_UBER_WARNING=1 to silence this message. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)\n",
      "  result = engine.execute(\"SELECT @@Version\")\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy.engine.url import URL\n",
    "\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",
    "# Print the resulting URL string\n",
    "# print(db_url)\n",
    "\n",
    "# Connect to the Azure SQL Database using the URL string\n",
    "engine = create_engine(db_url)\n",
    "\n",
    "# Test the connection\n",
    "try:\n",
    "    conn = engine.connect()\n",
    "    print(\"Connection successful!\")\n",
    "    result = engine.execute(\"SELECT @@Version\")\n",
    "    for row in result:\n",
    "        print(row)\n",
    "    conn.close()\n",
    "    \n",
    "except OperationalError:\n",
    "    print(\"Connection failed.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "acaf202c-33a1-4105-b506-c26f2080c1d8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Table covidtracking succesfully created\n",
      "rows: 0 - 1000 inserted\n",
      "rows: 1000 - 2000 inserted\n",
      "rows: 2000 - 3000 inserted\n",
      "rows: 3000 - 4000 inserted\n",
      "rows: 4000 - 5000 inserted\n",
      "rows: 5000 - 6000 inserted\n",
      "rows: 6000 - 7000 inserted\n",
      "rows: 7000 - 8000 inserted\n",
      "rows: 8000 - 9000 inserted\n",
      "rows: 9000 - 10000 inserted\n",
      "rows: 10000 - 11000 inserted\n",
      "rows: 11000 - 12000 inserted\n",
      "rows: 12000 - 13000 inserted\n",
      "rows: 13000 - 14000 inserted\n",
      "rows: 14000 - 15000 inserted\n",
      "rows: 15000 - 16000 inserted\n",
      "rows: 16000 - 17000 inserted\n",
      "rows: 17000 - 18000 inserted\n",
      "rows: 18000 - 19000 inserted\n",
      "rows: 19000 - 20000 inserted\n",
      "rows: 20000 - 20780 inserted\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",
    "    engine.execute(create_table_sql)\n",
    "    print(\"Table\",table_name,\"succesfully 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 ENV variables and it will connect succesfully."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "7faef3c0-8166-4f3b-a5e3-d30acfd65fd3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create or LLM Langchain object using GPT-4 deployment\n",
    "# Again we need GPT-4. It is necesary in the use of Agents. GPT-35-Turbo will make many mistakes.\n",
    "llm = AzureChatOpenAI(deployment_name=\"gpt-4\", temperature=0, max_tokens=500)"
   ]
  },
  {
   "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": 12,
   "id": "ae80c022-415e-40d1-b205-1744a3164d70",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Natural Language question (query)\n",
    "QUESTION = \"How may patients were hospitalized during July 2020 in Texas, and nationwide as the total of all states? Use the hospitalizedIncrease column\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "95052aba-d0c5-4883-a0b6-70c20e236b6a",
   "metadata": {},
   "source": [
    "### SQL Agent"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eb8b1352-d6d7-4319-a0b8-ae7b9c2fd234",
   "metadata": {},
   "source": [
    "Let's use an agent now and see how ReAct framework solves the problem."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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",
    "    format_instructions = MSSQL_AGENT_FORMAT_INSTRUCTIONS,\n",
    "    llm=llm,\n",
    "    toolkit=toolkit,\n",
    "    top_k=30,\n",
    "    verbose=True\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "21c6c6f5-4a14-403f-a1d0-fe6b0c34a563",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['sql_db_query', 'sql_db_schema', 'sql_db_list_tables', 'sql_db_query_checker']"
      ]
     },
     "execution_count": 14,
     "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.agent.allowed_tools"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "1cae3488-5334-4fbb-ab97-a710af07f966",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/markdown": [
       "\n",
       "\n",
       "You are an agent designed to interact with a SQL database.\n",
       "## Instructions:\n",
       "- Given an input question, create a syntactically correct mssql query to run, then look at the results of the query and return the answer.\n",
       "- Unless the user specifies a specific number of examples they wish to obtain, **ALWAYS** limit your query to at most 30 results.\n",
       "- You can order the results by a relevant column to return the most interesting examples in the database.\n",
       "- Never query for all the columns from a specific table, only ask for the relevant columns given the question.\n",
       "- You have access to tools for interacting with the database.\n",
       "- You MUST double check your query before executing it. If you get an error while executing a query, rewrite the query and try again.\n",
       "- DO NOT make any DML statements (INSERT, UPDATE, DELETE, DROP etc.) to the database.\n",
       "- DO NOT MAKE UP AN ANSWER OR USE PRIOR KNOWLEDGE, ONLY USE THE RESULTS OF THE CALCULATIONS YOU HAVE DONE. \n",
       "- Your response should be in Markdown. However, **when running  a SQL Query  in \"Action Input\", do not include the markdown backticks**. Those are only for formatting the response, not for executing the command.\n",
       "- ALWAYS, as part of your final answer, explain how you got to the answer on a section that starts with: \"Explanation:\". Include the SQL query as part of the explanation section.\n",
       "- If the question does not seem related to the database, just return \"I don't know\" as the answer.\n",
       "- Only use the below tools. Only use the information returned by the below tools to construct your final answer.\n",
       "\n",
       "## Tools:\n",
       "\n",
       "\n",
       "\n",
       "sql_db_query: 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', using sql_db_schema to query the correct table fields.\n",
       "sql_db_schema: 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'\n",
       "sql_db_list_tables: Input is an empty string, output is a comma separated list of tables in the database.\n",
       "sql_db_query_checker: 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!\n",
       "\n",
       "\n",
       "\n",
       "## Use the following format:\n",
       "\n",
       "Question: the input question you must answer. \n",
       "Thought: you should always think about what to do. \n",
       "Action: the action to take, should be one of [sql_db_query, sql_db_schema, sql_db_list_tables, sql_db_query_checker]. \n",
       "Action Input: the input to the action. \n",
       "Observation: the result of the action. \n",
       "... (this Thought/Action/Action Input/Observation can repeat N times)\n",
       "Thought: I now know the final answer. \n",
       "Final Answer: the final answer to the original input question. \n",
       "\n",
       "Example of Final Answer:\n",
       "<=== Beginning of example\n",
       "\n",
       "Action: query_sql_db\n",
       "Action Input: SELECT TOP (10) [death] FROM covidtracking WHERE state = 'TX' AND date LIKE '2020%'\n",
       "Observation: [(27437.0,), (27088.0,), (26762.0,), (26521.0,), (26472.0,), (26421.0,), (26408.0,)]\n",
       "Thought:I now know the final answer\n",
       "Final Answer: There were 27437 people who died of covid in Texas in 2020.\n",
       "\n",
       "Explanation:\n",
       "I queried the `covidtracking` table for the `death` column where the state is 'TX' and the date starts with '2020'. The query returned a list of tuples with the number of deaths for each day in 2020. To answer the question, I took the sum of all the deaths in the list, which is 27437. \n",
       "I used the following query\n",
       "\n",
       "```sql\n",
       "SELECT [death] FROM covidtracking WHERE state = 'TX' AND date LIKE '2020%'\"\n",
       "```\n",
       "===> End of Example\n",
       "\n",
       "\n",
       "\n",
       "Begin!\n",
       "\n",
       "Question: {input}\n",
       "Thought: I should look at the tables in the database to see what I can query.  Then I should query the schema of the most relevant tables.\n",
       "{agent_scratchpad}"
      ],
      "text/plain": [
       "<IPython.core.display.Markdown object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# And let's see now our clever crafted prompt\n",
    "printmd(agent_executor.agent.llm_chain.prompt.template)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "6d7bb8cf-8661-4174-8185-c64b4b20670d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\u001b[1m> Entering new AgentExecutor chain...\u001b[0m\n",
      "\u001b[32;1m\u001b[1;3mAction: sql_db_list_tables\n",
      "Action Input: \"\"\u001b[0m\n",
      "Observation: \u001b[38;5;200m\u001b[1;3mcovidtracking\u001b[0m\n",
      "Thought:\u001b[32;1m\u001b[1;3mThe `covidtracking` table seems to be the most relevant one for this question. I should check its schema to understand its structure and the data it contains.\n",
      "Action: sql_db_schema\n",
      "Action Input: \"covidtracking\" \u001b[0m\n",
      "Observation: \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] INTEGER 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] INTEGER NULL, \n",
      "\t[inIcuCumulative] FLOAT(53) NULL, \n",
      "\t[inIcuCurrently] FLOAT(53) NULL, \n",
      "\tnegative FLOAT(53) NULL, \n",
      "\t[negativeIncrease] INTEGER 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] INTEGER NULL, \n",
      "\t[positiveScore] INTEGER 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] INTEGER NULL, \n",
      "\t[totalTestResults] FLOAT(53) NULL, \n",
      "\t[totalTestResultsIncrease] INTEGER 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] INTEGER NULL, \n",
      "\t[totalTestsViral] FLOAT(53) NULL, \n",
      "\t[totalTestsViralIncrease] INTEGER 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\n",
      "Thought:\u001b[32;1m\u001b[1;3mThe `covidtracking` table contains the data I need. The `hospitalizedIncrease` column represents the number of new hospitalizations, the `state` column represents the state, and the `date` column represents the date. I will write a query to sum the `hospitalizedIncrease` for Texas and for all states during July 2020.\n",
      "Action: sql_db_query_checker\n",
      "Action Input: \n",
      "\"SELECT SUM(hospitalizedIncrease) as TotalHospitalized \n",
      "FROM covidtracking \n",
      "WHERE date LIKE '2020-07%' AND state = 'TX'\"\u001b[0m\n",
      "Observation: \u001b[36;1m\u001b[1;3mSELECT SUM(hospitalizedIncrease) as TotalHospitalized \n",
      "FROM covidtracking \n",
      "WHERE date LIKE '2020-07%' AND state = 'TX'\u001b[0m\n",
      "Thought:\u001b[32;1m\u001b[1;3mThe query syntax is correct. Now I will run this query to get the number of patients hospitalized in Texas during July 2020.\n",
      "Action: sql_db_query\n",
      "Action Input: \n",
      "\"SELECT SUM(hospitalizedIncrease) as TotalHospitalized \n",
      "FROM covidtracking \n",
      "WHERE date LIKE '2020-07%' AND state = 'TX'\"\u001b[0m\n",
      "Observation: \u001b[36;1m\u001b[1;3m[(0,)]\u001b[0m\n",
      "Thought:\u001b[32;1m\u001b[1;3mThe result indicates that there were no new hospitalizations in Texas during July 2020. Now, I will write and run a query to get the total number of patients hospitalized in all states during July 2020.\n",
      "Action: sql_db_query_checker\n",
      "Action Input: \n",
      "\"SELECT SUM(hospitalizedIncrease) as TotalHospitalized \n",
      "FROM covidtracking \n",
      "WHERE date LIKE '2020-07%'\"\u001b[0m\n",
      "Observation: \u001b[36;1m\u001b[1;3mSELECT SUM(hospitalizedIncrease) as TotalHospitalized \n",
      "FROM covidtracking \n",
      "WHERE date LIKE '2020-07%'\u001b[0m\n",
      "Thought:\u001b[32;1m\u001b[1;3mThe query syntax is correct. Now I will run this query to get the total number of patients hospitalized in all states during July 2020.\n",
      "Action: sql_db_query\n",
      "Action Input: \n",
      "\"SELECT SUM(hospitalizedIncrease) as TotalHospitalized \n",
      "FROM covidtracking \n",
      "WHERE date LIKE '2020-07%'\"\u001b[0m\n",
      "Observation: \u001b[36;1m\u001b[1;3m[(63105,)]\u001b[0m\n",
      "Thought:\u001b[32;1m\u001b[1;3mI now know the final answer.\n",
      "Final Answer: During July 2020, there were no new hospitalizations in Texas and 63,105 new hospitalizations nationwide.\n",
      "\n",
      "Explanation:\n",
      "I queried the `covidtracking` table for the sum of the `hospitalizedIncrease` column where the date starts with '2020-07' and the state is 'TX'. The query returned 0, indicating that there were no new hospitalizations in Texas during July 2020. \n",
      "\n",
      "I used the following query:\n",
      "\n",
      "```sql\n",
      "SELECT SUM(hospitalizedIncrease) as TotalHospitalized \n",
      "FROM covidtracking \n",
      "WHERE date LIKE '2020-07%' AND state = 'TX'\n",
      "```\n",
      "\n",
      "Then, I queried the `covidtracking` table for the sum of the `hospitalizedIncrease` column where the date starts with '2020-07'. The query returned 63105, indicating that there were 63,105 new hospitalizations nationwide during July 2020.\n",
      "\n",
      "I used the following query:\n",
      "\n",
      "```sql\n",
      "SELECT SUM(hospitalizedIncrease) as TotalHospitalized \n",
      "FROM covidtracking \n",
      "WHERE date LIKE '2020-07%'\n",
      "```\u001b[0m\n",
      "\n",
      "\u001b[1m> Finished chain.\u001b[0m\n"
     ]
    }
   ],
   "source": [
    "for i in range(2):\n",
    "    try:\n",
    "        response = agent_executor.run(QUESTION) \n",
    "        break\n",
    "    except Exception as e:\n",
    "        response = str(e)\n",
    "        continue"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "f23d2135-2199-474e-ae83-455aefc9b93b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/markdown": [
       "During July 2020, there were no new hospitalizations in Texas and 63,105 new hospitalizations nationwide.\n",
       "\n",
       "Explanation:\n",
       "I queried the `covidtracking` table for the sum of the `hospitalizedIncrease` column where the date starts with '2020-07' and the state is 'TX'. The query returned 0, indicating that there were no new hospitalizations in Texas during July 2020. \n",
       "\n",
       "I used the following query:\n",
       "\n",
       "```sql\n",
       "SELECT SUM(hospitalizedIncrease) as TotalHospitalized \n",
       "FROM covidtracking \n",
       "WHERE date LIKE '2020-07%' AND state = 'TX'\n",
       "```\n",
       "\n",
       "Then, I queried the `covidtracking` table for the sum of the `hospitalizedIncrease` column where the date starts with '2020-07'. The query returned 63105, indicating that there were 63,105 new hospitalizations nationwide during July 2020.\n",
       "\n",
       "I used the following query:\n",
       "\n",
       "```sql\n",
       "SELECT SUM(hospitalizedIncrease) as TotalHospitalized \n",
       "FROM covidtracking \n",
       "WHERE date LIKE '2020-07%'\n",
       "```"
      ],
      "text/plain": [
       "<IPython.core.display.Markdown object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "printmd(response)"
   ]
  },
  {
   "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-4.\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. GPT-4 has 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 REACT 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."
   ]
  }
 ],
 "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
}