I'm trying to implement a date picker which would insert selected date into query and pull requested data from database(DASH-PLOTLY)

I’ve been doing extensive research including going through dash documentation but I’m still missing something as I can’t get that right. I am trying to include a date picker in the app. I’ve tried to use a few different logics here, but my latest approach was returning the whole table with updated data from the callback. So it meant to use the date from the picker, pass it to the method running the queries which fills it into a query using the .format() function, then create an updated table with new data and return it to the dash HTML component. However, I am probably doing something terribly wrong as it doesn’t work.


    from classes import DataManager
from dash import html, Output, Input, callback, dcc, dash_table
import dash_bootstrap_components as dbc
from navbar import create_navbar
import pandas as pd
from queries import QUERIES
from datetime import date

nav = create_navbar()

data_manager = DataManager(QUERIES)


def make_table():
    queries_ids = ["orders_in", "orders_out", "lenses_in", "lenses_out", "wip", "UT_in_lenses",
                   "surf_3rd_party", "rejects_coating", "coat_3rd_party", "coating_reworks",
                   "lenses_in_coat", "out_on_time", "SLG_lenses", "SLG_orders", "SLGordersAc",
                   "reworks_lenses_surfacing", "rejects_lenses_surfacing", "avg_to_dispatch",
                   "SLG_lenses", "SLG_orders", "SLGordersAc", ]
    keys = ["Orders In", "Orders Out", "Lenses In", "Lenses Out", "Wip", "UT In Lenses",
            "Surfacing 3rd Party Rejects", "Rejects Coating", "Coating 3rd Party Rejects",
            "Coating Reworks", "Lenses In Coating", "Out On Time", "SLG Lenses", "SLG Orders",
            "SLG Orders Accepted", "Reworks Lenses Surfacing", "Rejects Lenses Surfacing",
            "Average Days To Dispatch", "SLG Lenses", "SLG Orders", "SLG Orders Accepted"]
    for query_id, key in zip(queries_ids, keys):
        kpis = data_manager.get_single_value_added_to_a_dict(query_id, key, date.today())

    def calculate_mar_percent():
        kpis['MAR In %'] = [
            round(((kpis['Lenses In Coating'][0] - kpis['UT In Lenses'][0]) / kpis['Lenses In'][0]) * 100, 2)]
        return kpis

    def calculate_ut_percent():
        kpis['UT In %'] = [round((kpis['UT In Lenses'][0] / kpis['Lenses In'][0]) * 100, 2)]
        return kpis

    def calculate_otd_percent():
        kpis['OTD %'] = [round((kpis['Out On Time'][0] / kpis['Orders Out'][0]) * 100, 2)]
        return kpis

    def calculate_slg_orders_percent():
        kpis['SLG Orders %'] = [round((kpis['SLG Orders'][0] / kpis['Orders Out'][0]) * 100, 2)]
        return kpis

    def calculate_slg_orders_accepted_percent():
        kpis['SLG Orders Accepted %'] = [round((kpis['SLG Orders Accepted'][0] / kpis['Orders Out'][0]) * 100, 2)]
        return kpis

    def round_average_days_to_dispatch():
        kpis['Average Days To Dispatch'] = [round(kpis['Average Days To Dispatch'][0], 2)]
        return kpis

    df = pd.DataFrame(kpis)
    header = html.P([id,
                     dash_table.DataTable(
                         data=df.to_dict('records'),
                         columns=[{'id': c, 'name': c} for c in df.columns],
                         style_as_list_view=True,
                         style_table={'width': '100%'},
                         style_cell={'padding': '13px',
                                     'textAlign': 'left',
                                     'backgroundColor': '#f4f4f4',
                                     'text': 'bold'},
                         style_header={
                             'backgroundColor': '#009b4f',
                             'fontWeight': 'bold',
                             'textAlign': 'left',
                             'color': 'white'

                         },
                         style_cell_conditional=[
                             {

                                 'if': {'column_id': c},
                                 'textAlign': 'left'
                             }
                             for c in ['Date', 'Region']
                         ],
                     )
                     ], style={'width': '33%',
                               'padding-top': '30px'})
    return header


