Raspberry Pi Temperature Sensor Web Server - Part 2 (Setting up and writing to a MySQL Database)

To store our temperature values we will need a database, we will use MySQL for this. To access the database online we will need a web server and a script to run on it, Apache and PHP respectively.

To install Apache and PHP on the raspberry pi enter this command, when prompted type y.

sudo apt-get install apache2 php5 libapache2-mod-php5

Once complete you can test the web server by simply entering the IP address of the Pi into a web browser of a device that is on the same local network as the Pi. You will get the following page if successful:

Screen Shot 2014-12-28 at 14.17.49

Now install MySQL by entering the following, press y again when prompted.

sudo apt-get install mysql-server mysql-client php5-mysql

A prompt will pop up asking you to enter a password for the MySQL database. Make sure you remember it/write it down!!!

We will also be editing our MySQL database from our Python script in the future so download the corresponding Python library:

sudo apt-get install python-mysqldb

Now open MySQL:

sudo mysql –u –p

This logs us in to MySQL as the root user (-u) and it will prompt for a password (-p) on entry.

Enter the password you created earlier.

We are now going to create a database, I called mine temp_database which is a bit unimaginative.

CREATE DATABASE temp_database;

We can check this has worked by entering the below. A list of the databases currently held by MySQL will be displayed. Don't be alarmed if there is more than the one you just created.

SHOW DATABASES;

+--------------------+ | Database           | +--------------------+ | information_schema | | mysql             | | performance_schema | | temp_database     | +--------------------+ 4 rows in set (0.00 sec)

Now we want to make a new table in the temp_database. To this we firstly have to tell MySQL that we wish to use the temp_database:

USE temp_database;

We now create a table in MySQL using the following commands. MySQL commands are out of the scope of this blog but essentially we are making a table called tempLog that has two fields; datetime (of type DATETIME) and temperature(of type FLOAT). Both must have values (i.e. not null).

CREATE TABLE tempLog(datetime DATETIME NOT NULL, temperature FLOAT(5,2) NOT NULL);

To check that our table is correct we can check by entering the following:

mysql> DESCRIBE tempLog;

You will get the following output, describing the table's fields.

+-------------+------------+------+-----+---------+-------+ | Field       | Type       | Null | Key | Default | Extra | +-------------+------------+------+-----+---------+-------+ | datetime   | datetime   | NO   |     | NULL   |       | | temperature | float(5,2) | NO   |     | NULL   |       | +-------------+------------+------+-----+---------+-------+

Exit MySQL by pressing ctrl+z. Now go to your tempLog directory and add another Python script in nano called readTempSQL.py

cd tempLog

sudo nano readTempSQL.py

Copy the following code into your new Python script.

import os
import time
import datetime
import glob
import MySQLdb
from time import strftime
 
os.system('modprobe w1-gpio')
os.system('modprobe w1-therm')
temp_sensor = '/sys/bus/w1/devices/28-00000622fd44/w1_slave'
 
# Variables for MySQL
db = MySQLdb.connect(host="localhost", user="root",passwd="password", db="temp_database")
cur = db.cursor()
 
def tempRead():
    t = open(temp_sensor, 'r')
    lines = t.readlines()
    t.close()
 
    temp_output = lines[1].find('t=')
    if temp_output != -1:
        temp_string = lines[1].strip()[temp_output+2:]
        temp_c = float(temp_string)/1000.0
    return round(temp_c,1)
 
while True:
    temp = tempRead()
    print temp
    datetimeWrite = (time.strftime("%Y-%m-%d ") + time.strftime("%H:%M:%S"))
    print datetimeWrite
    sql = ("""INSERT INTO tempLog (datetime,temperature) VALUES (%s,%s)""",(datetimeWrite,temp))
    try:
        print "Writing to database..."
        # Execute the SQL command
        cur.execute(*sql)
        # Commit your changes in the database
        db.commit()
        print "Write Complete"
 
    except:
        # Rollback in case there is any error
        db.rollback()
        print "Failed writing to database"
 
    cur.close()
    db.close()
    break

This is a modification of our original Python script but we are adding code to handle the MySQL functionality. Firstly, at the top of the script we add an import for the MySQLdb Python library we downloaded earlier. A bit further down you will see variables that will be used when communicating with MySQL (password, user, host etc) - remember to change them to your variables!

"sql = ..." is a string that we will send to MySQL as a command that adds our datetime and temperature values to the tempLog database. There is then a try statement that executes the sql command. If for some reason that fails the except code will run, printing a fail message and discarding attempted changes to the database. Play about with this code and try to change stuff, its the only way I learnt how to it works.

Now run the Python script we just made a few times.

sudo python readTempSQL.py

You should see the following output on your terminal window if all has worked:

pi@raspberrypi ~/tempLog $ sudo python readTempSQL.py 18.6 2015-01-04 22:29:24 Writing to database... Write Complete

 

Now lets check that the Python script actually entered data into the MySQL database. Log back into MySQL and USE the temp_database. We can then query the tempLog table:

mysql -u root -p

USE temp_database;

mysql> SELECT * FROM tempLog;

In the terminal window you will see the following output.

+---------------------+-------------+

| datetime           | temperature | +---------------------+-------------+ | 2014-12-28 17:26:20 |       18.90 | | 2014-12-28 17:27:05 |       18.90 | | 2014-12-28 17:27:52 |       18.90 | | 2014-12-28 17:30:39 |       19.00 | | 2014-12-28 17:31:02 |       18.90 | +---------------------+-------------+ 5 rows in set (0.00 sec)

If all has worked, well done! Next we will look at getting the data from the database for our app.

← Back to home