{ "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": [ "" ] }, "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": [ "" ] }, "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": [ "" ] }, "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": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namecountry_codelatitudelongitude
0hab1GB0.00.0
\n", "
" ], "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": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
flighttstemppressurehumidityaccel_xaccel_yaccel_z
0hab12015-01-01 09:00:0025.51020400.00.00.0
1hab12015-01-01 09:01:0025.51019400.00.00.0
2hab12015-01-01 09:02:0025.51019410.00.00.0
\n", "
" ], "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": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namecountry_codelatitudelongitude
0hab1GB0.00.0
1hab2GB0.00.0
\n", "
" ], "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": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
flighttstemppressurehumidityaccel_xaccel_yaccel_z
0hab12015-01-01 09:00:0025.51020400.00.00.0
1hab12015-01-01 09:01:0025.51019400.00.00.0
2hab12015-01-01 09:02:0025.51019410.00.00.0
3hab22015-01-01 09:00:0027.51020400.00.00.0
4hab22015-01-01 09:01:0025.01019400.00.00.0
5hab22015-01-01 09:02:0026.21021410.00.00.0
\n", "
" ], "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": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namecountry_codelatitudelongitude
0hab1GB0.00.0
\n", "
" ], "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": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
flighttstemppressurehumidityaccel_xaccel_yaccel_z
0hab12015-01-01 09:00:0025.51020400.00.00.0
1hab12015-01-01 09:01:0025.51019400.00.00.0
2hab12015-01-01 09:02:0025.51019410.00.00.0
\n", "
" ], "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 }