Answer a question

I have a dash (flask) app that runs on Azure App Services where I ask the user for input, then they click "Submit" and my code runs an SQL query. It works almost all of the time but sometimes the query fails to connect (Communication link failure). When I restart the App Service it starts working again.

Is there a way to utilize a Try & Except clause to try and re-establish the SQL connection or something? How would my code look if I wanted to try and implement that kind of logic? I'm struggling to figure out the right way to write the syntax for it.

@dash_app.callback(
    Output('hidden-dataframe', 'children'),
    [Input('submit-button', 'n_clicks')],
    [State('company-dropdown', 'value')])
def select_company(n_clicks, value):
    if value is None:
        raise PreventUpdate
    else:
        x = value
        if n_clicks:
            from datetime import datetime, timedelta
            start_date = (datetime.today() - timedelta(29)).strftime('%Y-%m-%d')
            end_date = datetime.today().strftime('%Y-%m-%d')

            cnxn = pyodbc.connect(driver='{OBDC Driver 17 for SQL Server}',host,database,trusted_connection,user,password)
            query = f"SELECT data FROM table WHERE ..."

            df = pd.read_sql_query(query, cnxn)

   return df.to_json(date_format='iso', orient='split')

Here's the error code that I get when the connection fails:

Answers

For a one-retry shot at re-connecting, replace

    df = pd.read_sql_query(query, cnxn)

with

    try:
        df = pd.read_sql_query(query, cnxn)
    except pyodbc.OperationalError:
        cnxn.close()
        cnxn = pyodbc.connect(driver='{OBDC Driver 17 for SQL Server}',host,database,trusted_connection,user,password)
        df = pd.read_sql_query(query, cnxn)

This has worked for me in azure app service/flask. Using the cursor directly though, not pandas so your specific error to handle may be different.