Hey,
I have been trying to put together a dashboard in Bokeh that will eventually help us track the number of records that get inserted into the tables in our Data Warehouse.
When the dashboard first loads it will show the counts of the tables for all of the tables, but then there is a Select object in a widgetbox that you can select the Status of the record.
When I first open it, I can go from the status ‘All’ to the status ‘Good’ and it will filter the pandas dataframe down to only the records with the status ‘Good’ as expected.
However, sometimes when I go back to ‘All’ it will work and I can see all of the records but other times it won’t. The same thing happens if I go from ‘Good’ to ‘Bad’.
The filtering just seems to be in an inconsistent. I pasted the code below, sorry if this seems inconsistent but I was wondering if anyone could point me in the direction of
what I am doing wrong.
import pandas as pd
``
from os.path import dirname, join
from bokeh.io import curdoc
from bokeh.layouts import row, widgetbox
from bokeh.models import ColumnDataSource, CustomJS
from bokeh.models.widgets import Slider, Button, DataTable, TableColumn, NumberFormatter
from bokeh.models.widgets import Select
this is just a custom module I put together to create pypyodbc connections
basically creates a connection object and returns a cursor
from Connection import Connection
source = ColumnDataSource(data=dict())
conn = Connection()
pw_src_cur = conn.pw_src.return_cursor()
def query_db():
global results
sql = """
SELECT 'FactPlacementPerformance' TableName, COUNT(1) Count, 'Good' Status
FROM dw.FactPlacementPerformance
UNION ALL
SELECT 'DimAdPlacements' TableName, COUNT(1) COUNT, 'Bad' Status
FROM dw.DimAdPlacements
"""
results = pd.DataFrame(pw_src_cur.execute(sql).fetchall())
results.columns = ['Table', 'Count', 'Status']
def create_figure(value):
query_db()
current = results.copy()
if value != 'All':
current = current[current['Status'] == value]
source.data = {
'Table': current.Table,
'Count': current.Count,
'Status': current.Status,
}
data_table = DataTable(source=source, columns=columns, width=800)
return data_table
def update(attr, old, new):
layout.children[1] = create_figure(new)
menu = [‘All’, ‘Good’, ‘Bad’]
select = Select(title=“Status”, value=‘All’, options=menu)
select.on_change(‘value’, update)
columns = [
TableColumn(field="Table", title="Table Name"),
TableColumn(field="Count", title="Count"),
TableColumn(field="Status", title="Status")
]
results = query_db()
controls = widgetbox(select)
table = widgetbox(create_figure(‘All’))
layout = row(controls, table)
curdoc().add_root(layout)
curdoc().title = “Dashboard”