08-Link-Tables-Together.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Link Tables Together\n",
    "\n",
    "So far, we have worked with a single table table to hold readings taken from balloon flights.\n",
    "\n",
    "What if we wanted to hold details of the flights themselves?\n",
    "\n",
    "First, let's connect to our database:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlalchemy as sa\n",
    "import pandas as pd\n",
    "engine = sa.create_engine('sqlite:///flight.db')\n",
    "connection = engine.connect()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And now, let's create a table to hold a name for each flight along with the country code, latitude and longitude of where that flight took place:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.result.ResultProxy at 0x10639b4a8>"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = \"\"\"\n",
    "CREATE TABLE flights (\n",
    "    name          VARCHAR(10) NOT NULL,\n",
    "    country_code  VARCHAR(2) NOT NULL,\n",
    "    latitude      REAL NOT NULL DEFAULT 0.0,\n",
    "    longitude     REAL NOT NULL DEFAULT 0.0,\n",
    "\n",
    "    CONSTRAINT flights_pk PRIMARY KEY (name),\n",
    "    CONSTRAINT lat_ck CHECK (latitude BETWEEN -90 AND 90),\n",
    "    CONSTRAINT long_ck CHECK (longitude BETWEEN -180 AND 180)\n",
    ")\n",
    "\"\"\"\n",
    "connection.execute(sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We dropped our readings table earlier, so it no longer exists.\n",
    "\n",
    "We'll need to re-create it but this time we'll add an extra 'Foreign Key' constraint.\n",
    "\n",
    "This will ensure that any values in the 'flight' column must be valid entries in our new 'flights' table:\n",
    "\n",
    "\n",
    "```sql\n",
    "CREATE TABLE readings (\n",
    "    flight    VARCHAR(10) NOT NULL,\n",
    "    ts        TIMESTAMP NOT NULL,\n",
    "    temp      NUMERIC(3,1) NOT NULL,\n",
    "    pressure  NUMERIC(4,0) NOT NULL,\n",
    "    humidity  NUMERIC(3,0) NOT NULL,\n",
    "    accel_x   REAL DEFAULT 0 NOT NULL,\n",
    "    accel_y   REAL DEFAULT 0 NOT NULL,\n",
    "    accel_z   REAL DEFAULT 0 NOT NULL,\n",
    "\n",
    "    CONSTRAINT readings_pk PRIMARY KEY (flight, ts),\n",
    "    CONSTRAINT temp_ck CHECK (temp BETWEEN -70 AND 70),\n",
    "    CONSTRAINT pres_ck CHECK (pressure BETWEEN 0 AND 2000),\n",
    "    CONSTRAINT hum_ck CHECK (humidity BETWEEN 0 AND 100),\n",
    "    CONSTRAINT flights_fk FOREIGN KEY (flight) REFERENCES flights(name)\n",
    "```\n",
    "\n",
    "`CONSTRAINT flights_fk FOREIGN KEY (flight) REFERENCES flights(name)` can be read as:\n",
    "\n",
    "* Create a constraint and name it 'flights_fk'\n",
    "* It's a foreign key constraint on the 'flight' column in this table\n",
    "* Any value in the flight column must refer to a value in the 'name' column on the 'flights' table\n",
    "\n",
    "But what if we have readings for a flight which we then delete? That would break the constraint we just defined.\n",
    "\n",
    "We can specify what should happen in the readings table when a record in the flights table is updated or deleted.\n",
    "\n",
    "We'll say that any deletions should be cascaded to our readings table meaning that all readings for that flight should also be deleted. (You can read more detail about the other possible options in the [SQLite documentation]([https://sqlite.org/foreignkeys.html)):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.result.ResultProxy at 0x10d1bb9e8>"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = \"\"\"\n",
    "CREATE TABLE readings (\n",
    "    flight    VARCHAR(10) NOT NULL,\n",
    "    ts        TIMESTAMP NOT NULL,\n",
    "    temp      NUMERIC(3,1) NOT NULL,\n",
    "    pressure  NUMERIC(4,0) NOT NULL,\n",
    "    humidity  NUMERIC(3,0) NOT NULL,\n",
    "    accel_x   REAL DEFAULT 0 NOT NULL,\n",
    "    accel_y   REAL DEFAULT 0 NOT NULL,\n",
    "    accel_z   REAL DEFAULT 0 NOT NULL,\n",
    "\n",
    "    CONSTRAINT readings_pk PRIMARY KEY (flight, ts),\n",
    "    CONSTRAINT temp_ck CHECK (temp BETWEEN -70 AND 70),\n",
    "    CONSTRAINT pres_ck CHECK (pressure BETWEEN 0 AND 2000),\n",
    "    CONSTRAINT hum_ck CHECK (humidity BETWEEN 0 AND 100),\n",
    "    CONSTRAINT flights_fk FOREIGN KEY (flight)\n",
    "        REFERENCES flights(name) ON DELETE CASCADE\n",
    ")\n",
    "\"\"\"\n",
    "connection.execute(sql)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For reasons of backwards compatibility, foreign keys are not enabled by default in SQLite and we need to issue a command to switch them on:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.result.ResultProxy at 0x10d1bbe10>"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "connection.execute(\"PRAGMA foreign_keys=on\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's try to re-create the readings records from chapter 3:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(sqlite3.IntegrityError) FOREIGN KEY constraint failed [SQL: \"\\n    INSERT INTO readings(flight, ts, temp, pressure, humidity)\\n    VALUES \\n        ('hab1', '2015-01-01 09:00:00', 25.5, 1020, 40),\\n        ('hab1', '2015-01-01 09:01:00', 25.5, 1019, 40),\\n        ('hab1', '2015-01-01 09:02:00', 25.5, 1019, 41)\\n\"] (Background on this error at: http://sqlalche.me/e/gkpj)\n"
     ]
    }
   ],
   "source": [
    "sql = \"\"\"\n",
    "    INSERT INTO readings(flight, ts, temp, pressure, humidity)\n",
    "    VALUES \n",
    "        ('hab1', '2015-01-01 09:00:00', 25.5, 1020, 40),\n",
    "        ('hab1', '2015-01-01 09:01:00', 25.5, 1019, 40),\n",
    "        ('hab1', '2015-01-01 09:02:00', 25.5, 1019, 41)\n",
    "\"\"\"\n",
    "try:\n",
    "    connection.execute(sql)\n",
    "except Exception as e:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SQLAlchemy raises an Integrity error because we tried to create readings for flight 'hab1' but no such record exists in our 'flights' table.\n",
    "\n",
    "Let's put that right and try again. We'll create a record for the 'hab' flight but leave it with default values for latitude and longitude:"
   ]
  },
  {
   "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>name</th>\n",
       "      <th>country_code</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>hab1</td>\n",
       "      <td>GB</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   name country_code  latitude  longitude\n",
       "0  hab1           GB       0.0        0.0"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "connection.execute(\"INSERT INTO flights(name, country_code) VALUES ('hab1', 'GB')\")\n",
    "pd.read_sql('flights', connection)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, we should be able to create our readings:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "connection.execute(sql)\n",
    "pd.read_sql('readings', connection)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's create a second flight:"
   ]
  },
  {
   "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>name</th>\n",
       "      <th>country_code</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>hab1</td>\n",
       "      <td>GB</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>hab2</td>\n",
       "      <td>GB</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   name country_code  latitude  longitude\n",
       "0  hab1           GB       0.0        0.0\n",
       "1  hab2           GB       0.0        0.0"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "connection.execute(\"INSERT INTO flights(name, country_code) VALUES ('hab2', 'GB')\")\n",
    "pd.read_sql('flights', connection)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And insert some readings for that second flight:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>hab2</td>\n",
       "      <td>2015-01-01 09:00:00</td>\n",
       "      <td>27.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>4</th>\n",
       "      <td>hab2</td>\n",
       "      <td>2015-01-01 09:01:00</td>\n",
       "      <td>25.0</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>5</th>\n",
       "      <td>hab2</td>\n",
       "      <td>2015-01-01 09:02:00</td>\n",
       "      <td>26.2</td>\n",
       "      <td>1021</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",
       "3   hab2 2015-01-01 09:00:00  27.5      1020        40      0.0      0.0   \n",
       "4   hab2 2015-01-01 09:01:00  25.0      1019        40      0.0      0.0   \n",
       "5   hab2 2015-01-01 09:02:00  26.2      1021        41      0.0      0.0   \n",
       "\n",
       "   accel_z  \n",
       "0      0.0  \n",
       "1      0.0  \n",
       "2      0.0  \n",
       "3      0.0  \n",
       "4      0.0  \n",
       "5      0.0  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = \"\"\"\n",
    "    INSERT INTO readings(flight, ts, temp, pressure, humidity)\n",
    "    VALUES \n",
    "        ('hab2', '2015-01-01 09:00:00', 27.5, 1020, 40),\n",
    "        ('hab2', '2015-01-01 09:01:00', 25.0, 1019, 40),\n",
    "        ('hab2', '2015-01-01 09:02:00', 26.2, 1021, 41)\n",
    "\"\"\"\n",
    "connection.execute(sql)\n",
    "pd.read_sql('readings', connection)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can now see our 'ON CASCADE DELETE' in action. We'll delete flight 'hab2' from the 'flights' table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>name</th>\n",
       "      <th>country_code</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>hab1</td>\n",
       "      <td>GB</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   name country_code  latitude  longitude\n",
       "0  hab1           GB       0.0        0.0"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "connection.execute(\"DELETE FROM flights where name = 'hab2'\")\n",
    "pd.read_sql('flights', connection)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We should see that the readings for hab2 were also deleted:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql('readings', connection)"
   ]
  }
 ],
 "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
}