How to refresh an app's page which uses caching?

Using Streamlit
1

Hi,

I have a mysql database with email texts and I’m trying to build a Streamlit app to classify these emails as “good” or “bad”.
So, what the app does is to retrieve a random entry from the database – an email which was not classified yet – and ask the user to classify the email buy pressing the “good” or the “bad” button.

I’m having a hard time with caching, so my problem is:

  • if I don’t use caching, the page refreshes with a new email after I press one of the “classification buttons”, but the proper email_ID used for the UPDATE query is wrong;
  • if I use caching, the email_ID used for the UPDATE query will be correct, but the page won’t refresh with a new email after I press one of the “classification buttons”.

I tried to simplify the code to post here:

import streamlit as st
import mysql.connector

list_features = ['Start', 'Classify emails']

option = st.sidebar.selectbox('Choose feature', list_features)

st.header(option)

if option == 'Classify emails':

    @st.cache(allow_output_mutation=True, hash_funcs={"_thread.RLock": lambda _: None})
    def init_connection():
        return mysql.connector.connect(**st.secrets["mysql"])

    conn = init_connection()

    @st.cache(allow_output_mutation=True)
    def run_query(query):
        with conn.cursor() as cur:
            cur.execute(query)
            return cur.fetchone()
    
    row = run_query("SELECT email_ID, email_date, email_from, email_subject, email_message FROM email WHERE email_type IS NULL ORDER BY RAND() LIMIT 1")

    email_ID = row[0]
    email_date = row[1]
    email_from = row[2]
    email_subject = row[3]
    email_message = row[4]

    st.write(f"Date: {email_date}")
    st.write(f"From: {email_from}")
    st.write(f"Subject: {email_subject}")
    st.write(f"Message: {email_message}")

    st.write("Is this email GOOD or BAD?")

    if st.button('GOOD'):
        sql = f"UPDATE email SET email_type = 1 WHERE email_ID = {email_ID}"
        conn.cursor().execute(sql)
        conn.commit()
    if st.button('BAD'):
        sql = f"UPDATE email SET email_type = 0 WHERE email_ID = {email_ID}"
        conn.cursor().execute(sql)
        conn.commit()

I appreciate any ideas to help me solve this.
Thanks a lot!