Caching Sqlite DB connection resulting in glitchy rendering of the page

Hello, I am trying to build a multipage app. I have a login page and one other page that is accessible only after a valid user logs in. I am using sqlite db for authentication. Without caching the sqlite db connection, the login page renders seamlessly. However, on caching the db connection, I notice glitchy rendering only on the very first interaction with all the widgets. It gets stable from the second time onwards. The code is as follows:
app.py

import streamlit as st
from page import login_page, species_search_page
from auth import init_db


def app_title():
    st.title("Multipage App")
    st.write("An Experimental Multipage App")


def main():
    init_db()

    app_title()

    menu = ['Login', 'Species Search']
    menu_selection = st.sidebar.radio("Menu", menu, key='menu_selection')

    if menu_selection == 'Login':
        if 'login' not in st.session_state or not st.session_state['login']:
            login_page()
        elif 'login' in st.session_state and st.session_state['login']:
            st.success(
                f"\n You are successfully logged in as user '{st.session_state.username}'")
    elif menu_selection == 'Species Search':
        if 'login' in st.session_state and st.session_state['login']:
            species_search_page()
        else:
            st.warning("\n Please Login before you proceed \n")


if __name__ == "__main__":
    main()

page.py

import streamlit as st
from callback import login_callback


def login_page():
    login = st.container()
    with login:
        st.subheader("Login")
        username = st.text_input("Username")
        password = st.text_input("Password", type="password")
        st.button("Login", on_click=login_callback,
                  args=(login, username, password,))


def species_search_page():
    return

callback.py

import streamlit as st
from auth import is_valid_user


def login_callback(placeholder, username, password):
    if is_valid_user(username, password):
        st.session_state['username'] = username
        st.session_state['password'] = password
        st.session_state['login'] = True
    else:
        placeholder.error("Please Enter Valid Username and Password")

auth.py

import sqlite3
from config import URI_SQLITE_DB, ADMIN_USERNAME, ADMIN_PWD, USERS_TABLE
import streamlit as st

# @st.cache(allow_output_mutation=True)
def get_connection(path: str):
    conn = sqlite3.connect(path, check_same_thread=False)
    # st.session_state.sqlite_conn = conn
    return conn


def init_db():
    conn = get_connection(URI_SQLITE_DB)
    conn.execute(
        f"""CREATE TABLE IF NOT EXISTS {USERS_TABLE}
        (
            USERNAME TEXT,
            PASSWORD TEXT,
            IS_ADMIN INTEGER
        );"""
    )
    cur = conn.execute("SELECT * FROM USERS WHERE USERNAME = ? AND PASSWORD = ?",
                       (ADMIN_USERNAME, ADMIN_PWD))
    if cur.fetchone() == None:
        conn.execute(
            f"INSERT INTO {USERS_TABLE} VALUES (?, ?, ?)", (ADMIN_USERNAME, ADMIN_PWD, 1))
    conn.commit()
    conn.close()


def is_valid_user(username, password):
    conn = get_connection(URI_SQLITE_DB)
    # conn = st.session_state.sqlite_conn
    cur = conn.execute(f"SELECT * FROM {USERS_TABLE} WHERE USERNAME = ? AND PASSWORD = ?",
                       (username, password))
    if cur.fetchone() == None:
        return False
    else:
        return True
    conn.close()

config.py

URI_SQLITE_DB = "auth.db"
ADMIN_USERNAME = "admin"
ADMIN_PWD = "admin"
USERS_TABLE = "USERS"

With Caching:
App_with_db_connection_cached_SparkVideo

Without:
Login_Page_without_db_connection_cached_SparkVideo

Tried storing the db connection in the session state and used it in the is_user_valid function instead of doing a cache hit, and even that seems to work fine. But I wonder which is the best practice.

Appreciate any help!

Hey @puzzled-cognition,

