aut viam inveniam aut faciam

sql learning and poor decode routine

Learning how to extract data from sqlite and decode routine allows wrong capture through to storage


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;

  • specific days

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!

decode failures

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 ping or $----

  • 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


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):
        return True
    except ValueError:
        return False

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")
        print('not number is: ',msg)

Recent posts


See more



aut viam inveniam aut faciam