How to add/delete rows from Snowflake table using data_editor

[staceylg]

I’m pretty new to Streamlit (in Snowflake) - as well as Python - and have been building by tweaking examples I find. I’ve created an app which pulls the data from a Snowflake table into a data_editor, and successfully submit changes to the data back to the SF table. Where I’m struggling is adding or deleting rows in the data_editor and having those changes write back to the SF table. I appreciate any guidance anyone can give! The section of code that identifies and submits changed rows is:

if submit_button:
        with st.spinner("Processing updates..."):
            # Function to compare dataframes and get changes
            def get_changes(original, edited):
                changes = {}
                for idx, edited_row in edited.iterrows():
                    original_row = original.loc[idx]
                    # Identify columns that have changed
                    changed_columns = edited_row[edited_row != original_row].dropna().index.tolist()
                    if changed_columns:
                        merchant_id = edited_row['TITLE']
                        changes[merchant_id] = {col: edited_row[col] for col in changed_columns}
                return changes

            # Get changes
            changes = get_changes(df_merch, updated_merch)

            if changes:
                update_results = []
                for merchant_id, changed_columns in changes.items():
                    set_clauses = []
                    for col, value in changed_columns.items():
                        if pd.isna(value):
                            set_clauses.append(f"{col} = NULL")
                        elif isinstance(value, str):
                            # Escape single quotes in strings
                            safe_value = value.replace("'", "''")
                            set_clauses.append(f"{col} = '{safe_value}'")
                        else:
                            set_clauses.append(f"{col} = {value}")

                    set_clause = ", ".join(set_clauses)
                
                    update_query = f"""
                    UPDATE PC_FIVETRAN_DB_CLONE.SHAREPOINT_DATA_SOURCES_CLONE.MERCHANT_CROSS_REFERENCE_CLONE
                    SET {set_clause}
                    WHERE TITLE = '{merchant_id}'
                    """
                
                    try:
                        session.sql(update_query).collect()
                        update_results.append({
                        "merchant ID": merchant_id,
                        "status": "success",
                        "message": "Update successful",
                        "changes": changed_columns
                         })
                        st.success(f"Update successful for merchant ID: {merchant_id}")
                    except Exception as e:
                        st.error(f"Error updating merchant ID {merchant_id}: {str(e)}")


                st.container()
                st.subheader("Update Results")
                for result in update_results:
                    with st.expander(f"Merchant ID: {result['merchant ID']} - {result['status'].capitalize()}"):
                        st.write(f"Status: {result['status'].capitalize()}")
                        st.write(f"Message: {result['message']}")
                        st.write("Changes:")
                        for col, value in result['changes'].items():
                            st.write(f"- {col}: {value}")

            else:
                st.info("No changes detected. Nothing to update.")

[blackary]

HI @staceylg,

To access to the rows that have been deleted, the best way is to use the st.session_state values that get updated if you edit, add or delete rows.

You can see an example here.