Look at the Thermostat I found at Home Depot

A friend of mine was looking for a new thermostat.  He's not a techie, and didn't want to build his own so he was prowling around Home Depot seeing what they had.  He called me from the store and told me they were selling my thermostat <link>.

Of course they weren't, but it was really flattering when I took a look at what he was talking about.  Here is the Honeywell model RTH221B.  It's a basic programmable thermostat that'll do a nice job for someone, but the resemblance to mine is COOL.

Here's mine, on the wall working away.

Of course I don't have a crew of engineers to fine tune the location and shape of the buttons.  Another crew that contracts out the fabrication. Or, even another crew that does silkscreen work on the front, so theirs is classier.

Of course, theirs isn't infinitely internally programmable, ethernet connected, adapted for peak demand billing, or have a backlit high contrast display.

Hey, I'm flattered.

Sending Mail From a Raspberry PI

Remember, one of the reasons I got the Pi was to be able to send mail to myself when something bad happened?  Finally got around to looking at that.  What a royal red pain in the butt!  First, regular mail is a configuration nightmare that I gave up on after looking at it for a while.  Then, I went looking for a simple client that could be scripted or had some kind of api I could use...no luck.  Then I ran across two pretty good possibilities: sendEmail  and smtp.cli.  So, googling around I decided to try sendEmail because it had some really good posts out there.

I installed it using apt-get and gave it a shot: failed.  There was a message about not having the right TCP libraries, so I went to the author's site to see what to do and he said to install a couple of perl libraries and give it another shot.  Did some searching and there's an installation tool in perl that could do it, so I tried it.  About an hour later after a ton of compiles, and a hundred miles of console output, I tried it again:  it failed.  OK, FINE, maybe the version of perl isn't the right one, I'll just upgrade perl to get the latest.  apt-get upgrade perl... about an hour of wasted time watching the console being worked to death and got to try it again: it failed.

Now, I'm getting seriously annoyed, so I hunted some more.  Found someone that had exactly the same set of problems which isn't surprising since this is a vanilla Pi (pun intended) and somebody out there must have had the problem.  There were solutions in this post that actually made much more sense than the mess I had gotten myself into.  So, I followed the suggestions, and of course, they were wrong for my particular system.

It wasn't the author's fault, he did the best he could describing it, but I had to allow for different versions of perl, slight directory differences, different source files for things, etc.  I finally found the offending file and edited it to fix the bug and ... wait for it ... it worked.

Remember, your file locations and the actual line that is messed up will vary by location, so if you try this, use this as a guide, not a tutorial.

After installing sendEmail using "sudo apt-get install sendEmail", I ran it with this line:

sendEmail -f "myname@gmail.com" -t "myname@gmail.com" -u "this is a subject" -m "inside the message" -s "smtp.gmail.com":587 -o tls=yes -xu "meagain" -xp "mypassword"

And sendEmail nicely told me:

sendEmail[14590]: ERROR => No TLS support!  SendEmail can't load required libraries. (try installing Net::SSLeay and IO::Socket::SSL)

First, what the heck kind of message is that?  What the heck language is it written in?  Ancient sanscrit makes much more sense to look at than this thing.  So, I searched for the terms and found out that perl has a tool to get updates.  This is where I tried using perl to install the packages and sat forever watching it install, test, print reports, and generally mess around wasting my time.  But, it finished, so I tried it again:  same result.  So, I upgraded perl.  If you're not using perl, don't do this.  It takes forever.  And it didn't help a bit.

This is where I found the post that was actually some help <link>, so I used good ol' apt-get to load the darn libraries that were needed:

sudo apt-get install libnet-ssleay-perl libio-socket-ssl-perl

Which only took a a couple of minutes, and I was back to trying the sendEmail command again.  It failed, but the message was different this time:

invalid SSL_version specified at /usr/local/share/perl/5.14.2/IO/Socket/SSL.pm line 418

