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.")
