Using Streamlit: making a table based on selections

[sammy22]

Hello,

i am new to streamlit and am using it via snowflake. i am trying to create an application where a user selects their category and a table shows their selection. They will then be able to edit the some of the fields which will flow into another table in snowflake. My code is below but i do not know how i can make the table reflect the selections, the whole table (blah) is displaying.

in SQL i would use something like ‘where country=‘USA’ and Line=2’ but i’m not sure what to do here. Apologies for my ignorance, i’m new to this!

Bring in npv combined dataset

df=session.table(‘DEV_PROJECT.blah’)

filter_country_df=session.table(‘DEV_PROJECT.blah’).select(‘Country’).distinct()
select_country=st.selectbox(“Select Country”,filter_country_df)

filter_BL_df=session.table(‘DEV_PROJECT.blah’).select(‘Line’).distinct()
select_BL=st.selectbox(“Select Line”,filter_BL_df)

st.header(“ Edit Data”)
with st.form(“Table write”):
df_edited = st.experimental_data_editor(df, use_container_width = True)
snowpark_df = session.create_dataframe(df_edited)
write_to_snowflake = st.form_submit_button(“Update”)

if write_to_snowflake:
with st.spinner(“Updating…”):
snowpark_df.write.mode(“overwrite”).save_as_table(‘DEV_PROJECT.MANUAL_blah’)
st.success(“Wrote to Snowflake successfully ”)

[sahirmaharaj]

Hello @sammy22,

To create a Streamlit application that interacts with Snowflake and allows users to select and edit data, you need to implement a few key functionalities:

1. You need to filter your Snowflake table based on the user’s selection of country and line.
2. Allow users to edit the filtered data and reflect those changes back in Snowflake.

import streamlit as st
import pandas as pd

def get_distinct_values(table, column):
    query = f"SELECT DISTINCT {column} FROM {table}"
    return pd.read_sql(query, session)

def get_filtered_data(table, country, line):
    query = f"SELECT * FROM {table} WHERE Country = '{country}' AND Line = {line}"
    return pd.read_sql(query, session)

# Fetch distinct values for filtering
distinct_countries = get_distinct_values('DEV_PROJECT.blah', 'Country')
distinct_lines = get_distinct_values('DEV_PROJECT.blah', 'Line')

# User selection
select_country = st.selectbox("Select Country", distinct_countries['Country'])
select_line = st.selectbox("Select Line", distinct_lines['Line'])

# Filter data based on selection
filtered_df = get_filtered_data('DEV_PROJECT.blah', select_country, select_line)
st.write(filtered_df)  # Display the filtered dataframe

# Editable form
st.header(":pencil2: Edit Data")
with st.form("Table write"):
    df_edited = st.experimental_data_editor(filtered_df, use_container_width=True)
    write_to_snowflake = st.form_submit_button("Update")

# Write changes back to Snowflake
if write_to_snowflake:
    with st.spinner("Updating…"):
        snowpark_df = session.create_dataframe(df_edited)
        snowpark_df.write.mode("overwrite").save_as_table('DEV_PROJECT.MANUAL_blah')
        st.success("Wrote to Snowflake successfully :tada:")

Let me know if you might require any further assistance!

Kind Regards,
Sahir

P.S. Lets connect on LinkedIn!

[sammy22]

Hello,

Thank you for this. The function ‘get_filtered_data’ does not exist?