Callback taking too long with SQL query

Dash Python
1

I have a dash app that calls to a sql database. The database sometimes takes too long to generate the information I need. My boss has asked me to implement a two minute allowance. If the sql server decides to take longer than two minutes, I will need to put an alert that it’s busy and to try again later. I also need to make sure the callback then stops.

Here is my callback.

@app.callback(
    Output('store', 'data'),
    Output('error-alert', 'is_open'),
    Output('graph-redirect', 'pathname'),
    Input('data-button', 'n_clicks'),
    State('engine-drop', 'value'),
    State('start-date', 'value'),
    State('region-drop', 'value'),
    State('report-options', 'value'),
    State('loading', 'children'),
    prevent_initial_call=True,
    background=True,
    running=[
        (Output('data-button', 'disabled'), True, False),
        # (Output('loading', 'visibility'), True, False),
        (Output('loading', 'children'), dls.Scale(), ''),
    ],
    
)
def query_data(n1, engine, date, region, report, loading):
    if loading is None:
            return dash.no_update, dash.no_update, dash.no_update

    if n1:
        if engine == None or region == None:
            store = {}
            return store, True, dash.no_update
        else: 
            data = sqlc.get_data_for_app(region=region, engine=engine, end_date=date)
            data = wr.get_past_year(df=data, end_date=date)
            store = {"df": data.to_dict('records'), "date": date, "engine": engine, "report": report}
            return store, False, '/graph'

    return dash.no_update, dash.no_update, dash.no_update,

The line where it calls to the sql database is: data = sqlc.get_data_for_app(region=region, engine=engine, end_date=date)

I’ve attempted to use concurrent.features.ThreadPoolExecutor, but it wasn’t really working.

Any thoughts?

2

Have you looked into Dash’s Background callbacks? This is the kind of use-case they are intended for.

3

Yeah I have and I use one for a loading spinner while the query runs actually. But how can I make it work with a timeout situation?

4

hmmm that is a good question.

just based on looking through docs, there may not be a super convenient way to do that.

there is the cancel param to the callback decorator, which will cause backgrounded callbacks to be cancelled when a target attribute in the layout changes. that seems to be intended to be used for interactive user supplied events to cancel though, as the example in the dash docs on Background callbacks shows. I wonder if you could wire up the dcc.Interval component to do that automatically? the interval component isn’t really designed to be used as a countdown timer, but you might be able to configure it to behave like one with the right params.

1 Like
5

You could possible use a clientside_callback that is triggered off of the same click and if it doesn’t come back, it will click a button that could also be triggered by the user.

That way, the user could also cancel it if they want to.

6

I ended up coming up with a solution that used threading.

I have my function that’s called get_engine_data and then created this:

def get_engine_data_with_timeout(timeout, engine, start_date, end_date):
    sql_engine = sa.create_engine(__connection_string)

    df = None
    def query_thread():
        nonlocal df
        df = get_engine_data(engine, start_date, end_date, sql_engine)

    thread = threading.Thread(target=query_thread)
    thread.start()
    thread.join(timeout=timeout)

    if thread.is_alive():
        raise TimeoutError(f"Query timed out after {timeout} seconds")

    return df

and then implemented it with a try except block in my script!

1 Like
7

For my longer queries, I have them associated with stored_procs and I return the query to a table, instead of trying to return it directly.

Especially if you are not updating the information per user, or very often, (especially if the underlying data doesnt update too frequently)