Tuesday, September 10, 2013

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'

# 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


#------------ 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.
                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,
                                        time.strftime("%A, %B, %d at %H:%M:%S")))

                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")))
                        #print ("can\'t handle " + rxList[0] + ' yet')
                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))

# This is where the update to Xively happens
def updateXively():
        print("Updating Xively with value: %s and %s"%(CurrentPower, CurrentOutTemp))
        # 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 = [
                        current_value = c.execute(
                                "select currentouttemp from housestatus")
                        current_value = c.execute(
                                "select curentpower from housestatus")
        dbconn.close() # close the data base
        feed.update()  # and update Xively with the latest

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

# 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:
                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.
        except KeyboardInterrupt:

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

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');

# 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)){
        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.


  1. It's amazing that you've come this far already! You got your RPi and figured out python, SQLite3, and php in less than TWO weeks. You've either not had much to do so far in September or things have really been clicking for you lately. I'm trying to follow in your footsteps, I'm working on controlling/monitoring power and water consumption for my home brewery (it's almost fully automated now!) and it takes all of my efforts just to keep up with what you were doing 6-8 months ago. Although looking at your blog history July - October have seemed to be the time when you make leaps and bounds on your home. I develop open-source automated aquatic instruments and sensors in Milwaukee WI for a living and I really appreciate how you've been setting up your home and most importantly that you are sharing your knowledge with other hobbyist so everyone can advance. It's very humbling...
    avid reader

    1. Aw shucks... Actually, I've been working on things that aren't related to automating the house. Y'know, fixing up a trailer for my motorcycle, cleaning the stupid yard, trying to get the dead rabbit out from under the porch, that kind of thing. My latest is a roof leak around one of those solar tubes that replaces ceiling lights in my hallway. I've already 'fixed' it once, but it didn't last. I have a lead on something that may be more permanent than 12-13 months though.

      And, I have a very selfish motive for sharing the stuff I do. I get responses from people that lead me to new ideas and techniques. I got on the Pi train because a guy is setting up his garage doors to send him mail when they get opened or left open by mistake. When we corresponded on the various techniques, I sort of got caught up in it myself. Still can't send mail, but I'll get there eventually.

      I'm jealous of your home brewery.

  2. being that you're in Arizona I can only assume that you also have a very high water bill. Have you ever thought about monitoring water usage?


    1. Heh, I have my own well. I did think about monitoring water, but not for cost analysis, more to just keep track of usage. But, since I only drip water maybe a dozen plants and allow the indigenous plant care for themselves, my water usage is really low. As it stands, my water costs (electricity and maintenance) runs about $200 a year.

  3. Hello!

    FIrst of all, congratulations on development of the system!

    Secondly, is there a way to access the database without ``translating`` into a webpage? I mean, access from outside of Pi, from other devices for example.


    1. Yes, but you have to open a machine up to the internet. I used a web page because I wanted to actually see it; you could send the data to a public mttp server and retrieve there from any machine on the internet.

  4. Hi Dave.
    i want build a wireless sensor network using raspberry pi and xbee for irrigation system.I want to have 5 xbee nodes. Each node consist of standalone xbee module with soil moisture sensor and a relay connected to it.At the coordinator side there is Raspberry with an xbee connected to it.

    My question is that is it possible that I can configure my xbees such that they all reads the moisture sensor every 1 second and sends reading to the coordinator.At the coordinator I want it to compared with the threshold values.If the value compared of a particular xbee is great than threshold then th xbee should send a command to that particular xbee ONLY to turn high on relay.I also want to save data in database in the raspberry pi.

    If this is is possible .Can you give me guideline of how to do it.