Show a loading symbol while gathering data

I just sent this code but now have a different issue I’m trying to solve.

I am looking for a way to implement a loading symbol while my data is being gathered from a SQL database. I have a loading state on the page that this callback redirects to, but what takes the longest is this line in the callback function:

data = sqlc.get_data_for_app(region=region, engine=engine, end_date=date)

So I would like a loading symbol while that works. Any thoughts?

Full callback below.

layout = html.Div([

       ...

        dbc.Row([
            html.Center([
                dbc.Button(
                    'Generate Graph',
                    id='data-button',
                    n_clicks=0,
                    color='secondary',
                )
            ])
        ]),

       ...

        dcc.Location(id='graph-redirect', pathname='/')
])


@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'),
)
def query_data(n1, engine, date, region, report):
    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,

Hello @bpolasek,

The easiest way would be to use a background callback. :slight_smile:

Check this out too:

So I need to use Celery and Redis when it comes to production?

Nope, diskcache, which is a file system one should suffice.

You just need something to tell the client that they should check for updates every second, instead of waiting until they receive a full response.

This is also a nice way to avoid the typical timeout of 30 seconds for really long callbacks. Keeping in mind that the entire round trip is taken into account.

Okay as soon as I installed dash[diskcache] it started throwing an error.

Could it be because I have other databases connected? Including a sqlite one?

I also undid all my changes and it’s still unhappy :frowning:

(Monthly_Report) C:\Projects\Monthly_Report>C:/Users/bp15630/.virtualenvs/Monthly_Report-sxQLaxjL/Scripts/python.exe c:/Projects/Monthly_Report/app.py
2.6.0
Traceback (most recent call last):
  File "C:\Users\bp15630\.virtualenvs\Monthly_Report-sxQLaxjL\lib\site-packages\sqlalchemy\engine\base.py", line 1410, in execute      
    meth = statement._execute_on_connection
