Connection to snowflake spans multiple windows

conn_details = {
        "account": os.getenv("SNOWFLAKE_ACCOUNT"),
        "authenticator": "externalbrowser",
        "user": email,
        "warehouse": os.getenv("SNOWFLAKE_WAREHOUSE"),
    }

_conn = snowflake.connector.connect(**conn_details)

I have a problem with my streamlit app. Every time the main script runs top bottom some window for connection is opening.

I tried setting the @st.cache_resource but I guess it doesn’t cache anything after the scripts reruns, just during the execution of my main file, so if I call the function 2 times it’s gonna use the cache, but if it’s reruned(basically everytime something happens in my custom component), I get an external browser page.

Is there any way to connect to snowflake just once and to reuse that even after the script reruns?

Thanks in advance!

Hi @Jigau_Rafael,

Thanks for sharing this question!

You’re on the right track with @st.cache_resource – that’s how we’d recommend caching your database connection. If your database connection is cached with st.cache_resource, it shouldn’t be rerunning when a user interacts with a custom component. If you can share a larger code snippet that includes the cached function, happy to take a look.

Thanks for the involvement @Caroline !

@st.cache_resource
def get_DM_conn(email: str):
    #Get connection to snowflake


def get_data_from_snowflake(_conn):
    # Get the data from the database using the connection




def is_valid_email(email):
    pattern = r"^[\w\.-]+@[\w\.-]+\.\w+$"
    return re.match(pattern, email) is not None and "" in email.lower()


def main():
    st.session_state.setdefault("page", "home")
    st.session_state.setdefault("email_submitted", False)

    if not st.session_state.get("email_submitted"):
        email = st.text_input("Please enter your work email address:")

        if email and is_valid_email(email):
            st.session_state.email_submitted = True
            st.session_state.email = email
            try:
                DM_conn = get_DM_conn(email=email)
            except Exception as e:
                st.error(f"Snowflake Programming Error: {str(e)}")
                return
            try:
                json_array = get_data_from_snowflake(DM_conn)
            except Exception as e:
                st.error(f"Snowflake Programming Error: {e.msg}")
                return
            user_role = get_user_role()
            dataFromFrontend = streamlit_app.init(
                name="Frontend", table_data=json_array, email=email, user_role=user_role
            )
        elif email:
            st.error("Please enter a valid email address.")
    else:
        if "email" in st.session_state:
            email = st.session_state.email
            try:
                DM_conn = get_DM_conn(email=email)
            except Exception as e:
                st.error(f"Snowflake Programming Error: {str(e)}")
                return
            try:
                json_array = get_data_from_snowflake(DM_conn)
            except Exception as e:
                st.error(f"Snowflake Programming Error: {e.msg}")
                return
            ORACLE_conn = get_ORACLE_conn(email=email)
            user_role = get_user_role()
            dataFromFrontend = streamlit_app.init(
                name="Frontend", table_data=json_array, email=email, user_role=user_role
            )
    submit_data(dataFromFrontend["tableData"], email=email)


if __name__ == "__main__":
    main()

I know I’m trying to make something that Streamlit isn’t used for, but I just landed on this and really need to make it work for the business…

In get_DM_conn, you’re basically doing this, right?

conn_details = {
        "account": os.getenv("SNOWFLAKE_ACCOUNT"),
        "authenticator": "externalbrowser",
        "user": email,
        "warehouse": os.getenv("SNOWFLAKE_WAREHOUSE"),
    }

_conn = snowflake.connector.connect(**conn_details)

Yes… I have exhausted everything on this subject, I think it’s due to the fact that Snowflake closes my connection very quickly so even if I cache it and try to reuse it, second time it’s gonna be closed… Have you heard of any issue like this on snowflake? Any idea how to fix?

You might try explicitly setting a timeout on your connection Connecting to Snowflake with the Python Connector | Snowflake Documentation

Isn’t a timeout just a mechanism for retries? I could set the timeout but due to the fact the connection is already closed I don’t think it’s something working…

I am facing the same issue. Any solution for this?