08-SQLDB_QA.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "66ab3cc5-aee4-415a-9391-1e5d37ccaf1d",
   "metadata": {},
   "source": [
    "# Skill 3: Q&A against a SQL Database (Azure SQL, Azure Fabric, Synapse, SQL Managed Instance, etc)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "306fc0a9-4044-441d-9ba7-f54f32e6ea9f",
   "metadata": {},
   "source": [
    "Now that we know (from the prior Notebook) how to query tabular data on a CSV file and how to perform data analysis with Python, let's try now to keep the data at is source and ask questions directly to a SQL Database.\n",
    "The goal of this notebook is to demonstrate how a LLM can understand a human question and translate that into a SQL query to get the answer. \n",
    "\n",
    "We will be using the Azure SQL Server that you created on the initial deployment. However the same code below works with any SQL database like Synapse for example.\n",
    "\n",
    "Let's begin.."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "c1fb79a3-4856-4721-988c-112813690a90",
   "metadata": {
    "metadata": {},
    "tags": []
   },
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import pyodbc\n",
    "from langchain_openai import AzureChatOpenAI\n",
    "from langchain_community.agent_toolkits import SQLDatabaseToolkit\n",
    "from langchain_community.utilities.sql_database import SQLDatabase\n",
    "from langgraph.prebuilt import create_react_agent\n",
    "\n",
    "\n",
    "from common.prompts import MSSQL_AGENT_PROMPT_TEXT\n",
    "\n",
    "from IPython.display import Markdown, HTML, display  \n",
    "\n",
    "from dotenv import load_dotenv\n",
    "load_dotenv(\"credentials.env\")\n",
    "\n",
    "def printmd(string):\n",
    "    display(Markdown(string))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "258a6e99-2d4f-4147-b8ee-c64c85296181",
   "metadata": {
    "metadata": {},
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Set the ENV variables that Langchain needs to connect to Azure OpenAI\n",
    "os.environ[\"OPENAI_API_VERSION\"] = os.environ[\"AZURE_OPENAI_API_VERSION\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1e8e0b32-a6b5-4b1c-943d-e57b737213fa",
   "metadata": {},
   "source": [
    "# Install MS SQL DB driver in your machine\n",
    "\n",
    "Use `lsb_release -a` to verify OS version details"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "9a353df6-0966-4e43-a914-6a2856eb140a",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "No LSB modules are available.\n",
      "Distributor ID:\tUbuntu\n",
      "Description:\tUbuntu 20.04.6 LTS\n",
      "Release:\t20.04\n",
      "Codename:\tfocal\n"
     ]
    }
   ],
   "source": [
    "!lsb_release -a"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8112882c",
   "metadata": {},
   "source": [
    "## Using AML Instance"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3f6b661e",
   "metadata": {},
   "source": [
    "\n",
    "You might need the driver installed in order to talk to the SQL DB, so run the below cell once. Then restart the kernel and continue<br>\n",
    "[Microsoft Learn Reference](https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver16&tabs=ubuntu18-install%2Calpine17-install%2Cdebian8-install%2Credhat7-13-install%2Crhel7-offline)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "65fbffc7-e149-4eb3-a4db-9f114b06f205",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# !sudo ./download_odbc_driver.sh"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "357bca72",
   "metadata": {},
   "source": [
    "## Using Dev Container\n",
    "\n",
    "You might need the driver installed in order to talk to the SQL DB, so run the below cell once. Then restart the kernel and continue<br>\n",
    "[Microsoft Learn Reference](https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver16&tabs=ubuntu18-install%2Cdebian17-install%2Cdebian8-install%2Credhat7-13-install%2Crhel7-offline#17)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "59c04434",
   "metadata": {
    "metadata": {},
    "tags": []
   },
   "outputs": [],
   "source": [
    "# !chmod +x ./download_odbc_driver_dev_container.sh\n",
    "# !./download_odbc_driver_dev_container.sh"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "35e30fa1-877d-4d3b-80b0-e17459c1e4f4",
   "metadata": {},
   "source": [
    "# Load Azure SQL DB with the Covid Tracking CSV Data\n",
    "\n",
    "**Note**: We are here using Azure SQL, however the same code will work with Synapse, SQL Managed instance, or any other SQL engine. You just need to provide the right values for the ENV variables and it will connect succesfully."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b4352dca-7159-4e41-983d-2c6951cf18db",
   "metadata": {},
   "source": [
    "The Azure SQL Database is currently empty, so we need to fill it up with data. Let's use the same data on the Covid CSV filed we used on the prior Notebook, that way we can compare results and methods. \n",
    "For this, you will need to type below the credentials you used at creation time."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "26739d89-e075-4098-ab38-92cccf9f9425",
   "metadata": {
    "metadata": {},
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Connection successful!\n",
      "('Microsoft SQL Azure (RTM) - 12.0.2000.8 \\n\\tOct  2 2024 11:51:41 \\n\\tCopyright (C) 2022 Microsoft Corporation\\n',)\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import create_engine, text\n",
    "from sqlalchemy.engine import URL\n",
    "import os\n",
    "\n",
    "# Configuration for the database connection\n",
    "db_config = {\n",
    "    'drivername': 'mssql+pyodbc',\n",
    "    'username': os.environ[\"SQL_SERVER_USERNAME\"] + '@' + os.environ[\"SQL_SERVER_NAME\"],\n",
    "    'password': os.environ[\"SQL_SERVER_PASSWORD\"],\n",
    "    'host': os.environ[\"SQL_SERVER_NAME\"],\n",
    "    'port': 1433,\n",
    "    'database': os.environ[\"SQL_SERVER_DATABASE\"],\n",
    "    'query': {'driver': 'ODBC Driver 17 for SQL Server'},\n",
    "}\n",
    "\n",
    "# Create a URL object for connecting to the database\n",
    "db_url = URL.create(**db_config)\n",
    "\n",
    "# Connect to the Azure SQL Database using the URL string\n",
    "engine = create_engine(db_url)\n",
    "\n",
    "# Test the connection using the SQLAlchemy 2.0 execution style\n",
    "with engine.connect() as conn:\n",
    "    try:\n",
    "        # Use the text() construct for safer SQL execution\n",
    "        result = conn.execute(text(\"SELECT @@VERSION\"))\n",
    "        version = result.fetchone()\n",
    "        print(\"Connection successful!\")\n",
    "        print(version)\n",
    "    except Exception as e:\n",
    "        print(e)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "acaf202c-33a1-4105-b506-c26f2080c1d8",
   "metadata": {
    "metadata": {},
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(pyodbc.ProgrammingError) ('42S01', \"[42S01] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]There is already an object named 'covidtracking' in the database. (2714) (SQLExecDirectW)\")\n",
      "[SQL: CREATE TABLE covidtracking (date VARCHAR(MAX), state VARCHAR(MAX), death FLOAT, deathConfirmed FLOAT, deathIncrease INT, deathProbable FLOAT, hospitalized FLOAT, hospitalizedCumulative FLOAT, hospitalizedCurrently FLOAT, hospitalizedIncrease INT, inIcuCumulative FLOAT, inIcuCurrently FLOAT, negative FLOAT, negativeIncrease INT, negativeTestsAntibody FLOAT, negativeTestsPeopleAntibody FLOAT, negativeTestsViral FLOAT, onVentilatorCumulative FLOAT, onVentilatorCurrently FLOAT, positive FLOAT, positiveCasesViral FLOAT, positiveIncrease INT, positiveScore INT, positiveTestsAntibody FLOAT, positiveTestsAntigen FLOAT, positiveTestsPeopleAntibody FLOAT, positiveTestsPeopleAntigen FLOAT, positiveTestsViral FLOAT, recovered FLOAT, totalTestEncountersViral FLOAT, totalTestEncountersViralIncrease INT, totalTestResults FLOAT, totalTestResultsIncrease INT, totalTestsAntibody FLOAT, totalTestsAntigen FLOAT, totalTestsPeopleAntibody FLOAT, totalTestsPeopleAntigen FLOAT, totalTestsPeopleViral FLOAT, totalTestsPeopleViralIncrease INT, totalTestsViral FLOAT, totalTestsViralIncrease INT)]\n",
      "(Background on this error at: https://sqlalche.me/e/20/f405)\n"
     ]
    }
   ],
   "source": [
    "# Read CSV file into a pandas dataframe\n",
    "csv_path = \"./data/all-states-history.csv\"\n",
    "df = pd.read_csv(csv_path).fillna(value = 0)\n",
    "\n",
    "# Infer column names and data types\n",
    "column_names = df.columns.tolist()\n",
    "column_types = df.dtypes.to_dict()\n",
    "\n",
    "# Generate SQL statement to create table\n",
    "table_name = 'covidtracking'\n",
    "\n",
    "create_table_sql = f\"CREATE TABLE {table_name} (\"\n",
    "for name, dtype in column_types.items():\n",
    "    if dtype == 'object':\n",
    "        create_table_sql += f\"{name} VARCHAR(MAX), \"\n",
    "    elif dtype == 'int64':\n",
    "        create_table_sql += f\"{name} INT, \"\n",
    "    elif dtype == 'float64':\n",
    "        create_table_sql += f\"{name} FLOAT, \"\n",
    "    elif dtype == 'bool':\n",
    "        create_table_sql += f\"{name} BIT, \"\n",
    "    elif dtype == 'datetime64[ns]':\n",
    "        create_table_sql += f\"{name} DATETIME, \"\n",
    "create_table_sql = create_table_sql[:-2] + \")\"\n",
    "\n",
    "try:\n",
    "    #Createse the table in Azure SQL\n",
    "    with engine.connect() as conn:\n",
    "        # Execute the create table SQL statement\n",
    "        conn.execute(text(create_table_sql))\n",
    "        print(\"Table\", table_name, \"successfully created\")\n",
    "    # Insert data into SQL Database\n",
    "    lower = 0\n",
    "    upper = 1000\n",
    "    limit = df.shape[0]\n",
    "\n",
    "    while lower < limit:\n",
    "        df[lower:upper].to_sql(table_name, con=engine, if_exists='append', index=False)\n",
    "        print(\"rows:\", lower, \"-\", upper, \"inserted\")\n",
    "        lower = upper\n",
    "        upper = min(upper + 1000, limit)\n",
    "\n",
    "except Exception as e:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "33ad46af-11a4-41a6-94af-15509fd9e16c",
   "metadata": {},
   "source": [
    "# Create the SQL Agent Graph"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ea2ef524-565a-4f28-9955-fce0d01bbe21",
   "metadata": {},
   "source": [
    "As you can infer from Notebook 6 and 7, the process of talking to a SQL database will follow the same pattern:\n",
    "1. Create the tools to interact with the database\n",
    "2. Create a detailed prompt with instructions on how to act\n",
    "3. Create a LangGraph agent to use the above"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "2403a051-130d-42bd-9eb3-6db8bf65a618",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "llm = AzureChatOpenAI(deployment_name=os.environ[\"GPT4o_DEPLOYMENT_NAME\"], \n",
    "                      temperature=0, \n",
    "                      max_tokens=2000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "4a876d93-c541-4766-b6ad-022a351dc755",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Let's create the db object\n",
    "db = SQLDatabase.from_uri(db_url)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7ec76926-101a-4c42-9f12-4041090c9541",
   "metadata": {},
   "source": [
    "### Define the tools\n",
    "\n",
    "LangChain has created a set of tools inside a toolkit to interact with SQL-based Databases [Reference](https://python.langchain.com/api_reference/community/agent_toolkits/langchain_community.agent_toolkits.sql.toolkit.SQLDatabaseToolkit.html). Let's use that: `SQLDatabaseToolkit`\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "26e92e18-0e95-4690-ae85-1f2a50cfd49f",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "toolkit = SQLDatabaseToolkit(db=db, llm=llm)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "aea78d0a-d5e6-460f-b18d-72f24da84c1f",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "tools = toolkit.get_tools()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "1417fff1-676c-4e5d-91e2-e536ed218492",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "sql_db_query\n",
      "sql_db_schema\n",
      "sql_db_list_tables\n",
      "sql_db_query_checker\n"
     ]
    }
   ],
   "source": [
    "for tool in tools:\n",
    "    print(tool.name)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8009ef21-13a5-4b97-ab44-17446136903b",
   "metadata": {},
   "source": [
    "### Define our instructions for the Agent"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "52662f75-2e00-4822-945f-ba55247b4287",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/markdown": [
       "\n",
       "## Profile\n",
       "- You are an agent designed to interact with a MS SQL database.\n",
       "\n",
       "## Process to answer the human\n",
       "1. Fetch the available tables from the database\n",
       "2. Decide which tables are relevant to the question\n",
       "3. Fetch the DDL for the relevant tables\n",
       "4. Generate a query based on the question and information from the DDL\n",
       "5. Double-check the query for common mistakes \n",
       "6. Execute the query and return the results\n",
       "7. Correct mistakes surfaced by the database engine until the query is successful\n",
       "8. Formulate a response based on the results or repeat process until you can answer\n",
       "\n",
       "## Instructions:\n",
       "- Unless the user specifies a specific number of examples they wish to obtain, **ALWAYS** limit your query to at most 5 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",
       "- 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",
       "- ALWAYS, as part of your final answer, explain how you got to the answer on a section that starts with: \"Explanation:\".\n",
       "- If the question does not seem related to the database, just return \"I don't know\" as the answer.\n",
       "- Do not make up table names, only use the tables returned by the right tool.\n",
       "\n",
       "### Examples of Final Answer:\n",
       "\n",
       "Example 1:\n",
       "\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",
       "\n",
       "Example 2:\n",
       "\n",
       "Final Answer: The average sales price in 2021 was $322.5.\n",
       "\n",
       "Explanation:\n",
       "I queried the `sales` table for the average `price` where the year is '2021'. The SQL query used is:\n",
       "\n",
       "```sql\n",
       "SELECT AVG(price) AS average_price FROM sales WHERE year = '2021'\n",
       "```\n",
       "This query calculates the average price of all sales in the year 2021, which is $322.5.\n",
       "\n",
       "Example 3:\n",
       "\n",
       "Final Answer: There were 150 unique customers who placed orders in 2022.\n",
       "\n",
       "Explanation:\n",
       "To find the number of unique customers who placed orders in 2022, I used the following SQL query:\n",
       "\n",
       "```sql\n",
       "SELECT COUNT(DISTINCT customer_id) FROM orders WHERE order_date BETWEEN '2022-01-01' AND '2022-12-31'\n",
       "```\n",
       "This query counts the distinct `customer_id` entries within the `orders` table for the year 2022, resulting in 150 unique customers.\n",
       "\n",
       "Example 4:\n",
       "\n",
       "Final Answer: The highest-rated product is called UltraWidget.\n",
       "\n",
       "Explanation:\n",
       "I queried the `products` table to find the name of the highest-rated product using the following SQL query:\n",
       "\n",
       "```sql\n",
       "SELECT TOP 1 name FROM products ORDER BY rating DESC\n",
       "```\n",
       "This query selects the product name from the `products` table and orders the results by the `rating` column in descending order. The `TOP 1` clause ensures that only the highest-rated product is returned, which is 'UltraWidget'.\n",
       "\n"
      ],
      "text/plain": [
       "<IPython.core.display.Markdown object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "printmd(MSSQL_AGENT_PROMPT_TEXT)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c369bbdb-1bbc-46de-b49d-412b026aa8e2",
   "metadata": {},
   "source": [
    "### Create the Graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "c97d5d75-ff97-495a-9cea-ba331ea1af5f",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "graph = create_react_agent(llm, tools=tools, state_modifier=MSSQL_AGENT_PROMPT_TEXT)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ee3d08f8-659b-4929-81ed-429ed7a07bc1",
   "metadata": {},
   "source": [
    "### Run the Graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "9ec8a54a-5179-410f-9eca-04cc1580f561",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "def print_stream(stream):\n",
    "    for s in stream:\n",
    "        message = s[\"messages\"][-1]\n",
    "        if isinstance(message, tuple):\n",
    "            print(message)\n",
    "        else:\n",
    "            message.pretty_print()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "ae80c022-415e-40d1-b205-1744a3164d70",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Natural Language question (query)\n",
    "QUESTION = \"\"\"\n",
    "How may patients were hospitalized during July 2020 in Texas. \n",
    "And nationwide as the total of all states? \n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "405e143c-4af5-40ec-8e61-7a7a8082d6bb",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "================================\u001b[1m Human Message \u001b[0m=================================\n",
      "\n",
      "\n",
      "How may patients were hospitalized during July 2020 in Texas. \n",
      "And nationwide as the total of all states? \n",
      "\n",
      "==================================\u001b[1m Ai Message \u001b[0m==================================\n",
      "Tool Calls:\n",
      "  sql_db_list_tables (call_1KQT16J1TuxqsJmGA8QUYwj8)\n",
      " Call ID: call_1KQT16J1TuxqsJmGA8QUYwj8\n",
      "  Args:\n",
      "=================================\u001b[1m Tool Message \u001b[0m=================================\n",
      "Name: sql_db_list_tables\n",
      "\n",
      "covidtracking\n",
      "==================================\u001b[1m Ai Message \u001b[0m==================================\n",
      "Tool Calls:\n",
      "  sql_db_schema (call_W3W9x5p2POZEFy7DMXkVv81r)\n",
      " Call ID: call_W3W9x5p2POZEFy7DMXkVv81r\n",
      "  Args:\n",
      "    table_names: covidtracking\n",
      "=================================\u001b[1m Tool Message \u001b[0m=================================\n",
      "Name: sql_db_schema\n",
      "\n",
      "\n",
      "CREATE TABLE covidtracking (\n",
      "\tdate VARCHAR(max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, \n",
      "\tstate VARCHAR(max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, \n",
      "\tdeath FLOAT(53) NULL, \n",
      "\t[deathConfirmed] FLOAT(53) NULL, \n",
      "\t[deathIncrease] BIGINT NULL, \n",
      "\t[deathProbable] FLOAT(53) NULL, \n",
      "\thospitalized FLOAT(53) NULL, \n",
      "\t[hospitalizedCumulative] FLOAT(53) NULL, \n",
      "\t[hospitalizedCurrently] FLOAT(53) NULL, \n",
      "\t[hospitalizedIncrease] BIGINT NULL, \n",
      "\t[inIcuCumulative] FLOAT(53) NULL, \n",
      "\t[inIcuCurrently] FLOAT(53) NULL, \n",
      "\tnegative FLOAT(53) NULL, \n",
      "\t[negativeIncrease] BIGINT NULL, \n",
      "\t[negativeTestsAntibody] FLOAT(53) NULL, \n",
      "\t[negativeTestsPeopleAntibody] FLOAT(53) NULL, \n",
      "\t[negativeTestsViral] FLOAT(53) NULL, \n",
      "\t[onVentilatorCumulative] FLOAT(53) NULL, \n",
      "\t[onVentilatorCurrently] FLOAT(53) NULL, \n",
      "\tpositive FLOAT(53) NULL, \n",
      "\t[positiveCasesViral] FLOAT(53) NULL, \n",
      "\t[positiveIncrease] BIGINT NULL, \n",
      "\t[positiveScore] BIGINT NULL, \n",
      "\t[positiveTestsAntibody] FLOAT(53) NULL, \n",
      "\t[positiveTestsAntigen] FLOAT(53) NULL, \n",
      "\t[positiveTestsPeopleAntibody] FLOAT(53) NULL, \n",
      "\t[positiveTestsPeopleAntigen] FLOAT(53) NULL, \n",
      "\t[positiveTestsViral] FLOAT(53) NULL, \n",
      "\trecovered FLOAT(53) NULL, \n",
      "\t[totalTestEncountersViral] FLOAT(53) NULL, \n",
      "\t[totalTestEncountersViralIncrease] BIGINT NULL, \n",
      "\t[totalTestResults] FLOAT(53) NULL, \n",
      "\t[totalTestResultsIncrease] BIGINT NULL, \n",
      "\t[totalTestsAntibody] FLOAT(53) NULL, \n",
      "\t[totalTestsAntigen] FLOAT(53) NULL, \n",
      "\t[totalTestsPeopleAntibody] FLOAT(53) NULL, \n",
      "\t[totalTestsPeopleAntigen] FLOAT(53) NULL, \n",
      "\t[totalTestsPeopleViral] FLOAT(53) NULL, \n",
      "\t[totalTestsPeopleViralIncrease] BIGINT NULL, \n",
      "\t[totalTestsViral] FLOAT(53) NULL, \n",
      "\t[totalTestsViralIncrease] BIGINT NULL\n",
      ")\n",
      "\n",
      "/*\n",
      "3 rows from covidtracking table:\n",
      "date\tstate\tdeath\tdeathConfirmed\tdeathIncrease\tdeathProbable\thospitalized\thospitalizedCumulative\thospitalizedCurrently\thospitalizedIncrease\tinIcuCumulative\tinIcuCurrently\tnegative\tnegativeIncrease\tnegativeTestsAntibody\tnegativeTestsPeopleAntibody\tnegativeTestsViral\tonVentilatorCumulative\tonVentilatorCurrently\tpositive\tpositiveCasesViral\tpositiveIncrease\tpositiveScore\tpositiveTestsAntibody\tpositiveTestsAntigen\tpositiveTestsPeopleAntibody\tpositiveTestsPeopleAntigen\tpositiveTestsViral\trecovered\ttotalTestEncountersViral\ttotalTestEncountersViralIncrease\ttotalTestResults\ttotalTestResultsIncrease\ttotalTestsAntibody\ttotalTestsAntigen\ttotalTestsPeopleAntibody\ttotalTestsPeopleAntigen\ttotalTestsPeopleViral\ttotalTestsPeopleViralIncrease\ttotalTestsViral\ttotalTestsViralIncrease\n",
      "2021-03-07\tAK\t305.0\t0.0\t0\t0.0\t1293.0\t1293.0\t33.0\t0\t0.0\t0.0\t0.0\t0\t0.0\t0.0\t1660758.0\t0.0\t2.0\t56886.0\t0.0\t0\t0\t0.0\t0.0\t0.0\t0.0\t68693.0\t0.0\t0.0\t0\t1731628.0\t0\t0.0\t0.0\t0.0\t0.0\t0.0\t0\t1731628.0\t0\n",
      "2021-03-07\tAL\t10148.0\t7963.0\t-1\t2185.0\t45976.0\t45976.0\t494.0\t0\t2676.0\t0.0\t1931711.0\t2087\t0.0\t0.0\t0.0\t1515.0\t0.0\t499819.0\t392077.0\t408\t0\t0.0\t0.0\t0.0\t0.0\t0.0\t295690.0\t0.0\t0\t2323788.0\t2347\t0.0\t0.0\t119757.0\t0.0\t2323788.0\t2347\t0.0\t0\n",
      "2021-03-07\tAR\t5319.0\t4308.0\t22\t1011.0\t14926.0\t14926.0\t335.0\t11\t0.0\t141.0\t2480716.0\t3267\t0.0\t0.0\t2480716.0\t1533.0\t65.0\t324818.0\t255726.0\t165\t0\t0.0\t0.0\t0.0\t81803.0\t0.0\t315517.0\t0.0\t0\t2736442.0\t3380\t0.0\t0.0\t0.0\t481311.0\t0.0\t0\t2736442.0\t3380\n",
      "*/\n",
      "==================================\u001b[1m Ai Message \u001b[0m==================================\n",
      "Tool Calls:\n",
      "  sql_db_query_checker (call_KbrxQLTR8faVf2gYZpDUKmuN)\n",
      " Call ID: call_KbrxQLTR8faVf2gYZpDUKmuN\n",
      "  Args:\n",
      "    query: SELECT SUM(hospitalizedIncrease) AS total_hospitalized_texas FROM covidtracking WHERE state = 'TX' AND date LIKE '2020-07%'\n",
      "=================================\u001b[1m Tool Message \u001b[0m=================================\n",
      "Name: sql_db_query_checker\n",
      "\n",
      "```sql\n",
      "SELECT SUM(hospitalizedIncrease) AS total_hospitalized_texas \n",
      "FROM covidtracking \n",
      "WHERE state = 'TX' \n",
      "AND date LIKE '2020-07%'\n",
      "```\n",
      "==================================\u001b[1m Ai Message \u001b[0m==================================\n",
      "Tool Calls:\n",
      "  sql_db_query (call_1ShYKvZyR0VsgKNKJ6lhQ1BO)\n",
      " Call ID: call_1ShYKvZyR0VsgKNKJ6lhQ1BO\n",
      "  Args:\n",
      "    query: SELECT SUM(hospitalizedIncrease) AS total_hospitalized_texas FROM covidtracking WHERE state = 'TX' AND date LIKE '2020-07%'\n",
      "=================================\u001b[1m Tool Message \u001b[0m=================================\n",
      "Name: sql_db_query\n",
      "\n",
      "[(0,)]\n",
      "==================================\u001b[1m Ai Message \u001b[0m==================================\n",
      "Tool Calls:\n",
      "  sql_db_query_checker (call_F1dXYEzd6H4cbUoO1RQajZIg)\n",
      " Call ID: call_F1dXYEzd6H4cbUoO1RQajZIg\n",
      "  Args:\n",
      "    query: SELECT SUM(hospitalizedIncrease) AS total_hospitalized_nationwide FROM covidtracking WHERE date LIKE '2020-07%'\n",
      "=================================\u001b[1m Tool Message \u001b[0m=================================\n",
      "Name: sql_db_query_checker\n",
      "\n",
      "```sql\n",
      "SELECT SUM(hospitalizedIncrease) AS total_hospitalized_nationwide \n",
      "FROM covidtracking \n",
      "WHERE date LIKE '2020-07%'\n",
      "```\n",
      "==================================\u001b[1m Ai Message \u001b[0m==================================\n",
      "Tool Calls:\n",
      "  sql_db_query (call_m6B62doA85jH9Wl4KYY5SV6L)\n",
      " Call ID: call_m6B62doA85jH9Wl4KYY5SV6L\n",
      "  Args:\n",
      "    query: SELECT SUM(hospitalizedIncrease) AS total_hospitalized_nationwide FROM covidtracking WHERE date LIKE '2020-07%'\n",
      "=================================\u001b[1m Tool Message \u001b[0m=================================\n",
      "Name: sql_db_query\n",
      "\n",
      "[(63105,)]\n",
      "==================================\u001b[1m Ai Message \u001b[0m==================================\n",
      "\n",
      "Final Answer: There were 0 patients hospitalized in Texas during July 2020. Nationwide, a total of 63,105 patients were hospitalized during the same period.\n",
      "\n",
      "Explanation:\n",
      "I queried the `covidtracking` table for the sum of `hospitalizedIncrease` where the state is 'TX' and the date is in July 2020. The query returned 0, indicating no new hospitalizations were recorded in Texas during that period. For the nationwide total, I queried the same table for the sum of `hospitalizedIncrease` for all states in July 2020, which returned 63,105. \n",
      "\n",
      "The SQL queries used are:\n",
      "\n",
      "```sql\n",
      "SELECT SUM(hospitalizedIncrease) AS total_hospitalized_texas \n",
      "FROM covidtracking \n",
      "WHERE state = 'TX' \n",
      "AND date LIKE '2020-07%'\n",
      "```\n",
      "\n",
      "```sql\n",
      "SELECT SUM(hospitalizedIncrease) AS total_hospitalized_nationwide \n",
      "FROM covidtracking \n",
      "WHERE date LIKE '2020-07%'\n",
      "```\n"
     ]
    }
   ],
   "source": [
    "inputs = {\"messages\": [(\"user\", QUESTION)]}\n",
    "\n",
    "print_stream(graph.stream(inputs, stream_mode=\"values\"))"
   ]
  },
  {
   "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.\n",
    "\n",
    "This process shows why it is NOT necessary to move the data from its original source as long as the source has an API and a common language we can use to interface with. LLMs have been trained on the whole public Github corpus, so it can pretty much understand most of the coding and database query languages that exists out there. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "02073623-91b4-40d6-8eaf-cb6d9c6a7a9a",
   "metadata": {},
   "source": [
    "# NEXT\n",
    "\n",
    "The Next Notebook will show you how to create a custom agent that connects to the internet using BING SEARCH API to answer questions grounded on search results with citations. Basically a clone of [Copilot](https://copilot.cloud.microsoft/)."
   ]
  }
 ],
 "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.14"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}