Delete Some Data¶
We have created a table, inserted three records into it and made changes to one of those records.
What if we want to delete a record completely?
Lets's connect to our database and remind ourselves of its content:
In [5]:
import sqlalchemy as sa
import pandas as pd
engine = sa.create_engine('sqlite:///flight.db')
connection = engine.connect()
pd.read_sql('readings', connection)
Out[5]:
| 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 | 1021 | 42 | 0.0 | 0.0 | 0.0 |
We use a DELETE statement to remove a record entirely.
Let's delete the first record in our table:
In [6]:
sql = """
DELETE FROM readings
WHERE flight = 'hab1' and pressure = 1020
"""
connection.execute(sql)
pd.read_sql('readings', connection)
Out[6]:
| flight | ts | temp | pressure | humidity | accel_x | accel_y | accel_z | |
|---|---|---|---|---|---|---|---|---|
| 0 | hab1 | 2015-01-01 09:01:00 | 25.5 | 1019 | 40 | 0.0 | 0.0 | 0.0 |
| 1 | hab1 | 2015-01-01 09:02:00 | 25.5 | 1021 | 42 | 0.0 | 0.0 | 0.0 |
The WHERE clause is important. It defines the set of records we want to delete.
If we omit the WHERE clause, the DELETE statement will remove all records from the table.
Let's do that:
In [10]:
connection.execute("DELETE FROM readings")
pd.read_sql('readings', connection)
Out[10]:
| flight | ts | temp | pressure | humidity | accel_x | accel_y | accel_z |
|---|
And now our table is back to its empty state just as when we first created it.
If we want to delete the table itself, we use a DROP statement.
Let's try it:
In [8]:
connection.execute("DROP TABLE readings")
Out[8]:
<sqlalchemy.engine.result.ResultProxy at 0x105202d30>