Code Editor for app.py

import streamlit as st
import pandas as pd
from sqlalchemy import create_engine, Column, Integer, String, Boolean, Sequence
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session

Base = declarative_base()

# Define the model for the database table
class Record(Base):
    __tablename__ = 'records'
    id = Column(Integer, Sequence('record_id_seq'), primary_key=True)
    name = Column(String(50), nullable=False)
    value = Column(Integer, nullable=False)
    favorite = Column(Boolean, default=False)

# Create the SQLite database and table
engine = create_engine('sqlite:///mydb.sqlite')
Base.metadata.create_all(bind=engine)

# Load data from the database into a DataFrame
with Session(engine) as session:
    data = session.query(Record).all()
    df = pd.DataFrame([(record.id, record.name, record.value, record.favorite) for record in data],
                      columns=['id', 'name', 'value', 'favorite'])

# Initialize the DataFrame that will hold edited data
if 'edited_df' not in st.session_state:
    st.session_state.edited_df = df.copy()

# Function to commit new records to the database
def commit_new_records(new_records):
    with Session(engine) as session:
        for idx, row in new_records.iterrows():
            record = Record(name=row['name'], value=row['value'], favorite=row['favorite'])
            session.add(record)
        session.commit()

# Render the data editor
edited_df = st.data_editor(
    st.session_state.edited_df,
    column_config={
        'name': 'Name',
        'value': st.column_config.NumberColumn(
            'Value',
            min_value=0,
            required=True
        ),
        'favorite': st.column_config.CheckboxColumn(
            'Favorite?'
        )
    },
    disabled=['id'],  # Disable 'id' for existing records
    num_rows='dynamic'
)

# Check for new records and commit to database
if st.button('Save Changes'):
    # Determine which rows are new
    new_records = edited_df[~edited_df['id'].isin(df['id'])]
    if not new_records.empty:
        commit_new_records(new_records)
        st.success('New records saved to the database!')

# Display the updated dataframe
st.dataframe(edited_df)