src/digitemp/python/mysql/home_weather.py
#!/usr/bin/env python
#
# DigiTemp temperature and q-wire home weather station logging
# Copyright 2003 by Brian C. Lane <bcl@brianlane.com>
# http://www.digitemp.com
#
# Requires DigiTemp v3.1.0 or later for counter support
# Requires Python MySQLdb module and Round Robin Database binaries
#
# Logs data to a MySQL database and to a RRDb graph log
#
# Create a MySQL database called weather and give a weather user permission
# to access it.
# GRANT SELECT,INSERT,CREATE ON weather.* TO weather@localhost IDENTIFIED BY 'password';
#
# CREATE TABLE temperature (
# TemperatureKey bigint UNSIGNED NOT NULL auto_increment,
# SerialNumber varchar(20) NOT NULL,
# RecTime timestamp NOT NULL,
# C float NOT NULL,
# PRIMARY KEY(SerialNumber),
# KEY(TemperatureKey)
# );
#
# CREATE TABLE counter (
# CounterKey bigint UNSIGNED NOT NULL auto_increment,
# SerialNumber varchar(20) NOT NULL,
# RecTime timestamp NOT NULL,
# CounterNum tinyint UNSIGNED NOT NULL,
# CounterValue mediumint UNSIGNED NOT NULL,
# PRIMARY KEY(SerialNumber),
# KEY(CounterKey)
# );
#
import string, os, sys, time
import MySQLdb
timefmt = '%Y-%m-%d %H:%M:%S'
# Connect to the database
try:
mydb = MySQLdb.Connect(host='localhost',user='weather',passwd='password',db='weather')
except DatabaseError:
print "Problem connecting to database"
sys.exit(-1)
cursor=mydb.cursor()
# 1. Read the output of DigiTemp
cmd = '/home/brian/temperature/digitemp -c/home/brian/temperature/digitemp.cfg -a -q'
for outline in os.popen(cmd).readlines():
outline = outline[:-1]
# print outline
if outline[0:1] == 'T':
# Parse the temperature sensor line
S = string.split(outline, " ")
# print S
# Add the reading to the database
sql = "INSERT INTO temperature VALUES(NULL, %s, %s, %s)"
sqltime = time.strftime( timefmt, time.localtime(int(S[2])))
cursor.execute( sql, (S[1], sqltime, S[3]) );
if outline[0:1] == 'C':
# Parse the counter line
S = string.split(outline, " ")
# print S
# Add the reading to the database
sql = "INSERT INTO counter VALUES(NULL, %s, %s, %s, %s)"
sqltime = time.strftime( timefmt, time.localtime(int(S[2])))
cursor.execute( sql, (S[1], sqltime, S[3], S[4]) );
# -----------------------------------------------------------------------
# Do interesting things with the just-logged information
# =======================================================================
# Brian's Sensor Map:
#
#10E8A00E00000055 Room (grey wire)
#10B95E05000800AA Attic
#10575A050008008F Desk
#22B9B20500000049 DS1822
#286D1D2D000000EA DS18B20 (kermit)
#1092B9330008002E Drink
#1D9CB900000000B3 Rain Gauge
#1DFA15010000005F Wind Speed
#1009212E0008004B DT-1A passive sensor
#
# Dict of serial numbers and what to name them
sensors = {'10E8A00E00000055':'Office',
'10B95E05000800AA':'Attic',
'10575A050008008F':'Desk',
'22B9B20500000049':'DS1822',
'286D1D2D000000EA':'DS18B20',
'1092B9330008002E':'Drink',
'1009212E0008004B':'DT1A'
}
counters= {'1D9CB900000000B3':'Rain',
'1DFA15010000005F':'Wind'
}
rrdtool_path = "/usr/local/rrdtool/bin/rrdtool"
rrd_path = "/home/brian/temperature/"
def c2f( c ):
f = 32.0 + ((c * 9.0) / 5.0)
return f
rrd_sensors = {}
# grab the latest readings for the sensors
for skey in sensors.keys():
sql = "SELECT UNIX_TIMESTAMP(rectime), C from temperature where serialnumber='"+skey+"' ORDER BY rectime DESC LIMIT 1"
# print sql
cursor.execute(sql)
result = cursor.fetchone()
# print result
rrd_sensors[sensors[skey]] = result
# grab the counters
rrd_counters = {}
# grab the latest readings for the sensors
for ckey in counters.keys():
sql = "SELECT UNIX_TIMESTAMP(rectime), countervalue from counter where serialnumber='"+ckey+"' AND counternum=0 ORDER BY rectime DESC LIMIT 1"
# print sql
cursor.execute(sql)
result = cursor.fetchone()
# print result
rrd_counters[counters[ckey]] = result
# Log the temperatures to the RRD
# rrdtool update drink.rrd time:value
rrd = "%s update %s/drink.rrd %ld:%0.2f" % (rrdtool_path, rrd_path, rrd_sensors['Drink'][0], rrd_sensors['Drink'][1])
os.system(rrd)
#print rrd
# rrdtool update outside.rrd time:value
rrd = "%s update %s/outside.rrd %ld:%0.2f" % (rrdtool_path, rrd_path, rrd_sensors['Attic'][0], rrd_sensors['Attic'][1])
os.system(rrd)
#print rrd
# rrdtool update room.rrd time:value
rrd = "%s update %s/room.rrd %ld:%0.2f" % (rrdtool_path, rrd_path, rrd_sensors['Office'][0], rrd_sensors['Office'][1])
os.system(rrd)
#print rrd
# rrdtool update room.rrd time:value
rrd = "%s update %s/kermit.rrd %ld:%0.2f" % (rrdtool_path, rrd_path, rrd_sensors['DS1822'][0], rrd_sensors['DS1822'][1])
os.system(rrd)
#print rrd
# Log the counter values
# rrdtool update rain.rrd time:value
rrd = "%s update %s/rain.rrd %ld:%ld" % (rrdtool_path, rrd_path, rrd_counters['Rain'][0], rrd_counters['Rain'][1])
os.system(rrd)
#print rrd
# rrdtool update wind.rrd rime:value
rrd = "%s update %s/wind.rrd %ld:%ld" % (rrdtool_path, rrd_path, rrd_counters['Wind'][0], rrd_counters['Wind'][1])
os.system(rrd)
#print rrd
# Write a new .signature file
outfile = open('/home/user/.signature','w')
sig = "--[Inside %0.1fF]--[Outside %0.1fF]--[Kermit %0.1fF]--[Coaster %0.1fF]--\n" % (c2f(rrd_sensors['Office'][1]),c2f(rrd_sensors['Attic'][1]),c2f(rrd_sensors['DS1822'][1]),c2f(rrd_sensors['Drink'][1]))
outfile.write(sig)
outfile.close()
mydb.close()