Since converting to storing the data in sqlite form i've been learning how to extract specific sections of data ready to then learn how to graph it and serve on the home dashboard and during these experiments i can see my decode routine is not quite as efficient as i originally thought as it lets through non-numeric data so lets look at the sql extraction code and then the decode issue;
sqlite data extraction
so far i've figured out how to extract data for;
print ("\nDatabase entries where the date is today x:\n") for row in curs.execute("SELECT * FROM home_mon WHERE DATE=(?)", ('2018-08-17',)): print (row)
events when a temperature exceeds x
maxTemp = 26 print ("\nDatabase entries where the temperature is above x:\n") for row in curs.execute("SELECT * FROM home_mon WHERE EXTERNAL>(?)", (maxTemp,)): print (row)
the last hour of todays data
# date & time manipulation today = date.today() t_now = datetime.now() # get time 1hr ago from now t_1hr = t_now - timedelta(minutes=60) # format same as database and for easier reading t_now = t_now.strftime('%H:%M:%S') t_1hr = t_1hr.strftime('%H:%M:%S') t_1hr = '06:34:00' #used for testing local copy has data upto 2018.08.17 07:34 print ("\nDatabase entries from the last hour:\n") for row in curs.execute("SELECT * FROM home_mon WHERE DATE=? AND TIME>?", (today, t_1hr,)): print (row)
so with that set of queries i am ready to graph!
Examples of what is getting through the decode routine;
(118, '2018-08-11', '00:44:10', 12.07, 19.14, 'PING-', 20.0) (119, '2018-08-11', '00:44:34', 11.98, 19.14, 'PING-', 20.0) (120, '2018-08-11', '00:59:34', 11.93, 19.14, 'PING-', 20.0) (121, '2018-08-11', '01:06:54', 11.93, 18.96, 'PING-', 20.0) (122, '2018-08-11', '01:07:15', 11.93, 18.96, 'PING-', 19.87) (123, '2018-08-11', '01:10:53', 11.93, 18.96, 'PING-', 19.87)
(1817, '2018-08-17', '05:04:27', 'E----', 20.23, 19.91, 21.0) (1818, '2018-08-17', '05:11:45', 'E----', 20.14, 19.91, 21.0) (1819, '2018-08-17', '05:14:53', 'E----', 20.14, 19.91, 21.0)
so i get two jobs from this and one surprise;
1: capture the incoming msgs which show
2: add some complexity to the decode routine so that if any non-numeric value is ignored.
the surprise is how frequently there is data being broadcast in that the sensors are set for a 30min perhaps 1hr broadcast or at least i thought.
So going back to the decode routine;
def decdict(incoming): # sample: aAETMPA12.45 if incoming.get('msg') != '': # get sensor id from llapmsg id = incoming.get("msg") id = id[1:3] # get value from llapmsg val = incoming.get("msg") val = val[7:12] # assign value to correct sensor in dictionary incoming[id] = val return(incoming)
you can see its simple, merely extracting data from a specific point in the string with no numeric identification hence the random text entering the database. So I wrote a test routine to ensure the data was numeric;
def is_number(s): try: float(s) return True except ValueError: return False #msg='aAETMPA12.45' msg='aAETMPA----' if msg != '': # get sensor id from llapmsg id = msg id = id[1:3] print (id,"\n") # get value from llapmsg val = msg val = val[7:12] if is_number(val)==True: print('your value is: ',val,"\n") else: print('not number is: ',msg)