✊🏿 Black Lives Matter. Please consider donating to Black Girls Code today.
⚡️ Concerned about the grid? Kyle Baranko teaches how to predicting peak loads using XGBoost. Register for the August webinar!

How to properly implement dcc.Store() for a dynamic dashboard

Greetings, I have asked a lot of questions here on the last month and just wanted to give a big thanks and shoutout to this community!

I have seen how people have implemented dcc.Store into their dashboards using a static dataframe or csv file, it seems pretty straightforward. However, I don’t think I can follow the same syntax for my dashboard.

The dashboard I made pulls stock market data. Naturally I will be dealing with a high volume of data. I do not want to store my dataset on the client side due to its size (caching and storing are concepts that are new to me, so I might be wrong here).

Users call the data they wish to see using the dcc.Dropdown to select stock symbols. This then fires all my @callbacks to display html.Components, one graph and multiple tables of data from SQL queries. I store the graph data in a python dictionary, and table data in dataframes on the server.

How can I store data pulled from my SQL queries, along with my table and graph objects? I assume I can create this in my callbacks, but I worry that I would be needlessly storing multiple JSON calls of data that would be redundant when I do it this way.

Below is a snippet of some code, I added comments to make it easier to navigate:


get_stock_list = execute_query(conn, "SELECT DISTINCT id FROM security_price;")
stock_ids = [i[0] for i in get_stock_list]
## INPUT OPTIONS FOR USERS ##
options = [{'label': i, 'value': i} for i in stock_ids]

app = DjangoDash('StockBuckets', add_bootstrap_links=True, external_stylesheets="https://codepen.io/chriddyp/pen/bWLwgP.css", suppress_callback_exceptions = True,  
                  meta_tags=[{"name": "viewport", "content": "width=device-width, initial-scale=1.0"}])


