Database does not reload on app

🎈 Using Streamlit
1

Summary

Dear all,

I have recently got started with streamlit and am very excited about its capabilities. I have built my first data app and have come across an issue I haven’t been able to solve myself. My data app is connected to a sql database that for the time being we are storing on Github. I have scheduled daily local runs to request data from APIs and update the database with automatic pushes to Git and Github. However, the app does not automatically reload the database and I have to manually reboot it to sync with the most recent updates on Github. However, I should emphasize that the changes I make to the app itself (not to the database) and push to Github are displayed right away on the deployed app.

For performance issues (not query the database at every click) I am storing the query at the cache. Do you think that this should be the problem? If so, is there any work around this?

Steps to reproduce (using functions that are hosted and other python package we created to manage the database)

Code snippet:

import pandas as pd
import warnings
warnings.filterwarnings('ignore')
import SMARTControl as sc
import streamlit as st
import warnings
warnings.filterwarnings('ignore')
import utils_dashboard as utl
import plotly.express as px
from plotly.subplots import make_subplots


@st.cache (allow_output_mutation=True)  # No need for TTL this time. It's static data :)
def Querying():
    database_fn = 'Data/Database.db' 
    Get = sc.queries.Get(database_fn) # Instantiating the variable
    
    # First and last date
    start, end = Get.StartEndDate ()

    
    #Hydraulic heads
    Get.LongTimeSeries(0)
    df = Get.LongTimeSeries_df.copy()
    df = df.set_index('Date')
    
    #River data
    Get.ShortTimeSeries(7, 'RG')
    r_df = Get.ShortTimeSeries_df.copy()
    r_df = r_df.set_index('Date')
    
    g_df = df.groupby(['Name', pd.Grouper(freq='D')])['Value'].mean().to_frame().reset_index()
    g_df = g_df.rename (columns = {'Name' : 'MonitoringPointName'})

    gr_df = r_df.groupby(['MonitoringPointName', pd.Grouper(freq='D')])['Value'].mean().to_frame().reset_index()
    
    return Get, g_df, gr_df

Get, g_df, gr_df = Querying()

def iTS_ ():
    
    fig = make_subplots(rows=1, cols=1)
    
    #plot river gage data
    line_plot = px.line(
        gr_df,
        x='Date', y='Value',
        color = 'MonitoringPointName',
        color_discrete_sequence = ['green']
        )
    
    fig.add_trace(line_plot['data'][0], row=1, col=1)
    

    g1_df = g_df [
        g_df.MonitoringPointName.isin (wells_wid)
    ].reset_index(drop = True)  

    
    #plot wells
    scatter_plot = px.scatter(
        g1_df,
        x='Date', y='Value',
        color = 'MonitoringPointName',
        height = 600,
        width = 1300,
        color_discrete_sequence = px.colors.qualitative.Dark24,
        opacity = 0.5,
        )
    
    for elm in scatter_plot['data']:
        fig.add_trace(elm, row=1, col=1)
    

    fig.update_layout(
    font_color = '#7D7D7D', 
    xaxis_title="<b>Time<b>",
    yaxis_title="<b>[M.A.S.L.]</b>",
    legend_title="<b>Monitoring point</b>",
    yaxis=dict(color="black"),
    )

    
    fig.update_yaxes( 
        title_font_size = 20,
        tickfont_family = 'Times New Roman'
        )
    
    fig.update_xaxes( 
        color="black",
        tickfont_family = 'Times New Roman',
        )

iTS_()

If applicable, please provide the steps we should take to reproduce the error or specified behavior.

Expected behavior:

Reload the database automatically

Actual behavior:

I have to manually reboot the database

Debug info

  • Streamlit version: streamlit==1.16.0
  • Python version: Python 3.9.13
  • OS version: Windows 10 home

Links

2

So you are caching the data assuming it is not going to change and even wrote a comment stating exactly that

@st.cache (allow_output_mutation=True)  # No need for TTL this time. It's static data :)

but now you want the application to be aware of changes in the data?

3

I seem to have the same issue. My code has no such comment either. Is there a reference on TTL that you can point me to. I read from MYSQL server hosted on a server.