Raspberry Pi, XBee, SQLite3, and a Web Page

It's been raining on and off, so I thought this would be a good chance (excuse) to experiment with reading XBee packets (in python), and saving them in a database.  It would be cool to also send the data out to Xively to be saved, and maybe display them on a web page.  So, I sat down and did it.

Granted, it was a pain switching between python, SQL, and php, but after a while, you kind of get used to it.  I took the XBee example I previously posted that does asynchronous reads of XBee network packets and modified it to save some of the data items to my SQLite3 database.  Then I modified the php web page from a couple of days ago to get the items out of the database and display them.  Things went much faster and easier since I didn't have to install anything, just add things here and there.

That's the way it usually goes; getting started is over 50% of the work.  Then the next 40% moves along pretty well with the last 10% taking forever.

Here's the updated XBee python code to catch stuff and save it in the database.  If you look closely, I started grabbing the packets from the Acid Pump and saving them.  That's my first attempt at grabbing a particular device and saving it.  The status packet I've been grabbing is forwarded by my controller <link> that I've been using for a couple of years and hope to replace.  I'll have to work on the packets being sent by my XBee thermostat <link> that is setting in a housing on a fence post outside next.

The python Script

#! /usr/bin/python
# This is the actual house Monitor Module
#
# I take the techniques tried in other modules and incorporate them
# to gather data around the house and save it in a data base.  The
# data base can be read for presentation in a web page and also
# forwarded to Xively for cloud storage and graphing.
#
# This particular version only reads the XBee network, it
# doesn't go to my internal lan to gather data, that's next
# on my list.
#
# For the XBee network, I fork off a new process
# to do the XBee receive.  This way, the main
# code can go do somthing else and hand waiting
# for the XBee messages to come in to another
# process.

from xbee import ZigBee
from apscheduler.scheduler import Scheduler
import logging
import datetime
import time
import serial
import Queue
import xively
import sqlite3


#-------------------------------------------------
# on the Raspberry Pi the serial port is ttyAMA0
XBEEPORT = '/dev/ttyAMA0'
XBEEBAUD_RATE = 9600

# The XBee addresses I'm dealing with
BROADCAST = '\x00\x00\x00\x00\x00\x00\xff\xff'
UNKNOWN = '\xff\xfe' # This is the 'I don't know' 16 bit address

# The Xively feed id and API key that is needed
FEED_ID = '1428598370'
API_KEY = 'put_your_xively_key_here'

# Global items that I want to keep track of
CurrentPower = 0
DayMaxPower = 0
DayMinPower = 50000
CurrentOutTemp = 0
DayOutMaxTemp = -50
DayOutMinTemp = 200

#-------------------------------------------------
logging.basicConfig()

#------------ XBee Stuff ------------------------
packets = Queue.Queue() # When I get a packet, I put it on here

# Open serial port for use by the XBee
ser = serial.Serial(XBEEPORT, XBEEBAUD_RATE)

# this is a call back function.  When a message
# comes in this function will get the data
def message_received(data):
        packets.put(data, block=False)
        #print 'gotta packet'

def sendPacket(where, what):
        # I'm only going to send the absolute minimum.
        zb.send('tx',
                dest_addr_long = where,
                # I always use the 'unknown' value for this
                # it's too much trouble to keep track of two
                # addresses for the device
                dest_addr = UNKNOWN,
                data = what)

# In my house network sending a '?\r' (question mark, carriage
# return) causes the controller to send a packet with some status
# information in it as a broadcast.  As a test, I'll send it and
# the receive above should catch the response.
def sendQueryPacket():
        # I'm broadcasting this message only
        # because it makes it easier for a monitoring
        # XBee to see the packet.  This allows me to monitor
        # some of the traffic with a regular XBee and not
        # load up the network unnecessarily.
        #print 'sending query packet'
        sendPacket(BROADCAST, '?\r')

