The MySQLdb package is used for MySQL access. Create a MySQL user and database named "weather_logger". My current tables are listed as python comments below.
The script listens for the sigterm signal from systemctl and has some error handlers for network failures. It will write information from these events into the "error_logger" table.
The approach is based on this older post. It assumes one tellsticknet unit in the network. If there are more, replace the tellsticknet discovery string '<broadcast>' with the ip address of the tellsticknet unit. It does not care about the rolling code (id) of the sensors as well, even if it puts this information into the MySQL database.
The optimal approach for this script could be to remove all sensor knowledge and just put every string collected into the "unknown_logger" table for others to process.
This script is run at startup of the system by a systemd file, "/lib/systemd/system/tellsticknet.service".
[Unit] Description=Read oregon sensorvalues from tellsticknet and save in mysql db After=multi-user.target [Service] Type=idle ExecStart=/usr/bin/python /home/<myuser>/tellsticknet_service/tellsticknet.py [Install] WantedBy=multi-user.target
(html by hilite.me)
#! /usr/bin/env python2 # -*- coding: utf-8 -*- import socket from datetime import timedelta, datetime import time, sys, os import MySQLdb import signal from sensors import * sigterm_exit = False def exit_check(_signo, _stack_frame): global sigterm_exit sigterm_exit = True # CREATE TABLE `weather_logger`.`temperature_logger` ( `ID` INT NOT NULL AUTO_INCREMENT, # `sensorID` INT UNSIGNED NULL DEFAULT NULL , `datetime` DATETIME NULL DEFAULT NULL , # `temperature` FLOAT NULL DEFAULT NULL , `humidity` FLOAT NULL DEFAULT NULL, # `battery` TINYINT NULL DEFAULT NULL, PRIMARY KEY (`ID`)) ENGINE = InnoDB; # CREATE TABLE `weather_logger`.`wind_logger` ( `ID` INT NOT NULL AUTO_INCREMENT, # `sensorID` INT UNSIGNED NULL DEFAULT NULL , `datetime` DATETIME NULL DEFAULT NULL , # `wind` FLOAT NULL DEFAULT NULL , `gust` FLOAT NULL DEFAULT NULL, `direction` FLOAT NULL DEFAULT NULL, # `battery` TINYINT NULL DEFAULT NULL, PRIMARY KEY (`ID`)) ENGINE = InnoDB; # CREATE TABLE `weather_logger`.`unknown_logger` ( `ID` INT NOT NULL AUTO_INCREMENT, # `datetime` DATETIME NULL DEFAULT NULL , `data` VARCHAR(2048) NULL DEFAULT NULL, # PRIMARY KEY (`ID`)) ENGINE = InnoDB; # CREATE TABLE `weather_logger`.`error_logger` ( `ID` INT NOT NULL AUTO_INCREMENT, # `datetime` DATETIME NULL DEFAULT NULL , `data` VARCHAR(2048) NULL DEFAULT NULL, # PRIMARY KEY (`ID`)) ENGINE = InnoDB; # # CREATE TABLE `weather_logger`.`raw` ( `ID` INT NOT NULL AUTO_INCREMENT, # `datetime` DATETIME NULL DEFAULT NULL , `raw_data` VARCHAR(1024) NULL DEFAULT NULL, # PRIMARY KEY (`ID`)) ENGINE = InnoDB; signal.signal(signal.SIGINT, exit_check) signal.signal(signal.SIGTERM, exit_check) MYSQLHOST = '10.0.0.7' SOCKET_TIMEOUT = timedelta(seconds=5) REG_INT = timedelta(minutes = 10) db = MySQLdb.connect(host=MYSQLHOST, user="weather_logger", passwd="weather_logger", db="weather_logger") cur = db.cursor() date_t = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) sql = 'insert into error_logger values(NULL,\"%s\", \"%s\")'%(date_t, "Starting tellsticknet logging") cur.execute(sql) db.commit() sock = socket.socket(socket.AF_INET, socket.SOCK_DGRAM) sock.setsockopt(socket.SOL_SOCKET, socket.SO_BROADCAST, 1) sock.setsockopt(socket.SOL_SOCKET, socket.SO_REUSEADDR, 1) sock.settimeout(SOCKET_TIMEOUT.seconds) sock.bind(('',42314)) comm_ok = False while not comm_ok: if sigterm_exit: db.close() sock.close() sys.exit(0) try: sock.sendto(b'D', ('<broadcast>', 30303)) data, (tellsticknetip, port) = sock.recvfrom(2048) comm_ok = True except socket.error: # power out? try again: date_t = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) sql = 'insert into error_logger values(NULL,\"%s\", \"%s\")'%(date_t, "No connection with TellstickNet from init") cur.execute(sql) db.commit() last_reg_listener = datetime.now() - REG_INT while not sigterm_exit: if datetime.now() - last_reg_listener > REG_INT: comm_ok = False while not comm_ok: if sigterm_exit: db.close() sock.close() sys.exit(0) try: sock.sendto("B:reglistener", (tellsticknetip, 42314)) comm_ok = True except: date_t = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) sql = 'insert into error_logger values(NULL,\"%s\", \"%s\")'%(date_t, "No connection with TellstickNet from reglistener") cur.execute(sql) db.commit() last_reg_listener = datetime.now() try: data,(address, port) = sock.recvfrom(2048) if (data.split(":")[6][6:10]=="F824"): (rc, temp,hum, batt) = decodeF824(data.split(":")[7][5:5+14]) date_t = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) sql = 'insert into temperature_logger values(NULL,%d,\"%s\", %f,%f, %d)'%(rc, date_t, temp, hum, int(batt)) #print(sql) cur.execute(sql) db.commit() elif (data.split(":")[6][6:10]=="1984"): (rc, w, wg, direction, batt) = decode1984(data.split(":")[7][5:5+16]) date_t = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) sql = 'insert into wind_logger values(NULL,%d,\"%s\", %f,%f,%f, %d)'%(rc, date_t, w, wg, direction, int(batt)) cur.execute(sql) db.commit() else: date_t = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) sql = 'insert into unknown_logger values(NULL,\"%s\", \"%s\")'%(date_t, data) cur.execute(sql) db.commit() except socket.timeout: # time out, try again pass except socket.error: #power fail? date_t = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) if sigterm_exit: sql = 'insert into error_logger values(NULL,\"%s\", \"%s\")'%(date_t, "sigterm recived") cur.execute(sql) db.commit() break else: sql = 'insert into error_logger values(NULL,\"%s\", \"%s\")'%(date_t, "No connection with TellstickNet from main loop") cur.execute(sql) db.commit() except: date_t = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) sql = 'insert into error_logger values(NULL,\"%s\", \"%s\")'%(date_t, sys.exc_info()[0]) cur.execute(sql) db.commit() #got SIGTERM db.close() sock.close()
The "sensors.py" file used:
#some sensors from telldus-core/service/ # this code depends on signal checking done in the tellsticknet # # def decode2914(inp): #source:telldus-core/service/ProtocolOregon.cpp@c9567f #// rain value = int(inp, 16) messageChecksum1 = value & 0xF value = value >> 4 messageChecksum2 = value & 0xF value = value >> 4 totRain1 = value & 0xF value = value >> 4 totRain2 = value & 0xF value = value >> 4 totRain3 = value & 0xF value = value >> 4 totRain4 = value & 0xF value = value >> 4 totRain5 = value & 0xF value = value >> 4 totRain6 = value & 0xF value = value >> 4 rainRate1 = value & 0xF value = value >> 4 rainRate2 = value & 0xF value = value >> 4 rainRate3 = value & 0xF value = value >> 4 rainRate4 = value & 0xF value = value >> 4 battery = value & 0xF #// PROBABLY battery value = value >> 4 rollingcode = ((value >> 4) & 0xF) + (value & 0xF) checksum = ((value >> 4) & 0xF) + (value & 0xF) value = value >> 8 channel = value & 0xF checksum = checksum + totRain1 + totRain2 + totRain3 + totRain4 + totRain5 + totRain6 +\ rainRate1 + rainRate2 + rainRate3 + rainRate4 +\ battery + channel + 0x2 + 0x9 + 0x1 + 0x4 if (((checksum >> 4) & 0xF) != messageChecksum1 or (checksum & 0xF) != messageChecksum2): #// checksum error return "" totRain = ((totRain1 * 100000) + (totRain2 * 10000) + (totRain3 * 1000) +\ (totRain4 * 100) + (totRain5 * 10) + totRain6)/1000.0*25.4 rainRate = ((rainRate1 * 1000) + (rainRate2 * 100) + (rainRate3 * 10) + rainRate4)/100.0*25.4 #std::stringstream retString; #retString << "class:sensor;protocol:oregon;model:2914;id:" << static_cast<int>(rollingcode) # << ";raintotal:" << std::fixed << std::setprecision(1) << totRain # << ";rainrate:" << std::fixed << std::setprecision(1) << rainRate << ";"; #return "%d\t%f\t%f"%(rollingcode, totRain, rainRate) return [rollingcode, totRain, rainRate, battery] def decode1994(inp): #source:telldus-core/service/ProtocolOregon.cpp@c9567f #wind value = int(inp, 16) crcCheck = value & 0xF value = value >> 4 messageChecksum1 = value & 0xF value = value >> 4 messageChecksum2 = value & 0xF value = value >> 4 avg1 = value & 0xF value = value >> 4 avg2 = value & 0xF value = value >> 4 avg3 = value & 0xF value = value >> 4 gust1 = value & 0xF value = value >> 4 gust2 = value & 0xF value = value >> 4 gust3 = value & 0xF value = value >> 4 unknown1 = value & 0xF value = value >> 4 unknown2 = value & 0xF value = value >> 4 direction = value & 0xF value = value >> 4 battery = value & 0xF #// PROBABLY battery value = value >> 4 rollingcode = ((value >> 4) & 0xF) + (value & 0xF) checksum = ((value >> 4) & 0xF) + (value & 0xF) value = value >> 8 channel = value & 0xF checksum = checksum + unknown1 + unknown2 + avg1 + avg2 + avg3 + gust1 + gust2 + gust3 + direction + battery + channel checksum = checksum + 0x1 + 0x9 + 0x9 + 0x4 if (((checksum >> 4) & 0xF) != messageChecksum1 or (checksum & 0xF) != messageChecksum2): #// checksum error return "" avg = ((avg1 * 100) + (avg2 * 10) + avg3)/10.0 gust = ((gust1 * 100) + (gust2 * 10) + gust3)/10.0 directiondegree = 22.5 * direction #retString << "class:sensor;protocol:oregon;model:1984;id:" << static_cast<int>(rollingcode) # << ";winddirection:" << directiondegree # << ";windaverage:" << std::fixed << std::setprecision(1) << avg # << ";windgust:" << std::fixed << std::setprecision(1) << gust << ";"; #return '%d\t%f\t%f\t%f'%(rollingcode, avg, gust, directiondegree) return [rollingcode, avg, gust, directiondegree, battery] def decode1984(inp): #source:telldus-core/service/ProtocolOregon.cpp@c9567f #// wind value = int(inp, 16) crcCheck = value & 0xF value = value >> 4 messageChecksum1 = value & 0xF value = value >> 4 messageChecksum2 = value & 0xF value = value >> 4 avg1 = value & 0xF value = value >> 4 avg2 = value & 0xF value = value >> 4 avg3 = value & 0xF value = value >> 4 gust1 = value & 0xF value = value >> 4 gust2 = value & 0xF value = value >> 4 gust3 = value & 0xF value = value >> 4 unknown1 = value & 0xF value = value >> 4 unknown2 = value & 0xF value = value >> 4 direction = value & 0xF value = value >> 4 battery = value & 0xF #// PROBABLY battery value = value >> 4 rollingcode = ((value >> 4) & 0xF) + (value & 0xF) checksum = ((value >> 4) & 0xF) + (value & 0xF) value = value >> 8 channel = value & 0xF checksum = checksum + unknown1 + unknown2 + avg1 + avg2 + avg3 + gust1 + gust2 + gust3 + direction + battery + channel checksum = checksum + 0x1 + 0x9 + 0x8 + 0x4 if (((checksum >> 4) & 0xF) != messageChecksum1 or (checksum & 0xF) != messageChecksum2): #// checksum error return "" avg = ((avg1 * 100) + (avg2 * 10) + avg3)/10.0 gust = ((gust1 * 100) + (gust2 * 10) + gust3)/10.0 directiondegree = 22.5 * direction #retString << "class:sensor;protocol:oregon;model:1984;id:" << static_cast<int>(rollingcode) # << ";winddirection:" << directiondegree # << ";windaverage:" << std::fixed << std::setprecision(1) << avg # << ";windgust:" << std::fixed << std::setprecision(1) << gust << ";"; #return '%d\t%f\t%f\t%f'%(rollingcode,avg, gust, directiondegree) return [rollingcode, avg, gust, directiondegree, battery] def decodeF824(inp): #source:telldus-core/service/ProtocolOregon.cpp@c9567f value = int(inp, 16) crcCheck = value & 0xF value = value>>4 messageChecksum1 = value & 0xF value = value >>4 messageChecksum2 = value & 0xF value = value >> 4 unknown = value & 0xF value = value >> 4 hum1 = value & 0xF value = value >> 4 hum2 = value & 0xF value = value >> 4 neg = value & 0xF value = value >> 4 temp1 = value & 0xF value = value >> 4 temp2 = value & 0xF value = value >> 4 temp3 = value & 0xF value = value >> 4 battery = value & 0xF value = value >> 4 rollingcode = ((value >> 4) & 0xF) + (value & 0xF) checksum = ((value >> 4) & 0xF) + (value & 0xF) value = value >> 8 channel = value & 0xF checksum += unknown + hum1 + hum2 + neg + temp1 + temp2 + temp3 + battery + channel + 0xF + 0x8 + 0x2 + 0x4 if ((((checksum >> 4) & 0xF) != messageChecksum1) or ((checksum & 0xF) != messageChecksum2)): return "" temperature = ((temp1 * 100) + (temp2 * 10) + temp3)/10.0 if (neg): temperature = -temperature humidity = (hum1 * 10.0) + hum2 #retStr = "class:sensor;protocol:oregon;model:F824;id:" #14;temp:0.0;humidity:46; #retStr = '%s%d;temp:%1.1f;humidity:%d;'%(retStr,rollingcode,temperature,humidity) #return retStr #return '%d\t%f\t%f'%(rollingcode, temperature, humidity) return [rollingcode, temperature, humidity, battery] def decode1A2D(inp): #source:telldus-core/service/ProtocolOregon.cpp value = int(inp,16) checksum2 = value & 0xFF value = value>>8 checksum1 = value & 0xFF value = value>>8 checksum = ((value >> 4) & 0xF) + (value & 0xF) hum1 = value & 0xF; value >>= 8; checksum = checksum + ((value >> 4) & 0xF) + (value & 0xF) neg = value & (1 << 3) hum2 = (value >> 4) & 0xF; value >>= 8 checksum = checksum + ((value >> 4) & 0xF) + (value & 0xF); temp2 = value & 0xF; temp1 = (value >> 4) & 0xF; value >>= 8; checksum = checksum + ((value >> 4) & 0xF) + (value & 0xF) temp3 = (value >> 4) & 0xF; value >>= 8 checksum = checksum + ((value >> 4) & 0xF) + (value & 0xF) rollingcode = value & 0xFF; value >>= 8; checksum = checksum + ((value >> 4) & 0xF) + (value & 0xF) channel = (value >> 4) & 0x7; battery = value&0xF #time will tell checksum = checksum + 0x1 + 0xA + 0x2 + 0xD - 0xA; if (checksum != checksum1): return "No"; temperature = ((temp1 * 100) + (temp2 * 10) + temp3)/10.0; if (neg): temperature = -temperature humidity = (hum1 * 10.0) + hum2 #print "temp/hum ", temperature, "/", humidity return [rollingcode, temperature, humidity, battery]
