Automatically update data fetched from SQL (no rerun)

[Pedro_Trama]

Hi!
I have an app that fetches data from a SQL database. The data is collected and used to create dashboards. The thing is that the database is constantly updated and I need the data in my app to update every 10 minutes to keep up. I’ve been using this app for the past two months and the only way I found to solve this is to manually rerun the app in my VsCode terminal (streamlit run app.py). I have already tried infinite loops for rerun and the autorefresh tool. But as far as I understood, the data only changes when I restart the app in a new terminal, not on a rerun.

Why is that? Is there a way to solve this?

[blackary]

Hi @Pedro_Trama,

My best guess is that if you rerun the app and the data doesn’t refresh, you might need to tweak the ttl so that your data cache expires. For example, if you’re using st.connection st.connection - Streamlit Docs, you might pass ttl=timedelta(minutes=10) to make the cache expire after 10 minutes.

However, that won’t make your app automatically rerun, it will just mean that if it does rerun, it should fetch new data if 10 or more minutes have passed since the last run.

If you want to make your app automatically fetch new data every few minutes, you might use the new experimental_fragment ⚡️ Launched in 1.33: st.experimental_fragment, which has a run_every argument that makes a function rerun on a schedule.

[Pedro_Trama]

Hi, @blackary, thanks for your reply!

I’m not using st.connection. I’m just connecting with sqlalchemy using an engine.
For the data cache I’m using st.cache_data(ttl=600). For the app refresh, I’m using autofresh. This way, I thought that the data would be cached for 10 minutes and after those 10 minutes the app would refresh and collect new data. That should’ve worked, right?

Anyway, I’m going to read about st.connection and see how it works, I might implement it with the experimental feature.

Thank you so much for your help

[blackary]

@Pedro_Trama

For the data cache I’m using st.cache_data(ttl=600). For the app refresh, I’m using autofresh. This way, I thought that the data would be cached for 10 minutes and after those 10 minutes the app would refresh and collect new data. That should’ve worked, right?

Yeah, that should work in theory. I’m not 100% sure how the autorefresh component works with st.cache_data, but I would have expected it to work. I think you might get a more reliable experience with st.experimental_fragment.

[Pedro_Trama]

Yeah, I just read about st.experimental_fragment. It looks amazing! Working on it right now, thanksss