Recording Data

To record data from a sensor we will use a Python program that checks the sensor every minute and writes the temperature (with a time stamp) into our MySQL database. First we’ll need to install a package that will help us connect Python to MySQL:

sudo apt-get install python3-mysqldb

Database preparation

First we will set up our database table that will store our data.

Using the phpMyAdmin web interface (http://localhost/phpmyadmin) that we set up, log on using the administrator (root) account and create a database called ‘measurements’.

Enter in the name of the table and the number of columns that we are going to use for our measured values. In the screenshot above we can see that the name of the table is ‘temperature’ (how imaginative) and the number of columns is ‘2’.

We will use two columns so that we can store a temperature reading and the time it was recorded.

Once we click on ‘Go’ we are presented with a list of options to configure our table’s columns. Don’t be intimidated by the number of options that are presented, we are going to keep the process as simple as practical.

For the first column we can enter the name of the ‘Column’ as ‘dtg’ (short for date time group) the ‘Type’ as ‘TIMESTAMP’ and the ‘Default’ value as ‘CURRENT_TIMESTAMP’. For the second column we will enter the name ‘temperature’ and the ‘Type’ is ‘FLOAT’ (we won’t use a default value).

Scroll down a little and click on the ‘Save’ button and we’re done.

Why did we choose those particular settings for our table?

Our ‘dtg’ column needs to store a value of time that includes the date and the time, so either of the types ‘TIMESTAMP’ or ‘DATETIME’ would be suitable. Either of them stores the time in the format ‘YYYY-MM-DD HH:MM:SS’. The advantage of selecting TIMESTAMP in this case is that we can select the default value to be the current time which means that when we write our data to the table we only need to write the temperature value and the ‘dtg’ will be entered automatically for us. The disadvantage of using ‘TIMESTAMP’ is that it has a more limited range than DATETIME. TIMESTAMP can only have a range between ‘1970-01-01 00:00:01’ to ‘2038-01-19 03:14:07’.

Our temperature readings are generated (by our sensor) as an integer value that needs to be divided by 1000 to show degrees Centigrade. We could therefore store the value as an integer. However when we were selecting the data or in later processing we would then need to do the math to convert it to the correct value. It could be argued (successfully) that this would be a more efficient solution in terms of the amount of space taken to support the data on the Pi. However, I have a preference for storing the values as they would be used later and as a result we need to use a numerical format that supports numbers with decimal places. There are a range of options for defining the ranges for decimal numbers, but FLOAT allows us to ignore the options (at the expense of efficiency) and rely on our recorded values being somewhere between -3.402823466E+38 and 3.402823466E+38 (if our temperature falls outside those extremes we are in trouble).

Record the temperature values

The following code (which is based on the code that is part of the great temperature sensing tutorial on Adafruit) is a Python script which allows us to check the temperature reading from the sensor approximately every 10 seconds and write it to our database.

The full code can be found here(pi_temp.py).

#!/usr/bin/python
# -*- coding: utf-8 -*-

import os
import glob
import time
import MySQLdb as mdb

os.system('modprobe w1-gpio')
os.system('modprobe w1-therm')

base_dir = '/sys/bus/w1/devices/'
device_folder = glob.glob(base_dir + '28*')[0]
device_file = device_folder + '/w1_slave'

def read_temp_raw():
f = open(device_file, 'r')
lines = f.readlines()
f.close()
return lines

def read_temp():
lines = read_temp_raw()
while lines[0].strip()[-3:] != 'YES':
time.sleep(0.2)
lines = read_temp_raw()
equals_pos = lines[1].find('t=')
if equals_pos != -1:
temp_string = lines[1][equals_pos+2:]
temp_c = float(temp_string) / 1000.0
return temp_c

while True:

try:
pi_temp = read_temp()
con = mdb.connect('localhost', \
'pi_insert', \
'xxxxxxxxxx', \
'measurements');
cur = con.cursor()
cur.execute("""INSERT INTO temperature(temperature) \
VALUES(%s)""", (pi_temp))
con.commit()

except mdb.Error, e:
con.rollback()
print "Error %d: %s" % (e.args[0],e.args[1])
sys.exit(1)

finally:
if con:
con.close()

time.sleep(10)

Additionally, if you’d like to use the Grove Pi, I’ve altered the grove_sound_sensor.py script to store the data values to our Measurements database.

#!/usr/bin/env python
import time
import grovepi
import MySQLdb as mdb

# Connect the Grove Sound Sensor to analog port A0
sound_sensor = 0

# Connect the Grove LED to digital port D5
led = 5

grovepi.pinMode(sound_sensor,"INPUT")
grovepi.pinMode(led,"OUTPUT")

def read_sensor():
        # Read the sound level
        sensor_value = grovepi.analogRead(sound_sensor)
        print("sensor_value = %d" %sensor_value)
        return sensor_value

while True:

    try:
        sensor_value = read_sensor()
        connection = mdb.connect('localhost',
                          'root',
                          'yourPassword',
                          'measurements');
        cursor = connection.cursor()
        cursor.execute("""INSERT INTO temperature(temperature)
                       VALUES(%s)""", (sensor_value))
        connection.commit()

    except mdb.Error, e:
        connection.rollback()
        print "Error %d: %s" % (e.args[0],e.args[1])
        sys.exit(1)

    finally:
        if connection:
            connection.close()

	time.sleep(10)