filter_sensors.py

   1 #!/usr/bin/python
   2
   3 import MySQLdb
   4 import ConfigParser
   5
   6 from pprint import pprint
   7 import datetime
   8
   9 import numpy as np
  10
  11 import scipy.signal
  12
  13 global database
  14
  15 def GetTables(name):
  16   if database:
  17     c = database.cursor()
  18     c.execute("SELECT * FROM Items WHERE ItemName like %s",[name])
  19     return c.fetchall()
  20   else:
  21     print "No connection to DB"
  22     exit()
  23
  24 def Today():
  25   dt = datetime.datetime.now()
  26   d_truncated = datetime.date(dt.year, dt.month, dt.day)
  27   return d_truncated
  28
  29 def Tomorrow():
  30   dt = Today()
  31   return dt + datetime.timedelta(days=1)
  32
  33 def Yesterday():
  34   dt = Today()
  35   return dt - datetime.timedelta(days=1)
  36
  37 def GetData(id,fromDate=Yesterday(),toDate=Today()):
  38   if database:
  39     c = database.cursor()
  40     c.execute("SELECT * FROM Item"+str(id).strip()+" WHERE Time>=%s AND Time<%s",[fromDate.strftime('%Y-%m-%d %H:%M:%S'),toDate.strftime('%Y-%m-%d %H:%M:%S')])
  41     return c.fetchall()
  42   else:
  43     print "No connection to DB"
  44     exit()
  45
  46 def FixRecord(id,date,value):
  47   if database:
  48     c = database.cursor()
  49     command="UPDATE Item"+str(id).strip()+" SET Value={} WHERE Time='{}'".format(value,date.strftime('%Y-%m-%d %H:%M:%S'))
  50     print command
  51     c.execute(command)
  52   else:
  53     print "No connection to DB"
  54     exit()
  55
  56 try:
  57
  58   cfg = ConfigParser.RawConfigParser(allow_no_value=True)
  59   cfg.readfp(open('/etc/openhab-db.conf'))
  60   dbhost = cfg.get("mysql","host")
  61   dbuser = cfg.get("mysql","user")
  62   dbpasswd = cfg.get("mysql","passwd")
  63   dbdb = cfg.get("mysql","db")
  64
  65   itemTemplate = cfg.get("openhab","template")
  66
  67   filterWindow = int(cfg.get("filter","window"))
  68   filterThreshold = float(cfg.get("filter","threshold"))
  69
  70 except:
  71
  72   print "Error reading configuration file"
  73   exit()
  74
  75 try:
  76
  77   database = MySQLdb.connect(host=dbhost,user=dbuser,passwd=dbpasswd,db=dbdb,use_unicode=True)
  78   database.set_character_set('utf8')
  79   c = database.cursor()
  80   c.execute('SET NAMES utf8;')
  81
  82   print "Connected..."
  83
  84 except:
  85
  86   print "Error connecting database"
  87   exit()
  88
  89 tables = GetTables(itemTemplate)
  90
  91 for id,name in tables:
  92
  93   print "Processing: "+name
  94
  95   data=GetData(id)
  96   sTime=[]
  97   sValue=[]
  98   for rec in data:
  99     sTime.append(rec[0])
 100     sValue.append(rec[1])
 101   sValue=np.array(sValue)
 102
 103   sValueFilt=scipy.signal.medfilt(sValue,5)
 104
 105   sValueDiff=abs(sValue-sValueFilt)
 106
 107   avg=np.mean(sValueDiff)
 108
 109   for i in range(0,len(sTime)-1):
 110     if sValueDiff[i]>avg*filterThreshold:
 111       print "fixing %s : %5.2f %5.2f %5.2f" % (sTime[i],sValue[i],sValueFilt[i],sValueDiff[i])
 112       FixRecord(id,sTime[i],sValueFilt[i])
 113
 114   database.commit()
 115
 116 print "Processed "
 117