Streamlit and sqlite | Update database by sharing the web app

Streamlit Cloud
#1

Hi I have a question, I would like to update the user input, in my database. Unfortunately, this only works locally but not when I share the app. Does anyone have an idea what I may not have considered?

My function looks like this:

def update_sql_database_ger(label):
    try:
        connection_de = sqlite3.connect('////Users/****/Documents/GitHub/****/G****.db', check_same_thread = False)
        cursor = connection_de.cursor()
        print("Connected to SQLite")

        ID = st.session_state.ID
        Gender = st.session_state.gender
        Index = str(st.session_state.current_index)
        Count = 1


        sql_update_query = """Update Hate_speech_ger set Label = ?, Individual_ID = ?, Gender =?, Count = ? where index_id = ?"""
        data = (label,ID,Gender, Count, Index)
        cursor.execute(sql_update_query, data)
        connection_de.commit()
        print("Record Updated successfully")
        cursor.close()
        connection_de.close()

    except sqlite3.Error as error:
        print("Failed to update sqlite table", error)

Thanks for your help!

#2

Hi @Raisarom, welcome to the Streamlit community!

The important thing to keep in mind is that GitHub is generally not considered a “drive” in the same way your local computer is, but rather that it’s a version control system. In that sense, when you deploy to Streamlit Cloud, your code is copied from GitHub, but the values aren’t pushed back to GitHub.

So in terms of code deployed on Streamlit Cloud, you should treat it like any other “container” system, in that multiple copies of the code/container might exist at any one time, different users might be on different instances of the app, etc. If you want a pattern like this to work, you need to have a database hosted outside of Streamlit Cloud. A “database” in this example might include something as simple as Google Sheets, writing to Amazon S3/Google Drive, or a full-fledged database instance hosted somewhere.

Best,
Randy

#3

Hi randyzwitch,

thanks for your quick reply! I first worked with a google sheet. This sheet was always updated via API… unfortunately this always gave me an API error when too many participants called the API at once… So I thought this would be a better solution… I will think about your suggestions! Thanks a lot for that.

Best,
Raisa