Reliable Wireless Temperatures

Freezer Temperatures

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 separate them.

+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 +connect+.

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 +commit()+ call.

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 +rollback()+ 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 do call +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[0]
            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")