06-SQLDB_QA.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "66ab3cc5-aee4-415a-9391-1e5d37ccaf1d",
   "metadata": {},
   "source": [
    "# Q&A against a SQL Database"
   ]
  },
  {
   "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. 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": 1,
   "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 import SQLDatabaseChain\n",
    "from langchain.chains import SQLDatabaseSequentialChain\n",
    "from langchain.agents import AgentExecutor\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",
    "def printmd(string):\n",
    "    display(Markdown(string))\n",
    "\n",
    "# Don't mess with this unless you really know what you are doing\n",
    "AZURE_OPENAI_API_VERSION = \"2023-03-15-preview\"\n",
    "\n",
    "# Change these below with your own services credentials\n",
    "AZURE_OPENAI_ENDPOINT = \"ENTER YOUR VALUE\"\n",
    "AZURE_OPENAI_API_KEY = \"ENTER YOUR VALUE\"\n",
    "SQL_SERVER_ENDPOINT = \"ENTER YOUR VALUE\"  # Server Name example: sql-server-4njhjc5ogtyfm.database.windows.net\n",
    "SQL_SERVER_DATABASE = \"ENTER YOUR VALUE\"\n",
    "SQL_SERVER_USERNAME = \"ENTER YOUR VALUE\"\n",
    "SQL_SERVER_PASSWORD = \"ENTER YOUR VALUE\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "258a6e99-2d4f-4147-b8ee-c64c85296181",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Set the ENV variables \n",
    "os.environ[\"OPENAI_API_BASE\"] = os.environ[\"AZURE_OPENAI_ENDPOINT\"] = AZURE_OPENAI_ENDPOINT\n",
    "os.environ[\"OPENAI_API_KEY\"] = os.environ[\"AZURE_OPENAI_API_KEY\"] = AZURE_OPENAI_API_KEY\n",
    "os.environ[\"OPENAI_API_VERSION\"] = os.environ[\"AZURE_OPENAI_API_VERSION\"] = AZURE_OPENAI_API_VERSION\n",
    "os.environ[\"OPENAI_API_TYPE\"] = \"azure\"\n",
    "os.environ[\"SQL_SERVER_ENDPOINT\"] = SQL_SERVER_ENDPOINT\n",
    "os.environ[\"SQL_SERVER_DATABASE\"] = SQL_SERVER_DATABASE\n",
    "os.environ[\"SQL_SERVER_USERNAME\"] = SQL_SERVER_USERNAME\n",
    "os.environ[\"SQL_SERVER_PASSWORD\"] = SQL_SERVER_PASSWORD"
   ]
  },
  {
   "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": 3,
   "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": 4,
   "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\\tMar 30 2023 16:38:37 \\n\\tCopyright (C) 2022 Microsoft Corporation\\n',)\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_ENDPOINT\"],\n",
    "                'password': os.environ[\"SQL_SERVER_PASSWORD\"],\n",
    "                'host': os.environ[\"SQL_SERVER_ENDPOINT\"],\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": 12,
   "id": "acaf202c-33a1-4105-b506-c26f2080c1d8",
   "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(255), state VARCHAR(255), 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/14/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(255), \"\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",
    "    engine.execute(create_table_sql)\n",
    "except Exception as e:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "dc1db50c-2736-445e-af8a-885b5ec8acb8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Insert data into SQL Database\n",
    "try:\n",
    "    df.to_sql(table_name, con=engine, if_exists='replace', index=False)\n",
    "except Exception as e:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "33ad46af-11a4-41a6-94af-15509fd9e16c",
   "metadata": {},
   "source": [
    "# Query with LLM"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "7faef3c0-8166-4f3b-a5e3-d30acfd65fd3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create or LLM Langchain object using GPT-4 deployment\n",
    "llm = AzureChatOpenAI(deployment_name=\"gpt-4\", temperature=0, max_tokens=500)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "6cbe650c-9e0a-4209-9595-de13f2f1ee0a",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/anaconda/envs/azureml_py310_sdkv2/lib/python3.10/site-packages/langchain/chains/sql_database/base.py:63: UserWarning: Directly instantiating an SQLDatabaseChain with an llm is deprecated. Please instantiate with llm_chain argument or using the from_llm class method.\n",
      "  warnings.warn(\n"
     ]
    }
   ],
   "source": [
    "# Let's use a type of Chain made for this type of SQL work.  \n",
    "db = SQLDatabase.from_uri(db_url)\n",
    "db_chain = SQLDatabaseChain(llm=llm, database=db, prompt=MSSQL_PROMPT, verbose=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "ad678667-9031-4140-a060-2e5dc46799b0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "You are an MS SQL expert. Given an input question, first create a syntactically correct MS SQL query to run, then look at the results of the query and return the answer to the input question.\n",
      "\n",
      "Unless the user specifies in the question a specific number of examples to obtain, query for at most {top_k} results using the TOP clause as per MS SQL. You can order the results to return the most informative data in the database.\n",
      "\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 square brackets ([]) to denote them as delimited identifiers.\n",
      "\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",
      "\n",
      "**Do not use double quotes on the SQL query**. \n",
      "\n",
      "Your response should be in Markdown.\n",
      "\n",
      "** ALWAYS before giving the Final Answer, try another method**. Then reflect on the answers of the two methods you did and ask yourself if it answers correctly the original question. If you are not sure, try another method.\n",
      "If the runs does not give the same result, reflect and try again two more times until you have two runs that have the same result. If you still cannot arrive to a consistent result, say that you are not sure of the answer. But, if you are sure of the correct answer, create a beautiful and thorough response. DO NOT MAKE UP AN ANSWER OR USE PRIOR KNOWLEDGE, ONLY USE THE RESULTS OF THE CALCULATIONS YOU HAVE DONE. \n",
      "\n",
      "ALWAYS, as part of your final answer, explain how you got to the answer on a section that starts with: \n",
      "\n",
      "Explanation:\n",
      ". Include the SQL query as part of the explanation section.\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",
      "Explanation:\n",
      "\n",
      "For example:\n",
      "<=== Beginning of example\n",
      "\n",
      "Question: How many people died of covid in Texas in 2020?\n",
      "SQLQuery: SELECT [death] FROM covidtracking WHERE state = 'TX' AND date LIKE '2020%'\n",
      "SQLResult: [(27437.0,), (27088.0,), (26762.0,), (26521.0,), (26472.0,), (26421.0,), (26408.0,)]\n",
      "Answer: There were 27437 people who died of covid in Texas in 2020.\n",
      "\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",
      "Only use the following tables:\n",
      "{table_info}\n",
      "\n",
      "Question: {input}\n"
     ]
    }
   ],
   "source": [
    "# Let's check our prompt we created in app/prompts.py\n",
    "print(db_chain.prompt.template)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "ae80c022-415e-40d1-b205-1744a3164d70",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Natural Language question (query)\n",
    "query_str = 'How may patients in total were hospitalized during July 2020 in Texas, and nationwide?'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "16c2b905-64b8-4fdc-8fc8-7c0274ec6a43",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\u001b[1m> Entering new SQLDatabaseChain chain...\u001b[0m\n",
      "How may patients in total were hospitalized during July 2020 in Texas, and nationwide?\n",
      "SQLQuery:\u001b[32;1m\u001b[1;3mSELECT [state], SUM([hospitalizedIncrease]) as TotalHospitalized\n",
      "FROM covidtracking\n",
      "WHERE [date] LIKE '2020-07%' AND ([state] = 'TX' OR [state] = 'US')\n",
      "GROUP BY [state]\u001b[0m"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Retrying langchain.chat_models.openai.ChatOpenAI.completion_with_retry.<locals>._completion_with_retry in 1.0 seconds as it raised RateLimitError: The server is currently overloaded with other requests. Sorry about that! You can retry your request, or contact us through an Azure support request at: https://go.microsoft.com/fwlink/?linkid=2213926 if the error persists..\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "SQLResult: \u001b[33;1m\u001b[1;3m[('TX', 0)]\u001b[0m\n",
      "Answer:\u001b[32;1m\u001b[1;3mIn July 2020, there were 0 patients hospitalized in Texas, and no data available for nationwide hospitalizations.\n",
      "\n",
      "Explanation:\n",
      "I queried the covidtracking table for the state and the sum of hospitalizedIncrease column where the date starts with '2020-07' and the state is either 'TX' or 'US'. The query returned a list of tuples with the total number of patients hospitalized in Texas during July 2020. To answer the question, I looked at the result which shows 0 patients hospitalized in Texas, and no data available for nationwide hospitalizations. I used the following query:\n",
      "\n",
      "```sql\n",
      "SELECT [state], SUM([hospitalizedIncrease]) as TotalHospitalized\n",
      "FROM covidtracking\n",
      "WHERE [date] LIKE '2020-07%' AND ([state] = 'TX' OR [state] = 'US')\n",
      "GROUP BY [state]\n",
      "```\u001b[0m\n",
      "\u001b[1m> Finished chain.\u001b[0m\n"
     ]
    },
    {
     "data": {
      "text/markdown": [
       "In July 2020, there were 0 patients hospitalized in Texas, and no data available for nationwide hospitalizations.\n",
       "\n",
       "Explanation:\n",
       "I queried the covidtracking table for the state and the sum of hospitalizedIncrease column where the date starts with '2020-07' and the state is either 'TX' or 'US'. The query returned a list of tuples with the total number of patients hospitalized in Texas during July 2020. To answer the question, I looked at the result which shows 0 patients hospitalized in Texas, and no data available for nationwide hospitalizations. I used the following query:\n",
       "\n",
       "```sql\n",
       "SELECT [state], SUM([hospitalizedIncrease]) as TotalHospitalized\n",
       "FROM covidtracking\n",
       "WHERE [date] LIKE '2020-07%' AND ([state] = 'TX' OR [state] = 'US')\n",
       "GROUP BY [state]\n",
       "```"
      ],
      "text/plain": [
       "<IPython.core.display.Markdown object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "printmd(db_chain(query_str)['result'])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "95052aba-d0c5-4883-a0b6-70c20e236b6a",
   "metadata": {},
   "source": [
    "### To use or not use Agents"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "564867d7-f261-4e4c-99d3-37cf4e5c5000",
   "metadata": {},
   "source": [
    "As you can see above we achieved our goal of Question->SQL without using an Agent, we did it just by using a clever prompt. (If you want to see the different kind of prompts templates that come with langchain for sql chain, you can check it out [HERE](https://github.com/hwchase17/langchain/blob/master/langchain/chains/sql_database/prompt.py)). **So the question is, why do we need an Agent then?**\n",
    "\n",
    "**This is why**: As we explained on the prior Notebook, an agent is a process in which the LLM self-asks about what approach and steps to take, questions the validity of the results and if sure, provides the answer. The SQLDatabaseChain doesn't do all this analysis, but instead tries a one-shot query in order to answer the question, which is good! but not enough for complex questions. That's why it couldn't solve the part about nationwide, it needs multiple steps in order to solve the problem, one query is not enough.\n",
    "Notice that it did't pay atention to the prompt where we explicitly say to try two methods and reflect on the answer."
   ]
  },
  {
   "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": 19,
   "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",
    "    verbose=True\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "21c6c6f5-4a14-403f-a1d0-fe6b0c34a563",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['query_sql_db', 'schema_sql_db', 'list_tables_sql_db', 'query_checker_sql_db']"
      ]
     },
     "execution_count": 20,
     "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": 21,
   "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 10 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",
       "- **ALWAYS before giving the Final Answer, try another method**. Then reflect on the answers of the two methods you did and ask yourself if it answers correctly the original question. If you are not sure, try another method.\n",
       "If the runs does not give the same result, reflect and try again until you have two runs that have the same result. If you still cannot arrive to a consistent result, say that you are not sure of the answer. But, if you are sure of the correct answer, create a beautiful and thorough response using Markdown. DO NOT MAKE UP AN ANSWER OR USE PRIOR KNOWLEDGE, ONLY USE THE RESULTS OF THE CALCULATIONS YOU HAVE DONE. \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",
       "query_sql_db: \n",
       "    Input to this tool is a detailed and correct SQL query, output is a result from the database.\n",
       "    If the query is not correct, an error message will be returned.\n",
       "    If an error is returned, rewrite the query, check the query, and try again.\n",
       "    \n",
       "schema_sql_db: \n",
       "    Input to this tool is a comma-separated list of tables, output is the schema and sample rows for those tables.\n",
       "    Be sure that the tables actually exist by calling list_tables_sql_db first!\n",
       "\n",
       "    Example Input: \"table1, table2, table3\"\n",
       "    \n",
       "list_tables_sql_db: Input is an empty string, output is a comma separated list of tables in the database.\n",
       "query_checker_sql_db: \n",
       "    Use this tool to double check if your query is correct before executing it.\n",
       "    Always use this tool before executing a query with query_sql_db!\n",
       "    \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 [query_sql_db, schema_sql_db, list_tables_sql_db, query_checker_sql_db]. \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.\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": 22,
   "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: list_tables_sql_db\n",
      "Action Input: \"\"\u001b[0m\n",
      "Observation: \u001b[38;5;200m\u001b[1;3mcovidtracking\u001b[0m\n",
      "Thought:"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Retrying langchain.chat_models.openai.ChatOpenAI.completion_with_retry.<locals>._completion_with_retry in 1.0 seconds as it raised RateLimitError: The server is currently overloaded with other requests. Sorry about that! You can retry your request, or contact us through an Azure support request at: https://go.microsoft.com/fwlink/?linkid=2213926 if the error persists..\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[32;1m\u001b[1;3mI should check the schema of the covidtracking table to see what columns are available.\n",
      "Action: schema_sql_db\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] 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",
      "2020-09-19\tOR\t521.0\t0.0\t0\t0.0\t2356.0\t2356.0\t145.0\t15\t0.0\t32.0\t599080.0\t3506\t0.0\t0.0\t936227.0\t0.0\t11.0\t30342.0\t0.0\t492\t0\t0.0\t0.0\t0.0\t0.0\t54359.0\t5376.0\t0.0\t0\t990586.0\t8300\t0.0\t0.0\t0.0\t0.0\t627951.0\t3787\t990586.0\t8300\n",
      "2020-09-19\tPA\t7956.0\t0.0\t22\t0.0\t0.0\t0.0\t470.0\t0\t0.0\t0.0\t1746280.0\t13475\t0.0\t0.0\t0.0\t0.0\t47.0\t149845.0\t145335.0\t1162\t0\t0.0\t0.0\t0.0\t0.0\t0.0\t122872.0\t2763669.0\t32393\t2763669.0\t32393\t0.0\t0.0\t0.0\t0.0\t1891615.0\t14582\t0.0\t0\n",
      "2020-09-19\tPR\t605.0\t435.0\t6\t170.0\t0.0\t0.0\t403.0\t0\t0.0\t61.0\t305972.0\t0\t0.0\t0.0\t395291.0\t0.0\t39.0\t19807.0\t19807.0\t694\t0\t21186.0\t0.0\t0.0\t0.0\t20103.0\t0.0\t0.0\t0\t325779.0\t694\t0.0\t0.0\t0.0\t0.0\t0.0\t0\t415664.0\t0\n",
      "*/\u001b[0m\n",
      "Thought:"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Retrying langchain.chat_models.openai.ChatOpenAI.completion_with_retry.<locals>._completion_with_retry in 2.0 seconds as it raised RateLimitError: Requests to the Creates a completion for the chat message Operation under Azure OpenAI API version 2023-03-15-preview have exceeded call rate limit of your current OpenAI S0 pricing tier. Please retry after 1 second. Please go here: https://aka.ms/oai/quotaincrease if you would like to further increase the default rate limit..\n",
      "Retrying langchain.chat_models.openai.ChatOpenAI.completion_with_retry.<locals>._completion_with_retry in 4.0 seconds as it raised RateLimitError: The server is currently overloaded with other requests. Sorry about that! You can retry your request, or contact us through an Azure support request at: https://go.microsoft.com/fwlink/?linkid=2213926 if the error persists..\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\u001b[1m> Entering new AgentExecutor chain...\u001b[0m\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Retrying langchain.chat_models.openai.ChatOpenAI.completion_with_retry.<locals>._completion_with_retry in 1.0 seconds as it raised RateLimitError: The server is currently overloaded with other requests. Sorry about that! You can retry your request, or contact us through an Azure support request at: https://go.microsoft.com/fwlink/?linkid=2213926 if the error persists..\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[32;1m\u001b[1;3mAction: list_tables_sql_db\n",
      "Action Input: \"\"\u001b[0m\n",
      "Observation: \u001b[38;5;200m\u001b[1;3mcovidtracking\u001b[0m\n",
      "Thought:"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Retrying langchain.chat_models.openai.ChatOpenAI.completion_with_retry.<locals>._completion_with_retry in 1.0 seconds as it raised RateLimitError: Requests to the Creates a completion for the chat message Operation under Azure OpenAI API version 2023-03-15-preview have exceeded call rate limit of your current OpenAI S0 pricing tier. Please retry after 4 seconds. Please go here: https://aka.ms/oai/quotaincrease if you would like to further increase the default rate limit..\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[32;1m\u001b[1;3mI should check the schema of the covidtracking table to see what columns are available.\n",
      "Action: schema_sql_db\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] 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",
      "2020-09-19\tOR\t521.0\t0.0\t0\t0.0\t2356.0\t2356.0\t145.0\t15\t0.0\t32.0\t599080.0\t3506\t0.0\t0.0\t936227.0\t0.0\t11.0\t30342.0\t0.0\t492\t0\t0.0\t0.0\t0.0\t0.0\t54359.0\t5376.0\t0.0\t0\t990586.0\t8300\t0.0\t0.0\t0.0\t0.0\t627951.0\t3787\t990586.0\t8300\n",
      "2020-09-19\tPA\t7956.0\t0.0\t22\t0.0\t0.0\t0.0\t470.0\t0\t0.0\t0.0\t1746280.0\t13475\t0.0\t0.0\t0.0\t0.0\t47.0\t149845.0\t145335.0\t1162\t0\t0.0\t0.0\t0.0\t0.0\t0.0\t122872.0\t2763669.0\t32393\t2763669.0\t32393\t0.0\t0.0\t0.0\t0.0\t1891615.0\t14582\t0.0\t0\n",
      "2020-09-19\tPR\t605.0\t435.0\t6\t170.0\t0.0\t0.0\t403.0\t0\t0.0\t61.0\t305972.0\t0\t0.0\t0.0\t395291.0\t0.0\t39.0\t19807.0\t19807.0\t694\t0\t21186.0\t0.0\t0.0\t0.0\t20103.0\t0.0\t0.0\t0\t325779.0\t694\t0.0\t0.0\t0.0\t0.0\t0.0\t0\t415664.0\t0\n",
      "*/\u001b[0m\n",
      "Thought:"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Retrying langchain.chat_models.openai.ChatOpenAI.completion_with_retry.<locals>._completion_with_retry in 2.0 seconds as it raised RateLimitError: Requests to the Creates a completion for the chat message Operation under Azure OpenAI API version 2023-03-15-preview have exceeded call rate limit of your current OpenAI S0 pricing tier. Please retry after 3 seconds. Please go here: https://aka.ms/oai/quotaincrease if you would like to further increase the default rate limit..\n",
      "Retrying langchain.chat_models.openai.ChatOpenAI.completion_with_retry.<locals>._completion_with_retry in 4.0 seconds as it raised RateLimitError: Requests to the Creates a completion for the chat message Operation under Azure OpenAI API version 2023-03-15-preview have exceeded call rate limit of your current OpenAI S0 pricing tier. Please retry after 1 second. Please go here: https://aka.ms/oai/quotaincrease if you would like to further increase the default rate limit..\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[32;1m\u001b[1;3mI should query the `covidtracking` table for the `hospitalized` column where the date is between '2020-07-01' and '2020-07-31' for Texas and nationwide. I will sum the `hospitalized` column for each state and then sum the results for nationwide.\n",
      "Action: query_checker_sql_db\n",
      "Action Input: \"SELECT state, SUM(hospitalized) as total_hospitalized FROM covidtracking WHERE date >= '2020-07-01' AND date <= '2020-07-31' GROUP BY state\"\u001b[0m\n",
      "Observation: \u001b[36;1m\u001b[1;3mThe original query seems to be correct. Here it is again:\n",
      "\n",
      "SELECT state, SUM(hospitalized) as total_hospitalized\n",
      "FROM covidtracking\n",
      "WHERE date >= '2020-07-01' AND date <= '2020-07-31'\n",
      "GROUP BY state\u001b[0m\n",
      "Thought:\u001b[32;1m\u001b[1;3mNow that I have checked the query, I will execute it to get the total hospitalized patients for each state during July 2020.\n",
      "Action: query_sql_db\n",
      "Action Input: \"SELECT state, SUM(hospitalized) as total_hospitalized FROM covidtracking WHERE date >= '2020-07-01' AND date <= '2020-07-31' GROUP BY state\"\u001b[0m\n",
      "Observation: \u001b[36;1m\u001b[1;3m[('AK', 3262.0), ('AL', 206707.0), ('AR', 63487.0), ('AS', 0.0), ('AZ', 207664.0), ('CA', 0.0), ('CO', 185231.0), ('CT', 328185.0), ('DC', 0.0), ('DE', 0.0), ('FL', 632246.0), ('GA', 450151.0), ('GU', 0.0), ('HI', 4294.0), ('IA', 0.0), ('ID', 16963.0), ('IL', 0.0), ('IN', 241508.0), ('KS', 44066.0), ('KY', 90292.0), ('LA', 0.0), ('MA', 361385.0), ('MD', 361964.0), ('ME', 11520.0), ('MI', 0.0), ('MN', 141456.0), ('MO', 0.0), ('MP', 92.0), ('MS', 113481.0), ('MT', 4796.0), ('NC', 0.0), ('ND', 8987.0), ('NE', 45380.0), ('NH', 19742.0), ('NJ', 645497.0), ('NM', 71517.0), ('NV', 0.0), ('NY', 0.0), ('OH', 287655.0), ('OK', 70306.0), ('OR', 39881.0), ('PA', 0.0), ('PR', 0.0), ('RI', 64820.0), ('SC', 121328.0), ('SD', 23337.0), ('TN', 110346.0), ('TX', 0.0), ('UT', 59989.0), ('VA', 217638.0), ('VI', 0.0), ('VT', 0.0), ('WA', 152219.0), ('WI', 124195.0), ('WV', 0.0), ('WY', 4262.0)]\u001b[0m\n",
      "Thought:\u001b[32;1m\u001b[1;3mI need to sum the total_hospitalized column for all states to get the nationwide total and find the total for Texas.\n",
      "Action: query_sql_db\n",
      "Action Input: \"SELECT SUM(total_hospitalized) as nationwide_total FROM (SELECT state, SUM(hospitalized) as total_hospitalized FROM covidtracking WHERE date >= '2020-07-01' AND date <= '2020-07-31' GROUP BY state) as state_totals\"\u001b[0m\n",
      "Observation: \u001b[36;1m\u001b[1;3m[(5535849.0,)]\u001b[0m\n",
      "Thought:\u001b[32;1m\u001b[1;3mI now know the final answer.\n",
      "Final Answer: In Texas, there were 0 patients hospitalized during July 2020. Nationwide, there were 5,535,849 patients hospitalized during July 2020.\n",
      "\n",
      "Explanation:\n",
      "I queried the `covidtracking` table for the `hospitalized` column where the date is between '2020-07-01' and '2020-07-31' for each state. I then summed the `hospitalized` column for each state and found the total for Texas and nationwide. The query returned 0 for Texas and 5,535,849 for nationwide. I used the following queries:\n",
      "\n",
      "```sql\n",
      "SELECT state, SUM(hospitalized) as total_hospitalized\n",
      "FROM covidtracking\n",
      "WHERE date >= '2020-07-01' AND date <= '2020-07-31'\n",
      "GROUP BY state\n",
      "```\n",
      "\n",
      "```sql\n",
      "SELECT SUM(total_hospitalized) as nationwide_total\n",
      "FROM (SELECT state, SUM(hospitalized) as total_hospitalized\n",
      "      FROM covidtracking\n",
      "      WHERE date >= '2020-07-01' AND date <= '2020-07-31'\n",
      "      GROUP BY state) as state_totals\n",
      "```\u001b[0m\n",
      "\n",
      "\u001b[1m> Finished chain.\u001b[0m\n"
     ]
    },
    {
     "data": {
      "text/markdown": [
       "In Texas, there were 0 patients hospitalized during July 2020. Nationwide, there were 5,535,849 patients hospitalized during July 2020.\n",
       "\n",
       "Explanation:\n",
       "I queried the `covidtracking` table for the `hospitalized` column where the date is between '2020-07-01' and '2020-07-31' for each state. I then summed the `hospitalized` column for each state and found the total for Texas and nationwide. The query returned 0 for Texas and 5,535,849 for nationwide. I used the following queries:\n",
       "\n",
       "```sql\n",
       "SELECT state, SUM(hospitalized) as total_hospitalized\n",
       "FROM covidtracking\n",
       "WHERE date >= '2020-07-01' AND date <= '2020-07-31'\n",
       "GROUP BY state\n",
       "```\n",
       "\n",
       "```sql\n",
       "SELECT SUM(total_hospitalized) as nationwide_total\n",
       "FROM (SELECT state, SUM(hospitalized) as total_hospitalized\n",
       "      FROM covidtracking\n",
       "      WHERE date >= '2020-07-01' AND date <= '2020-07-31'\n",
       "      GROUP BY state) as state_totals\n",
       "```"
      ],
      "text/plain": [
       "<IPython.core.display.Markdown object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "for i in range(5):\n",
    "    try:\n",
    "        response = agent_executor.run(query_str)\n",
    "        break\n",
    "    except:\n",
    "        response = \"Error too many failed retries\"\n",
    "        continue\n",
    "        \n",
    "printmd(response)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "a785e77a-d55c-432d-bd6c-4de9bdb42420",
   "metadata": {},
   "outputs": [],
   "source": [
    "## This answer is way better than only using the SQL Chain\n",
    "## IMPORTANT NOTE: Runing the above cell multiple times still will yield diferent results some times. 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 guide you on how we stick everything together. How do we use the features of the past notebooks and create a brain agent that can respond to any request accordingly."
   ]
  }
 ],
 "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.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}