Chapter 4 ■ Data transformation
cur.execute(strInsert.format(water_level, state))
cnx.commit()
cur.close()
cnx.close()
Take a moment to read through the code. Don’t worry too much about the connector library. Focus
instead on the code used to set the value for the state. It is rather straightforward, and the value determined
is used in the INSERT statement via parameter substitution.
To test the code, we need to create a test table as follows. Notice I add the two columns we will need
along with an auto-increment column for ease of identifying the rows.
CREATE TABLE pond (
id int(11) NOT NULL AUTO_INCREMENT,
water_level int NOT NULL,
state char(12) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
Next, we can run the script several times to create several rows. Savvy Python developers will see I
simulated the sensor read code with a randomly generated value. Clearly, this will create quite a number of
rows with wildly varying values. That is, in an actual pond, the water level would not fluctuate so radically
from one moment to another. However, the code is sufficient to test and demonstrate the technique of
enumerated values. Once several rows have been inserted, we can see the results as follows:
mysql> SELECT * FROM pond;
+----+-------------+--------+
| id | water_level | state |
+----+-------------+--------+
| 8 | 1054 | NORMAL |
| 10 | 1117 | NORMAL |
| 11 | 1278 | LOW |
| 12 | 1316 | LOW |
| 13 | 1451 | LOW |
| 14 | 1688 | CLEAN |
+----+-------------+--------+
6 rows in set (0.00 sec)
Take a moment to ensure the values match the enumerated value based on the data in Table 4-2.
I encourage you to run the script a number of times until you see an example for each state. Hint: you may
want to alter the read_sensor() method to return a random number for each range instead of a range from
0 to 1500.
Database Considerations
There are several ways to handle data interpretation in the database. I will discuss two such methods: using
enumerated values where we determine the state when we save the data (still requires a bit of code) and
using an advanced technique to determine the state automatically when the data is saved or updated.
Yes, another trigger!