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