Live updating of data loaded from an sql server with 300000+ lines and upto 15 columns

Dash Python
1

Hi all
I’m trying to build an app which currently connects with the sql server database (only be my company’s internet), then doing some calculations and displaying the graphs created from it on my Dash app.
I’m able to create a cool dashboard, but i’m still not able to know how to update the data of the graph after every 30 seconds without refreshing the page. Also i tried using the method of storing data in dcc.div and dcc.store but it did’nt work as my dataframe was too large. Please someone help me.

The database on the sql server also keeps increasing everyday as every minute a new line is added to that database. after importing the database some calculations are being done on it with help of pandas and then they’re sent to update two dynamic dropdowns. after selecting the required data from the dropdowns they’re used to update 10 different type of graphs and two datatables. everything works really well. The two dynamic dropdowns and all the 10 graph are also getting input from a datepickerrange and another dropdown.

I’m posting my code in comments as my code is too long and the body size of this form is limited

2

part-1 of code (upto layout)

import dash
import dash_core_components as dcc
import dash_html_components as html
import pandas as pd
import pyodbc
import dash_table
import plotly.express as px
import plotly.graph_objects as go
from dash.dependencies import Input, Output
from plotly.subplots import make_subplots
import numpy as np
import flask
from datetime import time
from flask_caching import Cache
import os

external_stylesheets = ['https://codepen.io/chriddyp/pen/bWLwgP.css', 'https://codepen.io/chriddyp/pen/brPBPO.css']

server = flask.Flask(__name__)
app = dash.Dash(__name__, external_stylesheets=external_stylesheets, server=server)

cache = Cache(app.server, config={
    'CACHE_TYPE': 'redis',
    'CACHE_REDIS_URL': os.environ.get('REDIS_URL', '')
})
app.config.suppress_callback_exceptions = True

TIMEOUT = 30

@cache.memoize(timeout=TIMEOUT)
def query_data():
    driver = "SQL Server"
    server = "***.**.146.96\SQLEXPRESS"
    database = "***IIOT"
    username = "IIOTUser"
    password = "****#13579"

    # Create the connection
    conn = pyodbc.connect(
        "driver=" + driver + ";server=" + server + ";uid=" + username + ";pwd=" + password + ";database=" + database)

    # query db
    sql = """

    SELECT * FROM Ichinose1

    """
    df = pd.read_sql(sql, conn)

    df.fillna(0, inplace=True)
    df['date_time1'] = pd.to_datetime(df['shiftDate'].str[:10] + " " + df['shiftTime'].str[:8])
    df.sort_values(by='date_time1', ascending=True, inplace=True)
    df['e'] = np.nan
    df.loc[((time(00, 00, 00) <= df['date_time1'].dt.time) & (df['date_time1'].dt.time <= time(6, 59, 59))), 'e'] = 1
    df.loc[df['e'] == 1, 'e'] = df['date_time1'] - pd.DateOffset(days=1)
    df.loc[pd.isnull(df['e']), 'e'] = df['shiftDate']
    df['e'] = pd.to_datetime(df.loc[pd.notnull(df['e']), 'e'])
    df['Date_Time'] = pd.to_datetime(df['e'].dt.date.astype(str).str[:10] + " " + df['shiftTime'].str[:8])
    df['cdate'] = df['Date_Time'].dt.date.astype(str).str[:10]
    df['MachineRunStatus'] = np.where(df['RunStatus'] == 1, "Running", "Idle")
    df.reset_index(inplace=True, drop=True)
    df['Production_Order'] = np.where(
        np.array(df['RunStatus'])[np.where(df.index == df.last_valid_index(), df.index, df.index + 1)] == 0,
        np.where(np.array(df['RunStatus'])[df.index] == 1, df['ProductionOrder'], np.nan), np.nan)
    df['PO_Dummy'] = np.where(df['ProductionOrder'] == 0, np.nan, df['ProductionOrder'])
    df['PO_Dummy'].ffill(inplace=True)
    df.loc[df.last_valid_index(), 'Production_Order'] = df.loc[df.last_valid_index(), 'ProductionOrder']
    df['Production_Order'].bfill(inplace=True)
    df['UniquePORun'] = np.where(
        np.array(df['POLength'])[np.where(df.index == df.last_valid_index(), df.index, df.index + 1)] <= 0,
        np.where(np.array(df['POLength'])[df.index] > 0, np.where(
            np.array(df['RunStatus'])[np.where(df.index == df.last_valid_index(), df.index, df.index + 1)] !=
            np.array(df['RunStatus'])[df.index], df['ProductionOrder'], np.nan), np.nan),
        np.nan)
    df['UniquePORun'] = np.where(
        np.array(df['Production_Order'])[np.where(df.index == df.last_valid_index(), df.index, df.index + 1)] ==
        np.array(df['Production_Order'])[df.index], df['UniquePORun'], df['Production_Order'])
    df['Time(mins)'] = 0.5
    df['count'] = 1
    df['RunNo'] = np.where(pd.isnull(df['UniquePORun']) == False,
                           df.groupby(['UniquePORun', 'RunCategory'])['count'].cumsum(), np.nan)
    df['RunNo'] = np.where(df['RunNo'] == 0.0, np.nan, df['RunNo'])
    df['TB'] = np.where(pd.isnull(df['UniquePORun']) == False, df['RunCategory'], df['UniquePORun'])
    df['TB'].bfill(inplace=True)
    df['RunNo'].bfill(inplace=True)
    df['Run_Category'] = np.where(df['TB'] == 1, "Trial ", np.where(df['TB'] == 2, "Bulk ", np.nan))
    df['Run_Category'] = df['Run_Category'].astype(str) + df['RunNo'].astype(str)
    df.drop(columns=['TB', 'UniquePORun', 'PO_Dummy', 'RunNo', 'count', 'RunCategory', 'ProductionOrder'], inplace=True)

    df_Stoppage1 = pd.DataFrame(
        list(zip([0, 1, 2, 3, 4, 201, 202, 203, 204, 205, 206, 207, 208, 209, 210, 211, 212, 213],
                 ['Not Selected', 'Line in Fabric', 'Dust', 'Screen Chokeup', 'End of Fabric', 'Pin hole', 'Rod Change', 'Fluff removal', 'Design out setting',
                 'Screen Wipping', 'Crease /line problem', 'Wider /Uneven width of fabric ( Remove /Apply Tape )',
                 'Screen Choke up', 'End of fabric', 'Color pump failure', 'Waiting of color', 'Selvedge cut in fabric',
                 'Improper gum problem in running'])),
        columns=['slowSpeedCode', 'Slow_Speed_Reason'])
    df_Stoppage0 = pd.DataFrame(
        list(zip([0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20,
                  101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120],
                 ['Not Selected', 'Machine Cleaning', 'Fabric Changeover', 'Trolley/Batch Non-Availability', 'Mechanical Breakdown',
                  'Electrical Breakdown', 'Preventive Maintenance', 'Low Steam Pressure', 'Low Air Pressure', 'No Water',
                  'Man Power Shortage', 'No Program', 'Design Change', 'Screen Washing', 'No Color Preparation', 'Design Not Ready',
                  'Corrective Maintenance', 'Low Temp Due To Thermopack', 'End Ring Not Fit', 'Blanket Coating',
                  'Gum Circulation Pump Not Working', 'Machine Cleaning', 'Fabric Changeover', 'Trolley/Batch Non-Availability',
                  'Mechanical Breakdown',
                  'Electrical Breakdown', 'Preventive Maintenance', 'Low Steam Pressure', 'Low Air Pressure', 'No Water',
                  'Man Power Shortage', 'No Program', 'Design Change', 'Screen Washing', 'No Color Preparation', 'Design Not Ready',
                  'Corrective Maintenance', 'Low Temp Due To Thermopack', 'End Ring Not Fit', 'Blanket Coating',
                  'Gum Circulation Pump Not Working',]))
        , columns=['Stoppage', 'Stoppage_Name'])

    df=pd.merge(df,df_Stoppage0,on="Stoppage",how="left")
    df=pd.merge(df,df_Stoppage1,on="slowSpeedCode",how="left")
    return df


