Panda & SQL pain

I've left the home monitoring system alone for a few years, its just sat quietly in the garage collating data into an SQL database, the only changes made recently were to move everything onto a Pi4 and add an additional camera into the motioneye system running on the same Pi. So as its a slow day i decided now was the time to update it or rather start work on it. One thing i wanted to do was re-instigate the live readings and an auto graph element and for this i needed to extract data - simple i got my old code somewhere …. somewhere … maybe.

After a bit of searching and playing around i decided to re-write;

Though I spent far longer testing this than i should have. The code when run will sometimes report correctly a full dataframe and yet next time with exactly the same code (no changes) it reports an empty one. Run it > empty > run it > empty > run it > data

Not sure why - yet.

Anyway ignoring why it sometimes works and then others dont for now the code is very simple and it outputs an interactive visualisation like;

You can find the interactive version here:

import sqlite3
import pandas as pd
import pandas_bokeh
from sqlalchemy import create_engine 
import datetime as dt
file = "home_mon_db.sqlite"
pandas_bokeh.output_file('data_out.html')

#define time window
timenow = dt.datetime.now()
# testing values
now_date = '2021-02-11'
#prev_time = '06:00:00'
#now_time = '08:00:00'
# programatic values
#now_date = timenow.strftime('%Y-%m-%d')
prev_time = str((dt.datetime.now() - dt.timedelta(hours=8,minutes=0)).strftime('%H:%M:%S')) # defines a window to get data from
now_time = str(timenow.strftime('%H:%M:%S'))

print('DATE:',now_date,'PREVTIME:',prev_time,'NOWTIME',now_time)

try:
        cnx = sqlite3.connect('home_mon_db.sqlite')
        df = pd.read_sql("SELECT * FROM home_mon WHERE DATE = ? AND time BETWEEN ? AND ?", cnx, params={now_date, prev_time, now_time})
except ValueError as e:
        print('failed', e)

if df.empty == True:
        print('DataFrame is empty!')
else:
        print(df)
        sel =df[['TIME','EXTERNAL']]
        # sel.plot_bokeh(rangetool=True)  # this i like
        sel.plot_bokeh(
                kind='line',
                x='TIME',
                xlabel='TIME',
                ylabel='Degrees C',
                title='Sensor inside garage'
        )

print('finished')
cnx.close()