Hello,
thanks for the reply, I did not know this approach, it is very useful.
I understand now that you need a callback to reconnect to the database and get the data, however
I am wondering if some sort of timeout in the connection could happen. I will share my code so that I can make myself clearier:
This is the setup, I learnt what caching is and tried to implement it, what I want is to have a persistent connection that every x minutes updates the data (also refreshing the data based on page refresh is fine, this was the first idea I got and I am worried I am adding too much overhead
import os
import sys
import dash_bootstrap_components as dbc
from apscheduler.schedulers.background import BackgroundScheduler
import dash
import pandas as pd
from dash import html, dcc, callback, Output, Input, ctx
from dash.dash_table import DataTable
from waitress import serve
# Set the server
server = Flask(__name__)
# Initialize the dash app object
app = dash.Dash(__name__,
server=server,
external_stylesheets=[dbc.themes.COSMO, dbc.icons.BOOTSTRAP],
pages_folder="",
meta_tags=[{"name": "viewport",
"content": "width=device-width, initial-scale=1"}],
suppress_callback_exceptions=True,
use_pages=False)
# Define caching
config = {
"DEBUG": True, # some Flask specific configs
"CACHE_TYPE": "SimpleCache", # Flask-Caching related configs
"CACHE_DEFAULT_TIMEOUT": 300 # 5 minutes of caching
}
# Configure the caching into Flask
app.server.config.from_mapping(config)
# Define the cache object
cache = Cache(app.server)
# Define app's title
app.title = 'App title'
# Create a connection to the db
db_connection = get_db_connection()
This is my ‘background cache update’:
# ----------------------- SET UP CONSTANTS AND DATA DEFINED AT THE APPLICATION START ---------------------
def load_data(connection):
"""This function extracts the data from the database"""
# Extract materials data info
materials_data = connection.execute_pandas_query(query_select_materials)
# Extract the list of unique businesses
businesses_list = connection.execute_pandas_query(query_select_business)
businesses_list = list(businesses_list.iloc[:, 0].unique())
# Extract a list of selectable materials
materials_list = list(materials_data.iloc[:, 0].unique())
# Extract a list of selectable material batches
materials_batches = list(materials_data.iloc[:, 1].unique())
# Get the data columns
data_columns = connection.execute_pandas_query(query_column_names).iloc[:, 0].to_list()
# Initialize an empty dataframe with same columns as in the db
empty_data = pd.DataFrame(columns=data_columns)
return (materials_list.copy(), materials_batches.copy(),
empty_data.copy(), materials_data.copy(), businesses_list.copy())
def update_cache(connection):
"""Function to update the cached data"""
# If the cache is empty then update the data
if cache.get('data') is None:
# Load the data from the db
mat_list, mat_batches, empty_data_table, data_table, business_list = load_data(connection)
# Cache the data
cache.set('data', data_table)
cache.set('empty_data', empty_data_table)
cache.set('materials_list', mat_list)
cache.set('materials_batches', mat_batches)
cache.set('business_list', business_list)
print("Cache updated.")
# Job scheduler configuration: in background the cache update is performed every 4 minutes
scheduler = BackgroundScheduler()
scheduler.add_job(func=lambda: update_cache(db_connection), trigger="interval", minutes=4)
scheduler.start()
# Populate the cache before starting the app
update_cache(db_connection)
And then my layout and callbacks are defined, I pass to my components the cached data, like:
dcc.dropdown(cache.get(‘cached list’))
I really don’t know if this is too much / an anti-pattern