AttributeError: 'str' object has no attribute '_execute_on_connection'

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "c:\Projects\Monthly_Report\app.py", line 9, in <module>
    app = dash.Dash(__name__, use_pages=True,
  File "C:\Users\bp15630\.virtualenvs\Monthly_Report-sxQLaxjL\lib\site-packages\dash\dash.py", line 494, in __init__
    self.init_app()
  File "C:\Users\bp15630\.virtualenvs\Monthly_Report-sxQLaxjL\lib\site-packages\dash\dash.py", line 578, in init_app
    self.enable_pages()
  File "C:\Users\bp15630\.virtualenvs\Monthly_Report-sxQLaxjL\lib\site-packages\dash\dash.py", line 2044, in enable_pages
    self._import_layouts_from_pages()
  File "C:\Users\bp15630\.virtualenvs\Monthly_Report-sxQLaxjL\lib\site-packages\dash\dash.py", line 2016, in _import_layouts_from_pages
    spec.loader.exec_module(page_module)
  File "<frozen importlib._bootstrap_external>", line 883, in exec_module
  File "<frozen importlib._bootstrap>", line 241, in _call_with_frames_removed
  File "c:\Projects\Monthly_Report\pages\prev_graph.py", line 16, in <module>
    reports_df = pod.get_prev_reports()
  File "c:\Projects\Monthly_Report\src\pod_sql.py", line 275, in get_prev_reports
    reports_df = pd.read_sql_query(sql=query_reports, con=conn)
  File "C:\Users\bp15630\.virtualenvs\Monthly_Report-sxQLaxjL\lib\site-packages\pandas\io\sql.py", line 397, in read_sql_query
    return pandas_sql.read_query(
  File "C:\Users\bp15630\.virtualenvs\Monthly_Report-sxQLaxjL\lib\site-packages\pandas\io\sql.py", line 1560, in read_query
    result = self.execute(*args)
  File "C:\Users\bp15630\.virtualenvs\Monthly_Report-sxQLaxjL\lib\site-packages\pandas\io\sql.py", line 1405, in execute
    return self.connectable.execution_options().execute(*args, **kwargs)
  File "C:\Users\bp15630\.virtualenvs\Monthly_Report-sxQLaxjL\lib\site-packages\sqlalchemy\engine\base.py", line 1412, in execute
    raise exc.ObjectNotExecutableError(statement) from err
sqlalchemy.exc.ObjectNotExecutableError: Not an executable object: '\n            SELECT [ID]\n                ,[ReportName]\n            FROM [POD].[dbo].[MonReport_Report]\n        '

No, there shouldnt be an issue with that.

Can you post your whole example of what you have going on?

When I wrapped my sql query with text() from sqlalchemy, it worked but now I have a host of other issues.

This is what I was attempting to do on my home page. It’s a lot going on.

import dash
from dash import register_page, callback, Input, Output, State, dcc, html, DiskcacheManager
import dash_bootstrap_components as dbc
from datetime import datetime, timedelta
import logging
from dateutil import relativedelta
import os
import dash_loading_spinners as dls
import pandas as pd
import src.sql_connections as sqlc
import src.wrangler as wr
import src.sqlite_functions as sqlf

register_page(__name__, path='/')

df = sqlf.query_engines()

current_day = datetime.now() - timedelta(hours=6)

day = current_day.strftime('%Y-%m-%d')
time = current_day.strftime('%H:%M')
current_day_and_time = f'{day}T{time}'

current_month = datetime.now().strftime('%b-%Y')
next_month = datetime.now() + relativedelta.relativedelta(months=1)
next_month = next_month.strftime('%b-%Y')

layout = html.Div([

    dbc.Container([
        # Region and Engine Dropdowns
        dbc.Row([
            html.Center([
                html.H3('Select a Region and then an Engine')
            ],
            style={'margin-top': '30px'})
        ]),
        dbc.Row([
            html.Center([
                dcc.Dropdown(
                    id='region-drop',
                    options=df.region.unique(),
                    placeholder='Region',
                    style={'width': '160px', 'text-align': 'left'}),
                dcc.Dropdown(
                    id='engine-drop',
                    options=[],
                    placeholder='Engine',
                    style={'width': '160px', 'text-align': 'left'})
            ],
            style={'display': 'flex', 'justify-content': 'center', 'gap': '100px', 'margin-top': '20px'})
        ]),

        # Report Info
        dbc.Row([
            html.Div([
                html.Center(
                    html.H3(
                        'What meeting is this for?',
                        style={'margin-bottom': '20px', 'margin-top': '50px'}),),
                dbc.RadioItems(
                    options=[
                        {'label': current_month, 'value': current_month},
                        {'label': next_month, 'value': next_month},
                    ],
                    value=current_month,
                    id='report-options',
                    inline=True,
                    style={'display': 'flex', 'justify-content': 'center', 'gap': '50px'}
                )
            ],
            style={"margin-top": "20px", "margin-bottom": "20px"})
        ]),

        # Date selection
        dbc.Row([
            html.Center([
                html.H3('Choose an End Date'),
                html.P('(Gets data going back a year from chosen date.)')
            ])
        ],
        style={'margin-top': '50px'}),

        dbc.Row([
            html.Center([
                dcc.Input(
                    type='datetime-local',
                    step='60',
                    min='2000-01-01T00:00',
                    max=datetime.now(),
                    value=datetime.now(),
                    id='start-date'
                )
            ])
        ],
        style={'margin-top': '10px'}),

        dbc.Row([
            html.Center([
                dbc.Button(
                    'Generate Graph',
                    id='data-button',
                    n_clicks=0,
                    color='secondary',
                    external_link=True,
                ),
            ]),
            html.Div(id='loading')
        ],
        style={'margin-top': '20px', 'margin-bottom': '30px'}),

        dbc.Row([
            html.Center([
                dbc.Alert(
                    children=["Did not select all required information."],
                    color='danger',
                    id='error-alert',
                    is_open=False,
                )
            ])
        ]),

        html.Hr(),

        dbc.Row([
            html.Center([
                dbc.Button(
                    'See Graphs for Previous Meetings',
                    id='prev-graph-button',
                    n_clicks=0,
                    color='secondary',
                    href='prev-graph',
                    outline=True
                )
            ])
        ],
        style={'margin-top': '30px'}),

        dcc.Location(id='graph-redirect', pathname='/')
    ])
])


@callback(
    Output('engine-drop', 'options'),
    Input('region-drop', 'value'),
    prevent_initial_call=True
)
def update_dropdowns(value):
    logging.info(f'Region Dropdown Selection: {value}')
    return df[df.region == value].name


@callback(
    Output('store', 'data'),
    Output('error-alert', 'is_open'),
    Output('graph-redirect', 'pathname'),
    Output('loading', 'children'),
    Input('data-button', 'n_clicks'),
    State('engine-drop', 'value'),
    State('start-date', 'value'),
    State('region-drop', 'value'),
    State('report-options', 'value'),
    background=True,
    running =[
         (Output('data-button', 'disabled'), True, False)
    ]
)
def query_data(n1, engine, date, region, report):
    if n1:
        if engine == None or region == None:
            store = {}
            return store, True, dash.no_update, dash.no_update
        else: 
            logging.info(f'Engine Selected: {engine}')
            logging.info(f'Creating report {report}')
            logging.debug('Clicked Generate Graph Button')
            logging.info(f'Year from Date: {date}')
            logging.info("Retrieving Data")
            data = sqlc.get_data_for_app(region=region, engine=engine, end_date=date)
            data = wr.get_past_year(df=data, end_date=date)
            logging.info('Saving for Graph')
            store = {"df": data.to_dict('records'), "date": date, "engine": engine, "report": report}
            logging.info('Data Saved to Store and Directed to Graph Page')
            return store, False, '/graph', dls.Scale()

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

Sounds like you downloaded some updates as well when you tried to do it.

Try this:

import dash
from dash import register_page, callback, Input, Output, State, dcc, html, DiskcacheManager
import dash_bootstrap_components as dbc
from datetime import datetime, timedelta
import logging
from dateutil import relativedelta
import os
import dash_loading_spinners as dls
import pandas as pd
import src.sql_connections as sqlc
import src.wrangler as wr
import src.sqlite_functions as sqlf

register_page(__name__, path='/')

df = sqlf.query_engines()

current_day = datetime.now() - timedelta(hours=6)

day = current_day.strftime('%Y-%m-%d')
time = current_day.strftime('%H:%M')
current_day_and_time = f'{day}T{time}'

current_month = datetime.now().strftime('%b-%Y')
next_month = datetime.now() + relativedelta.relativedelta(months=1)
next_month = next_month.strftime('%b-%Y')

layout = html.Div([

    dbc.Container([
        # Region and Engine Dropdowns
        dbc.Row([
            html.Center([
                html.H3('Select a Region and then an Engine')
            ],
            style={'margin-top': '30px'})
        ]),
        dbc.Row([
            html.Center([
                dcc.Dropdown(
                    id='region-drop',
                    options=df.region.unique(),
                    placeholder='Region',
                    style={'width': '160px', 'text-align': 'left'}),
                dcc.Dropdown(
                    id='engine-drop',
                    options=[],
                    placeholder='Engine',
                    style={'width': '160px', 'text-align': 'left'})
            ],
            style={'display': 'flex', 'justify-content': 'center', 'gap': '100px', 'margin-top': '20px'})
        ]),

        # Report Info
        dbc.Row([
            html.Div([
                html.Center(
                    html.H3(
                        'What meeting is this for?',
                        style={'margin-bottom': '20px', 'margin-top': '50px'}),),
                dbc.RadioItems(
                    options=[
                        {'label': current_month, 'value': current_month},
                        {'label': next_month, 'value': next_month},
                    ],
                    value=current_month,
                    id='report-options',
                    inline=True,
                    style={'display': 'flex', 'justify-content': 'center', 'gap': '50px'}
                )
            ],
            style={"margin-top": "20px", "margin-bottom": "20px"})
        ]),

        # Date selection
        dbc.Row([
            html.Center([
                html.H3('Choose an End Date'),
                html.P('(Gets data going back a year from chosen date.)')
            ])
        ],
        style={'margin-top': '50px'}),

        dbc.Row([
            html.Center([
                dcc.Input(
                    type='datetime-local',
                    step='60',
                    min='2000-01-01T00:00',
                    max=datetime.now(),
                    value=datetime.now(),
                    id='start-date'
                )
            ])
        ],
        style={'margin-top': '10px'}),

        dbc.Row([
            html.Center([
                dbc.Button(
                    'Generate Graph',
                    id='data-button',
                    n_clicks=0,
                    color='secondary',
                    external_link=True,
                ),
            ]),
            dls.Scale(id='loading', show_initially=True)
        ],
        style={'margin-top': '20px', 'margin-bottom': '30px'}),

        dbc.Row([
            html.Center([
                dbc.Alert(
                    children=["Did not select all required information."],
                    color='danger',
                    id='error-alert',
                    is_open=False,
                )
            ])
        ]),

        html.Hr(),

        dbc.Row([
            html.Center([
                dbc.Button(
                    'See Graphs for Previous Meetings',
                    id='prev-graph-button',
                    n_clicks=0,
                    color='secondary',
                    href='prev-graph',
                    outline=True
                )
            ])
        ],
        style={'margin-top': '30px'}),

        dcc.Location(id='graph-redirect', pathname='/')
    ])
])


@callback(
    Output('engine-drop', 'options'),
    Input('region-drop', 'value'),
    prevent_initial_call=True
)
def update_dropdowns(value):
    logging.info(f'Region Dropdown Selection: {value}')
    return df[df.region == value].name


@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'),
    background=True,
    running =[
         (Output('data-button', 'disabled'), True, False),
        (Output('loading', 'visibility'), True, False)
    ]
)
def query_data(n1, engine, date, region, report):
    if n1:
        if engine == None or region == None:
            store = {}
            return store, True, dash.no_update
        else: 
            logging.info(f'Engine Selected: {engine}')
            logging.info(f'Creating report {report}')
            logging.debug('Clicked Generate Graph Button')
            logging.info(f'Year from Date: {date}')
            logging.info("Retrieving Data")
            data = sqlc.get_data_for_app(region=region, engine=engine, end_date=date)
            data = wr.get_past_year(df=data, end_date=date)
            logging.info('Saving for Graph')
            store = {"df": data.to_dict('records'), "date": date, "engine": engine, "report": report}
            logging.info('Data Saved to Store and Directed to Graph Page')
            return store, False, '/graph'

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

Take a look at your packages, I had an issue when SQLAlchemy updated to an alpha version instead of the last stable.

Also, try updating dash to v2.8.

I’ve reverted back to a previous git commit and it’s still throwing this error. Ugh.

It has to be with your packages…

Yeah I’m comparing right now. It’s the only thing that makes sense.

I know we’re venturing out of dash territory now, but I rolled back changes from git and it’s still not working. Is that normal?

Rolling back from git doesnt do anything to your packages…

Take a look at sqlalchemy package and make sure its something like:
1.4.46 was what was working for me.

You’ll get to remove the text() wrapper too.

Yeah I have that. Here’s my pipfile

dash-bootstrap-components = "==1.3.0"
pandas = "==1.5.3"
pyodbc = "==4.0.35"
sqlalchemy = "==1.4.46"
dash-mantine-components = "==0.11.1"
scipy = "==1.10.0"
flask-sqlalchemy = "==3.0.2"
pyad = "==0.6.0"
fastapi = "==0.89.1"
uvicorn = {extras = ["standard"], version = "==0.20.0"}
connexion = {version = "==2.14.1", extras = ["swagger-ui"]}
pytest = "==7.2.1"
dash = {extras = ["testing"], version = "==2.8.0"}
pytest-mock = "==3.10.0"
gunicorn = "==20.1.0"
dash-loading-spinners = "==1.0.0"

I did just now change it all from latest version to the version in the rollbacks that had worked.

I’d drop the testing for dash.

It didn’t make a difference. It has to be sqlalchemy. I just can’t figure out why.

Hmm…

When in doubt, reinstall the venv…

I’m working on that right now. It’s taking a bit to re-upload data to sqlite. I’m wondering if when I did the disk cache that it messed with my sqlite files? So even when I was trying to go back to the old sqlalchemy it was already messed up? Or am I not understanding that.

Basically all was good until I did the disk cache.

Diskcache operates independently, unless you did something to the default settings.

It really shouldn’t have caused it to break.

Nope, nothing. I used to have my pipfile set to take the latest update. I’m recreating with hardcoded versions and will try again. Hopefully it was just the sqlalchemy update that released the other day.