dcc.Interval failed to update sql query

Dash Python
1

Hi, I’m planning to do a live update of the data from mysql database of storing the data from an IoT device

I saved the query in a dataframe df and first tested with time.sleep and it worked: the dataframe got updated together with the plot as well.

However applying Interval Input of Dash the update doesn’t work any more.
Got no error from running the app, but the graph doesn’t update at all

I found no sample of using dash interval with mysql, hopefully I got some luck here.
Thank you for your time

Below is the code I’ve used:*************

import dash
import dash_html_components as html
import dash_core_components as dcc
import pandas as pd
from dash.dependencies import Input, Output
import plotly.plotly as py
import plotly.graph_objs as go
import plotly
import pymysql
from datetime import datetime
import time

app = dash.Dash()

db = pymysql.connect()

#create base dataframe #####################################################

sql=""“select * from IoT_project.IoT WHERE Datetime >= ( CURDATE() - INTERVAL 3 DAY )
AND Node_ID = “********” ORDER BY Datetime ASC;”""

sql2=""“select * from IoT_project.IoT WHERE Datetime >= ( CURDATE() - INTERVAL 3 DAY )
AND Node_ID = “********” ORDER BY Datetime DESC LIMIT 1;”""

df = pd.read_sql(sql, db)
df[‘Datetime’] = pd.to_datetime(df[‘Datetime’])
df = df.sort_values(by=[‘Datetime’], ascending=True)

#####################################################

#layout ################################################
app.layout = html.Div([
dcc.Checklist(
id=‘selection’,
options=[
{‘label’: ‘Temperature’, ‘value’: ‘Temperature’},
{‘label’: ‘Humidity’, ‘value’: ‘Humidity’}
],
values=[‘Temperature’,‘Humidity’]
),
dcc.Graph(id=‘graph’),
dcc.Interval(
id=‘my-interval’,
interval=10*1000, # in milliseconds
n_intervals=0
)
])

##########################################################

#callback ############################################
@app.callback(
dash.dependencies.Output(‘graph’, ‘figure’),
[dash.dependencies.Input(‘selection’, ‘values’),
dash.dependencies.Input(‘my-interval’, ‘n_intervals’)])
######################################################

#return function

def update_output_div(columns, interval):
global df

trace1=go.Scatter(x=df['Datetime'], y=df['Temperature'], name='Temperature', 
                  mode = 'lines', marker=dict(color = 'red'))
trace2=go.Scatter(x=df['Datetime'], y=df['Humidity'], name='Humidity', 
                  mode = 'lines', marker=dict(color = 'blue'), yaxis='y2')
trace3=go.Scatter(x=df['Datetime'], y=df['Humidity'], name='Humidity', 
                  mode = 'lines', marker=dict(color = 'blue'))
y1=dict(
    title='Temperature (°C)',
    #range=[12,50]
)
y2=dict(
    title='Humidity (%)',

    range=[30,90],
    titlefont=dict(
        color='black'
    ),
    tickfont=dict(
        color='black'
    ),
    overlaying = "y",
    side='right'
)
y3=dict(
    title='Humidity (%)',
    range=[30,90],
    side='right'
)
if all(x in columns for x in ['Temperature', 'Humidity']):
    traces = [trace1,trace2]
    yaxis=y1
    yaxis2=y2
elif 'Temperature' in columns:
    traces = [trace1]
    yaxis=y1
    yaxis2=None
elif 'Humidity' in columns and 'Temperature' not in columns:
    traces = [trace3]
    yaxis=y3
    yaxis2=None
else:
    traces = []
    yaxis=None
    yaxis2=None

layout=go.Layout(
title='Temp and humidity of ******* (last day)',
yaxis=yaxis,
yaxis2=yaxis2
)

df = df.append(pd.read_sql(sql2, db))
df = df.drop_duplicates()
df = df.reset_index(drop=True)

return {
    'data': traces,
    'layout': layout}
2

I believe the error lies in the way you have structured your code.
Right now, it looks to me like you make a single SQL query and make a dataframe
Then every 10*1000 millisecond you app takes the data from this static dataframe, look what is inside it, and plots it.
You need to make your SQL query dependent on the interval component.

Personally, I would make your Query + data sort a function and make your callback call it every dcc.Interval.
Something like:

def grabbing_me_sum_data():
   sql = "select * from database"
   sql2 = "select some other stuff from a database"
   df = collect stuff

@app.callback(
   Output('graph', 'figure'),
   [Input('my-interval', 'n_intervals')]
def update_stuff(n)
   data = grabbing_me_sum_data()
   >all your plotting stuff goes here...

I hope it make sense, I might have had a few cups of coffee over the top today. :sweat_smile:

1 Like