Skip to main content ramblings of a lost one

Learning SQL and Some Poor Python

Since converting to storing the data in sqlite form i’ve been learning how to extract specific sections of data so i can then learn how to graph it and ultimately serve it 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

python code snippet start

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)

python code snippet end

  • events when a temperature exceeds x

python code snippet start

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)

python code snippet end

  • the last hour of todays data

python code snippet start

# 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)

python code snippet end

so with that set of queries i am ready to graph!

decode failures

Examples of what is getting through the decode routine;

verse code snippet start

(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)

verse code snippet end

or

verse code snippet start

(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)

verse code snippet end

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 for me 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;

python code snippet start

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)

python code snippet end

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 in order to filter out non-numeric data before storing in the database I wrote a simple check routine to ensure the data was numeric;

python code snippet start


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)

python code snippet end

This then gets rolled out into the main program and I should now never see non-numeric data again.