05-Change-the-Data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Change The Data\n",
    "\n",
    "To change records in a table, we use an 'UPDATE' statement.\n",
    "\n",
    "First, let's connect to our database and remind ourselves what records we have created so far:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "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>1019</td>\n",
       "      <td>41</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      1019        41      0.0      0.0   \n",
       "\n",
       "   accel_z  \n",
       "0      0.0  \n",
       "1      0.0  \n",
       "2      0.0  "
      ]
     },
     "execution_count": 1,
     "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": [
    "In an UPDATE statement, we define the table containing the records we want to change, the columns we want to change and the new values for those columns:\n",
    "\n",
    "```sql\n",
    "UPDATE readings\n",
    "SET pressure = 1021, humidity = 42\n",
    "```\n",
    "\n",
    "If we were to run this statement, it would update all the records in our table.\n",
    "\n",
    "Let's imagine we only wanted to change one of them. To do that, we need to include a 'WHERE' clause:\n",
    "\n",
    "```sql\n",
    "UPDATE readings\n",
    "SET pressure = 1021, humidity = 42\n",
    "WHERE flight = 'hab1' and humidity = 41\n",
    "```\n",
    "\n",
    "Let's execute this statement and use pandas to show us the updated result:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = \"\"\"\n",
    "    UPDATE readings\n",
    "    SET pressure = 1021, humidity = 42\n",
    "    WHERE flight = 'hab1' and humidity = 41\n",
    "\"\"\"\n",
    "connection.execute(sql)\n",
    "pd.read_sql('readings', connection)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see that the third record now has the updated pressure and humidity values and the first two records are unchanged.\n",
    "\n",
    "We've seen previously that the check constraints on our table ensure that any record we try to insert must be valid. What happens if we attempt to update an existing record with invalid values?\n",
    "\n",
    "Let's try changing one of our humidity values to a negative number:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(sqlite3.IntegrityError) CHECK constraint failed: hum_ck [SQL: \"\\n    UPDATE readings\\n    SET humidity = -40\\n    WHERE flight = 'hab1' and humidity = 41\\n\"] (Background on this error at: http://sqlalche.me/e/gkpj)\n"
     ]
    }
   ],
   "source": [
    "sql = \"\"\"\n",
    "    UPDATE readings\n",
    "    SET humidity = -40\n",
    "    WHERE flight = 'hab1' and humidity = 41\n",
    "\"\"\"\n",
    "try:\n",
    "    connection.execute(sql)\n",
    "except Exception as e:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once again, SQLAlchemy raises an IntegrityError, our update fails and the check constraint has stopped us creating invalid records."
   ]
  }
 ],
 "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
}