layout2 = html.Div([
    dcc.DatePickerSingle(
        id='picker_main',
        with_portal=True,
        min_date_allowed=date(2022, 1, 1),
        max_date_allowed=date.today(),
        initial_visible_month=date.today(),
        date=date.today(),
        className="datePicker"
    ),

])


def create_displays_page():
    x = make_table()
    layout = html.Div(
        [
            dbc.Row(layout2)
        ]
    ), \
        html.Div(
                 [
                     html.Table(children=[x], id="main")
                 ]
             )
    return layout


@callback(
    Output('main', 'children'),
    Input('picker_main', 'date'))
def update_table(date_value):
    queries_ids_a = ["orders_in", "orders_out", "lenses_in", "lenses_out", "wip", "UT_in_lenses",
                     "surf_3rd_party", "rejects_coating", "coat_3rd_party", "coating_reworks",
                     "lenses_in_coat", "out_on_time", "SLG_lenses", "SLG_orders", "SLGordersAc",
                     "reworks_lenses_surfacing", "rejects_lenses_surfacing", "avg_to_dispatch",
                     "SLG_lenses", "SLG_orders", "SLGordersAc", ]
    keys_a = ["Orders In", "Orders Out", "Lenses In", "Lenses Out", "Wip", "UT In Lenses",
              "Surfacing 3rd Party Rejects", "Rejects Coating", "Coating 3rd Party Rejects",
              "Coating Reworks", "Lenses In Coating", "Out On Time", "SLG Lenses", "SLG Orders",
              "SLG Orders Accepted", "Reworks Lenses Surfacing", "Rejects Lenses Surfacing",
              "Average Days To Dispatch", "SLG Lenses", "SLG Orders", "SLG Orders Accepted"]
    for query_id_a, key_a in zip(queries_ids_a, keys_a):
        kpis_a = data_manager.get_single_value_added_to_a_dict(query_id_a, key_a, date_value)

        def calculate_mar_percent():
            kpis['MAR In %'] = [
                round(((kpis['Lenses In Coating'][0] - kpis['UT In Lenses'][0]) / kpis['Lenses In'][0]) * 100, 2)]
            return kpis

        def calculate_ut_percent():
            kpis['UT In %'] = [round((kpis['UT In Lenses'][0] / kpis['Lenses In'][0]) * 100, 2)]
            return kpis

        def calculate_otd_percent():
            kpis['OTD %'] = [round((kpis['Out On Time'][0] / kpis['Orders Out'][0]) * 100, 2)]
            return kpis

        def calculate_slg_orders_percent():
            kpis['SLG Orders %'] = [round((kpis['SLG Orders'][0] / kpis['Orders Out'][0]) * 100, 2)]
            return kpis

        def calculate_slg_orders_accepted_percent():
            kpis['SLG Orders Accepted %'] = [round((kpis['SLG Orders Accepted'][0] / kpis['Orders Out'][0]) * 100, 2)]
            return kpis

        def round_average_days_to_dispatch():
            kpis['Average Days To Dispatch'] = [round(kpis['Average Days To Dispatch'][0], 2)]
            return kpis

        df_a = pd.DataFrame(kpis_a)
        header = html.Div([html.P(id='table_out'),
                           dash_table.DataTable(
                               data=df_a.to_dict('records'),
                               columns=[{'id': c, 'name': c} for c in df_a.columns],
                               style_as_list_view=True,
                               style_table={'width': '100%'},
                               style_cell={'padding': '13px',
                                           'textAlign': 'left',
                                           'backgroundColor': '#f4f4f4',
                                           'text': 'bold'},
                               style_header={
                                   'backgroundColor': '#009b4f',
                                   'fontWeight': 'bold',
                                   'textAlign': 'left',
                                   'color': 'white'

                               },
                               style_cell_conditional=[
                                   {

                                       'if': {'column_id': c},
                                       'textAlign': 'left'
                                   }
                                   for c in ['Date', 'Region']
                               ],
                           )
                           ], style={'width': '33%',
                                     'padding-top': '30px'})
        return header

Hi,

I can’t say much about the queries, but as far as I can see you are trying to pass a htm.Div with a datatable and other components as children of html.Table (“main”), which is one of the problems with your app.

I would recommend you to start small: take a data sample from the db, save as dataframe and try to filter and update the layout with the new date. Then move to the queries when the layout updates exactly how you want it.