How to use st.cache with sqlalchemy.orm objects

🎈 Using Streamlit
5

Hi @amitg1 and @simtsc

I’ve built a small fully reproducible example so anyone can try :

import pandas as pd
import sqlite3
import sqlalchemy
from sqlalchemy import create_engine, Column, String, Integer
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, Session
from sqlalchemy.orm.properties import ColumnProperty
from sqlalchemy.orm.query import Query
import streamlit as st


Base = declarative_base()
class Company(Base):
    __tablename__ = "company"
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    age = Column(Integer, nullable=False)


@st.cache(allow_output_mutation=True)
def get_session(path: str):
    e = create_engine(f'sqlite:///{path}')
    Session = sessionmaker(bind=e)
    session = Session()
    return session


def create_test_db(session: Session):
    """Should create test.db file with "company" table
    Maybe should delete test.db if rerunning the script
    """
    Base.metadata.create_all(session.bind)
    session.add(Company(name="Streamlit", age=42))
    session.add(Company(name="is", age=24))
    session.add(Company(name="awesome", age=3))
    session.commit()


def check_sqlite_table(path: str):
    """Use sqlite3 library to read test.db content. 
    Just to check table creation went well.
    """
    e = sqlite3.connect(path)
    sql = """
    SELECT * FROM company;
    """
    df = pd.read_sql(sql, con=e)
    st.dataframe(df)


@st.cache(hash_funcs={Session: id})
def run_orm_query(q: Query, session: Session):
    df = pd.read_sql(q.statement, session.bind)
    st.dataframe(df)


session: Session = get_session("test.db")
if st.button("Create test table"):
    create_test_db(session)
if st.checkbox("Show sqlite test table using raw sqlite3"):
    check_sqlite_table("test.db")
if st.checkbox("Show sqlite test table using ORM query"):
    q: Query = session.query(Company.id).filter(Company.name == "Streamlit")
    run_orm_query(q, session)

With that we get the sqlalchemy.orm.properties.ColumnProperty and also can experience a little bit the slowness of the caching.