# OK, another thread has caught the packet from the XBee network,
# put it on a queue, this process has taken it off the queue and
# passed it to this routine, now we can take it apart and see
# what is going on ... whew!
def handlePacket(data):
        global CurrentPower, DayMaxPower, DayMinPower
        global CurrentOutTemp, DayOutMaxTemp, DayOutMinTemp

        #print data # for debugging so you can see things
        # this packet is returned every time you do a transmit
        # (can be configured out), to tell you that the XBee
        # actually send the darn thing
        if data['id'] == 'tx_status':
                if ord(data['deliver_status']) != 0:
                        print 'Transmit error = ',
                        print data['deliver_status'].encode('hex')
        # The receive packet is the workhorse, all the good stuff
        # happens with this packet.
        elif data['id'] == 'rx':
                rxList = data['rf_data'].split(',')
                if rxList[0] == 'Status':
                        # remember, it's sent as a string by the XBees
                        tmp = int(rxList[1]) # index 1 is current power
                        if tmp > 0:  # Things can happen to cause this
                                # and I don't want to record a zero
                                CurrentPower = tmp
                                DayMaxPower = max(DayMaxPower,tmp)
                                DayMinPower = min(DayMinPower,tmp)
                                tmp = int(rxList[3]) # index 3 is outside temp
                                CurrentOutTemp = tmp
                                DayOutMaxTemp = max(DayOutMaxTemp, tmp)
                                DayOutMinTemp = min(DayOutMinTemp, tmp)
                                dbconn = sqlite3.connect('/home/pi/database/desert-home')
                                c = dbconn.cursor()
                                # do stuff
                                c.execute("update housestatus "
                                        "set curentpower = ?, "
                                        "daymaxpower = ?,"
                                        "dayminpower = ?,"
                                        "currentouttemp = ?,"
                                        "dayoutmaxtemp = ?,"
                                        "dayoutmintemp = ?,"
                                        "utime = ?;",
                                        (CurrentPower, DayMaxPower, DayMinPower,
                                        CurrentOutTemp, DayOutMaxTemp,
                                        DayOutMinTemp,
                                        time.strftime("%A, %B, %d at %H:%M:%S")))
                                dbconn.commit()
                                dbconn.close()


                elif rxList[0] == 'AcidPump':
                        # This is the Acid Pump Status packet
                        # it has 'AcidPump,time_t,status,level,#times_sent_message
                        # I only want to save status, level, and the last
                        # time it reported in to the database for now
                        dbconn = sqlite3.connect('/home/pi/database/desert-home')
                        c = dbconn.cursor()
                        c.execute("update acidpump set status = ?, "
                                "'level' = ?,"
                                "utime = ?;",
                                (rxList[2], rxList[3],
                                time.strftime("%A, %B, %d at %H:%M:%S")))
                        dbconn.commit()
                        dbconn.close()
                else:
                        #print ("can\'t handle " + rxList[0] + ' yet')
                        pass
        else:
                print ('Unimplemented XBee frame type' + data['id'])

#-------------------------------------------------

# This little status routine gets run by scheduler
# every 15 seconds
def printHouseData():
        print('Power Data: Current %s, Min %s, Max %s'
                %(CurrentPower, DayMinPower, DayMaxPower))
        print('Outside Temp: Current %s, Min %s, Max %s'
                %(CurrentOutTemp, DayOutMinTemp, DayOutMaxTemp))
        print

# This is where the update to Xively happens
def updateXively():
        print("Updating Xively with value: %s and %s"%(CurrentPower, CurrentOutTemp))
        print
        # Currently I have to use UTC for the time,
        # there's a bug somewhere in the library or
        # Xively.  It doesn't matter though because
        # it's easy to convert
        now = datetime.datetime.utcnow()
        # open the database
        dbconn = sqlite3.connect('/home/pi/database/desert-home')
        c = dbconn.cursor()
        # Yes, there are better ways to do the stuff below,
        # but I wanted to use a single statement to get it
        # from the data base an update the field going to
        # Xively.  It turns out that is is a rather odd
        # looking statement, but it works.
        feed.datastreams = [
                xively.Datastream(id='outside_temp',
                        current_value = c.execute(
                                "select currentouttemp from housestatus")
                                .fetchone(),
                        at=now),
                xively.Datastream(id='power_usage',
                        current_value = c.execute(
                                "select curentpower from housestatus")
                                .fetchone(),
                        at=now)
                ]
        dbconn.close() # close the data base
        feed.update()  # and update Xively with the latest

