Chapter 4 ■ Data transformation
Now let’s see what the data looks like. Recall to get data from the table, we use the SELECT command as
follows:
mysql> SELECT * FROM derived_annotation_test;
+----+--------------+------------------------+--------+-------+
| id | blood_oxygen | blood_oxygen_corrected | weight | volts |
+----+--------------+------------------------+--------+-------+
| 5 | 94.88 | 95.665 | 46.07 | 68.42 |
+----+--------------+------------------------+--------+-------+
1 row in set (0.00 sec)
Here we see the corrected value has been saved in the blood_oxygen_corrected column. Best of all, we
didn’t have to do anything when we added the data. Indeed, the beauty of a trigger is you set it once and it
works for all data inserted.
■Note some of the python code examples use a special library for connecting to mysQL called a database
connector. in this case, it is the Connector/python connector library from oracle (http://dev.mysql.com/
downloads/connector/python/). You will see this connector in much more detail in Chapters 6 and 8.
Derived and Calculated Columns Using a SELECT Statement
Recall we can also generate derived or calculated columns on the fly as the data is read. More specifically, we
include the operation as part of the SELECT statement. Let’s insert some data to see how this is done. In this
example, we will show how to calculate the difference since the last value read for the volts column. Recall
from the sample table, we do not have a column to store the delta. The following shows an example INSERT
that inserts multiple rows. This is also called a bulk insert.
INSERT INTO derived_annotation_test (blood_oxygen, weight, volts)
VALUES (94.88, 46.07, 68.42), (95.23, 46.56, 68.52), (94.97, 46.42, 68.62);
Notice we simply supply a comma-separated list of values, each representing a new row of data. In this
case, we use the same data as shown in the earlier Arduino sketch. Once the data is inserted, we can view it
as follows:
mysql> SELECT * FROM derived_annotation_test;
+----+--------------+------------------------+--------+-------+
| id | blood_oxygen | blood_oxygen_corrected | weight | volts |
+----+--------------+------------------------+--------+-------+
| 5 | 94.88 | 95.665 | 46.07 | 68.42 |
| 7 | 94.88 | 95.665 | 46.07 | 68.42 |
| 8 | 95.23 | 96.015 | 46.56 | 68.52 |
| 9 | 94.97 | 95.755 | 46.42 | 68.62 |
+----+--------------+------------------------+--------+-------+
4 rows in set (0.00 sec)
To calculate the delta, we write a Python script to connect to the database and retrieve the rows from
the table. We begin by saving the value of the first value read and then compare it on subsequent rows,
calculating the change since the last value was read. Listing 4-10 shows a Python script that uses the MySQL
Connector/Python library to read data from the server.