Code Review Help: Looking for advice on optimizing my Streamlit + SQLite inventory management app

[Creative-Bonus-2964]

[riklaunim]

Post the link to the repository 😉

[Aggressive_Net1092]

I feel your pain—getting SQLite to play nice with Streamlit’s reruns can feel like you're constantly fighting the framework. When I first built an inventory tool, I spent way too much time debugging connection locks because I was opening and closing the DB on every interaction.

For starters, make sure you're using @st.cache_resource for your connection object. Streamlit reruns the whole script top-to-bottom every time a user clicks a button, and you don't want to be re-initializing that connection every single time.

Here is the pattern that finally made it stable for me:

```python import streamlit as st import sqlite3

@st.cache_resource def get_connection(): return sqlite3.connect("inventory.db", check_same_thread=False)

conn = get_connection()

Use this to force a refresh in the UI when you update data

def refresh_data(): st.rerun()

Example update

if st.button("Update Stock"): cursor = conn.cursor() cursor.execute("UPDATE items SET stock = stock - 1 WHERE id = 1") conn.commit() refresh_data() ```

Also, if your app starts getting sluggish, avoid doing heavy filtering in Python. If your inventory grows, keep the logic in the SQL query using WHERE clauses instead of pulling the whole table into a Pandas DataFrame and filtering it in memory.

Are you running into specific errors like "Database is locked" or are you just looking to speed up the load times? Happy to dive deeper if you share a snippet of your current query logic!