{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Dynamic SQL\n", "\n", "So far, the values we have used our INSERT and UPDATE statements have been contained within the SQL statements themselves.\n", "\n", "Now let's see how we can safely pass values in from our Python code.\n", "\n", "First, let's connect to our database and re-create our readings table (because we dropped it at the end of the previous chapter):" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "" ] }, "execution_count": 2, "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", "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", ")\n", "\"\"\"\n", "connection.execute(sql)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We'll re-create the readings from chapter 3.\n", "\n", "Let's use a list of named tuples to hold that data:" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [], "source": [ "from collections import namedtuple\n", "\n", "Reading = namedtuple('Reading', 'flight, ts, temp, pressure, humidity')\n", "\n", "readings = [\n", " Reading(flight='hab1', ts='2015-01-01 09:00:00', temp=25.5, pressure=1020, humidity=40),\n", " Reading(flight='hab1', ts='2015-01-01 09:01:00', temp=25.5, pressure=1019, humidity=40),\n", " Reading(flight='hab1', ts='2015-01-01 09:02:00', temp=25.5, pressure=1019, humidity=41),\n", "]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We'll now define our SQL statement using 'bound parameters.' These are a standard feature of SQL and will work across most DBMSs.\n", "\n", "Each '?' below is a placeholder which will take a value from our Python code at execution time." ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [], "source": [ "sql = \"\"\"\n", "INSERT INTO readings\n", " (flight, ts, temp, pressure, humidity)\n", "VALUES\n", " (?, ?, ?, ?, ?)\n", "\"\"\"" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We can now loop over our readings list and execute our SQL statement once for each entry." ] }, { "cell_type": "code", "execution_count": 18, "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": 18, "metadata": {}, "output_type": "execute_result" } ], "source": [ "for reading in readings:\n", " values = (reading.flight, reading.ts, reading.temp, reading.pressure, reading.humidity)\n", " connection.execute(sql, values)\n", "pd.read_sql('readings', connection)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "**NOTE:** Using bound parameters is the only safe way to pass values from Python into SQL. Do not be tempted to build the SQL statement using string manipulation as this is a well known security vulnerability." ] } ], "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.7.0" } }, "nbformat": 4, "nbformat_minor": 2 }