Uploading and using database with file_uploader

Hello everyone,
I’ve been learning various CS topics for few years now and Streamlit has been really helpful to me. Lately I’ve been trying to learn more about databases in general. Here’s the issue I’ve encountered:

I’d like for users to be able to load their own sqlite database with file_uploader. Once it’s loaded, the app would connect to it, fetch some data, adjust it with pandas and show it to the user. The user could then change it with data_editor, insert it into the database and download it.

The issue is that when I try to pass the file_uploade object into the connection I don’t get anything in return. The database I’m uploading works normally when I coonnect to it locally.
Is what I’m trying to do doable without saving the database to a tmp folder? Is it considered bad practice?

Here’s the code:

import streamlit as st
import sqlite3

user_db = st.file_uploader("Upload your database", type=".sqlite3")

if user_db is not None:
    connection = sqlite3.connect(user_db.name)
    cursor = connection.cursor()

    cursor.execute("SELECT * FROM sqlite_master;")
    data = cursor.fetchall()

    st.write(data)
    connection.close()

image

Hi @b_adam,

I think you will have to either:
a. save the uploaded sqllite db on your server and then create a connection to it as you would normally do with a local db file OR
b. as the uploaded file is in memory, you may want to experiment with the in-memory functions of sqllite ( In-Memory Databases (sqlite.org))

Cheers

I see, thank you.
Could you enlighten me why I’m getting no data in my former solution?

Hey everyone,

I’m encountering an issue with my app’s data handling. I’ve set up an SQLite database to store initial data on startup, but each time a user interacts with the app (like adding, deleting, or editing data), it seems to reset because the app reruns and fetches the data from the DB again.

I attempted writing the DB to disk for persistent storage, but it’s unexpectedly slow—I’m currently investigating this. As a workaround, I tried using an in-memory DB, but it resets whenever the user interacts with any button or element in the app.

Below, I’ve included a code snippet that replicates this issue. I’ve explored using st.cache_* functionalities, but they don’t seem to fit my needs due to the dynamic nature of the underlying DB.

My current preference for a solution:

  • Eliminate the second “display_data()” call triggered by the button click.
  • Instead, the first “display_data()” call should show the newly added entry.

I’m open to exploring various solutions to tackle this problem. Any suggestions or alternative approaches would be greatly appreciated!

Cheers,

import sqlite3
import streamlit as st


# Function to create an in-memory SQLite database and populate it
def create_in_memory_db():
    conn = sqlite3.connect(":memory:")
    cursor = conn.cursor()

    # Create a table
    cursor.execute(
        """CREATE TABLE IF NOT EXISTS users (
                        id INTEGER PRIMARY KEY,
                        name TEXT NOT NULL,
                        email TEXT NOT NULL
                    )"""
    )

    # Insert some initial data into the table
    cursor.execute(
        "INSERT INTO users (name, email) VALUES (?, ?)", ("Alice", "alice@example.com")
    )
    cursor.execute(
        "INSERT INTO users (name, email) VALUES (?, ?)", ("Bob", "bob@example.com")
    )
    cursor.execute(
        "INSERT INTO users (name, email) VALUES (?, ?)",
        ("Charlie", "charlie@example.com"),
    )

    conn.commit()
    return conn


# Streamlit app
def main():
    st.title("SQLite in-memory database in Streamlit")

    # Create or get the in-memory database
    conn = create_in_memory_db()

    # Display data from the database
    def display_data():
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM users")
        rows = cursor.fetchall()

        st.write("### Users in the database:")
        for row in rows:
            st.write(f"ID: {row[0]}, Name: {row[1]}, Email: {row[2]}")

    display_data()

    st.write("---")

    # Add new data to the database
    st.write("### Add new user")
    new_name = st.text_input("Name:")
    new_email = st.text_input("Email:")
    if st.button("Add User"):
        if new_name and new_email:
            cursor = conn.cursor()
            cursor.execute(
                "INSERT INTO users (name, email) VALUES (?, ?)", (new_name, new_email)
            )
            conn.commit()
            st.success("User added successfully!")
            display_data()  # Display all entries after adding a new one
        else:
            st.warning("Please enter both name and email.")