def dataframe():
    return query_data()

df = dataframe()

app.layout = html.Div([
    dcc.Interval(id='refresh', interval=30000),
    html.H1("Ichinose-1 IIOT Dashboard",
            style={"text-align": "centre", 'display': 'flex', 'justify-content': 'center'}),
    html.Div(id="div", children=[]),
    html.Br([]),
        html.Div([
        html.Div([
            dcc.DatePickerRange(id="select_date_range",
                                display_format="MMM D, YYYY",
                                clearable=True,
                                number_of_months_shown=4,
                                style={'width': '100%'},
                                ),

        ], className="three columns"),
        html.Div([
            dcc.Dropdown(id="Shift",
                         options=[
                             {"label": 'A', "value": 'A'},
                             {"label": 'B', "value": 'B'},
                             {"label": 'C', "value": 'C'},
                         ],
                         multi=False,
                         # value='A',
                         placeholder="Select a Shift",
                         style={'width': '100%'}
                         ),

        ], className="three columns"),
        html.Div([
            dcc.Dropdown(id="select_PO",
                         multi=False,
                         placeholder="Select a P.O.",
                         style={'width': '100%'}
                         ),
        ], className="three columns"),
        html.Div([
            dcc.Dropdown(id="select_RunCategory",
                         multi=False,
                         placeholder="Select Bulk or Trial?",
                         style={'width': '100%'}
                         ),
        ], className="three columns"),
    ], className="row"),

    html.Br([]),
    html.Br([]),
    html.Div([
        dcc.RadioItems(
            id="Running_Idle_Selector",
            options=[
                {'label': 'Machine Running  ', 'value': 'Running'},
                {'label': 'Machine Idle  ', 'value': 'Idle'},
                {'label': 'Both', 'value': 'Both'}
            ],
            value='Both',
            labelStyle={'display': 'inline-block'},
            style={
                "display": "block",
                "position": "relative",
                "padding-left": "35px",
                "cursor": "pointer",
                "font-size": "22px",
                "width": "100%",
                "height": "2em",
                "margin": "10px 0"
            },
        )
    ], style={'border': '1px solid black'}),
    html.Div([
        html.Div([
            dcc.Tabs([
                dcc.Tab(label="Speed_Length_Graph", children=[
                    dcc.Graph(id='Speed_Length_Graph', figure={}),
                ]),
                dcc.Tab(label="Air_Water_Graph", children=[
                    dcc.Graph(id='Air_Water_Graph', figure={}),
                ]),
                dcc.Tab(label="Thermic-Oil_Energy-Consumption_Graph", children=[
                    dcc.Graph(id='Thermic_Energy_Graph', figure={}),
                ])
            ])
        ], style={'width': '78%', 'border-right': '1px solid black'}),
        html.Div([
            dcc.Graph(id='piechart'),
        ], style={'width': '22%'}),
    ], style={'align-items': 'center', 'border': '1px solid black', 'display': 'flex', 'justify-content': 'center'}),
    html.Br([]),
    html.Div([
        html.Div(id="machine_run_status", children=[], style={
            'width': '25%',
            'background-color': '#00107a',
            'margin': '1px auto',
            'color': 'white',
            'font-size': 'x-large',
        }),
        html.Div(id="po_no", children=[], style={
            'width': '25%',
            'background-color': '#00107a',
            'margin': '1px auto',
            'color': 'white',
            'font-size': 'x-large',
        }),
        html.Div(id="speed", children=[], style={
            'width': '25%',
            'background-color': '#00107a',
            'margin': '1px auto',
            'color': 'white',
            'font-size': 'x-large',
        }),
        html.Div(id="po_length", children=[], style={
            'width': '25%',
            'background-color': '#00107a',
            'margin': '1px auto',
            'color': 'white',
            'font-size': 'x-large',
        }),
    ], style={
        'align-items': 'center',
        'display': 'flex',
        'justify-content': 'center',
        'border': '1px solid black',
    }),
    html.Br([]),
    html.Div([
        html.Div([
            dcc.Graph(id='Air_Graph', figure={}), ], className="three columns", style={'border': '1px solid black'}),
        html.Div([
            dcc.Graph(id='Water_Graph', figure={}), ], className="three columns", style={'border': '1px solid black'}),
        html.Div([
            dcc.Graph(id='Thermic_Graph', figure={}), ], className="three columns",
            style={'border': '1px solid black'}),
        html.Div([
            dcc.Graph(id='Energy_Graph', figure={}), ], className="three columns", style={'border': '1px solid black'})
    ], className="row"),
    html.Br([]),

    html.Div([
        html.Div([
            dcc.Tabs([
                dcc.Tab(label="Pareto of Stoppage", children=[
                    dcc.Graph(id="Pareto_Stoppage", figure={})
                ]),
                dcc.Tab(label="Pareto of Slow Speed", children=[
                    dcc.Graph(id="Pareto_Slow_Speed", figure={})
                ])
            ])
        ], style={
            'width': '78%',
            'border-right': '1px solid black'
        }),
        html.Div([
            html.Div(id="stoppage_data_name", children=[], style={
                'text-align': 'center',
                'background-color': '#03fcd3',
                'color': 'black',
                'margin': '3px auto',
                'font-size': 'xx-large',
                'border': '1px solid black',
            }),
            html.Div([
                html.Div(children=['Total Stoppage Time:'], style={
                    'text-align': 'center',
                    'background-color': '#ff4242',
                    'color': 'white',
                    'margin': '1px auto',
                    'font-size': 'xx-large',
                    'border': '1px solid black',
                }),
                html.Div(id="total_stoppage_time", children=[], style={
                    'text-align': 'center',
                    'background-color': '#ff4242',
                    'color': 'white',
                    'margin': '1px auto',
                    'font-size': 'xx-large',
                    'border': '1px solid black',
                }),
            ], style={
                'margin': '10px auto',
                'border': '1px solid black',
            }),
            html.Div([
                html.Div(children=['Last Stoppage Time:'], style={
                    'text-align': 'center',
                    'background-color': '#ff4242',
                    'color': 'white',
                    'margin': '1px auto',
                    'font-size': 'xx-large',
                    'border': '1px solid black',
                }),
                html.Div(id="last_stoppage_time", children=[], style={
                    'text-align': 'center',
                    'background-color': '#ff4242',
                    'color': 'white',
                    'margin': '1px auto',
                    'font-size': 'xx-large',
                    'border': '1px solid black',
                }),
            ], style={
                'margin': '10px auto',
                'border': '1px solid black',
            }),
            html.Div([
                html.Div(children=['Last Stoppage Name:'], style={
                    'text-align': 'center',
                    'background-color': '#ff4242',
                    'color': 'white',
                    'margin': '1px auto',
                    'font-size': 'xx-large',
                    'border': '1px solid black',
                }),
                html.Div(id="last_stoppage_name", children=[], style={
                    'text-align': 'center',
                    'background-color': '#ff4242',
                    'color': 'white',
                    'font-size': 'xx-large',
                    'margin': '1px auto',
                    'border': '1px solid black',
                }),
            ], style={
                'margin': '10px auto',
                'border': '1px solid black',
            }),
        ], style={
            'width': '22%',
            'position': 'relative'
        }),
    ], style={
        'align-items': 'center', 'border': '1px solid black', 'display': 'flex', 'justify-content': 'center'
    }),

    html.Br([]),
    dcc.Tabs([
        dcc.Tab(label="Detailed Data", children=[
            html.Div([
                dash_table.DataTable(
                    id='datatable',
                    editable=False,
                    row_deletable=False,
                    page_action="native",
                    page_current=0,
                    page_size=6,
                    style_data_conditional=[
                        {
                            'if': {'row_index': 'odd'},
                            'backgroundColor': 'rgb(248, 248, 248)'
                        }
                    ],
                    style_header={
                        'backgroundColor': 'rgb(230, 230, 230)',
                        'fontWeight': 'bold'
                    },
                    style_cell={'textAlign': 'center', 'font_size': '20px', 'font_family': 'noto sans'}
                ),
            ], className='row'),
        ]),
        dcc.Tab(label="PO Based Data", children=[
            html.Div([
                dash_table.DataTable(
                    id='po_based_data',
                    editable=False,
                    row_deletable=False,
                    page_action="native",
                    page_current=0,
                    page_size=6,
                    style_data_conditional=[
                        {
                            'if': {'row_index': 'odd'},
                            'backgroundColor': 'rgb(248, 248, 248)'
                        }
                    ],
                    style_header={
                        'backgroundColor': 'rgb(230, 230, 230)',
                        'fontWeight': 'bold'
                    },
                    style_cell={'textAlign': 'center', 'font_size': '20px', 'font_family': 'noto sans'}
                ),
            ], className='row'),
        ])
    ]),
    html.Br([]),
    html.Br([]),
    html.Br([]),
    html.Br([]),
    html.Br([]),

])
3

