Automatically pulling in SQLite database update & showing it in the app

[sborms]

Hi all,

I developed a Streamlit app that relies on a SQLite db. I want the data in the database to be updated once a week so I set up a GitHub Actions workflow that triggers a scraping script and pushes the changes back to the repository. I see the push being noticed on Community Cloud, but the database itself doesn’t change on the app… It only changes when I manually reboot the Streamlit app.

When I check the webhook, I see that both the .db file and another .txt file are effectively modified. Only the second file’s changes (to show the last refresh date on my home page) are carried forward to the app though.

Does anyone know what’s happening? Is it because the .db is a binary file? Should I remove and add it instead of modifying it?

I already added a ttl in the cache functions, moved the database in and out of my root folder, patiently waited for a few weeks, but no luck.

Information

App: https://futsalfriend.streamlit.app
GitHub repo: GitHub - sborms/futsalfriend: Your digital futsal companion: find a friendly opponent, look for a team to join, analyze vanity metrics, and receive jolly tactical advice
Versions: Python 3.9.13, Streamlit 1.27.0

[sborms]

A further overview of the things I tried:

• Added a TTL in the cache functions of 0 or 1800 seconds
• Used st.connection() with a TTL of None, 0 or 1800 seconds
• Added the SQLite .db file in a separate folder (database/) and in the main app folder (webapp/)
• Waited for a few hours/days up to one week
• Added some calls to st.cache_data.clear(), based on Cached query results are not scoped to the connection on which that query was run · Issue #7709 · streamlit/streamlit · GitHub

Only the last attempt seems to have helped (I’ll wait for a next update to be sure).

It still feels suboptimal, because it doesn’t permit proper caching and it doesn’t explain why a git committed change in the database file is ignored on Streamlit Community Cloud. I know SQLite is a semi-persistent storage so I assume it’s held on to somewhere on the server side, but not sure if that explains it.

[chaz-clark]

@sborms any updates on this points, a student is working on a similar project where we are having the same persistence issue with sqLite database in GitHub.

[Franky1]

If the database is constantly changing, I wouldn’t make life difficult for myself and host the database externally; there are even free options for small databases.

@sborms

so I set up a GitHub Actions workflow that triggers a scraping script and pushes the
changes back to the repository

I can think of another possibility, namely modifying the streamlit app itself each time with the github action and changing a dummy constant, e.g. a hash in the python code.