docs/examples/create_temporary_tables_from_sql_file.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "green-symphony",
   "metadata": {},
   "source": [
    "# Create Temporary Tables Using SQL Files\n",
    "\n",
    "This tutorial demonstrates how to create temporary tables in athena using `pydbtools.read_sql_queries`. This is an amended version of [create_temporary_version.ipynb](create_temporary_version.ipynb)."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "provincial-train",
   "metadata": {},
   "source": [
    "## Setup\n",
    "\n",
    "Just run this script to create the source database so we can use it for our example."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "responsible-april",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import awswrangler as wr\n",
    "import pydbtools as pydb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "dying-stock",
   "metadata": {},
   "outputs": [],
   "source": [
    "# setup your own testing area (set foldername = GH username)\n",
    "foldername = \"mratford\"  # GH username\n",
    "foldername = foldername.lower().replace(\"-\", \"_\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "chemical-maple",
   "metadata": {},
   "outputs": [],
   "source": [
    "bucketname = \"alpha-everyone\"\n",
    "s3_base_path = f\"s3://{bucketname}/{foldername}/\"\n",
    "\n",
    "db_name = f\"aws_example_{foldername}\"\n",
    "source_db_base_path = f\"s3://{bucketname}/{foldername}/source_db/\"\n",
    "\n",
    "pydb.delete_database_and_data(db_name)\n",
    "\n",
    "# Setup source database\n",
    "# Create the database\n",
    "pydb.create_database(db_name)\n",
    "\n",
    "# Iterate through the tables in data/ and write them to our db using awswrangler\n",
    "for table_name in [\"department\", \"employees\", \"sales\"]:\n",
    "    table_path = pydb.s3_path_join(source_db_base_path, f\"{table_name}/\")\n",
    "    pydb.file_to_table(\n",
    "        path=f\"data/{table_name}.csv\",\n",
    "        database=db_name,\n",
    "        table=table_name,\n",
    "        location=table_path,\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "blank-processing",
   "metadata": {},
   "source": [
    "## Task\n",
    "\n",
    "We are going to create a table that shows total sales per employee using all 3 tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "included-divide",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>employee_id</th>\n",
       "      <th>sex</th>\n",
       "      <th>forename</th>\n",
       "      <th>surname</th>\n",
       "      <th>department_id</th>\n",
       "      <th>manager_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>M</td>\n",
       "      <td>Dexter</td>\n",
       "      <td>Mitchell</td>\n",
       "      <td>1</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>F</td>\n",
       "      <td>Summer</td>\n",
       "      <td>Bennett</td>\n",
       "      <td>1</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>M</td>\n",
       "      <td>Pip</td>\n",
       "      <td>Carter</td>\n",
       "      <td>1</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>F</td>\n",
       "      <td>Bella</td>\n",
       "      <td>Long</td>\n",
       "      <td>1</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>F</td>\n",
       "      <td>Lexie</td>\n",
       "      <td>Perry</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   employee_id sex forename   surname  department_id  manager_id\n",
       "0            1   M   Dexter  Mitchell              1          17\n",
       "1            2   F   Summer   Bennett              1          17\n",
       "2            3   M      Pip    Carter              1          17\n",
       "3            4   F    Bella      Long              1          17\n",
       "4            5   F    Lexie     Perry           <NA>          17"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pydb.read_sql_query(\n",
    "    f\"SELECT * FROM {db_name}.employees LIMIT 5\", ctas_approach=False\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "opening-professional",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>department_id</th>\n",
       "      <th>department_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Sales</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Admin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Management</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Technical</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Maintenance</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   department_id department_name\n",
       "0              1           Sales\n",
       "1              2           Admin\n",
       "2              3      Management\n",
       "3              4       Technical\n",
       "4              5     Maintenance"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pydb.read_sql_query(\n",
    "    f\"SELECT * FROM {db_name}.department LIMIT 5\", ctas_approach=False\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "sought-northern",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>employee_id</th>\n",
       "      <th>qtr</th>\n",
       "      <th>sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>768.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>391.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>406.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>816.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>437.05</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   employee_id  qtr   sales\n",
       "0            1    1  768.17\n",
       "1            2    1  391.98\n",
       "2            3    1  406.36\n",
       "3            4    1  816.25\n",
       "4            5    1  437.05"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pydb.read_sql_query(\n",
    "    f\"SELECT * FROM {db_name}.sales LIMIT 5\", ctas_approach=False\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "future-treaty",
   "metadata": {},
   "source": [
    "pydbtools has `read_sql_queries` and `read_sql_queries` functions that allow you to create temporary tables within SQL which you can refer to in a `__temp__` database.\n",
    "\n",
    "**First create a total_sales table:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "asian-stocks",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "CREATE TEMP TABLE total_sales AS\n",
      "SELECT employee_id, sum(sales) as total_sales\n",
      "FROM aws_example_mratford.sales\n",
      "GROUP BY employee_id;\n",
      "\n"
     ]
    }
   ],
   "source": [
    "sql = f\"\"\"\n",
    "CREATE TEMP TABLE total_sales AS\n",
    "SELECT employee_id, sum(sales) as total_sales\n",
    "FROM {db_name}.sales\n",
    "GROUP BY employee_id;\n",
    "\"\"\"\n",
    "print(sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "sublime-armor",
   "metadata": {},
   "source": [
    "**Then create a table of employee names from the sales department:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "younger-miracle",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "CREATE TEMP TABLE total_sales AS\n",
      "SELECT employee_id, sum(sales) as total_sales\n",
      "FROM aws_example_mratford.sales\n",
      "GROUP BY employee_id;\n",
      "\n",
      "CREATE TEMP TABLE sales_employees AS\n",
      "SELECT e.employee_id, e.forename, e.surname, d.department_name\n",
      "FROM aws_example_mratford.employees AS e\n",
      "LEFT JOIN aws_example_mratford.department AS d\n",
      "ON e.department_id = d.department_id\n",
      "WHERE e.department_id = 1;\n",
      "\n"
     ]
    }
   ],
   "source": [
    "sql += f\"\"\"\n",
    "CREATE TEMP TABLE sales_employees AS\n",
    "SELECT e.employee_id, e.forename, e.surname, d.department_name\n",
    "FROM {db_name}.employees AS e\n",
    "LEFT JOIN {db_name}.department AS d\n",
    "ON e.department_id = d.department_id\n",
    "WHERE e.department_id = 1;\n",
    "\"\"\"\n",
    "print(sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "experimental-market",
   "metadata": {},
   "source": [
    "**Finally return our final tables**\n",
    "\n",
    "Note that more than one select statement can be used so the function returns an iterator yielding the results of each select."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "about-palestinian",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "CREATE TEMP TABLE total_sales AS\n",
      "SELECT employee_id, sum(sales) as total_sales\n",
      "FROM aws_example_mratford.sales\n",
      "GROUP BY employee_id;\n",
      "\n",
      "CREATE TEMP TABLE sales_employees AS\n",
      "SELECT e.employee_id, e.forename, e.surname, d.department_name\n",
      "FROM aws_example_mratford.employees AS e\n",
      "LEFT JOIN aws_example_mratford.department AS d\n",
      "ON e.department_id = d.department_id\n",
      "WHERE e.department_id = 1;\n",
      "\n",
      "SELECT se.*, ts.total_sales\n",
      "FROM __temp__.sales_employees AS se\n",
      "INNER JOIN __temp__.total_sales AS ts\n",
      "ON se.employee_id = ts.employee_id;\n",
      "\n"
     ]
    }
   ],
   "source": [
    "sql += f\"\"\"\n",
    "SELECT se.*, ts.total_sales\n",
    "FROM __temp__.sales_employees AS se\n",
    "INNER JOIN __temp__.total_sales AS ts\n",
    "ON se.employee_id = ts.employee_id;\n",
    "\"\"\"\n",
    "print(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "d1eb721f-d627-4b00-8617-618a44ae8d69",
   "metadata": {},
   "outputs": [],
   "source": [
    "total_sales = pydb.read_sql_queries(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "6387d360-a7bf-477b-9663-3a5431dfa507",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>employee_id</th>\n",
       "      <th>forename</th>\n",
       "      <th>surname</th>\n",
       "      <th>department_name</th>\n",
       "      <th>total_sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Dexter</td>\n",
       "      <td>Mitchell</td>\n",
       "      <td>Sales</td>\n",
       "      <td>2911.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Summer</td>\n",
       "      <td>Bennett</td>\n",
       "      <td>Sales</td>\n",
       "      <td>1785.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Pip</td>\n",
       "      <td>Carter</td>\n",
       "      <td>Sales</td>\n",
       "      <td>2590.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Bella</td>\n",
       "      <td>Long</td>\n",
       "      <td>Sales</td>\n",
       "      <td>2996.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6</td>\n",
       "      <td>Robert</td>\n",
       "      <td>Roberts</td>\n",
       "      <td>Sales</td>\n",
       "      <td>2207.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>7</td>\n",
       "      <td>Iris</td>\n",
       "      <td>Alexander</td>\n",
       "      <td>Sales</td>\n",
       "      <td>2465.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>9</td>\n",
       "      <td>Evan</td>\n",
       "      <td>Carter</td>\n",
       "      <td>Sales</td>\n",
       "      <td>2279.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>10</td>\n",
       "      <td>Lauren</td>\n",
       "      <td>Powell</td>\n",
       "      <td>Sales</td>\n",
       "      <td>1935.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>11</td>\n",
       "      <td>Alice</td>\n",
       "      <td>James</td>\n",
       "      <td>Sales</td>\n",
       "      <td>3092.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>12</td>\n",
       "      <td>Owen</td>\n",
       "      <td>Scott</td>\n",
       "      <td>Sales</td>\n",
       "      <td>2286.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>13</td>\n",
       "      <td>Sarah</td>\n",
       "      <td>Patterson</td>\n",
       "      <td>Sales</td>\n",
       "      <td>2711.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>15</td>\n",
       "      <td>Evie</td>\n",
       "      <td>Morgan</td>\n",
       "      <td>Sales</td>\n",
       "      <td>2613.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>16</td>\n",
       "      <td>Zachary</td>\n",
       "      <td>Rodriguez</td>\n",
       "      <td>Sales</td>\n",
       "      <td>2373.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>17</td>\n",
       "      <td>Madison</td>\n",
       "      <td>Kelly</td>\n",
       "      <td>Sales</td>\n",
       "      <td>2302.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>18</td>\n",
       "      <td>Jake</td>\n",
       "      <td>Harris</td>\n",
       "      <td>Sales</td>\n",
       "      <td>1759.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>19</td>\n",
       "      <td>Emma</td>\n",
       "      <td>Cooper</td>\n",
       "      <td>Sales</td>\n",
       "      <td>2442.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>20</td>\n",
       "      <td>Eva</td>\n",
       "      <td>Peterson</td>\n",
       "      <td>Sales</td>\n",
       "      <td>2851.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>21</td>\n",
       "      <td>Isaac</td>\n",
       "      <td>White</td>\n",
       "      <td>Sales</td>\n",
       "      <td>1643.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>23</td>\n",
       "      <td>Sienna</td>\n",
       "      <td>James</td>\n",
       "      <td>Sales</td>\n",
       "      <td>3036.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>24</td>\n",
       "      <td>Seren</td>\n",
       "      <td>Diaz</td>\n",
       "      <td>Sales</td>\n",
       "      <td>2248.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>26</td>\n",
       "      <td>Angel</td>\n",
       "      <td>Hayes</td>\n",
       "      <td>Sales</td>\n",
       "      <td>1994.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>27</td>\n",
       "      <td>Evan</td>\n",
       "      <td>Carter</td>\n",
       "      <td>Sales</td>\n",
       "      <td>3050.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>28</td>\n",
       "      <td>Olivia</td>\n",
       "      <td>Rogers</td>\n",
       "      <td>Sales</td>\n",
       "      <td>2071.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>29</td>\n",
       "      <td>Molly</td>\n",
       "      <td>Gray</td>\n",
       "      <td>Sales</td>\n",
       "      <td>2478.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>30</td>\n",
       "      <td>Lucas</td>\n",
       "      <td>Moore</td>\n",
       "      <td>Sales</td>\n",
       "      <td>2251.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>31</td>\n",
       "      <td>Kitty</td>\n",
       "      <td>Russell</td>\n",
       "      <td>Sales</td>\n",
       "      <td>2461.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>32</td>\n",
       "      <td>Leo</td>\n",
       "      <td>Thomas</td>\n",
       "      <td>Sales</td>\n",
       "      <td>2693.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>33</td>\n",
       "      <td>Evie</td>\n",
       "      <td>Morgan</td>\n",
       "      <td>Sales</td>\n",
       "      <td>2685.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>34</td>\n",
       "      <td>Eli</td>\n",
       "      <td>Mitchell</td>\n",
       "      <td>Sales</td>\n",
       "      <td>1387.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>36</td>\n",
       "      <td>joseph</td>\n",
       "      <td>Martin</td>\n",
       "      <td>Sales</td>\n",
       "      <td>1580.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>37</td>\n",
       "      <td>Alexandra</td>\n",
       "      <td>Russell</td>\n",
       "      <td>Sales</td>\n",
       "      <td>1984.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>38</td>\n",
       "      <td>Keira</td>\n",
       "      <td>Hughes</td>\n",
       "      <td>Sales</td>\n",
       "      <td>2158.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>39</td>\n",
       "      <td>Sophie</td>\n",
       "      <td>Morris</td>\n",
       "      <td>Sales</td>\n",
       "      <td>1931.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>40</td>\n",
       "      <td>Tobias</td>\n",
       "      <td>Collins</td>\n",
       "      <td>Sales</td>\n",
       "      <td>2595.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>41</td>\n",
       "      <td>Lola</td>\n",
       "      <td>Watson</td>\n",
       "      <td>Sales</td>\n",
       "      <td>1326.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>42</td>\n",
       "      <td>Maisie</td>\n",
       "      <td>Torres</td>\n",
       "      <td>Sales</td>\n",
       "      <td>1688.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>43</td>\n",
       "      <td>Phoebe</td>\n",
       "      <td>Peterson</td>\n",
       "      <td>Sales</td>\n",
       "      <td>2073.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>44</td>\n",
       "      <td>Mia</td>\n",
       "      <td>Murphy</td>\n",
       "      <td>Sales</td>\n",
       "      <td>2184.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>45</td>\n",
       "      <td>Freya</td>\n",
       "      <td>Bailey</td>\n",
       "      <td>Sales</td>\n",
       "      <td>2778.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>46</td>\n",
       "      <td>Sebastian</td>\n",
       "      <td>Hall</td>\n",
       "      <td>Sales</td>\n",
       "      <td>2547.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>47</td>\n",
       "      <td>Emily</td>\n",
       "      <td>Stewart</td>\n",
       "      <td>Sales</td>\n",
       "      <td>1489.52</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    employee_id   forename    surname department_name  total_sales\n",
       "0             1     Dexter   Mitchell           Sales      2911.65\n",
       "1             2     Summer    Bennett           Sales      1785.73\n",
       "2             3        Pip     Carter           Sales      2590.60\n",
       "3             4      Bella       Long           Sales      2996.54\n",
       "4             6     Robert    Roberts           Sales      2207.77\n",
       "5             7       Iris  Alexander           Sales      2465.13\n",
       "6             9       Evan     Carter           Sales      2279.84\n",
       "7            10     Lauren     Powell           Sales      1935.67\n",
       "8            11      Alice      James           Sales      3092.89\n",
       "9            12       Owen      Scott           Sales      2286.28\n",
       "10           13      Sarah  Patterson           Sales      2711.01\n",
       "11           15       Evie     Morgan           Sales      2613.67\n",
       "12           16    Zachary  Rodriguez           Sales      2373.06\n",
       "13           17    Madison      Kelly           Sales      2302.02\n",
       "14           18       Jake     Harris           Sales      1759.39\n",
       "15           19       Emma     Cooper           Sales      2442.86\n",
       "16           20        Eva   Peterson           Sales      2851.36\n",
       "17           21      Isaac      White           Sales      1643.14\n",
       "18           23     Sienna      James           Sales      3036.47\n",
       "19           24      Seren       Diaz           Sales      2248.35\n",
       "20           26      Angel      Hayes           Sales      1994.65\n",
       "21           27       Evan     Carter           Sales      3050.02\n",
       "22           28     Olivia     Rogers           Sales      2071.77\n",
       "23           29      Molly       Gray           Sales      2478.25\n",
       "24           30      Lucas      Moore           Sales      2251.47\n",
       "25           31      Kitty    Russell           Sales      2461.53\n",
       "26           32        Leo     Thomas           Sales      2693.30\n",
       "27           33       Evie     Morgan           Sales      2685.07\n",
       "28           34        Eli   Mitchell           Sales      1387.50\n",
       "29           36     joseph     Martin           Sales      1580.06\n",
       "30           37  Alexandra    Russell           Sales      1984.24\n",
       "31           38      Keira     Hughes           Sales      2158.55\n",
       "32           39     Sophie     Morris           Sales      1931.27\n",
       "33           40     Tobias    Collins           Sales      2595.53\n",
       "34           41       Lola     Watson           Sales      1326.88\n",
       "35           42     Maisie     Torres           Sales      1688.76\n",
       "36           43     Phoebe   Peterson           Sales      2073.25\n",
       "37           44        Mia     Murphy           Sales      2184.14\n",
       "38           45      Freya     Bailey           Sales      2778.84\n",
       "39           46  Sebastian       Hall           Sales      2547.63\n",
       "40           47      Emily    Stewart           Sales      1489.52"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_sales"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "edfe111e-79f6-439f-a1d2-369da576439d",
   "metadata": {},
   "source": [
    "The `read_sql_queries_gen` function allows you to use more than `SELECT` statement, returning an iterator of dataframes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "38684f07-0f13-4291-9855-30661056b3f1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "CREATE TEMP TABLE total_sales AS\n",
      "SELECT employee_id, sum(sales) as total_sales\n",
      "FROM aws_example_mratford.sales\n",
      "GROUP BY employee_id;\n",
      "\n",
      "CREATE TEMP TABLE sales_employees AS\n",
      "SELECT e.employee_id, e.forename, e.surname, d.department_name\n",
      "FROM aws_example_mratford.employees AS e\n",
      "LEFT JOIN aws_example_mratford.department AS d\n",
      "ON e.department_id = d.department_id\n",
      "WHERE e.department_id = 1;\n",
      "\n",
      "SELECT se.*, ts.total_sales\n",
      "FROM __temp__.sales_employees AS se\n",
      "INNER JOIN __temp__.total_sales AS ts\n",
      "ON se.employee_id = ts.employee_id;\n",
      "\n",
      "SELECT forename, surname, sum(s.sales) as q1_sales\n",
      "FROM __temp__.sales_employees AS se\n",
      "LEFT JOIN aws_example_mratford.sales AS s\n",
      "ON se.employee_id = s.employee_id\n",
      "GROUP BY forename, surname;\n",
      "\n"
     ]
    }
   ],
   "source": [
    "sql += f\"\"\"\n",
    "SELECT forename, surname, sum(s.sales) as q1_sales\n",
    "FROM __temp__.sales_employees AS se\n",
    "LEFT JOIN {db_name}.sales AS s\n",
    "ON se.employee_id = s.employee_id\n",
    "GROUP BY forename, surname;\n",
    "\"\"\"\n",
    "print(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "d1c93cee-521f-4853-b942-c7cb949b843b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>forename</th>\n",
       "      <th>surname</th>\n",
       "      <th>q1_sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Dexter</td>\n",
       "      <td>Mitchell</td>\n",
       "      <td>2911.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Elliot</td>\n",
       "      <td>King</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Madison</td>\n",
       "      <td>Kelly</td>\n",
       "      <td>2302.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Eva</td>\n",
       "      <td>Peterson</td>\n",
       "      <td>2851.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Olivia</td>\n",
       "      <td>Rogers</td>\n",
       "      <td>2071.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Angel</td>\n",
       "      <td>Hayes</td>\n",
       "      <td>1994.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Bella</td>\n",
       "      <td>Long</td>\n",
       "      <td>2996.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Rosie</td>\n",
       "      <td>Bennett</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Lauren</td>\n",
       "      <td>Powell</td>\n",
       "      <td>1935.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Tobias</td>\n",
       "      <td>Collins</td>\n",
       "      <td>2595.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Evan</td>\n",
       "      <td>Carter</td>\n",
       "      <td>5329.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Sarah</td>\n",
       "      <td>Patterson</td>\n",
       "      <td>2711.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Robert</td>\n",
       "      <td>Roberts</td>\n",
       "      <td>2207.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Emily</td>\n",
       "      <td>Stewart</td>\n",
       "      <td>1489.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Owen</td>\n",
       "      <td>Scott</td>\n",
       "      <td>2286.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>joseph</td>\n",
       "      <td>Martin</td>\n",
       "      <td>1580.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Keira</td>\n",
       "      <td>Hughes</td>\n",
       "      <td>2158.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Iris</td>\n",
       "      <td>Alexander</td>\n",
       "      <td>2465.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Eli</td>\n",
       "      <td>Mitchell</td>\n",
       "      <td>1387.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Pip</td>\n",
       "      <td>Carter</td>\n",
       "      <td>2590.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Kitty</td>\n",
       "      <td>Russell</td>\n",
       "      <td>2461.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Lola</td>\n",
       "      <td>Watson</td>\n",
       "      <td>1326.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Lucas</td>\n",
       "      <td>Moore</td>\n",
       "      <td>2251.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Sebastian</td>\n",
       "      <td>Hall</td>\n",
       "      <td>2547.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Phoebe</td>\n",
       "      <td>Peterson</td>\n",
       "      <td>2073.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Zachary</td>\n",
       "      <td>Rodriguez</td>\n",
       "      <td>2373.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Emma</td>\n",
       "      <td>Cooper</td>\n",
       "      <td>2442.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Seren</td>\n",
       "      <td>Diaz</td>\n",
       "      <td>2248.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Sophie</td>\n",
       "      <td>Morris</td>\n",
       "      <td>1931.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Alexandra</td>\n",
       "      <td>Russell</td>\n",
       "      <td>1984.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Summer</td>\n",
       "      <td>Bennett</td>\n",
       "      <td>1785.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>Jake</td>\n",
       "      <td>Harris</td>\n",
       "      <td>1759.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>Alice</td>\n",
       "      <td>James</td>\n",
       "      <td>3092.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Mia</td>\n",
       "      <td>Murphy</td>\n",
       "      <td>2184.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Freya</td>\n",
       "      <td>Bailey</td>\n",
       "      <td>2778.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Maisie</td>\n",
       "      <td>Torres</td>\n",
       "      <td>1688.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Leo</td>\n",
       "      <td>Thomas</td>\n",
       "      <td>2693.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Evie</td>\n",
       "      <td>Morgan</td>\n",
       "      <td>5298.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>Isaac</td>\n",
       "      <td>White</td>\n",
       "      <td>1643.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>Molly</td>\n",
       "      <td>Gray</td>\n",
       "      <td>2478.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>Sienna</td>\n",
       "      <td>James</td>\n",
       "      <td>3036.47</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     forename    surname  q1_sales\n",
       "0      Dexter   Mitchell   2911.65\n",
       "1      Elliot       King       NaN\n",
       "2     Madison      Kelly   2302.02\n",
       "3         Eva   Peterson   2851.36\n",
       "4      Olivia     Rogers   2071.77\n",
       "5       Angel      Hayes   1994.65\n",
       "6       Bella       Long   2996.54\n",
       "7       Rosie    Bennett       NaN\n",
       "8      Lauren     Powell   1935.67\n",
       "9      Tobias    Collins   2595.53\n",
       "10       Evan     Carter   5329.86\n",
       "11      Sarah  Patterson   2711.01\n",
       "12     Robert    Roberts   2207.77\n",
       "13      Emily    Stewart   1489.52\n",
       "14       Owen      Scott   2286.28\n",
       "15     joseph     Martin   1580.06\n",
       "16      Keira     Hughes   2158.55\n",
       "17       Iris  Alexander   2465.13\n",
       "18        Eli   Mitchell   1387.50\n",
       "19        Pip     Carter   2590.60\n",
       "20      Kitty    Russell   2461.53\n",
       "21       Lola     Watson   1326.88\n",
       "22      Lucas      Moore   2251.47\n",
       "23  Sebastian       Hall   2547.63\n",
       "24     Phoebe   Peterson   2073.25\n",
       "25    Zachary  Rodriguez   2373.06\n",
       "26       Emma     Cooper   2442.86\n",
       "27      Seren       Diaz   2248.35\n",
       "28     Sophie     Morris   1931.27\n",
       "29  Alexandra    Russell   1984.24\n",
       "30     Summer    Bennett   1785.73\n",
       "31       Jake     Harris   1759.39\n",
       "32      Alice      James   3092.89\n",
       "33        Mia     Murphy   2184.14\n",
       "34      Freya     Bailey   2778.84\n",
       "35     Maisie     Torres   1688.76\n",
       "36        Leo     Thomas   2693.30\n",
       "37       Evie     Morgan   5298.74\n",
       "38      Isaac      White   1643.14\n",
       "39      Molly       Gray   2478.25\n",
       "40     Sienna      James   3036.47"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_sales, q1_sales = tuple(pydb.read_sql_queries_gen(sql))\n",
    "q1_sales"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "secure-aviation",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Clean up\n",
    "\n",
    "pydb.delete_database_and_data(db_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "southern-sunglasses",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "pydbtools",
   "language": "python",
   "name": "pydbtools"
  },
  "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.9.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}