if __name__ == "__main__":
    main()

Hi @Pfanne,

Your db is reset because of the streamlit rerun. You could:
a. create a boolean session_state variable, initially set to false
b. check if this variable is false and only then run something that you only want to run once. Eg. create_in_memory_db(); if the variable value is true, then the function is bypassed.
c. change the boolean session_state variable value to true value from inside the function (create_in_memory_db()), so that on the next rerun the create_in_memory_db() function is not re-run.

Hope this helps.

Cheers

Hi @Shawn_Pereira,

thanks for the fast reply and your confirmation that this was really the reason for the issue.
Although the your proposed session_state variable kind of solves the issue, it creates another problem: The connection (“conn”-variable) isn’t initialzied the next time the app runs.
Therefore, I also saved the conn variable in a session_state. Nevertheless, this didn’t solve the issue:

app.py", line 62, in display_data
    cursor = conn.cursor()
             ^^^^^^^^^^^^^
sqlite3.ProgrammingError: SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 4228 and this is thread id 21232.    

Didn’t I implement your suggestion correctly? Do you have any other ideas?

Thanks a lot!

Cheers

import sqlite3
import streamlit as st


# Function to create an in-memory SQLite database and populate it
def create_in_memory_db():
    conn = sqlite3.connect(":memory:")
    cursor = conn.cursor()

    # Create a table
    cursor.execute(
        """CREATE TABLE IF NOT EXISTS users (
                        id INTEGER PRIMARY KEY,
                        name TEXT NOT NULL,
                        email TEXT NOT NULL
                    )"""
    )

    # Insert some initial data into the table
    cursor.execute(
        "INSERT INTO users (name, email) VALUES (?, ?)", ("Alice", "alice@example.com")
    )
    cursor.execute(
        "INSERT INTO users (name, email) VALUES (?, ?)", ("Bob", "bob@example.com")
    )
    cursor.execute(
        "INSERT INTO users (name, email) VALUES (?, ?)",
        ("Charlie", "charlie@example.com"),
    )

    conn.commit()

    # Set the session state to True
    st.session_state.db_created = True

    return conn


# Streamlit app
def main():
    st.title("SQLite in-memory database in Streamlit")

    if "db_created" not in st.session_state:
        st.session_state.db_created = False
        st.session_state.db_conn = None

    print(st.session_state.db_created)

    # Create or get the in-memory database
    if st.session_state.db_created is False and st.session_state.db_conn is None:
        print("Creating in-memory database...")
        conn = create_in_memory_db()
        st.session_state.db_conn = conn
    else:
        print("Using existing in-memory database...")
        conn = st.session_state.db_conn

    print(st.session_state.db_created)

    # Display data from the database
    def display_data():
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM users")
        rows = cursor.fetchall()

        st.write("### Users in the database:")
        for row in rows:
            st.write(f"ID: {row[0]}, Name: {row[1]}, Email: {row[2]}")

    display_data()

    st.write("---")

    # Add new data to the database
    st.write("### Add new user")
    new_name = st.text_input("Name:")
    new_email = st.text_input("Email:")
    if st.button("Add User"):
        if new_name and new_email:
            cursor = conn.cursor()
            cursor.execute(
                "INSERT INTO users (name, email) VALUES (?, ?)", (new_name, new_email)
            )
            conn.commit()
            st.success("User added successfully!")
        else:
            st.warning("Please enter both name and email.")


if __name__ == "__main__":
    main()

Hi @Pfanne, I think in-memory db may not be suited here due to it requiring to only be in a single process. You can use the standard db code - maybe, use a temp database and delete it when / as unnecessary. If you don’t know the commands used here, you will have to read up on them and learn accordingly. I have just added some code here to get you started, and you can take it further as per your wish.

import sqlite3
import streamlit as st
import os

ms = st.session_state
if "db_created" not in ms: ms.db_created = False

application_path = "./" # specify your own path
dbf_name = "tmp.db"     # specify your own db name

