Streamlit and SQLite

I have a Python application that saves some data to an SQLite database. The tables arent populated just once but multiples times. I want, when a table update occurs, to refresh some streamlit charts.
Does anyone know how to do this please? any help greatly appreciated.

Thanks

Hi @gmiliotis,

Thanks for posting!

Sharing @randyzwitch’s response from a similar post:

sqllite is a special case of databases, in that sqllite is a file that can be committed to a Git repo. Other databases, like postgres that you mention, are whole programs running in their own process on a server.
In the case of a private repo, the only way to make what you want work is to somehow be able to download that sqlite file into your app. Its possible that you could use secrets management to have SSH keys in your Streamlit sharing app that would allow you to clone a private repo into your Streamlit sharing app location. But that’s pretty close to having the sqlite database public.
So yes, if you want to have an app backed by a database, and you don’t want that data to be public, using a database such as postgres hosted somewhere else is probably the best solution.

I also recommend checking out the following thread: Streamlit and sqlite | Update database by sharing the web app

Caroline :balloon:

1 Like

Hi Caroline

thanks for helping out but I still do not get it. I want to feed data from the database to the dashboard not the other way round.

Hi @gmiliotis,

sqlite3 seems to be the standard way of connecting to an SQLite database via Python.

Caroline :balloon:

Hi Caroline

yes thats what I am using. What I would like to do is when my python app updates a table in my database, a chart on my dashboard to get refreshed with the latest data. I have no idea if this is doable.

Hi @gmiliotis,

If I understand your question correctly, this isn’t specific to SQLite – it’s just about streaming the data. If so, you’d probably want to use some combination of st.experimental_rerun and the strategies described in the following threads:

Caroline :balloon:

1 Like

Thanks for all your help Caroline. I would say it is indeed specific to SQLite in the sense that my data are saved on an SQLite database. What i havent figured out is how to update the streamlit dashboard when new data are added to the tables on my SQLite database.

Hi @gmiliotis,

You could combine the concepts I linked in my last reply with using the sqlite3 library – i.e. the sqlite3 library allows you to query your SQLite database, and you could use st.experimental_rerun(), for example, to rerun those queries and pull in the new data.

In terms of only rerunning your app when there’s new data in your SQLite database, I’m not aware of a way to do that currently – you’d basically want your app to listen for an SQLite webhook that would fire when your data has been updated. I’m under the impression that wouldn’t quite work with a Streamlit app since it would need to be always listening.

Caroline