#------------------Stuff I schedule to happen -----
sendsched = Scheduler()
sendsched.start()

# every 30 seconds send a house query packet to the XBee network
sendsched.add_interval_job(sendQueryPacket, seconds=30)
# every 15 seconds print the most current power info
sendsched.add_interval_job(printHouseData, seconds=15)
# every minute update the data store on Xively
sendsched.add_interval_job(updateXively, seconds=60)

# Create XBee library API object, which spawns a new thread
zb = ZigBee(ser, callback=message_received)

# Initialize api client
api = xively.XivelyAPIClient(API_KEY)
# and get my feed
feed = api.feeds.get(FEED_ID)

#This is the main thread.  Since most of the real work is done by
# scheduled tasks, this code checks to see if packets have been
# captured and calls the packet decoder
while True:
        try:
                time.sleep(0.1)
                if packets.qsize() > 0:
                        # got a packet from recv thread
                        # See, the receive thread gets them
                        # puts them on a queue and here is
                        # where I pick them off to use
                        newPacket = packets.get_nowait()
                        # now go dismantle the packet
                        # and use it.
                        handlePacket(newPacket)
        except KeyboardInterrupt:
                break

# halt() must be called before closing the serial
# port in order to ensure proper thread shutdown
zb.halt()
ser.close()

This has my most recent discoveries regarding python and its interface to the SQLite3 database.  It took me a while to figure out how to grab a single item out of the data base, but it's illustrated above.  Of course I updated the web page I'm working on to show off the current power usage and outside temperature that is forwarded by my controller.  Here's the modified web page:

The PHP Web Page

# This is about the minimal debugging

# I could find that was easy to use
ini_set('display_errors', 'On');
error_reporting(E_ALL|E_STRICT);

# This is the database open call, it returns a
# database object that has to be use going forward
$db = new SQLite3('/home/pi/database/desert-home');
#
# I'm going to get the power usage and outside temperature
# from the database and display it.  I use the querySingle()
# call because I haven't messed with it yet.  I could have gotten
# the entire record and just displayed the parts I needed instead
$power = $db->querySingle("select curentpower from housestatus;");
$outtemp = $db->querySingle("select currentouttemp from housestatus;");
print ("<Strong>Current Power usage: ". $power . " Watts<br>");
print ("Current Outside Temperature: " . $outtemp . "&deg;F</strong><br><br>");

# you do a query statment that return a strange
# SQLite3Result object that you have to use
# in the fetch statement below
$result = ($db->query('SELECT * FROM thermostats;'));
#print_r($result); # I wanted to see what it actually was
#
# The fetch call will return a boolean False if it hits
# the end, so why not use it in a while loop?
#
# The fetchArray() call can return an 'associated' array
# that actually means give you back an array of ordered
# pairs with name, value.  This is cool because it means
# I can access the various values by name. Each call to
# fetchArray return one row from the thermostats table
while ($res = $result->fetchArray(SQLITE3_ASSOC)){
        #var_dump($res);
        print ("<strong>" . $res["location"] ." thermostat,</strong><br />");
        print ("Currently: " . $res["status"] . " <br \>");
        print ("Temperature: " . $res["temp-reading"] . "&deg; <br \>");
        print ("Settings are: <br \>");
        print ("Mode: " . $res["s-mode"] . " <br \>");
        print ("Temperature: " . $res["s-temp"] . "&deg; <br \>");
        print ("Fan: " . $res["s-fan"] . " <br \>");
        print ("<br>");
}
$db->close(); # I opened it, I should close it
?>

There's not a whole lot I can point out that isn't already in the comments above.  Here's the web page as the browser displays it:

Yep, I've discovered how to put up a degree symbol.  Now, I have two processes that run in background all the time updating the database with different items.  That's a bit silly, so I'm going to combine them into one piece of code that goes over my lan and talks to the web devices as well as monitoring the XBee network for updates.  That way I only have one thing to make sure is running all the time.

And yes, the temperature up there is correct.  The rainstorm has lowered the temperature enough that I have all the doors open that don't get rained into.  Time to air out the house.