def button(*args, key=None, **kwargs):
  if key is None: raise ValueError("Must pass key")
  if key not in st.session_state: st.session_state[key] = False

  if st.button(*args, **kwargs):
    st.session_state[key] = not st.session_state[key]
    st.rerun()

  return st.session_state[key]

def OpenDB():
  dbcnn = sqlite3.connect(application_path + dbf_name)
  dbcursor = dbcnn.cursor()
  return dbcnn, dbcursor

def CloseDB(dbcnn, vcommit = True):
  if vcommit == True: dbcnn.commit()
  dbcnn.close()

def AddDB(dta, eflg = "one"):
    dbcnn, dbcursor = OpenDB()

    # dbcmd = f"INSERT INTO users VALUES (" + (total_tbl_columns * "?, ")[:-2] + ")"  # create a string like "INSERT INTO {vtbl} VALUES (?, ?, ?, ?, ?, ?) "
    dbcmd = "INSERT INTO users VALUES (?, ?)"

    if eflg == "one": dbcursor.execute(dbcmd, dta)
    elif eflg == "many": dbcursor.executemany(dbcmd, dta)

    CloseDB(dbcnn)

def InitializeDB():
    os.remove(application_path + dbf_name)  # for tmp use
    dbcnn = sqlite3.connect(application_path + dbf_name)
    dbcmd = f"CREATE TABLE IF NOT EXISTS users ( name TEXT NOT NULL, email TEXT NOT NULL )"
    if dbcmd == dbcmd:    # reverse nan chk for blank SQL
        dbcursor = dbcnn.cursor()
        dbcursor.execute(dbcmd)
        dbcnn.commit()
        dbcnn.close()

    dta = [("Alice", "alice@example.com"), ("Bob", "bob@example.com"), ("Charlie", "charlie@example.com")]
    AddDB(dta, eflg = "many")
    ms.db_created = True

def DisplayDB():
    dbcnn, dbcursor = OpenDB()
    rows = dbcursor.execute("SELECT * FROM users").fetchall()

    st.write("Users in the database:")
    for row in rows: st.write(f"Name: {row[0]}, Email: {row[1]}")
    CloseDB(dbcnn, False)

def main():
    st.subheader("SQLite database in Streamlit")
    if ms.db_created == False: InitializeDB()

    sc1, sc2, sc3 = st.columns((4,4,8))
    if sc1.button("Display Data"): DisplayDB()

    with sc2: abtn = button("Add Data", key="add")
    if abtn:
        st.write("Add new user")
        sc4, sc5 = st.columns(2)
        new_name = sc4.text_input("Name:", value="")
        new_email = sc5.text_input("Email:", value="")
        dsbld = False if new_name != "" and new_email != "" else True
        if st.button("Add User", disabled = dsbld): 
           AddDB((new_name, new_email))
           ms.add = False
           st.rerun()

if 'runpage' not in ms: ms.runpage = main
ms.runpage()

PS: the button function is attributed to @blackary

Cheers

Hi @Shawn_Pereira,
thanks for your help and your input. As I initially said, writing to disk wasn’t an option because it was very slow. Adding just a couple of information to the DB took around two seconds, thats why I asked for other solutions.
Based on Test 2 from SQLite Database Speed Comparison, one can increase the performance of sqlite by combining all requests into one and adding “BEGIN;” and “COMMIT;”. This also works perfectly fine using python’s cursor.executescript(query). Maybe this is common knowledge, but for every beginner who didn’t know about it, here is a code snippet how to speed up your sqlite scripts:

    def save_users(self, users):
        query = "BEGIN;\n"
        for user in users():
            query += "INSERT INTO users (name, email) VALUES (?, ?);", (user.name, user.mail)

        query += "COMMIT;"
        self.cursor.executescript(query)

        self.connection.commit()

Previously I executed every insertion separately. And even the combination into one query without “BEGIN” and “COMMIT” didn’t make a difference. This last step finally droped my execution time to milliseconds again.
I’m a litle bit scared that the duration increases again if the DB grows larger, but in my case the DB should remain small enough so that it doesn’t make a difference.

Once again thanks a lot for your help @Shawn_Pereira. During the last days I learned a lot about Streamlit’s mechanisms.

Cheers,