I guess you can call this progress.  At least it makes a little more sense and at looks a little different from random characters, and was talked about in the post I mentioned above.  So, I hunted for the SSL.pm file (it was right where the message above said it would be) and edited it.  I found the line that the post talks about around line 1640 or so and changed it.  Now, I'm ready to try it again:

 Email was sent successfully!

Whew!  Now, go check my mail; it was there in all its glory.

I didn't even get to trying smtp.cli darn it.  However after spending so much time getting this to work, I think I'll let someone else do that for me.

Raspberry Pi and emoncms

In the previous post <link> I described how I set up a separate process for logging to the cloud servers at ThingSpeak and Xively.  This removes the code from my house monitor process and makes changing or adding things much easier.  So, I had two more to do, my legacy feeds on Xively and the datastore on emoncms.  I just finished the emoncms process last night.  It was relatively easy except for one tiny item.  The emoncms server almost never returns an error.  I messed up the feed over and over again getting it to work and the server never returned an error.  So, I had to run the process, watch the site for updates and hope it updated fast enough for me to tell if it was getting through.

I got it working and ran it over night to be sure it was going to hold up.  The code looks very, very much like the code for updating ThingSpeak, so there aren't too many surprises.  Once again, this is a standalone process that takes items from a simple database and forwards them to emoncms for archival.

The Python Script

#! /usr/bin/python
from apscheduler.scheduler import Scheduler
import datetime
import logging
import time
import sqlite3
import httplib, urllib

# The API key that is needed
EMONKEY = "PUTSOMETHINGINHERE"
# the database where I'm storing stuff
DATABASE='/home/pi/database/desert-home'
# This is where the update to ThingSpeak happens
def updateEmonCms():
#print "Updating emoncms ", time.strftime("%A, %B %d at %H:%M:%S")
# open the database
dbconn = sqlite3.connect(DATABASE)
c = dbconn.cursor()
# Getting it out of the database a field at a time
# is probably less efficient than getting the whole record,
# but it works.  
#
# On ThingSpeak I only update real power, power factor, voltage,
# frequency, outside temperature and inside temperature
# so I'm simply going to put the values in variables instead of
# some complex (and probably faster) compound statement.
outsideTemp = c.execute(
"select currenttemp from xbeetemp").fetchone()[0]
# This a really cool thing about some languages
# the variable types are dynamic, so I can just change it
# from a string to a int on the fly.
outsideTemp = int(float(outsideTemp) +.5)
power = c.execute(
"select rpower from power").fetchone()[0]
power = int(float(power)+.5)
voltage = c.execute(
"select voltage from power").fetchone()[0]
voltage = int(float(voltage)+.5)
apparentPower = c.execute(
"select apower from power").fetchone()[0]
apparentPower = float(apparentPower)
current = c.execute(
"select current from power").fetchone()[0]
current = int(float(current)+.5)
frequency = c.execute(
"select frequency from power").fetchone()[0]
frequency = float(frequency)
powerFactor = c.execute(
"select pfactor from power").fetchone()[0]
powerFactor = float(powerFactor)
insideTemp = c.execute(
"select avg(\"temp-reading\") from thermostats").fetchone()[0]
insideTemp = int(float(insideTemp)+.5)
# OK, got all the stuff I want to update
dbconn.close() # close the data base
#
# This is a debug statement that I put in to show
# not only what the values were, but also how they
# can be formatted.
# print ("Power = %d \nVoltage = %d \nApparent Power = %d "
# "\nCurrent = %d \nFrequency %.2f \nPower Factor = %.2f "
# "\nOutside Temp = %d \nInside Temp = %d" %
# (power, voltage, apparentPower, current,
# frequency, powerFactor, outsideTemp, insideTemp))

