Dynamic SQL¶
So far, the values we have used our INSERT and UPDATE statements have been contained within the SQL statements themselves.
Now let's see how we can safely pass values in from our Python code.
First, let's connect to our database and re-create our readings table (because we dropped it at the end of the previous chapter):
import sqlalchemy as sa
import pandas as pd
engine = sa.create_engine('sqlite:///flight.db')
connection = engine.connect()
sql = """
CREATE TABLE readings (
flight VARCHAR(10) NOT NULL,
ts TIMESTAMP NOT NULL,
temp NUMERIC(3,1) NOT NULL,
pressure NUMERIC(4,0) NOT NULL,
humidity NUMERIC(3,0) NOT NULL,
accel_x REAL DEFAULT 0 NOT NULL,
accel_y REAL DEFAULT 0 NOT NULL,
accel_z REAL DEFAULT 0 NOT NULL,
CONSTRAINT readings_pk PRIMARY KEY (flight, ts),
CONSTRAINT temp_ck CHECK (temp BETWEEN -70 AND 70),
CONSTRAINT pres_ck CHECK (pressure BETWEEN 0 AND 2000),
CONSTRAINT hum_ck CHECK (humidity BETWEEN 0 AND 100)
)
"""
connection.execute(sql)
<sqlalchemy.engine.result.ResultProxy at 0x115ef47b8>
We'll re-create the readings from chapter 3.
Let's use a list of named tuples to hold that data:
from collections import namedtuple
Reading = namedtuple('Reading', 'flight, ts, temp, pressure, humidity')
readings = [
Reading(flight='hab1', ts='2015-01-01 09:00:00', temp=25.5, pressure=1020, humidity=40),
Reading(flight='hab1', ts='2015-01-01 09:01:00', temp=25.5, pressure=1019, humidity=40),
Reading(flight='hab1', ts='2015-01-01 09:02:00', temp=25.5, pressure=1019, humidity=41),
]
We'll now define our SQL statement using 'bound parameters.' These are a standard feature of SQL and will work across most DBMSs.
Each '?' below is a placeholder which will take a value from our Python code at execution time.
sql = """
INSERT INTO readings
(flight, ts, temp, pressure, humidity)
VALUES
(?, ?, ?, ?, ?)
"""
We can now loop over our readings list and execute our SQL statement once for each entry.
for reading in readings:
values = (reading.flight, reading.ts, reading.temp, reading.pressure, reading.humidity)
connection.execute(sql, values)
pd.read_sql('readings', connection)
| flight | ts | temp | pressure | humidity | accel_x | accel_y | accel_z | |
|---|---|---|---|---|---|---|---|---|
| 0 | hab1 | 2015-01-01 09:00:00 | 25.5 | 1020 | 40 | 0.0 | 0.0 | 0.0 |
| 1 | hab1 | 2015-01-01 09:01:00 | 25.5 | 1019 | 40 | 0.0 | 0.0 | 0.0 |
| 2 | hab1 | 2015-01-01 09:02:00 | 25.5 | 1019 | 41 | 0.0 | 0.0 | 0.0 |
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.