#!pip install ezhc
#!pip install pymysql
#!pip install ipywidgets=7.0.0
%reload_ext autoreload
import warnings
#warnings.filterwarnings('ignore')
from IPython.display import HTML
def hide_code():
return HTML('''<script>data=
code_show=true;
function code_toggle() {
if (code_show){
$("div.input").hide();
} else {
$("div.input").show();
}
code_show = !code_show
}
$( document ).ready(code_toggle);
</script>
The raw code for this Jupyter Python notebook is by default hidden for easier reading.
To toggle on/off the raw code, click <a href="javascript:code_toggle()">here</a>.
Please wait a few seconds to proceed to the application...
''')
from IPython.display import clear_output
clear_output()
hide_code()
import ipywidgets
print('iPyWidgets version: '+ipywidgets.__version__)
from ipywidgets import widgets
from ipywidgets import Layout, Button, Checkbox, Label, HBox, VBox
from ipywidgets import interact, interactive, fixed, interact_manual
from IPython.display import display_pretty, Javascript
from IPython.display import display, HTML
import pandas as pd
import ezhc as hc
from getpass import getpass
import pymysql as pysql
import sys
def format_ts( mydate, mytime):
mytime = int(mytime)
if mytime>1:
return "'%s %02d:59:59'" % (mydate, mytime-1)
else:
return "'%s %s:00:00'" % (mydate, mytime)
class flaoStatsDict(dict):
'''Dictionary that returns the key in case a value is missing'''
def __missing__(self, key):
return key
class DatabaseObj():
def __init__(self, host, name, user, password):
self.host = host
self.name = name
self.user = user
self.password = password
def connect(self):
print('Connecting to database...')
self.db_connection = pysql.connect(host=self.host, database=self.name, user=self.user, password=self.password)
self.cursor = self.db_connection.cursor()
# Table names are in this other database
hk_connection = pysql.connect(host=self.host, database='WFS_HOUSEKEEPING', user=self.user, password=self.password)
hk_cursor = hk_connection.cursor() # get the cursor
self._table_names = flaoStatsDict()
self._table_names_inv = flaoStatsDict()
hk_cursor.execute('SELECT * from column_names')
response = hk_cursor.fetchall()
for r in response:
self._table_names[r[1]] = r[2]
self._table_names_inv[r[2]] = r[1]
def table_names(self):
return self._table_names
def table_names_inv(self):
return self._table_names_inv
def executeQuery(self, query_string):
self.cursor.execute(query_string)
response = self.cursor.fetchall()
return response
def executeQueryPandas(self, query_string, parse_dates_=True):
df = pd.read_sql(query_string, parse_dates=parse_dates_, con=self.db_connection)
return df
def count_table_rows(self, table, start_date, end_date, start_time, end_time ):
start_date_s = format_ts( start_date, start_time)
end_date_s = format_ts( end_date, end_time)
condition = 'timestamp BETWEEN %s AND %s' % (start_date_s, end_date_s)
df = self.executeQueryPandas( 'SELECT COUNT(*) FROM %s where %s' % (table, condition) )
return df['COUNT(*)'][0]
def get_min_max_table_dates(self, table_name):
df = self.executeQueryPandas( 'SELECT DATE(MIN(timestamp)) AS date1, DATE(MAX(timestamp)) AS date2 FROM %s' % (table_name) )
return (df['date1'][0], df['date2'][0])
def loadTablesNamesAndStructure(self):
self.tables = {}
response = self.executeQuery("SHOW FULL TABLES")
columns = []
for row in response:
table_name = row[0]
self.tables[table_name] = []
ss = row[0]
ss += '[ '
response1 = self.executeQuery("SHOW columns FROM %s" % row[0])
for column in response1:
column_name = column[0]
self.tables[table_name].append(column_name)
ss += column_name + ", "
ss += "]"
self.tables_list = list(self.tables.keys())
def saveDFList(self, df_list):
i=0
for df in df_list:
df.to_csv('data_frame' + str(i) + '.csv')
i += 1
class MyLocalGui():
def __init__(self, controller, table_names, table_names_inv):
self.clear_button = None
self.datepicker_e = None
self.datepicker_s = None
self.dates_cont = None
# print(dir(hc))
self.g = hc.Highcharts()
# print(dir(self.g))
self.global_cont = None
self.db_cont = None
self.items_layout = None
self.one_label_box = None
self.one_plot = None
self.do_plot_button = None
self.time_s = None
self.time_e = None
self.controller = controller
self.table_names = table_names
self.table_names_inv = table_names_inv
self.db_list = widgets.Select( options=['WFS_SX', 'WFS_DX'])
self.db_list.on_trait_change(self.on_changed_db, name="value")
self.db_selection_box = HBox([self.db_list], layout=Layout(width='100%', height='100%'))
# display(self.db_selection_box)
self.global_cont = VBox( [], layout=Layout(display="flex-grow", height='auto') )
def on_clear_button_clicked(self, b):
aa = self.db_cont
self.controller.selected_tables = []
self.columns_from_select_tables = {}
self.controller.columns_list = []
self.setup_db_gui(self.controller.myDB.tables_list)
aa.close()
def tables_inteaction(self, selected):
self.controller.selected_tables = []
self.columns_from_select_tables = {}
self.controller.columns_list = []
self.datepicker_s.value = pd.to_datetime(self.controller.end_date)
self.datepicker_e.value = pd.to_datetime(self.controller.start_date)
for c in selected:
table_name = self.table_names_inv[c]
self.controller.selected_tables.append(table_name)
if ('timestamp' in set(self.controller.myDB.tables[table_name])):
d_min, d_max = self.controller.get_min_max_table_dates(table_name)
if (d_min!=None and d_max!=None):
d_min = pd.to_datetime(d_min)
d_max = pd.to_datetime(d_max)
if (self.datepicker_s.value > d_min):
self.datepicker_s.value = d_min
if (self.datepicker_e.value < d_max):
self.datepicker_e.value = d_max
else:
continue
this_table_columns = list(set(self.controller.myDB.tables[table_name]) - set(['id', 'timestamp']))
self.columns_from_select_tables[table_name] = this_table_columns
self.controller.columns_list = self.controller.columns_list + this_table_columns
columns_display = sorted(list(map( lambda x: self.table_names[x], self.controller.columns_list )))
self.cb_columns = widgets.SelectMultiple( options=list(columns_display),
description='Selected Columns',
disabled=False,
rows = len(columns_display),
layout=Layout(display="flex", flex_flow='column', height='400px') )
self.do_plot_button.layout.visibility = 'visible'
# self.clear_button.layout.visibility = 'visible'
self.date_box.layout.visibility = 'visible'
self.cb_tables_and_columns_box = HBox([self.cb_tables, self.cb_columns], layout=Layout(width='100%', height='100%'))
self.db_cont.children = [ HBox([self.cb_tables_and_columns_box, self.buttons_box], layout=Layout(width='100%', height='100%') ), self.one_label_box]
def columns_inteaction(self, selected):
pass
def on_changed_db(self):
self.on_clear_button_clicked(True)
self.controller.connectToDB(self.db_list.value)
self.setup_gui(self.controller.myDB.tables_list)
# self.display_gui()
def on_plot_button_clicked(self, b):
self.controller.selected_columns = {}
for tt in self.controller.selected_tables:
self.controller.selected_columns[tt] = []
for col in self.cb_columns.value:
dbCol = self.table_names_inv[col]
if dbCol in self.columns_from_select_tables[tt]:
self.controller.selected_columns[tt].append(dbCol)
selected_table_list= []
df_list = []
start_date = str(self.datepicker_s.value)[0:10]
start_time = self.time_s.value
end_date = str(self.datepicker_e.value)[0:10]
end_time = self.time_e.value
for t in self.controller.selected_columns.keys():
if len(self.controller.selected_columns[t])>0:
t = self.table_names_inv[t]
selected_table_list.append(t)
if self.singleDay.value:
end_date = start_date
start_time=0
end_time=24
start_date_s = format_ts( start_date, start_time)
end_date_s = format_ts( end_date, end_time)
n_rows = self.controller.myDB.count_table_rows(t, start_date, end_date, start_time, end_time)
ratio = max(n_rows/self.controller.max_records_per_query, 1)
date_condition = '( timestamp BETWEEN %s AND %s )' % (start_date_s, end_date_s)
condition = 'id mod %s = 0 AND %s' % (str(int(ratio)), date_condition)
query_string = 'SELECT %s FROM %s where %s LIMIT %s' % ("*", t, condition, str(self.controller.max_records_per_query))
df = self.controller.myDB.executeQueryPandas(query_string)
df = df[list(set(self.controller.selected_columns[t] + ['timestamp'] ) - set(['id']) ) ]
if len(df)>0:
rename_columns = {colName: self.table_names[colName] for colName in map(str, list(df))}
df = df.rename(index=str, columns=rename_columns).set_index('timestamp')
df_list.append(df)
if (len(df_list)>0):
self.my_display_chart(df_list)
if self.controller.saveToAscii:
self.controller.myDB.saveDFList(df_list)
def my_display_chart(self, df_list):
self.g.chart.height = 550
self.g.series = []
self.g.legend.enabled = True
self.g.legend.layout = 'horizontal'
self.g.legend.align = 'center'
self.g.legend.maxHeight = 100
self.g.tooltip.enabled = True
self.g.tooltip.valueDecimals = 2
self.g.exporting.enabled = True
self.g.chart.zoomType = 'xy'
self.g.title.text = 'LBT Telemetry Data'
self.g.subtitle.text = 'FLAO Subsystem'
#g.plotOptions.series.compare = 'value'
#g.yAxis.labels.formatter = hc.scripts.FORMATTER_PERCENT
#g.tooltip.pointFormat = hc.scripts.TOOLTIP_POINT_FORMAT_PERCENT
self.g.tooltip.positioner = hc.scripts.TOOLTIP_POSITIONER_CENTER_TOP
self.g.xAxis.showEmpty = True
self.g.xAxis.type = 'datetime'
self.g.xAxis.lineWidth = 0.0
self.g.xAxis.gridLineWidth = 1.0
self.g.xAxis.gridLineDashStyle = 'Dot'
self.g.credits.enabled = True
self.g.credits.text = ''#'''Source: XXX Flow Strategy & Solutions.'
self.g.credits.href = ''#'http://www.example.com'
#self.g.yAxis.alignTicks = False
#self.g.yAxis.showEmpty = True
#self.g.yAxis.lineWidth = 0.0
#self.g.yAxis.gridLineWidth = 1.0
#self.g.yAxis.gridLineDashStyle = 'Dot'
DEFAULT_COLORS = ["#7cb5ec", "#434348", "#90ed7d", "#f7a35c", "#8085e9", "#f15c80", "#e4d354", "#2b908f", "#f45b5b", "#91e8e1"]
n_series = len(df_list)
self.g.yAxis = []
for i in range(n_series):
self.g.yAxis.append( {'lineWidth': 0,
'alignTicks' : False,
'title': { 'text': "", #str(i+1) +" Axis",
'style': {
'color': DEFAULT_COLORS[i]
}
},
'labels': {
'format': '{value}',
'style': {
'color': DEFAULT_COLORS[i]
}
},
'opposite': i%2==1,
} )
for df in df_list:
self.g.series.append(hc.build.series(df)[0]) # , secondary_y=[0,1,2]
for i in range(n_series):
self.g.series[i]['yAxis'] = i
#self.one_plot = self.g.plot(save=True, save_name='NoTable')
self.one_plot = self.g.plot(save=False)
display(self.one_plot)
def setup_gui(self, tables_list):
self.setup_db_gui(tables_list)
self.global_cont.children = [ self.db_selection_box , self.db_cont]
def setup_db_gui(self, tables_list):
self.items_layout = Layout(flex='1 1 auto', width='auto')
HTML('<style> .widget-hbox .widget-label { max-width:1550ex; text-align:left} </style>')
tables_display = list(map( lambda x: self.table_names[x], tables_list ))
self.cb_tables = widgets.SelectMultiple( options=tables_display,
description='Database Tables',
disabled=False,
rows = len(tables_display),
layout=Layout(display="flex", flex_flow='column', min_width='320px', max_width='320px', height='400px') )
columns_display = list(map( lambda x: self.table_names[x], self.controller.columns_list ))
self.cb_columns = widgets.SelectMultiple( options=columns_display,
description='Selected Columns',
disabled=False,
rows = len(columns_display),
layout=Layout(display="flex", flex_flow='column', min_width='320px', max_width='320px', height='400px') )
self.cb_tables_and_columns_box = HBox( [ interactive(self.tables_inteaction, selected=self.cb_tables),
interactive(self.columns_inteaction, selected=self.cb_columns) ],
layout=Layout(width='100%', height='100%') )
self.datepicker_s = widgets.DatePicker(description="Start Date")
self.singleDay = Checkbox(value =False, description='Single day')
self.datepicker_e = widgets.DatePicker(description="End Date")
self.time_s = widgets.IntSlider( value=0, min=0, max=24, step=1, description='Start Hour:', disabled=False, continuous_update=False, orientation='horizontal', readout=True, readout_format='d')
self.time_e = widgets.IntSlider( value=24, min=0, max=24, step=1, description='End Hour:', disabled=False, continuous_update=False, orientation='horizontal', readout=True, readout_format='d')
self.one_label = Label('Select Columns and Dates, then press PLOT',
layout=Layout(width='100%', height='100%'))
self.one_label_box = HBox([self.one_label], layout=Layout(width='100%', height='100%'))
self.date_box = VBox([HBox([self.datepicker_s, self.singleDay]), self.time_s, self.datepicker_e, self.time_e],
layout=Layout(width='80%', height='100%') )
self.do_plot_button = Button(description='PLOT', layout=Layout(width='50%', height='10%'))
self.do_plot_button.on_click(self.on_plot_button_clicked)
self.clear_button = Button(description='Clear All', layout=Layout(width='50%', height='10%'))
self.clear_button.on_click(self.on_clear_button_clicked)
self.do_plot_button.layout.visibility = 'hidden'
self.clear_button.layout.visibility = 'hidden'
self.date_box.layout.visibility = 'hidden'
self.buttons_box = VBox([self.do_plot_button, self.clear_button, self.date_box],
layout=Layout(width='50%', height='100%') )
self.db_cont = VBox( [HBox([self.cb_tables_and_columns_box, self.buttons_box],
layout=Layout(width='100%', height='100%') )] )
def display_gui(self):
display(self.global_cont)
class GlobalObj():
def __init__(self):
# some global variables
self.saveToAscii = False
self.max_records_per_query = 100000
# DB initizialization
self.connectToDB('WFS_DX')
self.initGui()
def connectToDB(self, db_name):
# init status variables
self.clear_status()
input_passwd = ''
#getpass(prompt='Enter your password to %s database: ' % database_name)
#password_widget = ipywidgets.Password(description='Password:', placeholder='Make it long!')
#password_widget
#input_passwd = password_widget.value
self.myDB = DatabaseObj('193.206.154.122', db_name, 'flaoadmin', input_passwd)
self.myDB.connect()
self.myDB.loadTablesNamesAndStructure()
clear_output()
def initGui(self):
# GUI inizialization
self.myGUI = MyLocalGui(self, self.myDB.table_names(), self.myDB.table_names_inv())
self.myGUI.setup_gui(self.myDB.tables_list)
self.myGUI.display_gui()
def clear_status(self):
self.start_date = '2000-01-01'
self.end_date = '2030-01-01'
self.columns_from_select_tables = {}
self.selected_columns = {}
self.columns_list = ['None']
self.selected_tables = []
def get_min_max_table_dates(self, table_name):
return self.myDB.get_min_max_table_dates(table_name)
# hide_code()