## BODY HTML ##
body = html.Div(
    [dbc.Jumbotron(
          [
            dbc.Row(dbc.Col(children=[dcc.Dropdown(id="dynamic-dropdown", options=options, multi=True, placeholder="Enter Symbols of Interest"),
                                     
                                      ], lg=6, md=7, sm=11, xs=11, align="center"),justify='center'),
            ], 
                           style = {'background-color': '#68d984'}, fluid=True,
                  )
   
    , dbc.Row(dbc.Col([  
                          ],id='graph',lg=9, md=11, sm=12 , xs=12),style={'width':'100%'},justify="center")



    , dbc.Row([
            dbc.Col(children=[  
                                ],id='quickfacts',lg=5, md=11, sm=12 ,xs=12)


          , dbc.Col(children=[
                                ], id='returnrates',lg=6, md=11, sm=12 ,xs=12)
                                                                        ],style = {'width': '100%'}, justify="center", align="center")



app.layout = html.Div([body])



## THIS CALLBACK LIMITS THE AMOUNT OF INPUTS TO ONLY 5 VALUES ##
@app.callback(

   Output("dynamic-dropdown", "options"), [Input("dynamic-dropdown", "value")],
)
def limit_drop_options(symbols):
    """Limit dropdown to at most two actives selections"""
    if len(symbols) >= 5:

      
      return [
          option
          for option in options
          if option["value"] in symbols
           ]
    else:
        return options

     

## THIS CALLBACK GRAPHS MY PLOTLY EXPRESS FIGURE  ##
@app.callback(
    Output('graph', 'children'),
    [Input('dynamic-dropdown', 'value')],
    [State('graph','children')])

def tickers(symbols, children):
    conn.rollback()
    if symbols == None:
        conn.rollback()
        return []
    elif symbols == []:
        conn.rollback()
        return []
    else:
        stock_info = {}
        d = {} #dates
        p = {} #prices

        sym_ids = tuple([id for id in symbols])

        stock_info = {}
        stock_info = get_dict_resultset("SELECT id, date, adj_close FROM security_price WHERE security_price.id IN %s;", [sym_ids])

        stock_data_by_symbol = defaultdict(list)
        for entry in stock_info:
            symbol = entry['id']
            stock_data_by_symbol[symbol].append(entry)

        trace = []
        for stock in symbols:
            d[stock] = [rec['date'] for rec in stock_data_by_symbol[stock]] 
            p[stock] = [rec['adj_close'] for rec in stock_data_by_symbol[stock]]
            trace.append(go.Scattergl(x=d[stock],
                                 y=p[stock],
                                 mode='lines',
                                 text = d[stock],
                                 opacity=0.7,
                                 name=stock,
                                 textposition='bottom center'))


        traces = [trace]
        data = [val for sublist in traces for val in sublist]
        figure = {'data': data,
                  'layout': go.Layout(
                      colorway=["#9b5de5", '#00f5d4', '#FFD23F', '#f15bb5', '#f71735', '#d08c60'],
                      paper_bgcolor='rgba(0, 0, 0, 0)',
                      plot_bgcolor='rgba(0, 0, 0, 0)',
                      margin={
                        'l': 40, # left margin, in px
                        'r': 10, # right margin, in px
                        't': 16, # top margin, in px
                        'b': 30,}, # bottom margin, in px
                      hovermode='x',
                      legend={"x" : 0, "y" : 1, 'font': {'size': 10}},
                      xaxis={'rangeselector': {'buttons': list([
                                                            {'count': 1, 'label': '1M', 
                                                             'step': 'month', 
                                                             'stepmode': 'backward'},
                                                            {'count': 3, 'label': '3M', 
                                                             'step': 'month', 
                                                             'stepmode': 'backward'},
                                                            {'count': 6, 'label': '6M', 
                                                             'step': 'month',
                                                             'stepmode': 'backward'},
                                                            {'count': 1, 'label': '1Y', 
                                                             'step': 'year',
                                                             'stepmode': 'backward'},
                                                            {'count': 3, 'label': '3Y', 
                                                             'step': 'year',
                                                             'stepmode': 'backward'},
                                                            {'count': 1, 'label': 'YTD', 
                                                             'step': 'year', 
                                                             'stepmode': 'todate'},
                                                            {'step': 'all','label':'MAX'},

                                                            ])},
                         'rangeslider': {'visible': True}, 'type': 'date'},

                      
                      
                  ),


                  }

        
        children = [
                      
               dbc.Row(dbc.Col(html.P("As of {}, {}".format(d[stock][-1].strftime('%A'),d[stock][-1].strftime('%d %m-%Y')),style={'font-size':'12px'}),style={'position':'relative','float':'right'}, width={"offset": 3}), justify='end'),
               html.Div(style={"width":"100%", "height":'30px'}),
               html.H4('Historical Price Chart:', style={'color':'#474747','margin-left':'30px'}),
               html.P('Interactive graph for end-of-day prices', style={'color':'#A2A2A2','margin-left':'30px'}), 
               dcc.Graph(id='output-graph', figure=figure ,config={'displayModeBar': False}, animate=True,style = {'width': '100%'}), 
               html.P('* Drag sliders to create your own custom date range', style={'font-size':'12px','color':'#A2A2A2','margin-left':'40px'}),
               html.P('** Double-tap or double-click the main graph to reset axis', style={'font-size':'12px','color':'#A2A2A2','margin-left':'40px'}),
               html.Div(style={"width":"100%", "height":'10px'}),
                    ]


        return children


## OUTPUTS A TABLE ##
@app.callback(
dash.dependencies.Output('quickfacts', 'children'),
[dash.dependencies.Input('dynamic-dropdown', 'value')])

def statsTable(symbols):
    conn.rollback()
    if symbols == None:
        conn.rollback()
        return []
    elif symbols == []:
        conn.rollback()
        return []
    else:
    
      placeholders = ", ".join(['%s' for _ in symbols])

      # PREPARED STATEMENT WITH PARAM PLACEHOLDERS
      sql = f"""SELECT DISTINCT ON (t1.id) t1.id, cast(t2.adj_close as money),cast(t1.marketcap as money), cast(t1.week52high as money), cast(t1.week52low as money)
                       , to_char(t1.dividend_yield * 100, '99D99%%')
                       , t1.pe_ratio, ROUND(t1.beta,2) 
                FROM security_stats as t1
                LEFT JOIN security_price as t2 USING (id) 
                WHERE id IN ({placeholders}) 
                ORDER BY id;
             """
  

      df = postgresql_to_dataframe_v1(conn, sql, symbols, stats_col)
      columns =[{"name": i, "id": i} for i in df.columns]
      data_dict = df.to_dict('records')
      



      children = html.Div([    
                 html.H4('Quick Facts:', style={'color':'#474747','margin-left':'10px'}), 
                 html.P('General information', style={'color': '#A2A2A2','margin-left':'10px'}), 
                 html.Div(dt.DataTable(data=data_dict, columns=columns, style_as_list_view=True,
                          tooltip_data=[quickFacts],
                          tooltip_duration  = 10000,
                          style_cell={'padding': '15px', 'font_family': 'Segoe UI','font_size': '10px'},
                          style_header={
                              'backgroundColor': 'white',
                              'fontWeight': 'bold'
                          },
                          style_data_conditional=[
          {
              'if': {'row_index': 'even'},
              'backgroundColor': 'rgb(248, 248, 248)'
          }
      ],
                          style_data = {'border': 'none'},
                          style_table={'overflowX': 'scroll'}
      ), style={'width': '100%','padding': '16px'})],style = {'margin-left':'15px','width':'100%'})



      return children




## OUTPUTS A TABLE ##

@app.callback(
dash.dependencies.Output('returnrates', 'children'),
[dash.dependencies.Input('dynamic-dropdown', 'value')])

def changeTable(symbols):
    conn.rollback()
    if symbols == None:
        conn.rollback()
        return []
    elif symbols == []:
        conn.rollback()
        return []
    else:
      
      placeholders = ", ".join(['%s' for _ in symbols])

      # PREPARED STATEMENT WITH PARAM PLACEHOLDERS
      sql = f"""SELECT DISTINCT ON (id) id, to_char(100.0*ytd_changepercent,'999D99%%'), to_char(100.0*day5_changepercent,'999D99%%'), to_char(100.0*day30_changepercent,'999D99%%')
                       , to_char(100.0*month3_changepercent,'999D99%%'), to_char(100.0*month6_changepercent,'999D99%%'), to_char(100.0*year1_changepercent,'999D99%%')
                       , to_char(100.0*year2_changepercent,'999D99%%'),to_char(100.0*year5_changepercent,'9999D99%%'),to_char(100.0*max_changepercent,'99999D99%%')
                FROM security_stats 
                WHERE security_stats.id IN ({placeholders}) ORDER BY id, date desc;
             """



      df = postgresql_to_dataframe_v1(conn, sql, symbols, change_col)
      columns =[{"name": i, "id": i} for i in df.columns]
      data_dict = df.to_dict('records')

      children = html.Div([html.H4('Historical Return-rates:', style={'color':'#474747','margin-left':'10px'}),
                                html.P('Equity growth based on time periods', style={'color':'#A2A2A2','margin-left':'10px'}), 
                                html.Div(dt.DataTable(data=data_dict, columns=columns, style_as_list_view=True,

                          style_cell={'padding': '15px', 'font_family': 'Segoe UI','font_size': '10px'},
                          style_header={
                              'backgroundColor': 'white',
                              'fontWeight': 'bold'
                          },style_data_conditional=[
          {
              'if': {'row_index': 'even'},
              'backgroundColor': 'rgb(248, 248, 248)'
          }
      ],
                          style_data = {'border': 'none'},
                          style_table={'overflowX': 'scroll'}

      ), style={'width': '100%','padding': '16px'})],style = {'margin-left':'15px','width':'100%'})


      return children

Would greatly appreciate some advice here, thank you!