Chapter 4 ■ Data transformationListing 4-10. Calculated Columns Using SELECT (Python)
import mysql.connector;
cnx = mysql.connector.connect(user="root", password="SECRET")
cur = cnx.cursor()
cur.execute("SELECT * FROM test.derived_annotation_test")
old_value = 0;
for row in cur.fetchall():
if oldvalue > 0:
print "{0}, {1}, {2}, {3}, {4}".format(row[1], row[2], row[3], row[4], row[4] - old
value)
else:
print "{0}, {1}, {2}, {3}".format(row[1], row[2], row[3], row[4])
old_value = row[4]
While there may be more efficient ways to read the column values, I wrote the code to access each
column in the row to show you how the result is returned as a list (like an array). I simply access the column
number, which starts at 0, to get the value for the column. Note the value for the volts column is the fifth
column in the table and therefore is referenced with [4]. The following shows the output of running this
script:
$ python ./derived_select_example.py
94.88, 95.665, 46.07, 68.42,
94.88, 95.665, 46.07, 68.42, 0.0
95.23, 96.015, 46.56, 68.52, 0.1
94.97, 95.755, 46.42, 68.62, 0.1
Here we see the code prints out the data for the blood_oxygen, blood_oxygen_corrected, weight, and
volts. There is no value for the first row since we don’t have a previous value to use to calculate the delta.
The remaining rows show the delta or change since the last value as a fifth value.
Derived and Calculated Columns Using an INSERT Statement
Now let’s look at the INSERT method. We will use the volts derived data from earlier. In this case,
we have stored the old value at some point in the code so that when we save the new value, we include
the calculation there. The following code is an example of how to form the INSERT statement to include
calculations. While the formula is simple, the example shows how it is possible to populate derived or
calculated columns when the data is inserted.
Recall the calculation is performed on the client as opposed to the trigger example, which is executed
on the server. I leave for an exercise converting this example to a trigger. Before we see the SQL code, let’s
add the calculated column to the table. There is a powerful command named ALTER TABLE, which you can
use to add or remove columns, and much more. We use it to add the new column as follows:
ALTER TABLE derived_annotation_test ADD COLUMN avg_weight float AFTER weight;
Now we add the new data. Recall from the Arduino example, we want to simply take the weight value
and divide it by 4 to get the average weight for each item the sensor has measured as weight. The following
is another bulk insert statement. Notice we simply include the formula in the space for the average weight
column.