The Weather Outside Is Frightful (Or Is It?)

Okay, great. With the script written and working, you easily can schedule it to run every hour as a cronjob. Before you configure the system to boot from a read-only filesystem (if you chose to), make sure you have SSH set up correctly and that you're using a static IP address. This will be necessary for you to collect the data from your web dashboard server. Otherwise, that takes care of this server (we call it the temperature server). You can confidently leave and lock the closet door if needs be. The TS-7500 is rated to operate between 0°C (32°F) and 70°C (158°F), but has been tested to withstand temperatures down to –40°C (–40°F). This particular project has been installed and operating since 2011 and has successfully withstood the harsh Montana winters, with lowest recorded outside temperature reaching –40°F (the highest temperature recorded is 95°F, for those who are curious).

The next part of the project puzzle is getting a web server set up that will collect the data from the temperature server, store it and display it in a nice dashboard.

Setting Up the Dashboard

For this step, you'll need to set up a web server. For our sake, with unreliable power and connections in a remote area (not to mention dial-up-like internet speeds), we chose to set up a server at a different, more reliable location. Technically, you could run a webserver right from the TS-7500 and have an all-in-one, low power, simpler solution. You'll need to decide which path to take, but for this project, we used a separate server. As long as the server has the ability to write to a datastore/database and run a scheduled job, you can use anything you'd like, be it a physical dedicated box, a virtual machine, shared host or cloud solution.

The general steps are to download data regularly (scheduled cronjob) from the temperature server using a script and SSH, store that data into a MySQL database, use a PHP script to read that data, parse and massage it into JSON format for the Google Charts API to understand and finally display it on the web page.

Admittedly, there is a lot to this step. As a reminder to keep the incentive going, the reason we're doing all of this is to be able to better prepare for the quickly upcoming cold months given historical data. For us, it answers the question of "when do we need to start worrying about winterizing?" For you, it might be something different, in which case you can adjust as necessary. The image below shows the dashboard we're striving for.

Database

The first thing we need is a database to store the data. MySQL was chosen since it was a familiar tool. The database "cabinstats" and table "cabintemps" was created to hold temperature data:


mysql> use cabinstats;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A


Database changed


mysql> show tables;
+----------------------+
| Tables_in_cabinstats |
+----------------------+
| cabintemps           |
+----------------------+
1 rows in set (0.00 sec)


mysql> desc cabintemps;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int(15)      | NO   | PRI | NULL    | auto_increment |
| timestamp | datetime     | YES  |     | NULL    |                |
| box       | decimal(4,1) | YES  |     | NULL    |                |
| lbath     | decimal(4,1) | YES  |     | NULL    |                |
| t_out     | decimal(4,1) | YES  |     | NULL    |                |
| t_in      | decimal(4,1) | YES  |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

Scheduled Downloading Script

The next thing we need is a script to fetch the temperatures from the temperature server regularly. Again, Python made fairly quick work of this task. It's set up to run every hour or so. Here's what our script ended up looking like:


get-cabin-temps.py


#!/usr/bin/python


import urllib, urllib2, csv, os, time
from datetime import datetime
from time import gmtime, strftime
#import subprocess as sp
from subprocess import Popen, PIPE
import MySQLdb


class TempDB(object):
    ''' '''


    def __init__(self, _host, _un, _pw, _db, _table):
      '''Initially establish connection with DB.'''


      # Define the name of the table that will keep the stats
      self.table = _table


      try:
        self.conn = MySQLdb.connect(host = _host,
                                    user = _un,
                                    passwd = _pw,
                                    db = _db)
        self.cursor = self.conn.cursor(MySQLdb.cursors.DictCursor)
      except MySQLdb.Error, e:
        raise Exception("Error %d: %s" % (e.args[0], e.args[1]))


    def add_temp_to_db(self, ts, t1, t2, t3, t4):
      ''' '''


      print "Adding to DB (%s)" % ts
      cursor = self.cursor


      cursor.execute("""
          SELECT id FROM %s WHERE timestamp='%s' AND box='%s' AND t_out='%s'
        """ % (self.table, ts, t1, t3))
      if cursor.rowcount == 0:
        cursor.execute("""
            INSERT INTO %s (timestamp,box,lbath,t_out,t_in) VALUES ('%s','%s','%s','%s','%s')
          """ % (self.table, ts, t1, t2, t3, t4))
        return True
      else:
        return False




class TempCsv(object):
  ''' '''


  def __init__(self, host='', file='', dest=''):
    ''' '''


    self.host = host
    self.file = file
    self.dest = dest


  def parse_csv_file(self):
    ''' '''


    #[(1,2,3,4),(1,2,3,4)]
    temps = []
    temp_reader = csv.reader(open(self.dest, 'rU'))
    for row in temp_reader:
      colnum = 0
      for col in row:
        if colnum == 0:
          date = col
        elif colnum == 1:
          the_time = col
        elif colnum == 2:
          box = col
        elif colnum == 3:
          lbath = col
        elif colnum == 4:
          outside = col
        elif colnum == 5:
          inside = col
        colnum += 1


      #Convert to datetime for MySQL
      #0  : 12/21/2011
      #1  : 8:01:50 PM  OR 8:01 PM
      tmp = date + " " + the_time
      try:
        date_obj = time.strptime(tmp, '%m/%d/%Y %I:%M:%S %p')
      except ValueError:
        date_obj = time.strptime(tmp, '%m/%d/%Y %I:%M %p')


      ts = time.strftime('%Y-%m-%d %H:%M:%S', date_obj)


      print "Appended to temps %s: %s %s %s %s\n" % (ts,box,lbath,outside,inside)
      temps.append((ts,box,lbath,outside,inside))


    return temps

  def get_temps(self):
    ''' '''


    os.system('scp "%s:%s" "%s"' % (self.host, self.file, self.dest))
    print "Finished downloading the file.  Getting ready to parse it.\n"
    return self.parse_csv_file()




def main():
  ''' '''


  host = "root@example.dyndns.org"
  file = "/mnt/usb/cabin_temps.csv"
  dest = "~/cabin_temps_master.csv"


  db_host = 'localhost'
  db_name = 'cabinstats'
  db_un = 'root'
  db_pw = 'password'
  db_table = 'cabintemps'


  tempdb = TempDB(db_host, db_un, db_pw, db_name, db_table)
  tempcsv = TempCsv(host, file, dest)

  print "Downloading and importing csv file to DB..."
  temps_table = tempcsv.get_temps()


  print "Getting ready to insert all temps...\n"
  for ts, t1, t2, t3, t4 in temps_table:
      if tempdb.add_temp_to_db(ts, t1, t2, t3, t4):
        print "Adding to temps array: %s, %s, %s, %s, %s" % (ts, t1, t2, t3, t4)


if __name__ == '__main__':
   main()

______________________