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",
    "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": 2,
   "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": 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\\tMay 22 2023 22:22:02 \\n\\tCopyright (C) 2022 Microsoft Corporation\\n',)\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_1082044/2420609734.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_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": 5,
   "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": "code",
   "execution_count": 6,
   "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": 22,
   "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, top_k=10, verbose=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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 common/prompts.py\n",
    "print(db_chain.prompt.template)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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?\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "16c2b905-64b8-4fdc-8fc8-7c0274ec6a43",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/markdown": [
       "There were 0 patients hospitalized in Texas during July 2020. Nationwide data is not available.\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 grouped the results by state. I filtered the results to show only Texas (TX) and nationwide (ALL) data. The query returned only one result for Texas with 0 patients hospitalized during July 2020. Nationwide data was not available in the table. I used the following query:\n",
       "\n",
       "```sql\n",
       "SELECT [state], SUM([hospitalizedIncrease]) as TotalHospitalized\n",
       "FROM covidtracking\n",
       "WHERE [date] LIKE '2020-07%'\n",
       "GROUP BY [state]\n",
       "HAVING [state] = 'TX' OR [state] = 'ALL'\n",
       "```"
      ],
      "text/plain": [
       "<IPython.core.display.Markdown object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "printmd(db_chain(QUESTION)['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": 41,
   "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": 42,
   "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": 42,
     "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": 43,
   "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 answer should be in Markdown.\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 schema_sql_db 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 list_tables_sql_db 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: \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 [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": 44,
   "id": "6d7bb8cf-8661-4174-8185-c64b4b20670d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\u001b[1m> Entering new  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;3mI should check the schema of the covidtracking table to see if it has the necessary information.\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 has the necessary information. I will query the hospitalized column for the total number of patients hospitalized in Texas and nationwide during July 2020. I will use the date and state columns to filter the data.\n",
      "Action: sql_db_query_checker\n",
      "Action Input: \"SELECT SUM(hospitalized) FROM covidtracking WHERE state = 'TX' AND date >= '2020-07-01' AND date <= '2020-07-31'\"\u001b[0m\n",
      "Observation: \u001b[36;1m\u001b[1;3mThe original query seems to be correct. Here it is again:\n",
      "\n",
      "SELECT SUM(hospitalized) FROM covidtracking WHERE state = 'TX' AND date >= '2020-07-01' AND date <= '2020-07-31'\u001b[0m\n",
      "Thought:\u001b[32;1m\u001b[1;3mNow I will run the query to get the total number of patients hospitalized in Texas during July 2020.\n",
      "Action: sql_db_query\n",
      "Action Input: \"SELECT SUM(hospitalized) FROM covidtracking WHERE state = 'TX' AND date >= '2020-07-01' AND date <= '2020-07-31'\"\u001b[0m\n",
      "Observation: \u001b[36;1m\u001b[1;3m[(0.0,)]\u001b[0m\n",
      "Thought:\u001b[32;1m\u001b[1;3mI will now run a query to get the total number of patients hospitalized nationwide during July 2020.\n",
      "Action: sql_db_query_checker\n",
      "Action Input: \"SELECT SUM(hospitalized) FROM covidtracking WHERE date >= '2020-07-01' AND date <= '2020-07-31'\"\u001b[0m\n",
      "Observation: \u001b[36;1m\u001b[1;3mThe original query seems to be correct. Here it is again:\n",
      "\n",
      "SELECT SUM(hospitalized) FROM covidtracking WHERE date >= '2020-07-01' AND date <= '2020-07-31'\u001b[0m\n",
      "Thought:\u001b[32;1m\u001b[1;3mNow I will run the query to get the total number of patients hospitalized nationwide during July 2020.\n",
      "Action: sql_db_query\n",
      "Action Input: \"SELECT SUM(hospitalized) FROM covidtracking WHERE date >= '2020-07-01' AND date <= '2020-07-31'\"\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: There were 0 patients hospitalized in Texas during July 2020, and 5,535,849 patients hospitalized nationwide during the same period.\n",
      "\n",
      "Explanation:\n",
      "I queried the `covidtracking` table for the sum of the `hospitalized` column for Texas and nationwide during July 2020. The queries returned the total number of patients hospitalized in Texas and nationwide during that period. The results are as follows:\n",
      "\n",
      "- Texas: 0 patients\n",
      "- Nationwide: 5,535,849 patients\n",
      "\n",
      "I used the following queries:\n",
      "\n",
      "```sql\n",
      "SELECT SUM(hospitalized) FROM covidtracking WHERE state = 'TX' AND date >= '2020-07-01' AND date <= '2020-07-31'\n",
      "```\n",
      "\n",
      "```sql\n",
      "SELECT SUM(hospitalized) FROM covidtracking WHERE date >= '2020-07-01' AND date <= '2020-07-31'\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": 45,
   "id": "f23d2135-2199-474e-ae83-455aefc9b93b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/markdown": [
       "There were 0 patients hospitalized in Texas during July 2020, and 5,535,849 patients hospitalized nationwide during the same period.\n",
       "\n",
       "Explanation:\n",
       "I queried the `covidtracking` table for the sum of the `hospitalized` column for Texas and nationwide during July 2020. The queries returned the total number of patients hospitalized in Texas and nationwide during that period. The results are as follows:\n",
       "\n",
       "- Texas: 0 patients\n",
       "- Nationwide: 5,535,849 patients\n",
       "\n",
       "I used the following queries:\n",
       "\n",
       "```sql\n",
       "SELECT SUM(hospitalized) FROM covidtracking WHERE state = 'TX' AND date >= '2020-07-01' AND date <= '2020-07-31'\n",
       "```\n",
       "\n",
       "```sql\n",
       "SELECT SUM(hospitalized) FROM covidtracking WHERE date >= '2020-07-01' AND date <= '2020-07-31'\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": [
    "This answer is way better than only using the SQL Chain\n",
    "\n",
    "**IMPORTANT NOTE**: Runing the above cell multiple times still 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.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}