Chapter 4 ■ Data transformation

Code Implementation

In this example, we use Python code to determine which enumeration to use. The following code excerpt is
taken from another Python script that uses the Connector/Python library. Don’t worry too much about the
mechanics of the library; rather, notice how I use Python code to effect the enumeration.
The values read from the sensor are in ohms in the range (300 to 1500 +/-10%). So how do we know
what ranges to use? Do we install the sensor and take some measurements? We could do that. In fact, we
could use a tall container and fill it with water slowly while observing the values from the sensor. This will
work and is a good way to test the sensor, but there is another, faster way to get started.
Most manufacturers provide what is called a data sheet that describes how the device performs. In
this case, the manufacturer provides a graph that indicates what values to expect for certain levels of liquid
(http://cdn.sparkfun.com/datasheets/Sensors/ForceFlex/eTape%20Datasheet%2012110215TC-
8_040213.pdf). From this data, we can determine the following ranges correspond to the water levels
observed. Table 4-2 shows the complete data.

Table 4-2. Liquid-Level Enumerated Values for Pond Monitoring

Depth from Top of Sensor in Inches Value Range Result

0 < 1 1500 and higher HIGH

1 < n < 3 1150 to 1500 NORMAL

3 < n < 6 1150 to 700 LOW

6 < n 700 and lower CLEAN

Notice how some of the ranges overlap. Only use or experimentation will determine the actual
values, but these should provide a good start. Now let’s see the Python code (see Listing 4-11). Here we are
constructing an INSERT statement that includes the derived data from code preceding the execution of the
INSERT statement.

Listing 4-11. Derived Values Example (Python)

import mysql.connector;
from random import random, randint

def read_sensor():
return 500 + randint(0,1500)

strInsert = "INSERT INTO pond VALUES (null, {0}, '{1}')"
cnx = mysql.connector.connect(user="root", password="SECRET", database="test")
cur = cnx.cursor()

Calculate enumerated value for sensor.

water_level = read_sensor()
if water_level > 1500:
state = 'CLEAN'
elif water_level > 1150:
state = 'LOW'
elif water_level > 700:
state = 'NORMAL'
else:
state = 'HIGH'