St.cache with db connection, connection expired

🎈 Using Streamlit
1

Hi,

First of all, Streamlit is AWESOME!
I’m building a CRUD app with streamlit.
using the following code:
from sqlalchemy import create_engine
from sqlalchemy.engine import Engine

def insert_df(df, table):
df.to_sql(name=table.split(’.’)[1],
schema=table.split(’.’)[0],
con=get_connection(),
if_exists=‘append’,
index=False,
dtype={})

@st.cache(allow_output_mutation=True)
def get_connection() -> Engine:
return create_engine(global_con)

@st.cache
def load_data(sql) -> pd.DataFrame:
with st.spinner(‘Loading Data…’):
time.sleep(0.5)
df = pd.read_sql_query(sql, get_connection())
return df

After x time I’m getting “Connection is closed”(which is reasonable, closed from the database side ( if I’m using the cached connection,
how would you solve it? with TTL? is it the best approach here? how can I check if the cached connection still exists?

Thanks,
Amit

1 Like
2

i solved it with https://nestedsoftware.com/2018/06/11/flask-and-sqlalchemy-without-the-flask-sqlalchemy-extension-3cf8.34704.html
works great.
no need to cache the connection

1 Like
3

Awesome, glad you figured it out @amitg1!