Connection to snowflake spans multiple windows

🎈 Using Streamlit
, ,
1 
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!

2

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.

3

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…

4

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)
5

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?

6

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

7

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…