# OK, now I've got all the data I want to record on emoncms
# so I have to put it in json form.  json isn't that hard if you 
# don't have multiple levels, so I'll just do it with a string
# format.  It's just a set of ordered pairs for this.
params = ("RealPower:%d,PowerFactor:%.2f,"
"PowerVoltage:%d,PowerFrequency:%.2f,"
"InsideTemp:%d,OutsideTemp:%d" %
(power,powerFactor,voltage,frequency,insideTemp,
outsideTemp))
# if you want to see the result of the formatting, just uncomment
# the line below.  This stuff gets confusing, so give it a try
#print params
#
# Now, just send it off to emoncms
conn = httplib.HTTPConnection("emoncms.org:80")
request = "/input/post?apikey=" + EMONKEY + "&" + "json=" + params
#print request
# emoncms uses a GET not a POST
conn.request("GET", request)
response = conn.getresponse()
#print "emoncms Response:", response.status, response.reason
# I only check for the 'OK' in the reason field.  That's 
# so I can print a failure to any log file I happen to set 
# up.  I don't want to print a lot of stuff that I have to
# manage somehow.  However, emoncms seldom returns an error,
# I messed this interaction up a number of times and never got
# an error.  
if (response.reason != 'OK'):
print "Problem, ", response.status, response.reason
# conn.close

# This is where the main code begins.  Notice how basically nothing
# happens here?  I simply show a sign on message, set up logging, and
# start a scheduled task to actually do the work.
print "started at ", time.strftime("%A, %B, %d at %H:%M:%S")
logging.basicConfig()

#------------------Stuff I schedule to happen -----
scheditem = Scheduler()
scheditem.start()
# every minute update the data store on ThingSpeak
scheditem.add_interval_job(updateEmonCms, seconds=60)

while True:
time.sleep(20) #This doesn't matter much since it is schedule driven

As usual, it's way over commented.  I think there's a lot more lines of comment than lines of code in these things.

Now, I have only one more of these to build, the one to keep my legacy feeds at Xively up to date.  I want to keep them running when I turn off the old controller so I can transfer the data from them to the new Xively interface.  Then, I'm going to take a look at a new service that just came on line a little while back to see how they work.

Cloud services are the way of the future IMHO.

Raspberry Pi and ThingSpeak

As I was continuing to program my Raspberry Pi, it came to me that I don't just update Xively, I also send data to ThingSpeak and emoncms.  They're both good services and I don't really want to drop them.  My previous post on ThingSpeak <link> was fun, so I thought about it a bit and decided to take that service on first.

One thing I thought of is that with the architecture I chose for the processes and data base, the updating of an external (or even internal) service could be a separate process that gets readings from the data base and forwards them off.  So, first I separated the Xively code into a separate process and tested the idea.  It worked like a charm.  So, I created a process for doing the same thing for ThingSpeak.

It followed the model of: not enough examples, not enough documentation in the examples that did exist, and nothing that actually fit what I wanted to do.  But, since I'm getting a little more buzz-word and jargon savvy, this effort was about a morning instead of days.

So, the python script below sucks items out of the database, formats, and forwards them to ThingSpeak.  I used the same tactic that I've found makes my job much, much easier, scheduling.  If you glance at the code, all the main processing does is set up a few things, schedule a task every minute, then hang in a loop.  Every minute, it wakes up a routine that does all the work.  That's all there is to it.  Unlike many of the examples out there, this code is running right this second on my little Pi and doing a fine job of sending the data off.

It runs in the background like the code that monitors the house and records the data in my data base.  I just invoke it by "nohup python -u updatethingspeak.py >logfile &" (leave out the quotes of course), and it just works its little heart out.  I'll eventually look into automating the bring up of the various pieces so that I don't have to bother with it after a reboot or power failure, but for now, doing it by hand is fine.

As usual, it is HIGHLY commented.  I hate examples the cause you to search the internet for hours to get any understanding of what is going on.  I also used the simplest statements I could while still leveraging the power of python.  It's funny that python was designed to be self-documenting and easy to read, but excels at being obtuse and strange.  That's almost entirely because people have chosen to use it that way.  My thinking is that I'm going to come back to this code in a year or two and have to remember what the heck I did and why.  When you do something similar, keep that in mind.

The Python Script

#! /usr/bin/python
from apscheduler.scheduler import Scheduler
import datetime
import logging
import time
import sqlite3
import httplib, urllib

