Notebook

Create a Table¶

Before we can add any data to our database, we'll need to create a table in which to hold it.

First, let's connect to our database:

In [1]:

import sqlalchemy as sa
engine = sa.create_engine('sqlite:///flight.db')
connection = engine.connect()

We want to hold readings of temperature and pressure taken during balloon flights.

We'll use a table with eight columns for the flight name, a timestamp, measured values for temperature, pressure and humidity and some readings from an onboard accelerometer.

Here's the sql to create that table:

CREATE TABLE readings (
    flight,
    ts,
    temp,
    pressure,
    humidity,
    accel_x,
    accel_y,
    accel_z,
)

NOTE: It is a common convention that SQL keywords are capitalised but this isn't strictly necessary in order to be valid SQL. It's largely redundant today since most editors will apply syntax highlighting to SQL but, for this tutorial, the convention is useful as we'll see shortly.

Although this SQL statement would work perfectly in SQLite, it is non-standard and wouldn't work in any other DBMS.

In most DBMSs, we have to define the data type for each column. For the purposes of this tutorial, we'll write SQL that conforms to the standard and would work in any DBMS including SQLite.

For a full description of exactly how SQLite differs, you can read the SQLite documentation.

Here's the standard form of the SQL to create our table with the data types included:

CREATE TABLE readings (
    flight    VARCHAR(10),
    ts        TIMESTAMP,
    temp      NUMERIC(3,1),
    pressure  NUMERIC(4,0),
    humidity  NUMERIC(3,0),
    accel_x   REAL,
    accel_y   REAL,
    accel_z   REAL,
)

We defined the flight name as a variable length character string (VARCHAR) and we set a maximum length for those strings of 10 characters.

Most DBMSs have a VARCHAR data type (although they may use a different name) and also a type for holding strings of fixed length.

The column 'ts' will hold the date and time of the reading.

For the temperature, pressure and humidity values, we defined those to be numeric values with precision (the maximum number of digits in the number) and scale (the number of digits following the decimal point).

i.e. Temperature readings will have 3 digits in total with one of those to the right of the decimal point. Pressure and humidity readings will both be integers of 4 and 3 digits respectively.

The accelerometer readings will be floating point numbers.

By default, each column will allow null values but we can specify that nulls are not allowed and, optionally, whether a default value should be used instead:

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,
)

We can (and should) define a 'primary key' constraint for the table. This defines a field, or combination of fields, which must be unique for each record. The primary key can then be used by the DBMS to perform efficient indexed searches of the table contents.

The constraint is given a name which, by convention, is often suffixed with _pk but can be anything you like:

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),
)

We can also add constraints to each column so that the database will throw an error if any attempt is made to enter invalid values. These are know as CHECK constraints and again, each has a name - commonly the field to which the constraint applies followed by _ck:

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)
)

In order to execute an SQL statement, we use the 'execute' method of our connection object and pass it our SQL statement as a string.

Let's create a variable to hold our SQL statement and pass that variable to the execute method.

This technique is why the capitalisation convention is useful for us. Most editors cannot handle syntax highlighting for SQL within string variables

In [2]:

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)

Out[2]:

<sqlalchemy.engine.result.ResultProxy at 0x10eccd0f0>

If you opted to run the code on your own computer and also installed the graphical tool, you should now be able to open 'flight.db' and see the new table within your database.