The temperature sensors that I use to drive the freezer graph are in my garage, which doesn’t have an easy way to run wires to the server room. I have a WRT54GL running DD-WRT configured as a bridge to the rest of my network. The problem with this is that the connection isn’t always reliable. I used to have a simple script that read the temps and fed them to my main mysql server, but the connection would frequently drop and it would lose the temperature data.
The solution is to save the 5 minute temperature readings locally and feed them to the mysql server, only deleting the local copies when the transaction is successful. The current version of that script is at the bottom of this post.
Some of my systems have older versions of Python so I first check to make sure
check_output exists and if not create a copy of it. Some variables are setup
for connecting to the mysql server and reading the temps from
DigiTemp. I use the -o option to set the
output to the Serial number, timestamp (in seconds) and temperature in
Centigrade. These are all separated by spaces so that you can use split to
check_output is called to run DigiTemp and the
output is all gathered up into a single string.
splitlines is then used to split
the multi-line string into a list of strings with the CR/LF removed. The list
looks like this:
['10100F34000800FF 1370106405 -11.437500', '104C4D55000800D9 1370106406 16.500000', '101F082E000800A9 1370106407 -17.687500', '10DF232E000800BE 1370106408 2.375000']
It then connects to the SQLite database and creates the table if it doesn’t
exist. The table holds the serial number, timestamp and temperature. If the
file doesn’t exist it will be created by the SQLite module on the call to
It then iterates over the temperature list (
temps) that were read from
DigiTemp, creating a SQL command to insert each
of them into the SQLite database. The changes are then committed to the
database with the
Now the temperatures are safely stored on the local system. If things fail after this point they will be available for the next attempt to connect to the MySQL server.
The script then connects to the MySQL server and then reads all of the
temperatures from the SQLite database. This ensures that we send any readings
from previous failures. It uses
strftime to convert the temperature reading’s
timestamp into the local time in a form that MySQL will use for its timestamp.
If there are any errors inserting the data into the server we call
and exit without deleting the local copy of the data. If the connection fails
the transaction is aborted. When all the temperatures are successfully updated
commit() and then delete them from the local SQLite database.
#!/usr/bin/python import os import sys import time import subprocess from subprocess import check_call, call import MySQLdb import sqlite3 try: from subprocess import check_output except Exception: def check_output(*popenargs, **kwargs): if 'stdout' in kwargs: raise ValueError('stdout argument not allowed, it will be overridden.') process = subprocess.Popen(stdout=subprocess.PIPE, *popenargs, **kwargs) output, unused_err = process.communicate() retcode = process.poll() if retcode: cmd = kwargs.get("args") if cmd is None: cmd = popenargs raise subprocess.CalledProcessError(retcode, cmd, output=output) return output # SN TIME FAHRENHEIT DT_CMD = ["digitemp_DS9097U", "-q", "-a", "-o", "%R %N %C"] DB_FILE = os.path.expanduser("~/.digitemp.db") DB_NAME = "weather" DB_HOST = "my.local.mysql.server" DB_USER = "weather" DB_PASS = "TooManySecrets" SQL_TIME = "%Y-%m-%d %H:%M:%S" DEBUG = False def print_debug(s): if DEBUG: print s # Read the temperatures try: output = check_output(DT_CMD) temps = output.splitlines() print_debug(temps) except subprocess.CalledProcessError as e: print("Error %s running %s: %s" % (e.returncode, e.cmd, e.output)) sys.exit(1) # Does the sqlite db exist? if not os.path.exists(DB_FILE): conn = sqlite3.connect(DB_FILE) sql_cur = conn.cursor() sql_cur.execute("create table temps (serial, time, temp_c)") else: conn = sqlite3.connect(DB_FILE) sql_cur = conn.cursor() # Add these to the local sqlitedb for line in temps: sn, ts, tmp_c = line.split() sql_cur.execute("insert into temps values (?, ?, ?)", (sn, ts, tmp_c)) conn.commit() # Connect to the database try: mydb = MySQLdb.Connect(host=DB_HOST,user=DB_USER,passwd=DB_PASS,db=DB_NAME,connect_timeout=30) except MySQLdb.DatabaseError as e: print("Problem connecting to database: %s" % e) sys.exit(1) mysql_cur=mydb.cursor() # Send all the temps in the sqlite db as a transaction. # Once it completes successfully remove them from the sqlite db try: for sn, ts, tmp_c in sql_cur.execute("SELECT * from temps"): print_debug("%s %s %s" % (sn, ts, tmp_c)) sql = "INSERT INTO temperature VALUES(NULL, %s, %s, %s)" sqltime = time.strftime(SQL_TIME, time.localtime(int(ts))) mysql_cur.execute( sql, (sn, sqltime, tmp_c) ) except Exception as e: print("Error inserting temp: %s" % e) mydb.rollback() else: mydb.commit() print_debug("new temps committed") # Remove the temps from the sqlite db. sql_cur.execute("delete from temps") conn.commit() print_debug("sqlite db cleaned out")