How can I reset ttl time for mysql connection?

Hello all, I am writing a basic database front end with Streamlit.

I have shortened my code since the rest is not necessary:

import streamlit as st
import pandas as pd

col1, col2 = st.columns([1,2])

# Initialize connection.
conn = st.connection('mysql', type='sql')

# Perform query.
part_query = conn.query('SELECT * from parts;', ttl=10)

df = pd.DataFrame(part_query)

with col2:
    # This column holds the data queried from the parts table.
    st.header("Part list:")
    st.dataframe(df, hide_index=True, use_container_width=True,)

with col1:
    # This column holds input components that will be used to insert a new record into the parts table.
    st.header("Add parts:")
    part_description = st.text_input('Description', '')
    st.button('Create new part')

Column 1 holds input components that will be used to create a part. Column 2 holds a dataframe to view the parts in the database.

I would like the ttl of the cached database data to be 10 seconds so if anyone makes changes, they should be reflected in the list when a user clicks on something, but also limits queries. However, I want to effectively reset this ttl to 0, or invalidate the cached data so when the user clicks the “Create new part” button, the new data will immediately be populated in the dataframe. I don’t want to clear the cache for everything, only the SQL query. This can be thought of as a “refresh” button as that is what I want it to do after inserting the new record.

How can I do this?

Thanks,
Andrew

I think it works to simply do a new query with a ttl of 0, like this:

[NOTE: This is called “mysql” but it actually sqlite under the hood for simplicity, so the SQL syntax hasn’t been tested with MySQL directly]

import streamlit as st
import pandas as pd

col1, col2 = st.columns([1, 2])

# Initialize connection.
conn = st.connection("mysql", type="sql")

# Perform query.
if st.button("Reset table"):
    with conn.session as s:
        s.execute("CREATE TABLE IF NOT EXISTS parts (description TEXT);")
        s.execute("DELETE FROM parts;")
        descriptions = [
            "Wheel",
            "Brake Pad",
            "Door",
            "Window",
        ]
        for d in descriptions:
            s.execute(
                "INSERT INTO parts (description) VALUES (:description);",
                params=dict(description=d),
            )
        s.commit()

part_query = conn.query("SELECT * from parts;", ttl=10)

df = pd.DataFrame(part_query)

with col2:
    # This column holds the data queried from the parts table.
    st.header("Part list:")
    st.dataframe(
        df,
        hide_index=True,
        use_container_width=True,
    )

with col1:
    # This column holds input components that will be used to insert a new record into the parts table.
    st.header("Add parts:")
    part_description = st.text_input("Description", "")
    if st.button("Create new part"):
        with conn.session as s:
            s.execute(
                "INSERT INTO parts (description) VALUES (:description);",
                params=dict(description=part_description),
            )
            s.commit()
        conn.query("SELECT 1", ttl=0)  # Resets the query cache.
        st.rerun()

In this case you can immediately see the table updated if you add a new part (note the use of st.rerun to make sure the whole page reruns), but if you hit “reset table” it won’t update for 10 seconds, even if you refresh the page.

1 Like

With this reply, you answered more questions than just the one I had here. Thanks!
I couldn’t figure out how to properly INSERT or DELETE records :slight_smile:

1 Like

This topic was automatically closed 2 days after the last reply. New replies are no longer allowed.