# The feed id and API key that is needed
thingSpeakKey = "putsomethinginhere"
# the database where I'm storing stuff
DATABASE='/home/pi/database/desert-home'
# This is where the update to ThingSpeak happens
def updateThingSpeak():
#print "Updating ThingSpeak ", time.strftime("%A, %B %d at %H:%M:%S")
# open the database
dbconn = sqlite3.connect(DATABASE)
c = dbconn.cursor()
# Getting it out of the database a field at a time
# is probably less efficient than getting the whole record,
# but it works.  
#
# On ThingSpeak I only update real power, power factor, voltage,
# frequency, outside temperature and inside temperature
# so I'm simply going to put the values in variables instead of
# some complex (and probably faster) compound statement.
outsideTemp = c.execute(
"select currenttemp from xbeetemp").fetchone()[0]
# This a really cool thing about some languages
# the variable types are dynamic, so I can just change it
# from a string to a int on the fly.
outsideTemp = int(float(outsideTemp) +.5)
power = c.execute(
"select rpower from power").fetchone()[0]
power = int(float(power)+.5)
voltage = c.execute(
"select voltage from power").fetchone()[0]
voltage = int(float(voltage)+.5)
apparentPower = c.execute(
"select apower from power").fetchone()[0]
apparentPower = float(apparentPower)
current = c.execute(
"select current from power").fetchone()[0]
current = int(float(current)+.5)
frequency = c.execute(
"select frequency from power").fetchone()[0]
frequency = float(frequency)
powerFactor = c.execute(
"select pfactor from power").fetchone()[0]
powerFactor = float(powerFactor)
insideTemp = c.execute(
"select avg(\"temp-reading\") from thermostats").fetchone()[0]
insideTemp = int(float(insideTemp)+.5)
# OK, got all the stuff I want to update
dbconn.close() # close the data base
#
# This is a debug statement that I put in to show
# not only what the values were, but also how they
# can be formatted.
# print ("Power = %d \nVoltage = %d \nApparent Power = %d "
# "\nCurrent = %d \nFrequency %.2f \nPower Factor = %.2f "
# "\nOutside Temp = %d \nInside Temp = %d" %
# (power, voltage, apparentPower, current,
# frequency, powerFactor, outsideTemp, insideTemp))

# OK, now I've got all the data I want to record on ThingSpeak
# So, I have to get involved with that thing called a REST interface
# It's actually not too bad, it's just the way people pass 
# data to a web page, you see it all the time if you watch
# how the URL on your browser changes.
#
# urlencode takes a python tuple as input, but I just create it
# as a parameter so you can see what it really is.
params = urllib.urlencode({'field1': power, 'field2':powerFactor,
'field3':voltage, 'field4':frequency, 
'field5':insideTemp, 'field6':outsideTemp,
'key':thingSpeakKey})
# if you want to see the result of the url encode, just uncomment
# the line below.  This stuff gets confusing, so give it a try
#print params
#
# Now, just send it off as a POST to ThingSpeak
headers = {"Content-type": "application/x-www-form-urlencoded", "Accept": "text/plain"}
conn = httplib.HTTPConnection("api.thingspeak.com:80")
conn.request("POST", "/update", params, headers)
response = conn.getresponse()
#print "Thingspeak Response:", response.status, response.reason
# I only check for the 'OK' in the reason field.  That's 
# so I can print a failure to any log file I happen to set 
# up.  I don't want to print a lot of stuff that I have to
# manage somehow.
if (response.reason != 'OK'):
print "Problem, ", response.status, response.reason

conn.close

# This is where the main code begins.  Notice how basically nothing
# happens here?  I simply show a sign on message, set up logging, and
# start a scheduled task to actually do the work.
print "started at ", time.strftime("%A, %B, %d at %H:%M:%S")
logging.basicConfig()

#------------------Stuff I schedule to happen -----
scheditem = Scheduler()
scheditem.start()
# every minute update the data store on ThingSpeak
scheditem.add_interval_job(updateThingSpeak, seconds=60)

