Streamlit with sqlalchemy

Hi, everyone! I have a problem using sqlalchemy with streamlit cache. The following function (st_get_shop_ids) works fine without the @st.cache decorator; however, when the decorator is added, it runs forever (literally, as if it is in an endless loop and laptop starts overheating wildly).

@st.cache
def st_get_shop_ids() -> pd.Series:
    print("st_select_ids")
    shop_ids = ping_shop_id()
    return shop_ids

def ping_shop_id() -> pd.DataFrame:
    session = SessionLocal()
    query = session.query(FacebookAd.shop_id)

    df = pd.read_sql(query.statement, session.bind)

    return df["shop_id"]

Python doesn’t even do the printing (first line in function); as if it blocks completely. I’ve also tried adding @st.cache(allow_output_mutation=True), but to no avail. I used to have the code from the help function ping_shop_id inside the st_get_shop_ids; then I got the “Streamlit cannot hash an object of type <class ‘sqlalchemy.orm” error. The way I understand caching, Streamlit only caches input and output of decorated functions (which are in my case of type string and pandas.DataFrame, neither of which should be problematic).

Thanks for any help in advance!

Hi @sanjin-lebesgue -

st.cache is v1 of the Streamlit caching mechanism, with st.experimental_memo and st.experimental_singleton being v2. Can you try one of those and see if the error still persists?

Best,
Randy

Thanks very much! It works perfectly now!