Hi Dash Community
I’m trying to build an app which currently connects with the sql server database (only available on 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.
Main challenge I’m facing is the live updation of my graph data as you can see in the last callback all the filteration on the basis of dropdowns is being done and the grahs gets updated on the basis of that data. The filtered data can still be of 100000 lines. The process is slow too sometimes but that’s the secondary issue . Primary one is that my graph data must get updated every 30 seconds.
That means whole dataframe must get updated every 30 seconds.
Explaining further my SQL database is fetching data from a machine every 30 seconds. I wanna show that update on my graph alongwith the previous values of the same date.
In my original data I’m updating 10 graphs from that data. Also some text data is also being displayed in that original app which i want to get updated every 30 seconds.
While using dcc.interval it’s updating the graphs after every 30 seconds but the data remains same. The latest data is not being fetched from the source.
So in simple words, the issue I’m facing is I’m not able to refresh my data displayed by my graphs in real time. The data is being fetched from a sql database.
import dash
import dash_core_components as dcc
import dash_html_components as html
import pandas as pd
import pyodbc
import plotly.graph_objects as go
from dash.dependencies import Input, Output
from plotly.subplots import make_subplots
import flask
from flask_caching import Cache
import os
import numpy as np
server = flask.Flask(__name__)
app = dash.Dash(__name__, 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)
#my real code contains 10 lines of codes for initial calculations which takes more time
df.fillna(0, inplace=True)
df['Date_Time'] = pd.to_datetime(df['shiftDate'].str[:10] + " " + df['shiftTime'].str[:8])
df['Run_Category'] = np.where(df['TB'] == 1, "Trial ", np.where(df['TB'] == 2, "Bulk ", np.nan))
return df #i chose returning original dataframe as in json form it was showing memory error
def dataframe():
return query_data()
df = dataframe() # this was done because
# if i was putting query_data() in all
#the three callbacks then it was taking alot of time to load
app.layout = html.Div([
dcc.Interval(id='refresh', interval=30000),
dcc.Dropdown(id="Date",
options=[{"label": i, "value": i} for i in df['shiftDate'].unique()
],
multi=False,
),
dcc.Dropdown(id="select_PO",
multi=False,
),
dcc.Dropdown(id="select_RunCategory",
multi=False,
),
dcc.Graph(id='Speed_Length_Graph', figure={}),
])
@app.callback([Output(component_id="select_PO", component_property="options"),
],
[Input(component_id="Date", component_property="value")
])
def update_dropdown(Date_value):
if Date_value is not None:
return_PO = df.loc[df['shiftDate'] == Date_value]['ProductionOrder'].unique()
else:
return_PO = df['ProductionOrder'].unique()
return [[{"label": i, "value": i} for i in return_PO]]
@app.callback(
[
Output(component_id="select_RunCategory", component_property="options")],
[
Input(component_id="Date", component_property="value"),
Input(component_id="select_PO", component_property="value"),
]
)
def update_run_category(Date_value, PO_Value):
if Date_value is not None:
if PO_Value is not None:
return_run_category = df.loc[(df['shiftDate'] == Date_value) & (df['ProductionOrder'] == 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['ProductionOrder'] == 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="Speed_Length_Graph", component_property="figure"),
],
[
Input(component_id="Date", component_property="value"),
Input(component_id="select_PO", component_property="value"),
Input(component_id="select_RunCategory", component_property="value"),
Input(component_id="refresh", component_property="n_intervals")
])
def update_graph(Date_value, PO_Number, Run_Category, n):
# To filter df w.r.t. dropdown values
if Date_value is not None:
if PO_Number is not None:
if Run_Category is not None:
df1 = df.loc[(df['shiftDate'] == Date_value) & (df['ProductionOrder'] == PO_Number) & (
df['Run_Category'] == Run_Category)]
else:
df1 = df.loc[(df['shiftDate'] == Date_value) & (df['ProductionOrder'] == PO_Number)]
else:
df1 = df.loc[df['shiftDate'] == Date_value]
else:
if PO_Number is not None:
if Run_Category is not None:
df1 = df.loc[(df['ProductionOrder'] == PO_Number) & (df['Run_Category'] == Run_Category)]
else:
df1 = df.loc[df['ProductionOrder'] == PO_Number]
else:
df1 = df.loc[df['shiftDate'] == df.loc[df.last_valid_index(), 'shiftDate']]
fig = make_subplots(
specs=[[{"secondary_y": True}]]
)
fig.add_trace(
go.Scattergl(x=df1['Date_Time'].astype(str), y=df1['FabricSpeed'], name='Fabric Speed',
), secondary_y=False,
)
fig.add_trace(
go.Scattergl(x=df1['Date_Time'].astype(str), y=df1['POLength'], name='Fabric Length',
), secondary_y=True,
)
fig.update_layout(
title_text='Speed & Length of P.O. w.r.t. Date-Time',
)
return fig
if __name__ == '__main__':
app.run_server(debug=True)
