SQL not updating in callback

[will]

Hi,

Firstly, congrats on Dash. It’s one of the most exciting packages I’ve seen in ages, and although I’m not an expert Python programmer, I’ve already got something working which would have been way beyond me otherwise. It’s utterly brilliant and exciting to see where it develops.

One issue though: I just tried adapting the live update example, but no matter what I try, the sql result (using pymysql) doesn’t update in the callback. I’ve tried Pandas’ method directly - same issue.

The function to get new data using satellite = Orbital('TERRA') works fine. Why would my very basic sql function not work in the same fashion?

Code:

def get_data():
    cur = db.cursor(pymysql.cursors.DictCursor)
    cur.execute(sql)
    sql_results=cur.fetchall()

    cur.close()
    return sql_results

app = dash.Dash(__name__)
app.layout = html.Div(
    html.Div([
        html.H4('TERRA Satellite Live Feed'),
        html.Div(id='live-update-text'),
        dcc.Interval(
            id='interval-component',
            interval=1*10000 # in milliseconds
        )
    ])
)

# The `dcc.Interval` component emits an event called "interval"
# every `interval` number of milliseconds.
# Subscribe to this event with the `events` argument of `app.callback`
@app.callback(Output('live-update-text', 'children'),
              events=[Event('interval-component', 'interval')])
def update_metrics():

    df = pd.DataFrame(get_data())
    lon, lat, alt = satellite.get_lonlatalt(datetime.datetime.now())
    print "lon",lon
    print "df:",df['thecount'].iloc[0]
    style = {'padding': '5px', 'fontSize': '16px'}
    return [
        html.Span('Longitude: {0}'.format(df['thecount'].iloc[0]), style=style)
    ]

update_metrics works fine. And get_data is called correctly too, but the result never changes. Related issue on Git: https://github.com/plotly/dash/issues/81

Thanks

Will

[chriddyp]

Thanks @will!

Hm, I’m not sure why the SQL querying wouldn’t work. Could you paste your SQL example code?

[will]

Hey @chriddyp - thanks, but fixed this morning! Just occurred to me; the db connection (db) needs to be opened/closed/initiated in the function, otherwise the call is somehow cached. Hope this helps someone else.

[kqureshi]

Thanks Will. Can you provide a working example of

" Just occurred to me; the db connection (db) needs to be opened/closed/initiated in the function, otherwise the call is somehow cached"

Same issue here.

[will]

Something like this

def get_data(sql,params=''):
    db = pymysql.connect(
    host = host,
    user = user,
    passwd = passwd,
    db = db_name,
    port = port
    )

    cur = db.cursor(pymysql.cursors.DictCursor)
    if params and params !='':
        cur.execute(sql,(params))
    else:
        cur.execute(sql)

    sql_results=cur.fetchall()
    df=pd.DataFrame(sql_results)
    return df

Then call get_data(sql) from inside your callback. Technically you should close the cursor too in the function: cur.close()

[kqureshi]

Thanks Will. Most of my callback functions look like the following, with the get_data typically called separately before the app=dash.Dash() call., or in a return function

@app.callback(Output(‘live-update-graph’, ‘figure’),
events=[Event(‘interval-component’, ‘interval’)])

Can you provide an example of the cal back with that get_data

[will]

with the get_data typically called separately before the app=dash.Dash() call

In this case, the data won’t be ‘live’ - it will return the data once and not update.

[will]

So @kqureshi, you need to ensure your get_data function is inside your callback. From what I understand, this is the process:

1. Start app
2. Get any “starting” or default data that you need to initialise the app.
3. Use a get_data() or similar function inside a callback to return live or fresh data.

That’s roughly what I’m doing. No. 2 is the big problem for me, and sharing data that I’ve manipulated in step 3 with other callbacks. I guess we all need to read up on Flask to understand the best practices - thoughts welcome from others though!

Will

[chriddyp]

Thanks Will. Most of my callback functions look like the following, with the get_data typically called separately before the app=dash.Dash() call., or in a return function

Hey @kqureshi - would you mind opening up a separate thread describing the issue that you’re having? It seems like it’s not related to the original issue that @will posted (and solved!). Thanks

[oligosoma]

Thanks Will!!! DB connection needs to be in the function: this is the key piece of information that I needed.

[reachkarthikt]

Hello,
I have a similar usecase - but just that i have several figures & text in my layout - which should get the same data from the single dataframe.

Right now, i am making this work - by re-creating the dataframe inside the function under every callback - even though its the same initial dataset, i am ending up querying the database many times.

Is there a way to avoid this??

Thanks
Karthik

[nedned]

If you’re seeing the same queries being made against the dataframe repeatedly, then you can cache the results of callbacks, avoiding the need to hit the database again for queries that have already been performed. This is described in the Dash docs: https://plot.ly/dash/performance

[chriddyp]

Right now, i am making this work - by re-creating the dataframe inside the function under every callback - even though its the same initial dataset, i am ending up querying the database many times.

Also see https://plot.ly/dash/sharing-data-between-callbacks

[anlopezl]

Thank you for that, @will, I had the same problem (did not closed the connection) but this fixed it