App refreshes everytime, when Value of multiselect is queried from db

Hey Guys,

I have one select box, one dictionary and one multiselect

Lets say select box has values as A, B, C.

Dictionary will have SQL’s corresponding to A, B, C.

So when I choose a value in select box , multiselect has to be populated with values after querying from Database

Problem what i am facing is, when i select value in select box, the multiselect is accurately populatd with values, but when i choose first value, i am unable to second value because, App refreshes everytime. Is there a work around on this

        filter_sqls = {'RPT_BRAND':"select distinct ASIS_RPT_BRAND_GROUP from EDGEDM.BRAND",
                   'RPT_BRAND_SUBGROUP':"select distinct ASIS_RPT_BRAND_subGROUP from EDGEDM.BRAND",
                   'LEVEL17_DESCR':"select distinct ASIS_LEVEL17_DESCR from EDGEDM.BRAND",
                   'OPP_IDENTIFIER':"select distinct opp_identifier_name from EDGEDM.OPP_IDENTIFIER",
                   'OPP_OWNER':"select distinct OWNING_ORG_SHRT_NM from EDGEDM.OWNING_ORG",
                   'IOT':'select distinct ASIS_IOT_GMU_NAME from edgedm.IBM_SALES_ORG',
                   'IMT':'select distinct ASIS_IMT_GMT_NAME from edgedm.IBM_SALES_ORG',
                   'ROAD_MAP_STATUS':'select distinct CRM_ROADMAP_STATUS from edgedm.PM_CMPNT_HISTORY_FACT'}

    session_state.forecast = st.selectbox("Select Forecast Quarter", ["CQ", "NQ", "NQ+1", "NQ+2"])
    session_state.dims = st.multiselect("How do you want to analyse?",fields)
    session_state.filter_iot = st.selectbox("select Your Field",fields)
    session_state.values = q.querying("ESAPDA", sqls=filter_sqls[session_state.filter]).import_sql().connect_warehouse().pull_data().data
    session_state.values = session_state.values[session_state.values.columns[0]]
1 Like

Have you tried using the streamlit decorator to cache functions st.cache()?

From my understanding as a new user this is how streamlit is supposed to work. Everytime the user interacts with your app the code is run from top to bottom. Using st.cache() allows you to cache certain functions so that they are not rerun on every interaction.

I tried with cache in another experiment. I was not able to get expected output. Problem is values are loaded accurately to multiselect box, but when i select a value, the app refreshes because of which my selection is gone. So i can never choose any value. I have found a workaround. Instead of querying values @ run time, i have queried all values and stored it in a pickle file as dictionary and den i am reading it and storing it as session state value. Let me know if its a good work around