I just wanted to pop by your question and give a couple of tips + my opinion on the best answer here.

  • best answer: abandon @st.cache for @st.experimental_singleton

  • tip: slightly adjust the format of your question to get more answers and participation

    • I can tell you went to a lot of time and effort in structuring your question (adding in your code, recording screen gifs of what’s happening) which is awesome!
    • but the actual question you have is hard to find. Make sure when you’re asking questions it is very clear what those questions are for the reader
      • use a question mark at the end of each question you want to be answered i.e. “But I wonder which is best practice?”
      • if your post is getting really long add the questions you have at the top and make them distinct (italics, bold, a bulleted list etc…) that way as someone reads your code and details about your problem, they already know the 2 or 3 things you want help/opinions with
  • tip: make your gif’s bigger

    • having gif’s demoing the behaviour you’re seeing is super helpful, but somehow on these they are super small, and I don’t have the option to expand them to actually see what’s happening in your demo
      • Screencastify is a free browser extension that works really well for me

Sorry this got so long!
Happy Streamlit-ing!
Marisa

2 Likes

Hello Marisa, Thank you for valuable tips; will definitely keep the pointers in mind. I converted mp4 to gif using some online tool and only after uploading did I realize the size is way too small. Thanks for mentioning Screencastify.

I had gone through the blog post and had tried out singleton but the intermittent issues of having to press submit button twice and inconsistent rendering on the page persisted.

1 Like

Hey @puzzled-cognition,

So I tried out snippets of your code and I was having that double button push error whether or not I was using @st.experimental_singleton. I suspected this was due to some funny interaction between using the callback function and using the return values of the text inputs and not storing them in state themselves.

I edited your code and now I have this working seamlessly on my machine.

  • upgrade streamlit >=1.1.0
  • I changed your login to be a form, that way the script only runs when you hit the submit button and not as you enter each values into the text_inputs
  • I changed the logic around the login key:value pair in session state
  • 'login' not in st.session_state and not st.session_state['login'] are the same thing, so I removed the duplicates of each (just 2 different ways of accessing the key login from session state)
  • added returns to all of the functions you made, its best practice and you want to make sure that you explicitly tell python to always return to the main function
import streamlit as st
import time

# this is the function to connect with the database since I am not 
# connecting i just put a progress bar here and a time delay to mimick that
@st.experimental_singleton(suppress_st_warning=True)
def init_db():
    start_up = st.empty()

    with start_up:
        bar = st.progress(0)

        for complete in range(100):
            time.sleep(0.01)
            bar.progress(complete+1)

        start_up.empty()
    return

def app_title():
    st.title("Multipage App")
    st.write("An Experimental Multipage App")
    return

# turned this into a form to keep your script from running without actually 
# pressing the "login" button. Because I added a form user and pass will only 
# be put in the state one time (when the form returns True), so when the user
# logs in with the correct password I transfer these values to your username 
# and password, as well as setting login to True
def login_page():
    st.subheader("Login")

    login = st.form("enter_form")
    with login:
        username = st.text_input("Username", key="user")
        password = st.text_input("Password", key="pass")
        st.form_submit_button("Login", on_click=login_callback)

    return

# i removed your function call in a callback, the things you were doing 
# in them seemed simple and sort enough that having to move to 
# another python module to see the code seemed more work than its worth

def login_callback():
# me mimicking opening the connection to your DB and checking the credentials
    if (st.session_state.user == "me")and(st.session_state["pass"] =="123"):
        st.session_state["login"] = True
        st.session_state.username = st.session_state.user #assign to diff key 
        st.session_state.password = st.session_state["pass"]
    elif (st.session_state.user == "me")and(st.session_state["pass"] !="123"):
        st.error("Wrong Password")
    elif (st.session_state.user != "me")and(st.session_state["pass"] =="123"):
        st.error("Wrong username")
    else:
        st.error("Incorrect login info")
    return


def species_search_page():
    return

def main():
    init_db()

    app_title()

    menu = ['Login', 'Species Search']
    menu_selection = st.sidebar.radio("Menu", menu, key='menu_selection')

# best practice is to add your key value pairs to session state and then work from there 
    if menu_selection == 'Login':
        if 'login' not in st.session_state:
            st.session_state.login = False

#shortened this to use only one condition as mentioned above
        if not st.session_state.login:
            login_page()
        else:
            st.success(
                f"\n You are successfully logged in as user '{st.session_state.username}'")
    elif menu_selection == 'Species Search':
        if 'login' in st.session_state:
            species_search_page()
        else:
            st.warning("\n Please Login before you proceed \n")


if __name__ == "__main__":
    main()

Happy Streamlit-ing!
Marisa