Change The Data¶

To change records in a table, we use an 'UPDATE' statement.

First, let's connect to our database and remind ourselves what records we have created so far:

In [1]:

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

pd.read_sql('readings', connection)

Out[1]:

	flight	ts	temp	pressure	humidity
0	hab1	2015-01-01 09:00:00	25.5	1020	40
1	hab1	2015-01-01 09:01:00	25.5	1019	40
2	hab1	2015-01-01 09:02:00	25.5	1019	41

In an UPDATE statement, we define the table containing the records we want to change, the columns we want to change and the new values for those columns:

UPDATE readings
SET pressure = 1021, humidity = 42

If we were to run this statement, it would update all the records in our table.

Let's imagine we only wanted to change one of them. To do that, we need to include a 'WHERE' clause:

UPDATE readings
SET pressure = 1021, humidity = 42
WHERE flight = 'hab1' and humidity = 41

Let's execute this statement and use pandas to show us the updated result:

In [2]:

sql = """
    UPDATE readings
    SET pressure = 1021, humidity = 42
    WHERE flight = 'hab1' and humidity = 41
"""
connection.execute(sql)
pd.read_sql('readings', connection)

Out[2]:

	flight	ts	temp	pressure	humidity
0	hab1	2015-01-01 09:00:00	25.5	1020	40
1	hab1	2015-01-01 09:01:00	25.5	1019	40
2	hab1	2015-01-01 09:02:00	25.5	1021	42

We can see that the third record now has the updated pressure and humidity values and the first two records are unchanged.

We've seen previously that the check constraints on our table ensure that any record we try to insert must be valid. What happens if we attempt to update an existing record with invalid values?

Let's try changing one of our humidity values to a negative number:

In [3]:

sql = """
    UPDATE readings
    SET humidity = -40
    WHERE flight = 'hab1' and humidity = 41
"""
try:
    connection.execute(sql)
except Exception as e:
    print(e)

(sqlite3.IntegrityError) CHECK constraint failed: hum_ck [SQL: "\n    UPDATE readings\n    SET humidity = -40\n    WHERE flight = 'hab1' and humidity = 41\n"] (Background on this error at: http://sqlalche.me/e/gkpj)

Once again, SQLAlchemy raises an IntegrityError, our update fails and the check constraint has stopped us creating invalid records.