while True:
time.sleep(20) #This doesn't matter much since it is schedule driven

Yep, that's all there is to it.  Using this technique, I can add or subtract services really easily.  If I run across something that catches my eye, it'll only be a little while before I'm testing it.

Have fun.

Raspberry Pi Home Automation Process Architecture

Don't you dare make fun of the title of this post.  Yes, it's a bit presumptuous, but I couldn't think of anything else.  After a lot of experimenting and trying to overcome various problems, I finally settled on an architecture of the various devices and controls that I think can work over time.

Let's talk about the XBee network I have as an example.  I have a network of XBees that monitor and control things around the house and this has to hook into the Pi.  So, in an earlier post, I discuss how easy to hook into the Pi it was.  Now, that I've used up the only serial port on the Pi, I ran into a problem.  Only one process can reliably hook to the Pi's serial port and communicate both ways.  If you have two processes talking to one port, bad things happen like overlaying data, seeing a character come in and not being able to get it because some other process got to it first; that kind of thing.  This isn't like an arduino where there is only one piece of code that needs this stuff, there's a number of them. So, what to do?  Functionally, the entire process interaction looks like this:

The House Monitor box is a process that runs all the time and constantly monitors the XBee network as well as periodically interrogating the ethernet devices.  When data comes in, it updates the database with the current readings.  It is the sole interface to the XBee network.  To get commands from the internet to do something like open a garage door, the web page sends a jquery() PUT to the command handler who forwards it (translating on the way) to the House Monitor.  The house monitor forwards it along to the XBee network.  In due course, the device will receive the command, do whatever, and send back a status that is recorded in the database.

If I load one of the presentation pages, it will send a jquery() GET to the Data Provider code that will suck the data out of the database and hand it back to the page for display on a user's browser.  The web pages have a timer running on them that refreshes the data every so often so it can be used as a monitor of what's going on.  For example, the swimming pool does several things on its own, like turn the motor on high in the morning, drop it to low for a while, then finally turn it off entirely during the peak period.  Each of these things is caught by the House Monitor process and recorded as the current state in the data base.  So a presentation page will update and show that the motor did its thing.  If I open the garage door, the garage controller will report it and House Monitor will stick that in the data base too.  Basically, the database is the holder of all the current status of devices, including the current power usage.

Yes, this means there is a small delay between commanding something to happen and seeing the result of it on the display, but that's not important to me.  There's always a delay between sending a signal to a real device and seeing the result.  

