{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Delete Some Data\n",
    "\n",
    "We have created a table, inserted three records into it and made changes to one of those records.\n",
    "\n",
    "What if we want to delete a record completely?\n",
    "\n",
    "Lets's connect to our database and remind ourselves of its content:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>flight</th>\n",
       "      <th>ts</th>\n",
       "      <th>temp</th>\n",
       "      <th>pressure</th>\n",
       "      <th>humidity</th>\n",
       "      <th>accel_x</th>\n",
       "      <th>accel_y</th>\n",
       "      <th>accel_z</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>hab1</td>\n",
       "      <td>2015-01-01 09:00:00</td>\n",
       "      <td>25.5</td>\n",
       "      <td>1020</td>\n",
       "      <td>40</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>hab1</td>\n",
       "      <td>2015-01-01 09:01:00</td>\n",
       "      <td>25.5</td>\n",
       "      <td>1019</td>\n",
       "      <td>40</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>hab1</td>\n",
       "      <td>2015-01-01 09:02:00</td>\n",
       "      <td>25.5</td>\n",
       "      <td>1021</td>\n",
       "      <td>42</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  flight                  ts  temp  pressure  humidity  accel_x  accel_y  \\\n",
       "0   hab1 2015-01-01 09:00:00  25.5      1020        40      0.0      0.0   \n",
       "1   hab1 2015-01-01 09:01:00  25.5      1019        40      0.0      0.0   \n",
       "2   hab1 2015-01-01 09:02:00  25.5      1021        42      0.0      0.0   \n",
       "\n",
       "   accel_z  \n",
       "0      0.0  \n",
       "1      0.0  \n",
       "2      0.0  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import sqlalchemy as sa\n",
    "import pandas as pd\n",
    "engine = sa.create_engine('sqlite:///flight.db')\n",
    "connection = engine.connect()\n",
    "\n",
    "pd.read_sql('readings', connection)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We use a DELETE statement to remove a record entirely.\n",
    "\n",
    "Let's delete the first record in our table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>flight</th>\n",
       "      <th>ts</th>\n",
       "      <th>temp</th>\n",
       "      <th>pressure</th>\n",
       "      <th>humidity</th>\n",
       "      <th>accel_x</th>\n",
       "      <th>accel_y</th>\n",
       "      <th>accel_z</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>hab1</td>\n",
       "      <td>2015-01-01 09:01:00</td>\n",
       "      <td>25.5</td>\n",
       "      <td>1019</td>\n",
       "      <td>40</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>hab1</td>\n",
       "      <td>2015-01-01 09:02:00</td>\n",
       "      <td>25.5</td>\n",
       "      <td>1021</td>\n",
       "      <td>42</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  flight                  ts  temp  pressure  humidity  accel_x  accel_y  \\\n",
       "0   hab1 2015-01-01 09:01:00  25.5      1019        40      0.0      0.0   \n",
       "1   hab1 2015-01-01 09:02:00  25.5      1021        42      0.0      0.0   \n",
       "\n",
       "   accel_z  \n",
       "0      0.0  \n",
       "1      0.0  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = \"\"\"\n",
    "    DELETE FROM readings\n",
    "    WHERE flight = 'hab1' and pressure = 1020\n",
    "\"\"\"\n",
    "connection.execute(sql)\n",
    "pd.read_sql('readings', connection)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The WHERE clause is important. It defines the set of records we want to delete. \n",
    "\n",
    "If we omit the WHERE clause, the DELETE statement will remove all records from the table.\n",
    "\n",
    "Let's do that:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>flight</th>\n",
       "      <th>ts</th>\n",
       "      <th>temp</th>\n",
       "      <th>pressure</th>\n",
       "      <th>humidity</th>\n",
       "      <th>accel_x</th>\n",
       "      <th>accel_y</th>\n",
       "      <th>accel_z</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [flight, ts, temp, pressure, humidity, accel_x, accel_y, accel_z]\n",
       "Index: []"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "connection.execute(\"DELETE FROM readings\")\n",
    "pd.read_sql('readings', connection)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And now our table is back to its empty state just as when we first created it.\n",
    "\n",
    "If we want to delete the table itself, we use a DROP statement.\n",
    "\n",
    "Let's try it:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.result.ResultProxy at 0x105202d30>"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "connection.execute(\"DROP TABLE readings\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:sql_python_tutorial]",
   "language": "python",
   "name": "conda-env-sql_python_tutorial-py"
  },
  "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.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}