part-2 of code (callbacks)

@app.callback([Output(component_id="select_PO", component_property="options"),
               ],
              [Input(component_id="select_date_range", component_property="start_date"),
               Input(component_id="select_date_range", component_property="end_date"),
               Input(component_id="Shift", component_property="value")
               ])
def update_dropdown(start_date, end_date, Shift_value):
    if all([start_date is not None, end_date is not None]) is True:
        Date_value = pd.date_range(start_date, end_date).strftime('%Y-%m-%d')
    else:
        if all([start_date is None, end_date is not None]) is True:
            Date_value = [end_date]
        elif all([end_date is None, start_date is not None]) is True:
            Date_value = [start_date]
        else:
            Date_value = None
    if Date_value is not None:
        if Shift_value is not None:
            return_PO = df.loc[(df['cdate'].isin(Date_value)) & (df['shift'] == Shift_value)]['Production_Order'].unique()
        else:
            return_PO = df.loc[df['cdate'].isin(Date_value)]['Production_Order'].unique()
    else:
        if Shift_value is not None:
            return_PO = df.loc[df['shift'] == Shift_value]['Production_Order'].unique()
        else:
            return_PO = df['Production_Order'].unique()
    return [[{"label": i, "value": i} for i in return_PO]]


@app.callback(
    [
        Output(component_id="select_RunCategory", component_property="options")],
    [
        Input(component_id="select_date_range", component_property="start_date"),
        Input(component_id="select_date_range", component_property="end_date"),
        Input(component_id="Shift", component_property="value"),
        Input(component_id="select_PO", component_property="value"),
    ]
)
def update_run_category(start_date, end_date, Shift_value, PO_Value):
    if all([start_date is not None, end_date is not None]) is True:
        Date_value = pd.date_range(start_date, end_date).strftime('%Y-%m-%d')
    else:
        if all([start_date is None, end_date is not None]) is True:
            Date_value = [end_date]
        elif all([end_date is None, start_date is not None]) is True:
            Date_value = [start_date]
        else:
            Date_value = None
    if Date_value is not None:
        if Shift_value is not None:
            if PO_Value is not None:
                return_run_category = df.loc[(df['cdate'].isin(Date_value)) & (df['shift'] == Shift_value) & (df['Production_Order'] == PO_Value)]['Run_Category'].unique()
            else:
                return_run_category = ["Please select a P.O."]
        else:
            if PO_Value is not None:
                return_run_category = df.loc[(df['cdate'].isin(Date_value)) & (df['Production_Order'] == PO_Value)]['Run_Category'].unique()
            else:
                return_run_category = ["Please select a P.O."]
    else:
        if Shift_value is not None:
            if PO_Value is not None:
                return_run_category = df.loc[(df['shift'] == Shift_value) & (df['Production_Order'] == PO_Value)]['Run_Category'].unique()
            else:
                return_run_category = ["Please select a P.O."]
        else:
            if PO_Value is not None:
                return_run_category = df.loc[df['Production_Order'] == PO_Value]['Run_Category'].unique()
            else:
                return_run_category = ["Please select a P.O."]
    return [[{"label": i, "value": i} for i in return_run_category]]


