Skip to main content leehalls.net

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.