The Central Heating Pi - Revisited!
Since building the Pi-based central heating controller I have improved it by adding temperature sensing. The controller now has access to sensors that record the temperature of the hot water tank and the main room. This allows me to now set a temperature target for these two which the controller will attempt to hit - in other words I now have a thermostat controlled heating system (like most central heating systems!).
The sensors I used were these:
http://shop.ciseco.co.uk/temperature-xrf-development-sensor-dallas-ds18b20/
These use an XRF radio module which allows me to communicate wirelessly with the Pi. When built the sensors look like this:
...and these can be placed anywhere in the house. The radio modules look like this:
By only waking up every five minutes the sensors use very little power - they run off CR2025 button cells and have been running for about 6 months so far without a battery change needed.
For the tank temperature I mounted the thermistor on the end of a short piece of wire so that I could nestle the tip of it right up against the metal skin of the tank.
When the reading is received the controller writes the data to the MySQL database and I use Google Visualizations to show the values on dials on the heating page. Clicking on the dial takes you through to charts showing how the temperature has varied over the day/week/month:
This is particularly useful for checking if there is any hot water in the tank before taking a shower or bath. I can tell if there is going to be enough without fumbling around in the airing cupboard to feel the tank! It's also quite revealing to see how the tank cools over the day - I may experiment with extra insulation to see if I can improve it....
Here is the new improved controller that responds to temperature readings:
#! /usr/bin/env python
import wiringpi
import MySQLdb
import sys
from GPIOpins import GPIO_HWstate
from GPIOpins import GPIO_CHstate
from GPIOpins import GPIO_switchHW
from GPIOpins import GPIO_switchCH
from GPIOpins import GPIO_ledCHred
from GPIOpins import GPIO_ledHWred
from GPIOpins import GPIO_ledCHgreen
from GPIOpins import GPIO_ledHWgreen
from time import sleep
intervalTime=20
gpio = wiringpi.GPIO(wiringpi.GPIO.WPI_MODE_GPIO)
gpio.pinMode(GPIO_switchHW,gpio.OUTPUT)
gpio.pinMode(GPIO_switchHW,gpio.OUTPUT)
gpio.pinMode(GPIO_ledCHred,gpio.OUTPUT)
gpio.pinMode(GPIO_ledHWred,gpio.OUTPUT)
gpio.pinMode(GPIO_ledCHgreen,gpio.OUTPUT)
gpio.pinMode(GPIO_ledHWgreen,gpio.OUTPUT)
gpio.pinMode(GPIO_CHstate,gpio.INPUT)
gpio.pinMode(GPIO_HWstate,gpio.INPUT)
###############################################################################
def thermostatCheck(cursor, CHState, HWState, RoomTemp, HWTemp):
# CHState -the desired Central Heating State 'ON' or 'OFF'
# HWState -the desired Hot Water State 'ON' or 'OFF'
# RoomTemp, HWTemp - the current temperature of each
rtn=getThermostat(cursor)
RoomThermostat = rtn[0]
WaterThermostat = rtn[1]
ThermActive = rtn[2]
# RoomThermostat, WaterThermostat - the target temperature selections
# ThermActive - Y/N controls whether the thermostat numbers should be obeyed or not
# return: a list - the first element is the heating value, the second is the hot water value
CHoutState = CHState
HWoutState = HWState
# if either is above threshold then turn it off:
if ThermActive=='Y':
if CHState=="ON":
if (RoomTemp>RoomThermostat):
CHoutState='OFF'
if HWState=="ON":
if (HWTemp>WaterThermostat):
HWoutState='OFF'
# if we need the heating on then hot water must be on too (this is just the way my central heating works!):
if CHoutState=="ON":
HWoutState="ON"
rtn_list=[CHoutState, HWoutState]
return rtn_list
###############################################################################
def getTemperature(cursor, sensor):
temperature=0
# the temperature table stores all the temp readings from the wireless sensors. A seperate process listens for the readings and writes them to the DB. Only select from the last day of readings otherwise the query takes too long:
cursor.execute("select time, reading from temperature where sensor='"+sensor+"' and time>= DATE_SUB(NOW(), INTERVAL 1 DAY) order by time desc")
row = cursor.fetchone();
if row is not None:
datatime = row[0]
temperature = row[1]
tempdate = datatime.strftime( '%d %b, %H:%M' )
print sensor + "=" + str(temperature)
return temperature
###############################################################################
def getThermostat(cursor):
cursor.execute("select roomtemp, watertemp, active from thermostat")
roomtemp, watertemp, active = cursor.fetchone()
print "therm= "+ str(roomtemp) + " " + str(watertemp) + " " + active
rtn_list=[roomtemp, watertemp, active]
return rtn_list
###############################################################################
def log(logmessage):
# Open database connection
logdb = MySQLdb.connect("localhost","user","******","heating" )
# prepare a cursor object using cursor() method
logcursor = logdb.cursor()
# Prepare SQL query to INSERT a record into the database.
sql = """INSERT INTO log(source,
message)
VALUES ('controller','"""+logmessage+"""')"""
try:
logcursor.execute(sql)
logdb.commit()
except:
logdb.rollback()
# disconnect from server
logdb.close()
###############################################################################
def switch(switchpin, statepin, desiredstate):
# first check the state of the channel (HW or CH)
currState = gpio.digitalRead(statepin)
# '1' indicates off, '0' indicates on!
if (currState==1):
currState = "OFF"
else:
currState = "ON"
print "switch pin ", switchpin, " statepin ", statepin, " desired state ", desiredstate, " curr state is ", currState
# check if there is anything needed to do:
if (desiredstate!=currState):
print "switching"
gpio.digitalWrite(switchpin,gpio.HIGH)
sleep(0.25)
gpio.digitalWrite(switchpin,gpio.LOW)
return
###############################################################################
print "controller starting"
log("controller starting")
firsttime=True
# open the database
connection = MySQLdb.connect(host="localhost", user="user", passwd="******", db="heating")
cursor = connection.cursor ()
cursor.execute("select ucase(dayname(curdate()))")
row = cursor.fetchone()
last_day = row[0]
cursor.execute("select curtime()")
row = cursor.fetchone()
last_time=row[0]
# set the heating to the state shown in the DB. This ensures that a re-boot does
# not affect the state of the heating. If we don't do this the GPIO pins tend to
# throw random values on start-up which can switch the HW/CH on or off. This piece
# was added to try and make sure that a re-boot doesn't make any change to the state.
cursor.execute("select heating, hotwater from current_state")
data = cursor.fetchall()
for row in data:
currDBHeating = row[0]
currDBHotWater = row[1]
print "Reseting state to HW=", currDBHotWater, " CH=", currDBHeating
switch(GPIO_switchHW, GPIO_HWstate, currDBHotWater)
switch(GPIO_switchCH, GPIO_CHstate, currDBHeating)
cursor.close()
connection.close()
print "start is ", last_day, " time=", last_time
sleep(intervalTime)
while True:
print "loop start==========================="
# open the database
connection = MySQLdb.connect(host="localhost", user="user", passwd="******", db="heating")
cursor = connection.cursor ()
# get the current scheduled state:
cursor.execute("select heating, hotwater from current_state")
data = cursor.fetchall()
for row in data:
currStateHeating = row[0]
currStateHotWater = row[1]
print "current: H=" + currStateHeating + " HW=" + currStateHotWater
# get the 'actual' state - that is, the state that the controller actually demanded:
cursor.execute("select heating, hotwater from actual_state")
data = cursor.fetchall()
for row in data:
actualStateHeating = row[0]
actualStateHotWater = row[1]
print "actual : H=" + actualStateHeating + " HW=" + actualStateHotWater
# get the state of the heating/HW from the GPIO pins:
gpioHW=gpio.digitalRead(GPIO_HWstate)
gpioCH=gpio.digitalRead(GPIO_CHstate)
# translate the detected state into ON/OFF values and set the red leds to show the current state
if (gpioHW==1):
detectedStateHotWater="OFF"
else:
detectedStateHotWater="ON"
if (gpioCH==1):
detectedStateHeating="OFF"
else:
detectedStateHeating="ON"
print "detected: H=" + detectedStateHeating + " HW=" + detectedStateHotWater
# if the detected state does not match the actual state then someone has pressed a button on the heating
# controller... In which case treat this just as though there has been an Override row found:
if detectedStateHeating!=actualStateHeating:
logline = "Heating button pressed det=" + detectedStateHeating + " act=" + actualStateHeating
log(logline)
currStateHeating=detectedStateHeating
if detectedStateHotWater!=actualStateHotWater:
logline = "Hot Water button pressed det=" + detectedStateHotWater + " act=" + actualStateHotWater
log(logline)
currStateHotWater=detectedStateHotWater
# get today's day
cursor.execute("select ucase(dayname(curdate()))")
row = cursor.fetchone()
day = row[0]
# get the current time
cursor.execute("select curtime()")
row = cursor.fetchone()
time=row[0]
#print "now it is ", day, " time=", time
# if the day has changed then we will just run a query from the last point in time up to midnight...
if last_day != day:
print "day change"
# save the name of 'today' so that we can put it back later:
tomorrow=day
# now pretend that the current time is 1 second to midnight 'yesterday':
day=last_day
time='23:59:59'
daychange=True
else:
daychange=False
# first see if the mode is 'AUTO' or 'MANUAL'
query = "select mode from current_state"
cursor.execute(query)
data = cursor.fetchall()
for row in data:
mode = row[0]
# look for any scheduled event that has occurred since the last run
query = "select * from schedule where day='"+day+"' and time>'" + str(last_time) + "' and time<='" + str(time) + "' order by time"
cursor.execute(query)
data = cursor.fetchall()
for row in data:
# found an event....
row_id = row[0]
day = row[1]
time = row[2]
hot_water = row[3]
heating = row[4]
print "id=", row_id, " day=", day, " time=", time, " hot_water=", hot_water, " heating=", heating
if mode=='AUTO':
print "executing at ", day, time
logline="scheduled event id="+str(row_id)+" day="+day+" time="+str(time)+" hot_water="+hot_water+" heating="+heating
log(logline)
currStateHeating=heating
currStateHotWater=hot_water
# if this was a day change then reset the 'last' point in time to midnight:
if daychange :
last_day=tomorrow
last_time='00:00:00'
else:
# record the last point in time ready for the next interval
last_time=time
last_day=day
# check for overrides:
query = "select * from override where status='P' order by time"
cursor.execute(query)
data = cursor.fetchall()
for row in data:
print "execute override"
logline="override hot_water="+row[1]+" heating="+row[0]
log(logline)
if (row[0]=="ON" or row[0]=="OFF"):
currStateHeating=row[0]
if (row[1]=="ON" or row[1]=="OFF"):
currStateHotWater=row[1]
# reset ALL overrides to 'C'omplete
cursor.execute("update override set status='C'")
connection.commit()
# turn off any green 'button' lights:
gpio.digitalWrite(GPIO_ledCHgreen,gpio.LOW)
gpio.digitalWrite(GPIO_ledHWgreen,gpio.LOW)
# finally check the temperature/thermostat settings:
roomTemp = getTemperature(cursor,"T1")
waterTemp = getTemperature(cursor,"T2")
print "b4 therm: H=" + actualStateHeating + " HW=" + actualStateHotWater
rtn = thermostatCheck(cursor, currStateHeating, currStateHotWater, roomTemp, waterTemp)
actualStateHeating = rtn[0]
actualStateHotWater = rtn[1]
print "after therm: H=" + actualStateHeating + " HW=" + actualStateHotWater
update_query= "update current_state set heating='"+ currStateHeating +"', hotwater='"+ currStateHotWater + "'"
print update_query
cursor.execute(update_query)
update_query= "update actual_state set heating='"+ actualStateHeating +"', hotwater='"+ actualStateHotWater + "'"
print update_query
cursor.execute(update_query)
connection.commit()
# Set the red LEDs to show the scheduled state:
if (currStateHeating=="ON"):
gpio.digitalWrite(GPIO_ledCHred,gpio.HIGH)
else:
gpio.digitalWrite(GPIO_ledCHred,gpio.LOW)
if (currStateHotWater=="ON"):
gpio.digitalWrite(GPIO_ledHWred,gpio.HIGH)
else:
gpio.digitalWrite(GPIO_ledHWred,gpio.LOW)
# and last of all, switch the heating/hot water on or off if needed:
switch(GPIO_switchHW, GPIO_HWstate, actualStateHotWater)
switch(GPIO_switchCH, GPIO_CHstate, actualStateHeating)
cursor.close()
connection.close()
sleep(intervalTime)
and here is the code that records the temperatures to the DB:
#! /usr/bin/env python
#
import serial
import MySQLdb
# import time functions
import datetime
from datetime import date
import time
#
# SETTINGS
#
# Default settings for program; port, baud rate, temperature threshold, number of readings to store
# set up serial port for temperature readings
DEVICE = '/dev/ttyAMA0'
BAUD = 9600
# END OF SETTINGS
#
#
#
# set battery level string to "????"
battlevel = "????"
# end of variables set up
def writeTemp(sensor, temp):
now = datetime.datetime.now()
# Open database connection
db = MySQLdb.connect("localhost","user","******","heating" )
# prepare a cursor for storing the temperature reading
cursor = db.cursor()
# Prepare SQL query to INSERT a record into the database.
sql = """INSERT INTO temperature(sensor,
reading,
time)
VALUES ('"""+sensor+"""',"""+str(temp)+""",'"""+str(now)+"""')"""
try:
cursor.execute(sql)
db.commit()
except:
db.rollback()
# prepare a cursor for storing just the current temperature reading
cursor3 = db.cursor()
# Prepare SQL query to UPDATE a record into the database.
sql_update = """UPDATE curr_temperature set reading = '"""+str(temp)+"""',
time = '"""+str(now)+"""'
where sensor='"""+sensor+"""'"""
print sql_update
try:
cursor3.execute(sql_update)
db.commit()
except:
db.rollback()
# prepare a cursor object using cursor() method
cursor2 = db.cursor()
# Prepare SQL query to INSERT a record into the database.
sql = """UPDATE sensors set last_reading_time='"""+str(now)+"""' where ident='"""+sensor+"""'"""
try:
cursor2.execute(sql)
db.commit()
except:
print "update failed " + sql
db.rollback()
cursor2.close()
# disconnect from server
db.close()#
############################################################################################
def writeBattery(sensor, voltage):
now = datetime.datetime.now()
# Open database connection
db = MySQLdb.connect("localhost","user","******","heating" )
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to INSERT a record into the database.
sql = """INSERT INTO battery(sensor,
time,
voltage)
VALUES ('"""+sensor+"""',"""+str(now)+""",'"""+str(voltage)+"""')"""
try:
cursor.execute(sql)
db.commit()
except:
db.rollback()
# disconnect from server
db.close()#
############################################################################################
#
print "Opening connection and waiting for response..."
#
ser = serial.Serial(DEVICE, BAUD)
print "Startup complete"
print " "
# read the time
now = datetime.datetime.now()
msg = 'monitor initialised : ' + now.strftime("%H:%M %m-%d-%Y")
print msg
#
# Start infinite while loop to listen to XRF module
while 1 :
# All XRF module read and write commands should have 12 characters and begin with the letter "a"
# Wait for message, the 1 second pause seems to improve the reading when several messages
# are arriving in sequence, such as: a--TMP22.12-a--AWAKE----a--BATT2.74-a--SLEEPINGtime.
time.sleep(1)
#if ser.inWaiting()>0:
# print ser.inWaiting()
# llapMsg = ser.read(ser.inWaiting())
# print llapMsg
if ser.inWaiting() >= 12 :
llapMsg = ser.read(12)
# display packet, helps to troubleshoot any errors
now = datetime.datetime.now()
now.strftime("%H:%M %m-%d-%Y")
print 'Received '+ llapMsg + ' at ' + str(now)
if 'a' == llapMsg[0] :
#llap msg detected
#print "(" + llapMsg[1:3] +")"
sensorName=llapMsg[1:3]
#
#
# Check for TMP reading or battery packet, ignore anything else.
#
# Is it a battery reading?
if 'BATT' in llapMsg :
# Battery reading sent
print "Battery level is " + llapMsg[7:10] + "V"
# Save this value for later.
battlevel = llapMsg[7:10]
writeBattery(sensorName, battlevel)
#
# Is it an temp reading?
if 'TMP' in llapMsg :
# reading sent
#
temp = llapMsg[7:12]
print "temp=" + temp
writeTemp(sensorName, temp)
# temp is a 1 element array, hence the "[" "]"
#except ValueError:
# if float operation fails, skip bad reading
# print "bad reading"
#
ser.flushInput()
#
# for want of a better phrase,- endwhile
# end of program
Please note - I haven't got the battery level working yet. Not sure why!
Here is the new improved controller that responds to temperature readings:
#! /usr/bin/env python
import wiringpi
import MySQLdb
import sys
from GPIOpins import GPIO_HWstate
from GPIOpins import GPIO_CHstate
from GPIOpins import GPIO_switchHW
from GPIOpins import GPIO_switchCH
from GPIOpins import GPIO_ledCHred
from GPIOpins import GPIO_ledHWred
from GPIOpins import GPIO_ledCHgreen
from GPIOpins import GPIO_ledHWgreen
from time import sleep
intervalTime=20
gpio = wiringpi.GPIO(wiringpi.GPIO.WPI_MODE_GPIO)
gpio.pinMode(GPIO_switchHW,gpio.OUTPUT)
gpio.pinMode(GPIO_switchHW,gpio.OUTPUT)
gpio.pinMode(GPIO_ledCHred,gpio.OUTPUT)
gpio.pinMode(GPIO_ledHWred,gpio.OUTPUT)
gpio.pinMode(GPIO_ledCHgreen,gpio.OUTPUT)
gpio.pinMode(GPIO_ledHWgreen,gpio.OUTPUT)
gpio.pinMode(GPIO_CHstate,gpio.INPUT)
gpio.pinMode(GPIO_HWstate,gpio.INPUT)
###############################################################################
def thermostatCheck(cursor, CHState, HWState, RoomTemp, HWTemp):
# CHState -the desired Central Heating State 'ON' or 'OFF'
# HWState -the desired Hot Water State 'ON' or 'OFF'
# RoomTemp, HWTemp - the current temperature of each
rtn=getThermostat(cursor)
RoomThermostat = rtn[0]
WaterThermostat = rtn[1]
ThermActive = rtn[2]
# RoomThermostat, WaterThermostat - the target temperature selections
# ThermActive - Y/N controls whether the thermostat numbers should be obeyed or not
# return: a list - the first element is the heating value, the second is the hot water value
CHoutState = CHState
HWoutState = HWState
# if either is above threshold then turn it off:
if ThermActive=='Y':
if CHState=="ON":
if (RoomTemp>RoomThermostat):
CHoutState='OFF'
if HWState=="ON":
if (HWTemp>WaterThermostat):
HWoutState='OFF'
# if we need the heating on then hot water must be on too (this is just the way my central heating works!):
if CHoutState=="ON":
HWoutState="ON"
rtn_list=[CHoutState, HWoutState]
return rtn_list
###############################################################################
def getTemperature(cursor, sensor):
temperature=0
# the temperature table stores all the temp readings from the wireless sensors. A seperate process listens for the readings and writes them to the DB. Only select from the last day of readings otherwise the query takes too long:
cursor.execute("select time, reading from temperature where sensor='"+sensor+"' and time>= DATE_SUB(NOW(), INTERVAL 1 DAY) order by time desc")
row = cursor.fetchone();
if row is not None:
datatime = row[0]
temperature = row[1]
tempdate = datatime.strftime( '%d %b, %H:%M' )
print sensor + "=" + str(temperature)
return temperature
###############################################################################
def getThermostat(cursor):
cursor.execute("select roomtemp, watertemp, active from thermostat")
roomtemp, watertemp, active = cursor.fetchone()
print "therm= "+ str(roomtemp) + " " + str(watertemp) + " " + active
rtn_list=[roomtemp, watertemp, active]
return rtn_list
###############################################################################
def log(logmessage):
# Open database connection
logdb = MySQLdb.connect("localhost","user","******","heating" )
# prepare a cursor object using cursor() method
logcursor = logdb.cursor()
# Prepare SQL query to INSERT a record into the database.
sql = """INSERT INTO log(source,
message)
VALUES ('controller','"""+logmessage+"""')"""
try:
logcursor.execute(sql)
logdb.commit()
except:
logdb.rollback()
# disconnect from server
logdb.close()
###############################################################################
def switch(switchpin, statepin, desiredstate):
# first check the state of the channel (HW or CH)
currState = gpio.digitalRead(statepin)
# '1' indicates off, '0' indicates on!
if (currState==1):
currState = "OFF"
else:
currState = "ON"
print "switch pin ", switchpin, " statepin ", statepin, " desired state ", desiredstate, " curr state is ", currState
# check if there is anything needed to do:
if (desiredstate!=currState):
print "switching"
gpio.digitalWrite(switchpin,gpio.HIGH)
sleep(0.25)
gpio.digitalWrite(switchpin,gpio.LOW)
return
###############################################################################
print "controller starting"
log("controller starting")
firsttime=True
# open the database
connection = MySQLdb.connect(host="localhost", user="user", passwd="******", db="heating")
cursor = connection.cursor ()
cursor.execute("select ucase(dayname(curdate()))")
row = cursor.fetchone()
last_day = row[0]
cursor.execute("select curtime()")
row = cursor.fetchone()
last_time=row[0]
# set the heating to the state shown in the DB. This ensures that a re-boot does
# not affect the state of the heating. If we don't do this the GPIO pins tend to
# throw random values on start-up which can switch the HW/CH on or off. This piece
# was added to try and make sure that a re-boot doesn't make any change to the state.
cursor.execute("select heating, hotwater from current_state")
data = cursor.fetchall()
for row in data:
currDBHeating = row[0]
currDBHotWater = row[1]
print "Reseting state to HW=", currDBHotWater, " CH=", currDBHeating
switch(GPIO_switchHW, GPIO_HWstate, currDBHotWater)
switch(GPIO_switchCH, GPIO_CHstate, currDBHeating)
cursor.close()
connection.close()
print "start is ", last_day, " time=", last_time
sleep(intervalTime)
while True:
print "loop start==========================="
# open the database
connection = MySQLdb.connect(host="localhost", user="user", passwd="******", db="heating")
cursor = connection.cursor ()
# get the current scheduled state:
cursor.execute("select heating, hotwater from current_state")
data = cursor.fetchall()
for row in data:
currStateHeating = row[0]
currStateHotWater = row[1]
print "current: H=" + currStateHeating + " HW=" + currStateHotWater
# get the 'actual' state - that is, the state that the controller actually demanded:
cursor.execute("select heating, hotwater from actual_state")
data = cursor.fetchall()
for row in data:
actualStateHeating = row[0]
actualStateHotWater = row[1]
print "actual : H=" + actualStateHeating + " HW=" + actualStateHotWater
# get the state of the heating/HW from the GPIO pins:
gpioHW=gpio.digitalRead(GPIO_HWstate)
gpioCH=gpio.digitalRead(GPIO_CHstate)
# translate the detected state into ON/OFF values and set the red leds to show the current state
if (gpioHW==1):
detectedStateHotWater="OFF"
else:
detectedStateHotWater="ON"
if (gpioCH==1):
detectedStateHeating="OFF"
else:
detectedStateHeating="ON"
print "detected: H=" + detectedStateHeating + " HW=" + detectedStateHotWater
# if the detected state does not match the actual state then someone has pressed a button on the heating
# controller... In which case treat this just as though there has been an Override row found:
if detectedStateHeating!=actualStateHeating:
logline = "Heating button pressed det=" + detectedStateHeating + " act=" + actualStateHeating
log(logline)
currStateHeating=detectedStateHeating
if detectedStateHotWater!=actualStateHotWater:
logline = "Hot Water button pressed det=" + detectedStateHotWater + " act=" + actualStateHotWater
log(logline)
currStateHotWater=detectedStateHotWater
# get today's day
cursor.execute("select ucase(dayname(curdate()))")
row = cursor.fetchone()
day = row[0]
# get the current time
cursor.execute("select curtime()")
row = cursor.fetchone()
time=row[0]
#print "now it is ", day, " time=", time
# if the day has changed then we will just run a query from the last point in time up to midnight...
if last_day != day:
print "day change"
# save the name of 'today' so that we can put it back later:
tomorrow=day
# now pretend that the current time is 1 second to midnight 'yesterday':
day=last_day
time='23:59:59'
daychange=True
else:
daychange=False
# first see if the mode is 'AUTO' or 'MANUAL'
query = "select mode from current_state"
cursor.execute(query)
data = cursor.fetchall()
for row in data:
mode = row[0]
# look for any scheduled event that has occurred since the last run
query = "select * from schedule where day='"+day+"' and time>'" + str(last_time) + "' and time<='" + str(time) + "' order by time"
cursor.execute(query)
data = cursor.fetchall()
for row in data:
# found an event....
row_id = row[0]
day = row[1]
time = row[2]
hot_water = row[3]
heating = row[4]
print "id=", row_id, " day=", day, " time=", time, " hot_water=", hot_water, " heating=", heating
if mode=='AUTO':
print "executing at ", day, time
logline="scheduled event id="+str(row_id)+" day="+day+" time="+str(time)+" hot_water="+hot_water+" heating="+heating
log(logline)
currStateHeating=heating
currStateHotWater=hot_water
# if this was a day change then reset the 'last' point in time to midnight:
if daychange :
last_day=tomorrow
last_time='00:00:00'
else:
# record the last point in time ready for the next interval
last_time=time
last_day=day
# check for overrides:
query = "select * from override where status='P' order by time"
cursor.execute(query)
data = cursor.fetchall()
for row in data:
print "execute override"
logline="override hot_water="+row[1]+" heating="+row[0]
log(logline)
if (row[0]=="ON" or row[0]=="OFF"):
currStateHeating=row[0]
if (row[1]=="ON" or row[1]=="OFF"):
currStateHotWater=row[1]
# reset ALL overrides to 'C'omplete
cursor.execute("update override set status='C'")
connection.commit()
# turn off any green 'button' lights:
gpio.digitalWrite(GPIO_ledCHgreen,gpio.LOW)
gpio.digitalWrite(GPIO_ledHWgreen,gpio.LOW)
# finally check the temperature/thermostat settings:
roomTemp = getTemperature(cursor,"T1")
waterTemp = getTemperature(cursor,"T2")
print "b4 therm: H=" + actualStateHeating + " HW=" + actualStateHotWater
rtn = thermostatCheck(cursor, currStateHeating, currStateHotWater, roomTemp, waterTemp)
actualStateHeating = rtn[0]
actualStateHotWater = rtn[1]
print "after therm: H=" + actualStateHeating + " HW=" + actualStateHotWater
update_query= "update current_state set heating='"+ currStateHeating +"', hotwater='"+ currStateHotWater + "'"
print update_query
cursor.execute(update_query)
update_query= "update actual_state set heating='"+ actualStateHeating +"', hotwater='"+ actualStateHotWater + "'"
print update_query
cursor.execute(update_query)
connection.commit()
# Set the red LEDs to show the scheduled state:
if (currStateHeating=="ON"):
gpio.digitalWrite(GPIO_ledCHred,gpio.HIGH)
else:
gpio.digitalWrite(GPIO_ledCHred,gpio.LOW)
if (currStateHotWater=="ON"):
gpio.digitalWrite(GPIO_ledHWred,gpio.HIGH)
else:
gpio.digitalWrite(GPIO_ledHWred,gpio.LOW)
# and last of all, switch the heating/hot water on or off if needed:
switch(GPIO_switchHW, GPIO_HWstate, actualStateHotWater)
switch(GPIO_switchCH, GPIO_CHstate, actualStateHeating)
cursor.close()
connection.close()
sleep(intervalTime)
and here is the code that records the temperatures to the DB:
#! /usr/bin/env python
#
import serial
import MySQLdb
# import time functions
import datetime
from datetime import date
import time
#
# SETTINGS
#
# Default settings for program; port, baud rate, temperature threshold, number of readings to store
# set up serial port for temperature readings
DEVICE = '/dev/ttyAMA0'
BAUD = 9600
# END OF SETTINGS
#
#
#
# set battery level string to "????"
battlevel = "????"
# end of variables set up
def writeTemp(sensor, temp):
now = datetime.datetime.now()
# Open database connection
db = MySQLdb.connect("localhost","user","******","heating" )
# prepare a cursor for storing the temperature reading
cursor = db.cursor()
# Prepare SQL query to INSERT a record into the database.
sql = """INSERT INTO temperature(sensor,
reading,
time)
VALUES ('"""+sensor+"""',"""+str(temp)+""",'"""+str(now)+"""')"""
try:
cursor.execute(sql)
db.commit()
except:
db.rollback()
# prepare a cursor for storing just the current temperature reading
cursor3 = db.cursor()
# Prepare SQL query to UPDATE a record into the database.
sql_update = """UPDATE curr_temperature set reading = '"""+str(temp)+"""',
time = '"""+str(now)+"""'
where sensor='"""+sensor+"""'"""
print sql_update
try:
cursor3.execute(sql_update)
db.commit()
except:
db.rollback()
# prepare a cursor object using cursor() method
cursor2 = db.cursor()
# Prepare SQL query to INSERT a record into the database.
sql = """UPDATE sensors set last_reading_time='"""+str(now)+"""' where ident='"""+sensor+"""'"""
try:
cursor2.execute(sql)
db.commit()
except:
print "update failed " + sql
db.rollback()
cursor2.close()
# disconnect from server
db.close()#
############################################################################################
def writeBattery(sensor, voltage):
now = datetime.datetime.now()
# Open database connection
db = MySQLdb.connect("localhost","user","******","heating" )
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to INSERT a record into the database.
sql = """INSERT INTO battery(sensor,
time,
voltage)
VALUES ('"""+sensor+"""',"""+str(now)+""",'"""+str(voltage)+"""')"""
try:
cursor.execute(sql)
db.commit()
except:
db.rollback()
# disconnect from server
db.close()#
############################################################################################
#
print "Opening connection and waiting for response..."
#
ser = serial.Serial(DEVICE, BAUD)
print "Startup complete"
print " "
# read the time
now = datetime.datetime.now()
msg = 'monitor initialised : ' + now.strftime("%H:%M %m-%d-%Y")
print msg
#
# Start infinite while loop to listen to XRF module
while 1 :
# All XRF module read and write commands should have 12 characters and begin with the letter "a"
# Wait for message, the 1 second pause seems to improve the reading when several messages
# are arriving in sequence, such as: a--TMP22.12-a--AWAKE----a--BATT2.74-a--SLEEPINGtime.
time.sleep(1)
#if ser.inWaiting()>0:
# print ser.inWaiting()
# llapMsg = ser.read(ser.inWaiting())
# print llapMsg
if ser.inWaiting() >= 12 :
llapMsg = ser.read(12)
# display packet, helps to troubleshoot any errors
now = datetime.datetime.now()
now.strftime("%H:%M %m-%d-%Y")
print 'Received '+ llapMsg + ' at ' + str(now)
if 'a' == llapMsg[0] :
#llap msg detected
#print "(" + llapMsg[1:3] +")"
sensorName=llapMsg[1:3]
#
#
# Check for TMP reading or battery packet, ignore anything else.
#
# Is it a battery reading?
if 'BATT' in llapMsg :
# Battery reading sent
print "Battery level is " + llapMsg[7:10] + "V"
# Save this value for later.
battlevel = llapMsg[7:10]
writeBattery(sensorName, battlevel)
#
# Is it an temp reading?
if 'TMP' in llapMsg :
# reading sent
#
temp = llapMsg[7:12]
print "temp=" + temp
writeTemp(sensorName, temp)
# temp is a 1 element array, hence the "[" "]"
#except ValueError:
# if float operation fails, skip bad reading
# print "bad reading"
#
ser.flushInput()
#
# for want of a better phrase,- endwhile
# end of program
Please note - I haven't got the battery level working yet. Not sure why!
