Raspberry Pi and Sqlite3

If you got here through a search engine looking for a tutorial on Sqlite3 on the Raspberry Pi, that's not what this is.  I've been setting up a new Raspberry Pi to control my house and decided to experiment with a data base to store current data in so multiple processes can get at it.  After reading way too much information on various data base managers, I decided to test Sqlite3.  It seems like a reasonable choice for a really small system since it doesn't require a manager process and isn't split across multiple controllers.  Since the interface language is SQL, whatever I wind up doing can be ported to a larger data base tool when I need to.

So, after installing Sqlite3 on my Pi, I spent about two hours cobbling together enough code to try it out.  I chose to monitor my thermostats <link>, since they are ethernet enabled and respond directly to requests, then to store their current state in the data base.  The idea is that I can put up a web page that shows the status of the two thermostats and eventually add controls to change the settings.  Once again, there was a ton of information on the web about how to use Sqlite3, and things went pretty smoothly.

I like this little data base manager.  There's not a huge set up, actually, there wasn't much set up at all.  Just install it, and use it.  As usual, here's the code I came up with:

The python Script

import sys
import time
from apscheduler.scheduler import Scheduler
import urllib2
import sqlite3
import logging


logging.basicConfig()

def openSite(Url):
        try:
                webHandle = urllib2.urlopen(Url)
        except urllib2.HTTPError, e:
                errorDesc = BaseHTTPServer.BaseHTTPRequestHandler.responses[e.code][0]
                print "Cannot retrieve URL: " + str(e.code) + ": " + errorDesc
                sys.exit(1);
        except urllib2.URLError, e:
                print "cannot retrieve URL: " + e.reason[1]
        except:
                print "Cannot retrieve URL: Unknown error"
                sys.exit (1)
        return webHandle

def getThermoStatus(whichOne):
        website = openSite("HTTP://" + whichOne[0] + "/status")
        # now read the status that came back from it
        websiteHtml = website.read()
        # After getting the status from the little web server on
        # the arduino thermostat, strip off the trailing cr,lf
        # and separate the values into a list that can
        # be used to tell what is going on
        return  websiteHtml.rstrip().split(",")

def ThermostatStatus():
        # The scheduler will run this as a separate thread
        # so I have to open and close the database within
        # this routine

        print(time.strftime("%A, %B %d at %H:%M:%S"))
        # open the database and set up the cursor (I don't have a
        # clue why a cursor is needed)
        dbconn = sqlite3.connect('/home/pi/database/desert-home')
        c = dbconn.cursor()
        for whichOne in ['North', 'South']:
                c.execute("select address from thermostats "
                        "where location=?; ", (whichOne,))
                thermoIp = c.fetchone()
                status = getThermoStatus(thermoIp)
                print whichOne + " reports: " + str(status)
                c.execute("update thermostats set 'temp-reading' = ?, "
                        "status = ?, "
                        "'s-temp' = ?, "
                        "'s-mode' = ?, "
                        "'s-fan' = ?, "
                        "peak = ?,"
                        "utime = ?"
                        "where location = ?;",
                        (status[0],status[1],
                        status[2],status[3],
                        status[4],status[5],
                        time.strftime("%A, %B %d at %H:%M:%S"),
                        whichOne))
                dbconn.commit()

        print
        dbconn.close()

# I like things that are scheduled
# that way I don't have to worry about them
# being called, because they take care of themselves
sched = Scheduler()
sched.start()

# schedule reading the thermostats for every minute
sched.add_interval_job(ThermostatStatus, minutes=1)

# This is a priming read to show immediate results
ThermostatStatus()
# all the real work is done by the scheduler
# so the main code can just hang
while 1:
        try:
                time.sleep(1)
        except KeyboardInterrupt:
                break

# I want to look into using atexit for this
sched.shutdown(wait=False)

This works really well to interrogate each thermostat and put the results into a data base.  While I was working on it, I decided to get a little bit fancy and actually put the location and IP addresses of the two thermostats in the data base, retrieving and using them in the communication process.  Now that I know how, I'll have to think about doing the same thing when I adapt this to talk to my little XBee devices.

Once again, I set up the scheduler to cause things to happen.  This led to an interesting discovery, the scheduler starts a new thread to do the work.  I noticed this when Sqlite3 refused a connection because I opened the data base in one thread and then tried to use it in another one.  Rather than being an inconvenience, this is actually great.  I managed to prove that I can pass data from independent processes through the data base.  This will make the eventual loading of data by a web server much easier.

Since that was easy, I decided to put the actual time I last talked to a particular device in the data base as well.  Later, I can look at that time to see if the devices are having a problem.  Knowing a device is doing well is a constant concern when you have a bunch of them that are working independently around the house.

Now, I need to decide if I should work on a web page to display the status of the thermostats or start combining the various pieces of code I've constructed together to monitor the house.  But right this second it started to sprinkle outside.  Since rain is so rare here, me and the dog are going for a walk.