Add Some Data¶
Now that we have a table, it's time to add some data into it.
First, let's connect to our database:
import sqlalchemy as sa
engine = sa.create_engine('sqlite:///flight.db')
connection = engine.connect()
To add data, we use an 'INSERT' statment.
We specify the columns for which we have data and then the values for the record we want to create.
In our case, we'll provide values for the flight name, timestamp and the teperature, pressure and humidity readings but we'll leave the accelerometer columns to have the default value of 0:
INSERT INTO readings(flight, ts, temp, pressure, humidity)
VALUES ('hab1', '2015-01-01 09:00:00', 25.5, 1020, 40)
Let's execute that statement:
sql = """
INSERT INTO readings(flight, ts, temp, pressure, humidity)
VALUES ('hab1', '2015-01-01 09:00:00', 25.5, 1020, 40)
"""
connection.execute(sql)
<sqlalchemy.engine.result.ResultProxy at 0x1124aa470>
A single INSERT statement can create multiple records:
INSERT INTO readings(flight, ts, temp, pressure, humidity)
VALUES
('hab1', '2015-01-01 09:01:00', 25.5, 1019, 40),
('hab1', '2015-01-01 09:02:00', 25.5, 1019, 41)
Let's execute this statment too so that our table has three records in total:
sql = """
INSERT INTO readings(flight, ts, temp, pressure, humidity)
VALUES
('hab1', '2015-01-01 09:01:00', 25.5, 1019, 40),
('hab1', '2015-01-01 09:02:00', 25.5, 1019, 41)
"""
connection.execute(sql)
<sqlalchemy.engine.result.ResultProxy at 0x1124b7048>
If you opted to run the code on your own computer and also installed the graphical tool, you can now click the 'Browse Data' button to view the three records you just inserted.
Our table has check constraints to validate the records we insert. What happens when we try to insert invalid records?
Let's try to create a record with a negative pressure reading:
sql = """
INSERT INTO readings(flight, ts, temp, pressure, humidity)
VALUES ('hab1', '2015-01-01 09:03:00', 25.5, -1000, 40)
"""
try:
connection.execute(sql)
except Exception as e:
print(e)
(sqlite3.IntegrityError) CHECK constraint failed: pres_ck [SQL: "\n INSERT INTO readings(flight, ts, temp, pressure, humidity)\n VALUES ('hab1', '2015-01-01 09:03:00', 25.5, -1000, 40)\n"] (Background on this error at: http://sqlalche.me/e/gkpj)
We see that SQLAlchemy raises an IntegrityError and the insert fails as expected.