We use sqlalchemy to access the database. It is going to call the psycopg2 library we installed in the previous part.

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Table, schema
from sqlalchemy.orm import sessionmaker

The same libraries for visualization and data analysis are called as in the previous example. Note DatetimeTickFormatter is necessary to modify the aspect of dates, otherwise they'll be not readable (and this without counting on the default unsorted American -- rather than ISO -- format).

import pandas as pd#data processing
import panel as pn
import holoviews as hv
import param
from bokeh.models.formatters import DatetimeTickFormatter
import hvplot.pandas#this will add a method to generate holoviews plots from pandas DataFrames!

XFORMATTER = DatetimeTickFormatter()#date formatter
XFORMATTER.years = ['%Y-%m-%d']#ISO
XFORMATTER.months = ['%Y-%m-%d']#ISO
XFORMATTER.days = ['%d/%m']#Zooming in, we show only date and month, without the year (French format)

pn.extension()

import os
import datetime as dt

We first set the database authentication variables, then we can create the PostgreSQL engine.

POSTGRES_USER = os.getenv('POSTGRES_USER')
POSTGRES_PASSWORD= os.getenv('POSTGRES_PASSWORD')
POSTGRES_ADDRESS= os.getenv('POSTGRES_ADDRESS')

engine = create_engine('postgresql://{0}:{1}@{2}/sensors'.format(POSTGRES_USER,
                                                                 POSTGRES_PASSWORD,
                                                                 POSTGRES_ADDRESS))

Then we can use an SQLAlchemy trick to load all available tables. With Node-Red, we create all tables within the th schema, and each table has the same features. We can fetch them all as follows:

Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base()
Base.metadata.reflect(engine,schema='th')
print(Base.metadata.tables.keys())

dict_keys(['th.cage_escalier', 'th.salon', 'th.sdb', 'th.entree', 'th.chambre', 'th.paul', 'th.bureau', 'th.cuisine'])

As for the live example, we check first only the office "bureau":

tablename = 'th.bureau'
table = Base.metadata.tables[tablename]

We can filter e.g. between today and three days ago:

dates = (table.c.msg_ts <= dt.date.today()) & (table.c.msg_ts >= dt.date.today()-dt.timedelta(days=3))
query = session.query(table).filter(dates)

We can inject the SQL response directly into the pandas Dataframe instanciator:

df = pd.DataFrame(query.all())

df.set_index('msg_ts').hvplot(y='humidity')

Formatting the dates in the French format:

df.set_index('msg_ts').hvplot(y='humidity',xformatter=XFORMATTER)

Alright, everything works as expected!

We now design a more complex dashboard. It should be able to:

• compare a feature between different rooms
• compare different features for a single room
• let the user pick the room(s) and feature(s).

A param class can be interpreted by Panel. This type of class lets us define each parameter with their types, limits, names, and whatnots. Then Panel will be able to automatically interpret these parameters to generate the appropriate widgets.

We can then make methods that return a type which can be visualized within a Panel Pane, e.g. a Holoviews plot.

Here is the doc: https://panel.holoviz.org/user_guide/Param.html

