workouts/views/workouts_templates.py

import streamlit as st
import mysql.connector
from mysql.connector import errorcode
import pandas as pd
from datetime import datetime, date
from dotenv import load_dotenv
import os

load_dotenv()
user = os.getenv('user')
password = os.getenv('password')
host = os.getenv('host')
database = os.getenv('database')
port = os.getenv('port')
workout_table = ['chest','back','legs']

selected_workout = st.selectbox('Select a Workout',options=workout_table)
edited_df=[]

try:
    conn = mysql.connector.connect(user=user,password=password, host=host,port=port,database=database)
except mysql.connector.Error as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Something is wrong with your user name or password")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
    else:
        print(err)
    
else:
    cursor = conn.cursor()
    if selected_workout == 'chest':
        query = ("SELECT * FROM `chest`")
        working_table='chest'
    elif selected_workout == 'back':
        query = ("SELECT * FROM `back`")
        working_table='back'
    else:
        query = ("SELECT * FROM `legs`")
        working_table='legs'
    cursor.execute(query)
    data = cursor.fetchall()
    conn.commit()
    cursor.close()
    conn.close()

df = pd.DataFrame(data,columns=cursor.column_names)
edited_df = st.data_editor(df)

# Button to save all changes back to the dataframe
if st.button('Save All Changes'):
    st.write("All changes saved!")
    conn = mysql.connector.connect(user=user,password=password, host=host,port=port,database=database)
    cursor = conn.cursor()
    query = (f"DELETE FROM {working_table}")
    cursor.execute(query)
    for index, row in edited_df.iterrows():
        query = (f"INSERT INTO `{working_table}`"
                "(`date`,`group`,`exercise`,`set`,`weight`,`reps`,`notes`) " 
                "VALUES (%s,%s,%s,%s,%s,%s,%s)")
        cursor.execute(query,(row.iloc[1],row.iloc[2],row.iloc[3],row.iloc[4],row.iloc[5],row.iloc[6],row.iloc[7]))
    conn.commit()
    cursor.close()
    conn.close()