@app.callback(
    [Output(component_id="datatable", component_property="data"),
     Output(component_id="datatable", component_property="columns"),
     Output(component_id="Speed_Length_Graph", component_property="figure"),
     Output(component_id="Air_Water_Graph", component_property="figure"),
     Output(component_id="Thermic_Energy_Graph", component_property="figure"),
     Output(component_id="piechart", component_property="figure"),
     Output(component_id="Air_Graph", component_property="figure"),
     Output(component_id="Water_Graph", component_property="figure"),
     Output(component_id="Thermic_Graph", component_property="figure"),
     Output(component_id="Energy_Graph", component_property="figure"),
     Output(component_id="Pareto_Stoppage", component_property="figure"),
     Output(component_id="Pareto_Slow_Speed", component_property="figure"),
     Output(component_id="speed", component_property="children"),
     Output(component_id="po_length", component_property="children"),
     Output(component_id="total_stoppage_time", component_property="children"),
     Output(component_id="last_stoppage_time", component_property="children"),
     Output(component_id="last_stoppage_name", component_property="children"),
     Output(component_id="machine_run_status", component_property="children"),
     Output(component_id="po_no", component_property="children"),
     Output(component_id="stoppage_data_name", component_property="children"),
     Output(component_id="po_based_data", component_property="data"),
     Output(component_id="po_based_data", component_property="columns"),
     ],
    [Input(component_id="select_date_range", component_property="start_date"),
     Input(component_id="select_date_range", component_property="end_date"),
     Input(component_id="Shift", component_property="value"),
     Input(component_id="select_PO", component_property="value"),
     Input(component_id="select_RunCategory", component_property="value"),
     Input(component_id="Running_Idle_Selector", component_property="value"),
     Input(component_id="refresh", component_property="n_intervals")
     ])