class Historic_Data_Panel(param.Parameterized):
    """
    Class with two methods returning plots:
    - show_plot to compare rooms
    - show_plot2 to compare features within a room.
    
    The third method, load_dataframes, is meant to fetch data from the SQL database.
    """
    
    #Every parameter is set here, this is a particularity of the param.Parameterized parent class
    
    #global parameters: time limits
    upper_date = param.Date(default=dt.date.today(),bounds=(None,dt.date.today()))
    lower_date = param.Date(default=dt.date.today()-dt.timedelta(days=3),bounds=(None,dt.date.today()))
    
    
    t_list = list(Base.metadata.tables.keys())
    
    #parameter for the first plot: picking one or several rooms and a single feature.
    table_selector = param.ListSelector(default=t_list[:1], objects=t_list)
    y_selec = param.Selector(default='temperature',objects=['temperature','humidity','pressure',
                                                            'linkquality','battery','voltage'])
    #parameter for the second plot: picking a single room and one or several features.
    table_selector2 = param.Selector(default=t_list[0], objects=t_list)
    y_selec2 = param.ListSelector(default=['temperature','humidity','pressure'],
                                 objects=['temperature','humidity','pressure',
                                                            'linkquality','battery','voltage'])
    
    
    
    @param.depends('upper_date','lower_date',watch=True)
    def load_dataframes(self):
        """
        Changing the date limits, this will update the self.dfs dictionnary with corresponding data.
        """
        self.dfs = {}#empty dict
        self.show_stuff.object = "{0} => {1}".format(self.lower_date,self.upper_date)
        for tablename, table in Base.metadata.tables.items():#for each table
            #add a  "where" filter within the SQL request.
            dates = ((table.c.msg_ts <= self.upper_date+dt.timedelta(days=1)) 
                     & (table.c.msg_ts >= self.lower_date))
            query = session.query(table).filter(dates)
            
            #puis on injecte la donnée avec la méthode all de la réponse
            self.dfs[tablename] = pd.DataFrame(query.all())
            
            
            
    @param.depends('table_selector','y_selec','upper_date','lower_date')
    def show_plot(self):
        """
        Upon changing the date limits, the sensors or the feature, 
        it will return an Overlay
        """
        
        
        l=[]
        grid_style = {"grid_line_color":"olive",
                     "minor_grid_line_color":None}
        for tn in self.table_selector:
            p = self.dfs[tn].hvplot.line(x='msg_ts',y=self.y_selec,rot=20,xformatter=XFORMATTER,
                                        label=tn,responsive=True).opts(show_grid=True)
            l.append(p)
            
        
        o = hv.Overlay(l)
        
        return pn.pane.HoloViews(o,sizing_mode="stretch_both",min_width=600,min_height=800)
    
    @param.depends('table_selector2','y_selec2','upper_date','lower_date')
    def show_plot2(self):
        """
        Upon changing the date limits, the sensor or the features, 
        it will return a Layout with one row per feature.
        """
        tn = self.table_selector2
        l=[]
        grid_style = {"grid_line_color":"olive",
                     "minor_grid_line_color":None}
        def_opt = dict(gridstyle=grid_style,
                   show_grid=True)
        for i, y in enumerate(self.y_selec2):
            p = self.dfs[tn].hvplot.line(x='msg_ts',y=y,rot=20,xformatter=XFORMATTER,responsive=True,
                                        min_height=150,min_width=300)
            if i==0:
                p.opts(xaxis='top',**def_opt)
            else:
                p.opts(xaxis=None, **def_opt)
            l.append(p)
        o = hv.Layout(l).cols(1)
        return pn.pane.HoloViews(o,sizing_mode="stretch_both",min_width=600,min_height=800)
    
    def __init__(self,**params):
        super().__init__(**params)
        
        #This object is meant to print any necessary string.
        self.show_stuff = pn.pane.markup.Markdown(object='')
        self.load_dataframes()

We instanciate

mypanel = Historic_Data_Panel()

We can now lay out the various widgets and methods returning viewable objects. We're going to use some of the Layout objects of Panel: rows and columns, but also tabs.

First, let's see how widgets for the parameters are generated and how to lay them out the way we want. We can simply call the param element of our instance mypanel inside a pn.Param layout:

date_selec = pn.Param(#pn.param wil generate widgets based on the fed parameters
    mypanel.param,#parameter source
         widgets={#widget types we can to use for specific parameters. We could also let the default
             'upper_date':pn.widgets.DatePicker,
         'lower_date':pn.widgets.DatePicker
         },
          parameters=[#parameters we want widgets for. If not given, all parameters are given a widget.
              'upper_date',
              'lower_date'],
                         default_layout=pn.Row,#rows rather than column
                         height_policy='fit',#reactive
                         width_policy='fit'#reactive
)
date_selec

We can now put one of the method inside a layout:

pn.Pane(mypanel.show_plot)

Note that even if the widgets are in a separate cell, the plot will update if you change the parameters. It is interactive! There is a single upper_date parameter for my mypanel instance. You can return the widgets as many times you want: it is actually the same object so they all update! You can try:

date_selec

Now we build our entire dashboard, with both plots, each in one tab, and their associated widgets/parameters:

app = pn.Column(
    pn.Param(mypanel.param,widgets={'upper_date':pn.widgets.DatePicker,
                                          'lower_date':pn.widgets.DatePicker},
                          parameters=['upper_date','lower_date'],
                         default_layout=pn.Row,
                         height_policy='fit',
                         width_policy='fit'),
    pn.Tabs(('Compare rooms',
        pn.Row(
            pn.Param(mypanel.param,
                          parameters=['table_selector','y_selec'],
                         height_policy='fit',
                         width_policy='fit'),
            mypanel.show_plot,
            sizing_mode='stretch_both'
        )),
            ('Check one room',
        pn.Row(
            pn.Param(mypanel.param,
                              parameters=['table_selector2','y_selec2'],
                             height_policy='fit',
                             width_policy='fit'),
                mypanel.show_plot2,
        sizing_mode='stretch_both')
        )
    ),
sizing_mode='stretch_both'
)
         

#calling .servable() will let you use this notebook as code source for your application.
app.servable()

Here's our dashboard!

There are certainly many points to improve, in particular fetching data everytime the dates are changed. If the new data set is already inside the previous one, we shouldn't need to do that.