I am converting a very basic Tkinter app to use Streamlit instead. I use a local SQLite database for the data storage. I connect using an st.connection object.
I use @st.cache_datadecorated functions to retrieve data from the database on app startup. I write back or delete data from the database with the app’s widgets. When I write or delete data, I want to invalidate the cache so that the app data is synced with the db data. If I use st.cache_data.clear() then I have no issues.
However I have found that I cannot use the individual decorated functions’ .clear() methods to achieve the same effect. This only appears to happen with functions using the st.connection object; I created a test app that wrote files to disk instead of writing to a database, and found that the function.clear() and st.cache_data.clear() functionality was the same (as expected).
Could this be a bug within st.connection or is there something inherent to its caching that I’m not understanding correctly? Below is a reproducible example of what I’m seeing, using Python 3.14 and Streamlit 1.58:
- Clicking the
Insertbutton causes rows to be inserted but not immediately displayed (as expected). - Clicking the
Insert with auto clearbutton causes rows to be inserted and immediately displayed (also as expected). - But clicking the
Insert button with manual clearbutton behaves the same as theInsertbutton functionality, when it’s expected to behave the same as theInsert with auto clearbutton.
import streamlit as st
from pathlib import Path
import os
from sqlalchemy import text
from datetime import datetime as dt
import pandas as pd
cwd = Path(os.getcwd())
if not (cwd / "test_db.db").is_file():
import subprocess
subprocess.run([
"sqlite3",
str(cwd.as_posix()),
], check=False, capture_output=True, text=True, shell=True)
conn = st.connection(
"sql",
url="sqlite:///test_db.db"
)
try:
with conn.session as session:
session.execute(text("CREATE TABLE my_table(id INTEGER PRIMARY KEY ASC, detail);"))
session.commit()
except Exception as e:
st.warning(e)
def insert_row() -> None:
with conn.session as session:
insert = text("INSERT INTO my_table (detail) VALUES (:detail)")
session.execute(insert, {"detail": dt.today().isoformat()})
session.commit()
def insert_row_with_manual_clear() -> None:
insert_row()
get_rows.clear()
def insert_row_with_auto_clear() -> None:
insert_row()
st.cache_data.clear()
@st.cache_data
def get_rows() -> pd.DataFrame:
res = conn.query("SELECT * FROM my_table order by detail desc")
return res
st.button("Insert", on_click=insert_row)
st.button("Insert with manual clear", on_click=insert_row_with_manual_clear)
st.button("Insert with auto clear", on_click=insert_row_with_auto_clear)
st.write(get_rows())
What you’re seeing is a known quirk with Streamlit’s caching when using