docs/examples/delete_databases_tables_and_partitions.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "4c6d5ff7-98fd-47e1-a18c-40d992ca06d4",
   "metadata": {},
   "source": [
    "# Delete databases, tables and partitions and data\n",
    "\n",
    "`pydbtools` now has functions to remove databases, database tables, and partitions, plus the underlying data on S3.\n",
    "\n",
    "## Setup\n",
    "\n",
    "First run the following cells to set up the database tables.\n",
    "\n",
    "Import the necessary libraries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "231f5906-d60f-47f5-bf40-e46848e7c7a6",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import awswrangler as wr\n",
    "import pydbtools as pydb"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5c6da254-b79d-495b-8555-b4078c921598",
   "metadata": {},
   "source": [
    "Set up your testing area. \n",
    "\n",
    "**Important:** substitute your own Github username below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "8f375823-a8f3-4bb5-8d6e-2304a48fb50e",
   "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": "c8a1e563-406e-43ba-bc80-fbcff44676c8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "deleting objs\n",
      "aws_example_mratford found deleting\n"
     ]
    }
   ],
   "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",
    "# Delete all the s3 files in a given path\n",
    "if wr.s3.list_objects(s3_base_path):\n",
    "    print(\"deleting objs\")\n",
    "    wr.s3.delete_objects(s3_base_path)\n",
    "\n",
    "# Delete the database if it exists\n",
    "df_dbs = wr.catalog.databases(None)\n",
    "if db_name in df_dbs[\"Database\"].to_list():\n",
    "    print(f\"{db_name} found deleting\")\n",
    "    wr.catalog.delete_database(name=db_name)\n",
    "\n",
    "# Setup source database\n",
    "# Create the database\n",
    "wr.catalog.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\"]:\n",
    "    df = pd.read_csv(f\"data/{table_name}.csv\")\n",
    "    table_path = os.path.join(source_db_base_path, f\"{table_name}/\")\n",
    "    wr.s3.to_parquet(\n",
    "        df=df,\n",
    "        path=table_path,\n",
    "        index=False,\n",
    "        dataset=True,  # True allows the other params below i.e. overwriting to db.table\n",
    "        database=db_name,\n",
    "        table=table_name,\n",
    "        mode=\"overwrite\",\n",
    "    )\n",
    "\n",
    "# For the sales table partition the data by employee_id and qtr\n",
    "# (reduce the size of the table for legibility)\n",
    "df = pd.read_csv(\"data/sales.csv\").query(\"employee_id < 5\")\n",
    "table_path = os.path.join(source_db_base_path, \"sales\")\n",
    "partition_info = wr.s3.to_parquet(\n",
    "    df=df,\n",
    "    path=table_path,\n",
    "    index=False,\n",
    "    dataset=True,\n",
    "    partition_cols=[\"employee_id\", \"qtr\"],\n",
    "    database=db_name,\n",
    "    table=\"sales\",\n",
    "    mode=\"overwrite\",\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dafefed4-6ace-4b47-9e20-66aa79c7043b",
   "metadata": {},
   "source": [
    "## Deleting a table\n",
    "\n",
    "Show the tables in the database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "b242ff37-ec3b-403d-a238-ff947ac6255b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['department', 'employees', 'sales']"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table_info = list(wr.catalog.get_tables(database=db_name))\n",
    "[x[\"Name\"] for x in table_info]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "687735a5-ed18-4727-aa24-263726ec71c5",
   "metadata": {},
   "source": [
    "Show the data for the `department` table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "bbe44bac-0cd8-40e6-8b85-e58937977b8f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['s3://alpha-everyone/mratford/source_db/department/be5d0e3a417a4c7bbb0bf0ff5996bba4.snappy.parquet']"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dept_info = next(x for x in table_info if x[\"Name\"] == \"department\")\n",
    "dept_location = dept_info[\"StorageDescriptor\"][\"Location\"]\n",
    "wr.s3.list_objects(dept_location)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fd6b1e22-4d25-4ddc-a4e7-c26dd8b827ee",
   "metadata": {},
   "source": [
    "Now delete the `department` table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "0b51ffd5-c51c-4a9f-afd9-e43291cb3bd4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pydb.delete_table_and_data(database=db_name, table=\"department\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f018f246-e82b-4d5e-ae4c-3402d822326f",
   "metadata": {},
   "source": [
    "Check that it's no longer in the database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "88a70fe5-6eaf-48ef-8232-98e21d682f75",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['employees', 'sales']"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table_info = list(wr.catalog.get_tables(database=db_name))\n",
    "[x[\"Name\"] for x in table_info]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d9a0b832-ce11-4c85-90c7-94e5fcdaaf91",
   "metadata": {},
   "source": [
    "Check that the data no longer exists."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "be12d9b1-263f-4649-ae49-d3945376d266",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wr.s3.list_objects(dept_location)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "18ba2b03-344a-45a5-a679-7b7a0966923f",
   "metadata": {},
   "source": [
    "## Deleting a partition\n",
    "\n",
    "Show the partitions from the `sales` table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "bc7a4a7f-b658-44ec-b922-1c6f4103300e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'s3://alpha-everyone/mratford/source_db/sales/employee_id=3/qtr=2/': ['3',\n",
       "  '2'],\n",
       " 's3://alpha-everyone/mratford/source_db/sales/employee_id=1/qtr=3/': ['1',\n",
       "  '3'],\n",
       " 's3://alpha-everyone/mratford/source_db/sales/employee_id=2/qtr=4/': ['2',\n",
       "  '4'],\n",
       " 's3://alpha-everyone/mratford/source_db/sales/employee_id=1/qtr=4/': ['1',\n",
       "  '4'],\n",
       " 's3://alpha-everyone/mratford/source_db/sales/employee_id=2/qtr=3/': ['2',\n",
       "  '3'],\n",
       " 's3://alpha-everyone/mratford/source_db/sales/employee_id=4/qtr=4/': ['4',\n",
       "  '4'],\n",
       " 's3://alpha-everyone/mratford/source_db/sales/employee_id=2/qtr=1/': ['2',\n",
       "  '1'],\n",
       " 's3://alpha-everyone/mratford/source_db/sales/employee_id=2/qtr=2/': ['2',\n",
       "  '2'],\n",
       " 's3://alpha-everyone/mratford/source_db/sales/employee_id=4/qtr=3/': ['4',\n",
       "  '3'],\n",
       " 's3://alpha-everyone/mratford/source_db/sales/employee_id=4/qtr=1/': ['4',\n",
       "  '1'],\n",
       " 's3://alpha-everyone/mratford/source_db/sales/employee_id=4/qtr=2/': ['4',\n",
       "  '2'],\n",
       " 's3://alpha-everyone/mratford/source_db/sales/employee_id=1/qtr=2/': ['1',\n",
       "  '2'],\n",
       " 's3://alpha-everyone/mratford/source_db/sales/employee_id=1/qtr=1/': ['1',\n",
       "  '1'],\n",
       " 's3://alpha-everyone/mratford/source_db/sales/employee_id=3/qtr=1/': ['3',\n",
       "  '1'],\n",
       " 's3://alpha-everyone/mratford/source_db/sales/employee_id=3/qtr=4/': ['3',\n",
       "  '4'],\n",
       " 's3://alpha-everyone/mratford/source_db/sales/employee_id=3/qtr=3/': ['3',\n",
       "  '3']}"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wr.catalog.get_partitions(database=db_name, table=\"sales\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9dcfe1dd-684d-4a74-beb0-c9a5371c6268",
   "metadata": {},
   "source": [
    "Check the data for one of the partitions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "a76161fc-9444-4047-a2c4-152372090d9d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['s3://alpha-everyone/mratford/source_db/sales/employee_id=1/qtr=4/a59b17a35bbf47d1bb2def454fb3bbc6.snappy.parquet']"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wr.s3.list_objects(f\"{source_db_base_path}sales/employee_id=1/qtr=4/\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1e7a85b5-4398-47c8-9399-bf15d32acf8f",
   "metadata": {},
   "source": [
    "Use an SQL like query to delete the partition and data for quarter 4."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "2fc17523-eba7-46d2-8fe8-94053600aaaa",
   "metadata": {},
   "outputs": [],
   "source": [
    "pydb.delete_partitions_and_data(\n",
    "    database=db_name, table=\"sales\", expression=\"qtr = 4\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "be3b4e55-ad50-4817-b720-2e1d9536b190",
   "metadata": {},
   "source": [
    "Check that the partition no longer exists."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "399c3be6-5fb0-481b-8e15-b42297b3563a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'s3://alpha-everyone/mratford/source_db/sales/employee_id=3/qtr=2/': ['3',\n",
       "  '2'],\n",
       " 's3://alpha-everyone/mratford/source_db/sales/employee_id=1/qtr=3/': ['1',\n",
       "  '3'],\n",
       " 's3://alpha-everyone/mratford/source_db/sales/employee_id=2/qtr=3/': ['2',\n",
       "  '3'],\n",
       " 's3://alpha-everyone/mratford/source_db/sales/employee_id=2/qtr=1/': ['2',\n",
       "  '1'],\n",
       " 's3://alpha-everyone/mratford/source_db/sales/employee_id=2/qtr=2/': ['2',\n",
       "  '2'],\n",
       " 's3://alpha-everyone/mratford/source_db/sales/employee_id=4/qtr=3/': ['4',\n",
       "  '3'],\n",
       " 's3://alpha-everyone/mratford/source_db/sales/employee_id=4/qtr=1/': ['4',\n",
       "  '1'],\n",
       " 's3://alpha-everyone/mratford/source_db/sales/employee_id=4/qtr=2/': ['4',\n",
       "  '2'],\n",
       " 's3://alpha-everyone/mratford/source_db/sales/employee_id=1/qtr=2/': ['1',\n",
       "  '2'],\n",
       " 's3://alpha-everyone/mratford/source_db/sales/employee_id=1/qtr=1/': ['1',\n",
       "  '1'],\n",
       " 's3://alpha-everyone/mratford/source_db/sales/employee_id=3/qtr=1/': ['3',\n",
       "  '1'],\n",
       " 's3://alpha-everyone/mratford/source_db/sales/employee_id=3/qtr=3/': ['3',\n",
       "  '3']}"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wr.catalog.get_partitions(database=db_name, table=\"sales\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "48a65836-0bb1-4a09-a7d5-7a341fe36f29",
   "metadata": {},
   "source": [
    "Check that the data no longer exists."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "c2babd0c-2851-4d98-ad8f-1f7081a4cc64",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wr.s3.list_objects(f\"{source_db_base_path}sales/employee_id=1/qtr=4/\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "574d56ef-cab5-4d37-939f-56bb5bf66aa5",
   "metadata": {},
   "source": [
    "Using a more complex query, delete quarters 1 and 2 for employee 3."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "6442cdd7-4e52-4ad8-bf46-7fe2bac733f8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'s3://alpha-everyone/mratford/source_db/sales/employee_id=1/qtr=3/': ['1',\n",
       "  '3'],\n",
       " 's3://alpha-everyone/mratford/source_db/sales/employee_id=2/qtr=3/': ['2',\n",
       "  '3'],\n",
       " 's3://alpha-everyone/mratford/source_db/sales/employee_id=2/qtr=1/': ['2',\n",
       "  '1'],\n",
       " 's3://alpha-everyone/mratford/source_db/sales/employee_id=2/qtr=2/': ['2',\n",
       "  '2'],\n",
       " 's3://alpha-everyone/mratford/source_db/sales/employee_id=4/qtr=3/': ['4',\n",
       "  '3'],\n",
       " 's3://alpha-everyone/mratford/source_db/sales/employee_id=4/qtr=1/': ['4',\n",
       "  '1'],\n",
       " 's3://alpha-everyone/mratford/source_db/sales/employee_id=4/qtr=2/': ['4',\n",
       "  '2'],\n",
       " 's3://alpha-everyone/mratford/source_db/sales/employee_id=1/qtr=2/': ['1',\n",
       "  '2'],\n",
       " 's3://alpha-everyone/mratford/source_db/sales/employee_id=1/qtr=1/': ['1',\n",
       "  '1'],\n",
       " 's3://alpha-everyone/mratford/source_db/sales/employee_id=3/qtr=3/': ['3',\n",
       "  '3']}"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pydb.delete_partitions_and_data(\n",
    "    database=db_name, table=\"sales\", expression=\"employee_id = 3 and qtr < 3\"\n",
    ")\n",
    "wr.catalog.get_partitions(database=db_name, table=\"sales\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ef3c1d5d-0f50-4a9b-957f-cf9d6938f0a2",
   "metadata": {},
   "source": [
    "See [the documentation](https://boto3.amazonaws.com/v1/documentation/api/latest/reference/services/glue.html#Glue.Client.get_partitions) for details on the expression syntax."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2472c24d-95aa-46f8-9650-da673f4e5a1b",
   "metadata": {},
   "source": [
    "## Deleting a database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "fae1b736-69d8-4e93-8064-998532158cf4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db_name in list(wr.catalog.databases()[\"Database\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "a5301983-931a-417e-a825-a9940cda6d93",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pydb.delete_database_and_data(db_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "05a01662-8f3b-4701-ac12-c197ea74d7d9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db_name in list(wr.catalog.databases()[\"Database\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "ceb9611a-15ef-47d5-8f1f-aa4a28204a49",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wr.s3.list_objects(source_db_base_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "a37074d1-573c-4fe3-803a-c3082a44f6c5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'s3://alpha-everyone/mratford/source_db/'"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "source_db_base_path"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e0e2cc80-aad8-4d75-b02d-19cc7f1c2b14",
   "metadata": {},
   "source": [
    "## Deleting temporary database tables\n",
    "\n",
    "It might be useful during development to get rid of the temporary database or one of it's tables if something has gone wrong. This can be accomplished by using `__temp__` as the database name in one of the functions above."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "6ec73350-12b6-464a-a162-8c5590ffd538",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "SELECT employee_id, sum(sales) as total_sales\n",
      "FROM aws_example_mratford.sales\n",
      "GROUP BY employee_id\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Setup source database\n",
    "# Create the database\n",
    "wr.catalog.create_database(db_name, exist_ok=True)\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",
    "\n",
    "    df = pd.read_csv(f\"data/{table_name}.csv\")\n",
    "    table_path = os.path.join(source_db_base_path, f\"{table_name}/\")\n",
    "    wr.s3.to_parquet(\n",
    "        df=df,\n",
    "        path=table_path,\n",
    "        index=False,\n",
    "        dataset=True,  # True allows the other params below i.e. overwriting to db.table\n",
    "        database=db_name,\n",
    "        table=table_name,\n",
    "        mode=\"overwrite\",\n",
    "    )\n",
    "\n",
    "sql = f\"\"\"\n",
    "SELECT employee_id, sum(sales) as total_sales\n",
    "FROM {db_name}.sales\n",
    "GROUP BY employee_id\n",
    "\"\"\"\n",
    "print(sql)\n",
    "pydb.create_temp_table(sql, table_name=\"total_sales\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "eccebc70-8e18-402e-88a4-d85ce68a848d",
   "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>total_sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>21</td>\n",
       "      <td>1643.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>93</td>\n",
       "      <td>688.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>101</td>\n",
       "      <td>817.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>60</td>\n",
       "      <td>1331.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>17</td>\n",
       "      <td>2302.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>2480.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>28</td>\n",
       "      <td>2071.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>6</td>\n",
       "      <td>2207.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>26</td>\n",
       "      <td>1994.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>3</td>\n",
       "      <td>2590.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>44</td>\n",
       "      <td>2184.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>12</td>\n",
       "      <td>2286.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>42</td>\n",
       "      <td>1688.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>20</td>\n",
       "      <td>2851.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>10</td>\n",
       "      <td>1935.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>32</td>\n",
       "      <td>2693.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>7</td>\n",
       "      <td>2465.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>19</td>\n",
       "      <td>2442.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>24</td>\n",
       "      <td>2248.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>220</td>\n",
       "      <td>1377.37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>1</td>\n",
       "      <td>2911.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>23</td>\n",
       "      <td>3036.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>38</td>\n",
       "      <td>2158.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>11</td>\n",
       "      <td>3092.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>59</td>\n",
       "      <td>927.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>43</td>\n",
       "      <td>2073.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>45</td>\n",
       "      <td>2778.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>37</td>\n",
       "      <td>1984.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>39</td>\n",
       "      <td>1931.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>46</td>\n",
       "      <td>2547.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>4</td>\n",
       "      <td>2996.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>63</td>\n",
       "      <td>721.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>9</td>\n",
       "      <td>2279.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>35</td>\n",
       "      <td>2624.64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>16</td>\n",
       "      <td>2373.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>47</td>\n",
       "      <td>1489.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>200</td>\n",
       "      <td>1108.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>57</td>\n",
       "      <td>953.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>13</td>\n",
       "      <td>2711.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>25</td>\n",
       "      <td>3099.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>31</td>\n",
       "      <td>2461.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>33</td>\n",
       "      <td>2685.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>80</td>\n",
       "      <td>611.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>34</td>\n",
       "      <td>1387.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>30</td>\n",
       "      <td>2251.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>29</td>\n",
       "      <td>2478.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>27</td>\n",
       "      <td>3050.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>2</td>\n",
       "      <td>1785.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>8</td>\n",
       "      <td>2155.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>15</td>\n",
       "      <td>2613.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>41</td>\n",
       "      <td>1326.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>40</td>\n",
       "      <td>2595.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>36</td>\n",
       "      <td>1580.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>18</td>\n",
       "      <td>1759.39</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    employee_id  total_sales\n",
       "0            21      1643.14\n",
       "1            93       688.05\n",
       "2           101       817.45\n",
       "3            60      1331.55\n",
       "4            17      2302.02\n",
       "5             5      2480.50\n",
       "6            28      2071.77\n",
       "7             6      2207.77\n",
       "8            26      1994.65\n",
       "9             3      2590.60\n",
       "10           44      2184.14\n",
       "11           12      2286.28\n",
       "12           42      1688.76\n",
       "13           20      2851.36\n",
       "14           10      1935.67\n",
       "15           32      2693.30\n",
       "16            7      2465.13\n",
       "17           19      2442.86\n",
       "18           24      2248.35\n",
       "19          220      1377.37\n",
       "20            1      2911.65\n",
       "21           23      3036.47\n",
       "22           38      2158.55\n",
       "23           11      3092.89\n",
       "24           59       927.30\n",
       "25           43      2073.25\n",
       "26           45      2778.84\n",
       "27           37      1984.24\n",
       "28           39      1931.27\n",
       "29           46      2547.63\n",
       "30            4      2996.54\n",
       "31           63       721.91\n",
       "32            9      2279.84\n",
       "33           35      2624.64\n",
       "34           16      2373.06\n",
       "35           47      1489.52\n",
       "36          200      1108.89\n",
       "37           57       953.09\n",
       "38           13      2711.01\n",
       "39           25      3099.01\n",
       "40           31      2461.53\n",
       "41           33      2685.07\n",
       "42           80       611.18\n",
       "43           34      1387.50\n",
       "44           30      2251.47\n",
       "45           29      2478.25\n",
       "46           27      3050.02\n",
       "47            2      1785.73\n",
       "48            8      2155.77\n",
       "49           15      2613.67\n",
       "50           41      1326.88\n",
       "51           40      2595.53\n",
       "52           36      1580.06\n",
       "53           18      1759.39"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pydb.read_sql_query(\"select * from __temp__.total_sales\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "a4cd41ce-0eec-4c0b-a02d-f066b00cc8b6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pydb.delete_database_and_data(\"__temp__\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "21b9a48b-5bd7-4d04-a9c6-2caa625dc0a6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query failed correctly.\n"
     ]
    }
   ],
   "source": [
    "try:\n",
    "    df = pydb.read_sql_query(\"select * from __temp__.total_sales\")\n",
    "    print(\"Error, temporary database not deleted correctly.\")\n",
    "except wr.exceptions.QueryFailed:\n",
    "    print(\"Query failed correctly.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f9548fd5-d4f0-45a5-a6b4-c4c661061bed",
   "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
}