<jupyter_start><jupyter_text>Dynamic SQLSo 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):<jupyter_code>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)<jupyter_output><empty_output><jupyter_text>We'll re-create the readings from chapter 3.Let's use a list of named tuples to hold that data:<jupyter_code>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),
]<jupyter_output><empty_output><jupyter_text>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.<jupyter_code>sql = """
INSERT INTO readings
    (flight, ts, temp, pressure, humidity)
VALUES
    (?, ?, ?, ?, ?)
"""<jupyter_output><empty_output><jupyter_text>We can now loop over our readings list and execute our SQL statement once for each entry.<jupyter_code>for reading in readings:
    values = (reading.flight, reading.ts, reading.temp, reading.pressure, reading.humidity)
    connection.execute(sql, values)
pd.read_sql('readings', connection)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10167364016736402, 'fraction_numerical': 0.040585774058577405, 'mean_word_length': 4.7614457831325305, 'pattern_counts': {'":': 0, '<': 17, '<?xml version=': 0, '>': 17, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '480389', 'license': 'MIT', 'n_tokens_mistral': 849, 'n_tokens_neox': 741, 'n_words': 261, 'path': '07-Dynamic-SQL.ipynb', 'repo_name': 'meatballs/sql_python_notebooks', 'size': '2390'}