submit to reddit       

Building an SQLite temperature logger

In this article I'm going to describe how I used a Raspberry Pi to build an SQLite based temperature logging system with a web UI. Follow this link to see the completed Raspberry Pi temperature logger with web UI.

You can download the code for this project from Github: https://github.com/Pyplate/rpi_temp_logger. There's a button to download the project as a zip file at the bottom of the right hand column.

The temperature logger consists of two parts: a script called monitor.py to measure the temperature at 15 minute intervals, and a script called webgui.py that displays temperatures in a web page. Monitor.py is triggered by a cron job. Every 15 minutes, it reads the temperature from a DS18B20 connected to my Pi's GPIO pins, and stores the reading in an SQLite database.

The other script, webgui.py, executes when it is requested by the Apache web server. It queries the database and displays the readings formatted in HTML. Temperatures are displayed in a javascript chart generated by code from Google charts.

Set up the SQLite database

The first thing to do is set up a database. Install SQLite using this command:

sudo apt-get install sqlite3

Then at a terminal type this command to enter the SQLite shell:

$ sqlite3 templog.db

In the SQLite shell I entered these commands to create a table called temps:


Temps has two fields: a timestamp, which is the date and time when a temperature is entered, and the other field is used to store the temperature. The BEGIN and COMMIT commands ensure that the transaction is saved in the database.

Use the .quit command to exit the SQLite shell, and then use these commands to put the database in /var/www, and set the database's owner to www-data:

$ sudo cp templog.db /var/www/ $ sudo chown www-data:www-data /var/www/templog.db

The Apache daemon has its own user name, www-data. I changed the database file's owner from pi to www-data so that Apache can read the file.

Writing the monitor script

Monitor.py reads temperatures from a DS18B20 on a breadboard connected to GPIO, and stores them in an SQLite database. The script starts by loading kernel modules for reading from 1 wire devices. Next, it searches for a directory in /sys/bus/w1/devices. The DS18B20 is represented by a directory starting with the digits '28', so searching for /sys/bus/w1/devices/28* finds the device path. To find the device file, we just append '/w1_slave' to the device path.

I wrote a function called get_temp to read from the device file:

# get temerature # argument devicefile is the path of the sensor to be read, # returns None on error, or the temperature as a float def get_temp(devicefile): try: fileobj = open(devicefile,'r') lines = fileobj.readlines() fileobj.close() except: return None # get the status from the end of line 1 status = lines[0][-4:-1] # is the status is ok, get the temperature from line 2 if status=="YES": print status tempstr= lines[1][-6:-1] tempvalue=float(tempstr)/1000 print tempvalue return tempvalue else: print "There was an error." return None

When read, the DS18B20 returns a two line string. If the device was read successfully, the end of the first line contains the letters 'YES'. The last five digits on the second line are the temperature in degrees Celsius. I converted them to a float and divided by 1000 to display the temperature with a decimal point in the correct place. If everything goes well, get_temp returns the temp as a float, otherwise it returns None.

The call to get_temp fails sometimes, most likely due to noise picked up by the jumper cables. If get_temp returns None, then we just call it again. See this page for detailed information on reading a DS18B20 temperature sensor on a Raspberry Pi.

Storing readings in the database

Monitor.py contains a function called log_temperature which stores readings in the database. This function connects to the database and creates a cursor. The cursor object is used to execute an SQL command to insert the temperature as a number along with the current date and time. Finally, log_temperature commits the transaction to the database and closes the connection.

# store the temperature in the database def log_temperature(temp): conn=sqlite3.connect(dbname) curs=conn.cursor() curs.execute("INSERT INTO temps values(datetime('now'), (?))", (temp,)) # commit the changes conn.commit() conn.close()

See this page for more information on accessing a database in Python.

Installing the monitor script

I saved the script as monitor.py in /usr/lib/cgi-bin/. I used this directory because it's the standard directory for executable scripts in Apache. Note, you have to configure Apache to execute .py files.

The following commands give monitor.py executable permissions, and change its owner to Apache's user name, www-data:

$ sudo chmod +x /usr/lib/cgi-bin/monitor.py $ sudo chown www-data:www-data /usr/lib/cgi-bin/monitor.py

Now a cron job needs to be set up to trigger monitor.py every 15 minutes. I did this by editing the user crontab file for www-data. This command opens the crontab file in the nano editor:

$ sudo crontab -u www-data -e

Crontab can be used with other editors, but nano is the default. I added this line at the end of www-data's crontab:

*/15 * * * * /usr/lib/cgi-bin/monitor.py

To save the file, I pressed Control O, and just hit return when prompted for the file name. The updated file will be saved to a temporary file and then automtically installed in the proper place. Press Control X to exit nano.

In the next post, I'll describe how I built the web UI for the Raspberry Pi temperature logger.



comments powered by Disqus

Follow me