I have a workout tracking web app using Streamlit and have recently switched to the st.experimental_data_editor instead of a typical dataframe. I want my database to update after I make a change, but after a refresh nothing is saved. If anyone has ideas to help that would be great!
Main section of my code:
def append_to_db(rowdata):
# Connect to the database
conn = sqlite3.connect(“workouts.db”)
c = conn.cursor()
# Create the table if it doesn't exist
c.execute(f"CREATE TABLE IF NOT EXISTS workouts_{username} (id INTEGER PRIMARY KEY AUTOINCREMENT, activity TEXT, duration INTEGER, calories_burned INTEGER, avg_bpm INTEGER, date DATE DEFAULT 'N/A')")
# Insert the data into the table
c.execute(f"INSERT INTO workouts_{username} (activity, duration, calories_burned, avg_bpm, date) VALUES (?, ?, ?, ?, ?, ?)", (rowdata[0], rowdata[1], rowdata[2], rowdata[3], rowdata[4], rowdata[5]))
# Commit the changes and close the connection
conn.commit()
conn.close()
def load_data():
# Connect to the database
conn = sqlite3.connect("workouts.db")
c = conn.cursor()
# Create the table if it doesn't exist
c.execute(f"CREATE TABLE IF NOT EXISTS workouts_{username} (id INTEGER PRIMARY KEY, activity TEXT, duration INTEGER, calories_burned INTEGER, avg_bpm INTEGER, date DATE DEFAULT 'N/A')")
# Select the necessary columns for the current user
c.execute(f"SELECT id, activity, duration, calories_burned, avg_bpm, date FROM workouts_{username}")
rows = c.fetchall()
# Close the connection
conn.close()
# Convert the data to a DataFrame
df = pd.DataFrame(rows, columns=["id", "Activity", "Duration (min)", "Calories Burned", "Avg. BPM","Date"])
if df.empty:
df = pd.DataFrame(columns=["id", "Activity", "Duration (min)", "Calories Burned", "Avg. BPM","Date"])
df = df.append({"id": 1, "Activity": "Placeholder stats, remove after entering your data!", "Duration (min)": "0", "Calories Burned": "0", "Avg. BPM": "0", "Date": "N/A"}, ignore_index=True)
return df
def delete_from_db(indices: list):
# Connect to the database
conn = sqlite3.connect("workouts.db")
c = conn.cursor()
# Delete the rows with the specified indices
for index in indices:
c.execute(f"DELETE FROM workouts_{username} WHERE id=?", (index,))
# Commit the changes and close the connection
conn.commit()
conn.close()
# HIDE STREAMLIT STYLE
hide_st_style = """
<style>
footer {visibility: hidden}
</style>
"""
st.markdown(hide_st_style, unsafe_allow_html=True)
def main():
st.sidebar.title(f"Welcome {name}")
# Load data from Excel file
df = load_data()
with st.sidebar:
page = option_menu(
menu_title="🧭Navigation",
options=["🏠Home", "📊Statistics", "💬Comments", "➡️Logout"],
icons=["house-door", "bar-chart-line", "chat", "box-arrow-right"],
menu_icon=["list"],
)
if page == "🏠Home":
st_lottie(
lottie_hello,
speed=.75,
height=150,
width=150,
)
st.title(":muscle: Workout Tracker")
st.subheader("View and add data to your workout history")
left_side, right_side = st.columns(2)
with left_side:
# Add form for user to input new workout data
new_workout_type = st.selectbox("🏋️Select workout type", ["Weight Lifting: Leg Day-Quads+Calfs", "Weight Lifting: Leg Day-Hammies", "Weight Lifting: Push Day", "Weight Lifting: Pull Day", "Basketball", "Cycling", "Core Training", "Hiking", "Multisport", "Running", "Stair Stepper", "Swimming", "Walk", "Yoga", "Other"])
new_duration = st.number_input("🕒Duration (min)", step=1, value=0)
new_calories_burned = st.number_input("🔥Calories burned", step=1, value=0)
new_avg_bpm = st.number_input("💓Avg. BPM", step=1, value=0)
new_date = st.date_input("📅Date", value=datetime.datetime.now().date())
# Add the new workout data to the database
if st.button("💪Add workout"):
append_to_db(
[
new_workout_type,
new_duration,
new_calories_burned,
new_avg_bpm,
new_date,
]
)
st.success("Workout added successfully!")
# Use dataframe_explorer to create an interactive UI for filtering and exploring the data in the dataframe
df = load_data()
filtered_df = dataframe_explorer(df)
# Display the filtered dataframe in a table
st.experimental_data_editor(filtered_df, use_container_width=True)
st.balloons()
else:
# Use dataframe_explorer to create an interactive UI for filtering and exploring the data in the dataframe
filtered_df = dataframe_explorer(df)
st.subheader("📚Newly added data")
df = load_data()
st.experimental_data_editor(filtered_df, use_container_width=True)
selected_rows = st.multiselect("❌Select Rows to delete:", df["id"], default=[])
confirm_delete = st.checkbox("I confirm I want to delete the selected rows")
if st.button("❌Delete Selected Rows"):
if not selected_rows:
st.warning("Please select at least one row to delete.")
elif confirm_delete:
delete_from_db(selected_rows)
df = load_data()
selected_rows = [index-1 for index in selected_rows] # update the selected_rows list to match the updated dataframe
st.success("✅Selected rows deleted successfully!")
else:
st.error("Please confirm that you want to delete the selected rows by checking the checkbox")
Links
https://bensellnow-theworkouttracker-1-workouttracker-s9pw2a.streamlit.app/
Username: test
Pass: test
