Getting Live Data from SQL Server

Dash Python
1

Hi,

I currently have my dash app set up such that I pull data from SQL using queries. However, I find this to be very slow.

Is there a way to load the data simultaneously or live as the data is populated in the SQL database every second? Does Dash Python have any features for this that I am unaware of?

1 Like
2

I think you could use dcc.Interval and dcc.Store to make it. Something like below:

dcc.Store(id='store-data', data=[], storage_type='memory'), # 'local' or 'session'    
    dcc.Interval(id='update', n_intervals = 0, interval=1000*30)
])
    
@app.callback(Output('store-data','data'),
             [Input('update', 'n_intervals')])

def update_data(n):
    global jobs_2
    db = mysql.connector.connect(
        host="localhost",              
        user="root",            
        password="",        
        database="indeed)   

    cur = db.cursor()
    cur.execute("SELECT * FROM jobs")
    columns = [col[0] for col in mycursor.description]
    data = mycursor.fetchall()
    jobs_2 = pd.DataFrame(data, columns=columns)
    db.close()
    jobs_2.to_dict()
    jobs_2 = jobs_2[jobs_2['Max Salary'].notnull()] 
    jobs_2 = jobs_2.loc[:,['Title','Company','Rating','Location','Max Salary']]
    jobs_2['Rating'].fillna(0,inplace=True) 
    jobs_2 = jobs_2.dropna(subset=['Max Salary'])    
    jobs_2['Max Salary'] = jobs_2['Max Salary'].str.replace(',','')
    jobs_2['Type'] = jobs_2['Max Salary'].str[-5:]
    jobs_2['Type'] = jobs_2['Type'].str.replace(' ','')
    jobs_2['Max Salary'] = jobs_2['Max Salary'].str.extract('(\d+)')
    jobs_2['Max Salary'] = jobs_2['Max Salary'].astype(int)
    jobs_2['Max Salary'] = np.where((jobs_2['Type'] == "year"),(jobs_2['Max Salary']/12).round(decimals=0),jobs_2['Max Salary'])
    jobs_2['Max Salary'] = np.where((jobs_2['Type'] == "week"),(jobs_2['Max Salary']*4).round(decimals=0),jobs_2['Max Salary'])          
    jobs_2['Max Salary'] = jobs_2['Max Salary'].astype(int)
    return jobs_2.to_dict(orient='records')
    
@app.callback(
    Output('sal_location', 'figure'),
    [Input('store-data','data')
   ])

def update_graph(jobs_location):    
    jobs_location = pd.pivot_table(jobs_2,values=['Max Salary',],index=['Location'],aggfunc=np.mean)
    jobs_location = jobs_location.reset_index()

    # Fig 1
    fig_1 = go.Figure(data=[
    go.Bar(x=jobs_location['Location'],
           y=jobs_location['Max Salary'].round(decimals=2),
           width=0.45,
           text = jobs_location['Max Salary'].round(decimals=2),
           textposition='inside',
           marker_color='indianred')])
    
    fig_1.update_layout(barmode='stack')
    fig_1.update_traces(texttemplate='%{text:,}')
    fig_1.update_layout(plot_bgcolor='rgba(0,0,0,0)')
    fig_1.update_yaxes(showline=False,showgrid=False,dtick=5000,exponentformat="none",separatethousands=True)
    return fig_1

Hope this help.

2 Likes