Doing things this way, I can make an interface inside the house that talks directly to the House Monitor and it can control stuff with no delay (other that what's required to do whatever it is).  The idea is that the House Monitor process is the thing in actual control, other software and devices talk to it.

I separated the presentation from the data gathering so I can change an HTML page anytime I want to without worrying about how the data handling will have to change to support it.  This way I can mess with tables, pictures, colors and stuff without accidentally screwing up the data handling.  It also hides the database entirely away from the web, so certain hacking techniques can't harm it.  It also means that I can have multiple presentation pages without having to duplicate the way I grab stuff out of the data base.

I also got ALL the devices I update to the various cloud services talking to the Pi.  I have updated my account on Xively to the latest stuff they came out with and am recording the actual readings there every minute or so.  I may lose the old data, since they still haven't given me any way to move it into the new system.  Or, I may just write a python script to suck the data out and put it back into the new feeds.  It would only take a few million transactions to get it here and put it there.  He he.  

Code?  You want me to show you the Code?  Actually, there's nothing secret about it and I'm totally willing to share, but I'm going to hold off a few more days until I can remove the old controller from service and reuse the page it is described on.  

Physically, the Pi is naked, no box, no special lights on it, and hooked to a breadboarded XBee.  Nope, I haven't even started looking for something to put it in, or decided if I want it to have a display.  It would be nice to have a display that shows something, but there are so many possibilities that I'm kind of stuck deciding what to do.   The odd way they constructed the little board doesn't lend itself to any simple ideas.  It'll work out in time; I may just use a cardboard box.

Here's the latest web page presentation:

Yes, the gauges reflect the very latest readings from the sensors, the boxes show the real state of the various devices, and the buttons work.  It isn't very pretty, but I wasn't working on pretty.  Actually, I made all the boxes float so they display in different places depending on the resolution of the browser.  I can see all of them on my phone by just scrolling around.  This part will be constantly modified as I think of something to try out.  It's just a presentation page and I don't have to change anything else to make it look completely different.   Oh, the little diag button up there will expand into a scrolling screen of debug information; makes it nice to see some of the underlying communication when I need to.

Less than an amp of power and roughly fifty buck so far.  Go ahead, try and beat it.

Raspberry Pi, Getting Control Of The Devices

Still making progress on this thing. There's a little pressure to get it finished since I want it to take over the house control functions. I managed to get the buttons working to control the thermostats and next is my first XBee device, and it is going to be painful. I haven't found or experimented with a php library for controlling the XBees so back to searching the web for clues. Meanwhile, I was having a lot of trouble getting the various syntax correct and was also having some trouble with the command to the devices. I needed something on the web page to tell me what was actually going on that could maybe become a monitor of the network internals. I sneaked the code that I use to put up the code boxes on this blog and shoved it into the controller web page. It worked like a charm; I was even able find a way to force it to scroll to the bottom as new items came in. This could turn out to be a great way of monitoring actual conversations between devices later when the entire device set is hooked in. Here's an example of it in operation:

Recognize the blue box?  It's got a bunch of debug on it from me hooking in the Acid Pump, but that particular piece doesn't work yet since it's XBee controlled.  It's actually pretty easy to hide things in web pages, so I may hide it and put a button up to show it when needed.  There's actually no limit to what kinds of things I can do now that I have a real web server running to present the data.

I still haven't settled on the entire architecture.  I have a background process that collects data from around the house and shoves it into a database.  Then the web page interacts with a php file to suck the data back out.  All the buttons connect to another php file that translates the clicks into commands that are tailored for each particular device since they are all different from each other.  Yes, I screwed up and made them different.  But as I went along, I learned things and did them differently as each new device came online.  Actually, the separation from data display and command makes it easier to add in a device.  I construct a basic page, implement the commands, and go back and play with presentation.  Since presentation is a never ending task, it's nice to have it isolated.

Raspberry Pi, Now it's actually getting somewhere

So, when I started trying to put things together, it got so darn complex that I changed a ton of stuff to make it somewhat simpler, at least for me.  Here's the current display:

The buttons don't do anything except show an alert to prove they do something, I still have a number of devices that I have to include, and there's a ton of stuff to pretty it up and put in some presets I've grown used to.  I currently have presets for things like A/C off, summer night settings, winter night settings, that kind of thing.

But, isn't it cool ?

All the data is live and real, including the SteelSeries gauges up at the top.  Sorry, it isn't on the internet yet, I have to wait until I get it finished enough to turn off the old controller before I can put it online.  I broke the web interface into two components, one that presents the web page and the other that sucks things out of the data base and hands them off to the web interface.  The code to create a complete screen was just getting out of hand, so I split it apart.

This gave me the opportunity to try out tables and colors and things, as well as experiment with getting data out of the database to construct a JSON response.  This actually turned out to be relatively easy, but it drove me nuts thinking of variable names.  I got to mess with jquery() which turns out to be quite a workhorse and makes things even simpler.

I'm starting to get tired of reading tutorials on various things.  But the single most annoying thing is the difference between comment delimiters in the various languages.  Heck, you have to use multiple different kinds in a single web page ... silly.

I'll do a post on the overall architecture of the system soon, I want to conquer the buttons and build in the rest of the devices so I can replace the current controller.  With the way things have been going I'll probably change the architecture two or three times before it's done.

With the cost of tablets dropping, my ultimate plan is to have a couple around the house that are used to control things.  Y'know, one of them by the bed so I don't have to get up to change something.  The ultimate remote control.

Heck, I may hook the barbecue up to it someday.

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.