def update_graph(start_date, end_date, Shift_value, PO_Number, Run_Category, Running_Idle_Selector, n):
    if all([start_date is not None, end_date is not None]) is True:
        Date_value = pd.date_range(start_date, end_date).strftime('%Y-%m-%d')
    else:
        if all([start_date is None, end_date is not None]) is True:
            Date_value = [end_date]
        elif all([end_date is None, start_date is not None]) is True:
            Date_value = [start_date]
        else:
            Date_value = None
    df_stoppage = df[['RunStatus', 'Stoppage_Name', 'Time(mins)']]
    df_slow_speed = df[['Slow_Speed_Reason','Time(mins)','RunStatus','slowSpeedStatus']]
    df_slow_speed = df_slow_speed.loc[(df_slow_speed['RunStatus'] == 1) & (df_slow_speed['slowSpeedStatus'] == 1.0)]
    if Date_value is not None:
        if PO_Number is not None:
            if Shift_value is not None:
                if Run_Category is not None:
                    df1 = df.loc[(df['cdate'].isin(Date_value)) & (df['shift'] == Shift_value) & (
                                df['Production_Order'] == PO_Number) & (df['Run_Category'] == Run_Category)]
                else:
                    df1 = df.loc[(df['cdate'].isin(Date_value)) & (df['shift'] == Shift_value) & (
                                df['Production_Order'] == PO_Number)]
            else:
                if Run_Category is not None:
                    df1 = df.loc[(df['cdate'].isin(Date_value)) & (df['Production_Order'] == PO_Number) & (
                                df['Run_Category'] == Run_Category)]
                else:
                    df1 = df.loc[(df['cdate'].isin(Date_value)) & (df['Production_Order'] == PO_Number)]
        else:
            if Shift_value is not None:
                df1 = df.loc[(df['cdate'].isin(Date_value)) & (df['shift'] == Shift_value)]
            else:
                df1 = df.loc[df['cdate'].isin(Date_value)]
    else:
        if PO_Number is not None:
            if Shift_value is not None:
                if Run_Category is not None:
                    df1 = df.loc[(df['shift'] == Shift_value) & (df['Production_Order'] == PO_Number) & (
                                df['Run_Category'] == Run_Category)]
                else:
                    df1 = df.loc[(df['shift'] == Shift_value) & (df['Production_Order'] == PO_Number)]
            else:
                if Run_Category is not None:
                    df1 = df.loc[(df['Production_Order'] == PO_Number) & (df['Run_Category'] == Run_Category)]
                else:
                    df1 = df.loc[df['Production_Order'] == PO_Number]
        else:
            if Shift_value is not None:
                df1 = df.loc[df['shift'] == Shift_value]
            else:
                df1 = df.loc[df['cdate'] == df.loc[df.last_valid_index(), 'cdate']]

    df_graph = df1.copy()
    df_mini_data = df1.loc[df['RunStatus'] == 1]
    df_mini_data.reset_index(inplace=True, drop=True)
    average_speed = round(df_mini_data['FabricSpeed'].mean(), 2)
    max_length = round(df_mini_data['POLength'].max(), 2)
    current_speed = np.array(df_mini_data['FabricSpeed'])[df_mini_data.last_valid_index()].round(decimals=2)
    po_based_data = df_mini_data.groupby(by=['cdate', 'Production_Order', 'Run_Category'])[
        ['POLength', 'FabricSpeed']].agg({'POLength': 'max', 'FabricSpeed': 'mean'}).reset_index()
    po_based_data.rename(columns={"cdate": "Date", "FabricSpeed": "Avg. Speed(mtr/min)", "POLength": "POLength(mtr)"},
                         inplace=True)

    if any([Date_value is not None, Shift_value is not None, PO_Number is not None]) is False:
        speed = "Current speed:" \
                "{} mtr/min".format(current_speed)
        machine_run_status = "Machine Status: {}".format(df1.loc[df1.last_valid_index(), 'MachineRunStatus'])
        stoppage_string = "Data of {}".format(df1['Date_Time'].dt.date.unique().astype(str))
        if np.array(df1['RunStatus'][df1.last_valid_index()]) == 1:
            current_po = 'Running PO No.: {}'.format(df1.loc[df1.last_valid_index(), 'Production_Order'])
        else:
            current_po = 'Running PO No.: -----'
    else:
        speed = "Average speed:" \
                "{} mtr/min".format(average_speed)
        machine_run_status = "Machine Status: -----"
        current_po = 'Running PO No.: -----'
        stoppage_string = "For Selected Data"

    if PO_Number is None:
        total_length = "Total Production:  " \
                       "{} mtr".format(
            df_mini_data.groupby(['Production_Order', 'Run_Category'])['POLength'].max().sum().round(decimals=2))
    else:
        total_length = "Total Production:  " \
                       "{} mtr".format(max_length)

    df_stoppage = df_stoppage.loc[df_stoppage['RunStatus'] == 0]

    df_stoppage2 = df_graph[['RunStatus', 'Stoppage_Name', 'Slow_Speed_Reason']]
    df_stoppage2 = df_stoppage2.loc[df_stoppage2['RunStatus'] == 0]
    df_stoppage2.reset_index(inplace=True, drop=True)
    last_stoppage = np.array(df_stoppage2['Stoppage_Name'])[df_stoppage2.last_valid_index()]
    last_stoppage_name = "{}".format(last_stoppage)
    last_stoppage_time = "{} mins".format(round(df_stoppage2.loc[df_stoppage2['Stoppage_Name'] == last_stoppage]
                                                ['Stoppage_Name'].count() / 2, 2))
    total_stoppage_time = "{} min".format(round(df_stoppage2['Stoppage_Name'].count() / 2, 2))

    stoppage_data = df_stoppage.groupby('Stoppage_Name').sum()['Time(mins)'].reset_index()
    stoppage_data.sort_values(by='Time(mins)', ascending=False, inplace=True)
    stoppage_data["cumpercentage"] = stoppage_data["Time(mins)"].cumsum() / stoppage_data["Time(mins)"].sum() * 100

    slow_speed_data = df_slow_speed.groupby('Slow_Speed_Reason').sum()['Time(mins)'].reset_index()
    slow_speed_data.sort_values(by='Time(mins)', ascending=False, inplace=True)
    slow_speed_data["cumpercentage"] = slow_speed_data["Time(mins)"].cumsum() / slow_speed_data[
        "Time(mins)"].sum() * 100

    Stoppage_Pareto = make_subplots(
        specs=[[{"secondary_y": True}]]
    )

    Stoppage_Pareto.add_trace(
        go.Scatter(
            x=stoppage_data['Stoppage_Name'],
            y=stoppage_data['cumpercentage'].round(decimals=2),
            name="Cummulative Percentage",
        ), secondary_y=True, )

    Stoppage_Pareto.add_trace(
        go.Bar(
            x=stoppage_data['Stoppage_Name'],
            y=stoppage_data['Time(mins)'],
            name="Total time of Occurence(mins)",
            marker={'color': 'red'},
            width=0.2,
        ), secondary_y=False, )

    Stoppage_Pareto.update_layout(
        title_text='Pareto Chart of Stoppage till Today',
        legend=dict(orientation="h", xanchor="center", yanchor="top", x=0.5, y=-1),
        hovermode="x unified", plot_bgcolor='white', xaxis=dict(type="category"), xaxis_tickangle=-90
    )

    Stoppage_Pareto.update_xaxes(ticks='outside', showline=True, linewidth=2, linecolor='black')

    Stoppage_Pareto.update_yaxes(title_text="<b>Total time of Occurence(mins)</b>", secondary_y=False,
                                 ticks='outside', showline=True, linewidth=2, linecolor='black', )

    Stoppage_Pareto.update_yaxes(title_text="<b>Cummulative Percentage</b>", secondary_y=True,
                                 ticks='outside', showline=True, linewidth=2, linecolor='black', autorange=False,
                                 range=[0, 110])

    ########
    Slow_Speed_Pareto = make_subplots(
        specs=[[{"secondary_y": True}]]
    )

    Slow_Speed_Pareto.add_trace(
        go.Scatter(
            x=slow_speed_data['Slow_Speed_Reason'],
            y=slow_speed_data['cumpercentage'].round(decimals=2),
            name="Cummulative Percentage",
        ), secondary_y=True, )

    Slow_Speed_Pareto.add_trace(
        go.Bar(
            x=slow_speed_data['Slow_Speed_Reason'],
            y=slow_speed_data['Time(mins)'],
            width=0.2,
            name="Total time of Occurence(mins)",
            marker={'color': 'red'},
        ), secondary_y=False, )

    Slow_Speed_Pareto.update_layout(
        title_text='Pareto Chart of Slow Speed till Today',
        legend=dict(orientation="h", xanchor="center", yanchor="top", x=0.5, y=-1),
        hovermode="x unified", plot_bgcolor='white', xaxis=dict(type="category"), xaxis_tickangle=-90
    )

    Slow_Speed_Pareto.update_xaxes(ticks='outside', showline=True, linewidth=2, linecolor='black')

    Slow_Speed_Pareto.update_yaxes(title_text="<b>Total time of Occurence(mins)</b>", secondary_y=False,
                                   ticks='outside', showline=True, linewidth=2, linecolor='black', )

    Slow_Speed_Pareto.update_yaxes(title_text="<b>Cummulative Percentage</b>", secondary_y=True,
                                   ticks='outside', showline=True, linewidth=2, linecolor='black', autorange=False,
                                   range=[0, 110])

    if Running_Idle_Selector == "Running":
        df1 = df1.loc[df1['RunStatus'] == 1]
    elif Running_Idle_Selector == "Idle":
        df1 = df1.loc[df1['RunStatus'] == 0]

    pie_chart = px.pie(
        data_frame=df_graph,
        names='MachineRunStatus',
        values='Time(mins)',
        color='MachineRunStatus',
        color_discrete_map={"Running": "green", "Idle": "red"},
        hole=0.3,

    )
    pie_chart.update_traces(textposition='outside', textinfo='percent+label',
                            marker=dict(line=dict(color='#000000', width=2)),
                            pull=[0, 0.2], opacity=0.9, rotation=180, hoverinfo="none")

    pie_chart.update_layout(
        title_text='Utilisation',
        legend=dict(orientation="h", xanchor="center", yanchor="top", x=0.5, y=-0.3),
        plot_bgcolor='white', margin=dict(t=10, b=0, l=0, r=0)
    )

    df_table = df1[['Date_Time', 'MachineRunStatus', 'shift', 'Production_Order', 'FabricSpeed', 'POLength']]
    df_table.rename(columns={"shift": "Shift", "FabricSpeed": "FabricSpeed(mtr/min)", "POLength": "POLength(mtr)"},
                    inplace=True)

    fig = make_subplots(
        specs=[[{"secondary_y": True}]]
    )

    fig.add_trace(
        go.Scattergl(x=df1['Date_Time'].astype(str), y=df1['FabricSpeed'], name='Fabric Speed',
                     hovertext=list(df1['MachineRunStatus'])), secondary_y=False,
    )

    fig.add_trace(
        go.Scattergl(x=df1['Date_Time'].astype(str), y=df1['POLength'], name='Fabric Length',
                     hovertext=list(df1['Production_Order'].astype(str) + ' ' + df1['Run_Category'].astype(str))), secondary_y=True,
    )

    fig.update_layout(
        title_text='Speed & Length of P.O. w.r.t. Date-Time',
        legend=dict(orientation="h", xanchor="center", yanchor="top", x=0.5, y=-1),
        hovermode="x unified", plot_bgcolor='white', xaxis=dict(type="category"),
    )

    fig.update_xaxes(ticks='outside', showline=True, linewidth=2, linecolor='black')

    fig.update_yaxes(title_text="<b>Fabric Speed</b>", secondary_y=False,
                     ticks='outside', showline=True, linewidth=2, linecolor='black')

    fig.update_yaxes(title_text="<b>Fabric Length</b>", secondary_y=True,
                     ticks='outside', showline=True, linewidth=2, linecolor='black')

    fig2 = make_subplots(
        specs=[[{"secondary_y": True}]]
    )

    fig2.add_trace(
        go.Scattergl(x=df1['Date_Time'].astype(str), y=df1['AirFlow'], name='Air Flow',
                     hovertext=list(df1['MachineRunStatus'])), secondary_y=False,
    )

    fig2.add_trace(
        go.Scattergl(x=df1['Date_Time'].astype(str), y=df1['WaterFlow'], name='Water Flow',
                     hovertext=list(df1['Production_Order'].astype(str) + ' ' + df1['Run_Category'].astype(str))), secondary_y=True,
    )

    fig2.update_layout(
        title_text='Air & Water Flow of P.O. w.r.t. Date-Time',
        legend=dict(orientation="h", xanchor="center", yanchor="top", x=0.5, y=-1),
        hovermode="x unified", plot_bgcolor='white', xaxis=dict(type="category")
    )

    fig2.update_xaxes(ticks='outside', showline=True, linewidth=2, linecolor='black')

    fig2.update_yaxes(title_text="<b>Air Flow</b>", secondary_y=False,
                      ticks='outside', showline=True, linewidth=2, linecolor='black')

    fig2.update_yaxes(title_text="<b>Water Flow</b>", secondary_y=True,
                      ticks='outside', showline=True, linewidth=2, linecolor='black')

    fig3 = make_subplots(
        specs=[[{"secondary_y": True}]]
    )

    fig3.add_trace(
        go.Scattergl(x=df1['Date_Time'].astype(str), y=df1['ThermicFlow'], name='Thermic Oil Flow',
                     hovertext=list(df1['MachineRunStatus'])), secondary_y=False,
    )

    fig3.add_trace(
        go.Scattergl(x=df1['Date_Time'].astype(str), y=df1['EnergyKW'], name='Energy Consumption',
                     hovertext=list(df1['Production_Order'].astype(str) + ' ' + df1['Run_Category'].astype(str))), secondary_y=True,
    )

    fig3.update_layout(
        title_text='Thermic Oil Flow & Energy Consumption of P.O. w.r.t. Date-Time',
        legend=dict(orientation="h", xanchor="center", yanchor="top", x=0.5, y=-1),
        hovermode="x unified", plot_bgcolor='white', xaxis=dict(type="category")
    )

    fig3.update_xaxes(ticks='outside', showline=True, linewidth=2, linecolor='black')

    fig3.update_yaxes(title_text="<b>Thermic Oil Flow</b>", secondary_y=False,
                      ticks='outside', showline=True, linewidth=2, linecolor='black')

    fig3.update_yaxes(title_text="<b>Energy Consumption</b>", secondary_y=True,
                      ticks='outside', showline=True, linewidth=2, linecolor='black')

    air_totaliser = go.Figure(data=[
        go.Bar(name='While Machine Running', x=['Air Consumption'],
               y=[df_graph[df_graph['RunStatus'] == 1]['AirFlow'].sum()], hovertemplate='%{y:.2f}<extra></extra>'),
        go.Bar(name='While Machine Not Running', x=['Air Consumption'],
               y=[df_graph[df_graph['RunStatus'] == 0]['AirFlow'].sum()], hovertemplate='%{y:.2f}<extra></extra>')
    ])

    # Change the bar mode
    air_totaliser.update_layout(barmode='stack',
                                legend=dict(orientation="h", xanchor="center", yanchor="top", x=0.5, y=-0.3)
                                , plot_bgcolor='white')

    air_totaliser.update_xaxes(ticks='outside', showline=True, linewidth=2, linecolor='black')

    air_totaliser.update_yaxes(ticks='outside', showline=True, linewidth=2, linecolor='black')

    water_totaliser = go.Figure(data=[
        go.Bar(name='While Machine Running', x=['Water Consumption'],
               y=[df_graph[df_graph['RunStatus'] == 1]['WaterFlow'].sum()], hovertemplate='%{y:.2f}<extra></extra>'),
        go.Bar(name='While Machine Not Running', x=['Water Consumption'],
               y=[df_graph[df_graph['RunStatus'] == 0]['WaterFlow'].sum()], hovertemplate='%{y:.2f}<extra></extra>')
    ])
    # Change the bar mode
    water_totaliser.update_layout(barmode='stack',
                                  legend=dict(orientation="h", xanchor="center", yanchor="top", x=0.5, y=-0.3)
                                  , plot_bgcolor='white')

    water_totaliser.update_xaxes(ticks='outside', showline=True, linewidth=2, linecolor='black')
    water_totaliser.update_yaxes(ticks='outside', showline=True, linewidth=2, linecolor='black')

    thermic_totaliser = go.Figure(data=[
        go.Bar(name='While Machine Running', x=['Thermic Consumption'],
               y=[df_graph[df_graph['RunStatus'] == 1]['ThermicFlow'].sum()],
               hovertemplate='%{y:.2f}<extra></extra>'),
        go.Bar(name='While Machine Not Running', x=['Thermic Consumption'],
               y=[df_graph[df_graph['RunStatus'] == 0]['ThermicFlow'].sum()], hovertemplate='%{y:.2f}<extra></extra>')
    ])
    # Change the bar mode
    thermic_totaliser.update_layout(barmode='stack',
                                    legend=dict(orientation="h", xanchor="center", yanchor="top", x=0.5, y=-0.3)
                                    , plot_bgcolor='white')

    thermic_totaliser.update_xaxes(ticks='outside', showline=True, linewidth=2, linecolor='black')
    thermic_totaliser.update_yaxes(ticks='outside', showline=True, linewidth=2, linecolor='black')

    energy_totaliser = go.Figure(data=[
        go.Bar(name='While Machine Running', x=['Energy Consumption'],
               y=[df_graph[df_graph['RunStatus'] == 1]['EnergyKW'].sum()], hovertemplate='%{y:.2f}<extra></extra>'),
        go.Bar(name='While Machine Not Running', x=['Energy Consumption'],
               y=[df_graph[df_graph['RunStatus'] == 0]['EnergyKW'].sum()], hovertemplate='%{y:.2f}<extra></extra>')
    ])
    # Change the bar mode
    energy_totaliser.update_layout(barmode='stack',
                                   legend=dict(orientation="h", xanchor="center", yanchor="top", x=0.5, y=-0.3)
                                   , plot_bgcolor='white')

    energy_totaliser.update_xaxes(ticks='outside', showline=True, linewidth=2, linecolor='black')
    energy_totaliser.update_yaxes(ticks='outside', showline=True, linewidth=2, linecolor='black')

    return df_table.round(decimals=2).to_dict('records'), [{"name": i, "id": i} for i in
                                                           df_table.columns], fig, fig2, fig3, pie_chart, air_totaliser, water_totaliser, thermic_totaliser, energy_totaliser, Stoppage_Pareto, Slow_Speed_Pareto, speed, total_length, total_stoppage_time, last_stoppage_time, last_stoppage_name, machine_run_status, current_po, stoppage_string, po_based_data.round(
        decimals=2).to_dict('records'), [{"name": i, "id": i} for i in po_based_data.columns]


if __name__ == '__main__':
    app.run_server(debug=True)
4

So the dcc.interval() didn’t work for you?

5

Hi @adamschroeder
Thanks for your reply.
First of all, thanks for your YouTube videos, it had helped me alot to learn about dash.
Yes the dcc.interval didn’t work as it was updating the graphs but not the data which was fetched from SQL.

6

Oh. Sorry. Not sure, then.
FYI, this won’t help solve your current problem, but since you work with big data, I thought you might be interested in this:
Dash with Vaex

7

Hi @adamschroeder
Thanks for recommending about Vaex. I’ll definitely gonna try it.
And as you told me in the inbox, I’m definitely gonna re-post all this with a code having a shorter example.

9

Use cache.memoize(timeout=30) decorator from flask caching