Table of Contents
collect the data and update the DB
- job script to write to sqlite
- cron
"*/15 * * * * /usr/local/bin/script_ds18b20.sh >/dev/null 2>&1" "*/15 * * * * /usr/lib/cgi-bin/monitor.py >/dev/null 2>&1"
file /usr/lib/cgi-bin/monitor.py
#!/usr/bin/env python
import sqlite3
import os
import time
import glob
# global variables
speriod=(15*60)-1
dbname='/var/www/templog.db'
# 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()
# display the contents of the database
def display_data():
conn=sqlite3.connect(dbname)
curs=conn.cursor()
for row in curs.execute("SELECT * FROM temps"):
print str(row[0])+" "+str(row[1])
conn.close()
# get temerature
# 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
# main function
# This is where the program starts
def main():
# enable kernel modules
os.system('sudo modprobe w1-gpio')
os.system('sudo modprobe w1-therm')
# search for a device file that starts with 28
devicelist = glob.glob('/sys/bus/w1/devices/28*')
if devicelist=='':
return None
else:
# append /w1slave to the device file
w1devicefile = devicelist[0] + '/w1_slave'
# while True:
# get the temperature from the device file
temperature = get_temp(w1devicefile)
if temperature != None:
print "temperature="+str(temperature)
else:
# Sometimes reads fail on the first attempt
# so we need to retry
temperature = get_temp(w1devicefile)
print "temperature="+str(temperature)
# Store the temperature in the database
log_temperature(temperature)
# display the contents of the database
# display_data()
# time.sleep(speriod)
if __name__=="__main__":
main()
display the data in web
http://10.11.12.110/cgi-bin/webgui.py
- file location /usr/lib/cgi-bin# cat webgui.py
#!/usr/bin/env python
import sqlite3
import sys
import cgi
import cgitb
# global variables
speriod=(15*60)-1
dbname='/var/www/templog.db'
# print the HTTP header
def printHTTPheader():
print "Content-type: text/html\n\n"
# print the HTML head section
# arguments are the page title and the table for the chart
def printHTMLHead(title, table):
print "<head>"
print " <title>"
print title
print " </title>"
print_graph_script(table)
print "</head>"
# get data from the database
# if an interval is passed,
# return a list of records from the database
def get_data(interval):
conn=sqlite3.connect(dbname)
curs=conn.cursor()
if interval == None:
curs.execute("SELECT * FROM temps")
else:
curs.execute("SELECT * FROM temps WHERE timestamp>datetime('now','-%s hours')" % interval)
# curs.execute("SELECT * FROM temps WHERE timestamp>datetime('2019-01-02 09:30:02','-%s hours') AND timestamp<=datetime('2019-12-31 21:31:02')" % interval)
rows=curs.fetchall()
conn.close()
return rows
# convert rows from database into a javascript table
def create_table(rows):
chart_table=""
for row in rows[:-1]:
rowstr="['{0}', {1}],\n".format(str(row[0]),str(row[1]))
chart_table+=rowstr
row=rows[-1]
rowstr="['{0}', {1}]\n".format(str(row[0]),str(row[1]))
chart_table+=rowstr
return chart_table
# print the javascript to generate the chart
# pass the table generated from the database info
def print_graph_script(table):
# google chart snippet
chart_code="""
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
google.load("visualization", "1", {packages:["corechart"]});
google.setOnLoadCallback(drawChart);
function drawChart() {
var data = google.visualization.arrayToDataTable([
['Time', 'Temperature'],
%s
]);
var options = {
title: 'Temperature'
};
var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
chart.draw(data, options);
}
</script>"""
print chart_code % (table)
# print the div that contains the graph
def show_graph():
print "<h2>Temperature Chart</h2>"
print '<div id="chart_div" style="width: 900px; height: 500px;"></div>'
# connect to the db and show some stats
# argument option is the number of hours
def show_stats(option):
conn=sqlite3.connect(dbname)
curs=conn.cursor()
if option is None:
option = str(24)
curs.execute("SELECT timestamp,max(temp) FROM temps WHERE timestamp>datetime('now','-%s hour') AND timestamp<=datetime('now')" % option)
# curs.execute("SELECT timestamp,max(temp) FROM temps WHERE timestamp>datetime('2013-09-19 21:30:02','-%s hour') AND timestamp<=datetime('2013-09-19 21:31:02')" % option)
rowmax=curs.fetchone()
rowstrmax="{0}   {1}C".format(str(rowmax[0]),str(rowmax[1]))
curs.execute("SELECT timestamp,min(temp) FROM temps WHERE timestamp>datetime('now','-%s hour') AND timestamp<=datetime('now')" % option)
# curs.execute("SELECT timestamp,min(temp) FROM temps WHERE timestamp>datetime('2013-09-19 21:30:02','-%s hour') AND timestamp<=datetime('2013-09-19 21:31:02')" % option)
rowmin=curs.fetchone()
rowstrmin="{0}   {1}C".format(str(rowmin[0]),str(rowmin[1]))
curs.execute("SELECT avg(temp) FROM temps WHERE timestamp>datetime('now','-%s hour') AND timestamp<=datetime('now')" % option)
# curs.execute("SELECT avg(temp) FROM temps WHERE timestamp>datetime('2013-09-19 21:30:02','-%s hour') AND timestamp<=datetime('2013-09-19 21:31:02')" % option)
rowavg=curs.fetchone()
print "<hr>"
print "<h2>Minumum temperature </h2>"
print rowstrmin
print "<h2>Maximum temperature</h2>"
print rowstrmax
print "<h2>Average temperature</h2>"
# print "%.3f" % rowavg+"C"
print rowavg
print "<hr>"
print "<h2>In the last hour:</h2>"
print "<table>"
print "<tr><td><strong>Date/Time</strong></td><td><strong>Temperature</strong></td></tr>"
rows=curs.execute("SELECT * FROM temps WHERE timestamp>datetime('new','-1 hour') AND timestamp<=datetime('new')")
# rows=curs.execute("SELECT * FROM temps WHERE timestamp>datetime('2013-09-19 21:30:02','-1 hour') AND timestamp<=datetime('2013-09-19 21:31:02')")
for row in rows:
rowstr="<tr><td>{0}  </td><td>{1}C</td></tr>".format(str(row[0]),str(row[1]))
print rowstr
print "</table>"
print "<hr>"
conn.close()
def print_time_selector(option):
print """<form action="/cgi-bin/webgui.py" method="POST">
Show the temperature logs for
<select name="timeinterval">"""
if option is not None:
if option == "6":
print "<option value=\"6\" selected=\"selected\">the last 6 hours</option>"
else:
print "<option value=\"6\">the last 6 hours</option>"
if option == "12":
print "<option value=\"12\" selected=\"selected\">the last 12 hours</option>"
else:
print "<option value=\"12\">the last 12 hours</option>"
if option == "24":
print "<option value=\"24\" selected=\"selected\">the last 24 hours</option>"
else:
print "<option value=\"24\">the last 24 hours</option>"
if option == "48":
print "<option value=\"48\" selected=\"selected\">the last 48 hours</option>"
else:
print "<option value=\"48\">the last 48 hours</option>"
if option == "168":
print "<option value=\"168\" selected=\"selected\">the last 7 days</option>"
else:
print "<option value=\"168\">the last 7 days</option>"
if option == "744":
print "<option value=\"744\" selected=\"selected\">the last 31 days</option>"
else:
print "<option value=\"744\">the last 31 days</option>"
if option == "8760":
print "<option value=\"8760\" selected=\"selected\">the last 365 days</option>"
else:
print "<option value=\"8760\">the last 365 days</option>"
else:
print """<option value="6">the last 6 hours</option>
<option value="12">the last 12 hours</option>
<option value="24">the last 24 hours</option>
<option value="48">the last 48 hours</option>
<option value="168">the last 7 days</option>
<option value="744">the last 31 days</option>
<option value="8760" selected="selected">the last 365 days</option>"""
print """ </select>
<input type="submit" value="Display">
</form>"""
# check that the option is valid
# and not an SQL injection
def validate_input(option_str):
# check that the option string represents a number
if option_str.isalnum():
# check that the option is within a specific range
if int(option_str) > 0 and int(option_str) <= 8760:
return option_str
else:
return None
else:
return None
#return the option passed to the script
def get_option():
form=cgi.FieldStorage()
if "timeinterval" in form:
option = form["timeinterval"].value
return validate_input (option)
else:
return None
# main function
# This is where the program starts
def main():
cgitb.enable()
# get options that may have been passed to this script
option=get_option()
if option is None:
option = str(24)
# get data from the database
records=get_data(option)
# print the HTTP header
printHTTPheader()
if len(records) != 0:
# convert the data into a table
table=create_table(records)
else:
print "No data found"
return
# start printing the page
print "<html>"
# print the head section including the table
# used by the javascript for the chart
printHTMLHead("Pi Temperature Logger Sensor1", table)
# print the page body
print "<body>"
print "<h1>Pi Temperature Logger Sensor1</h1>"
print "<hr>"
print_time_selector(option)
show_graph()
show_stats(option)
print "</body>"
print "</html>"
sys.stdout.flush()
if __name__=="__main__":
main()
source
https://github.com/Pyplate/rpi_temp_logger
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:
BEGIN; CREATE TABLE temps (timestamp DATETIME, temp NUMERIC); COMMIT;
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.
56 Comments