Streamlit Snowflake

Streamlit and Snowflake
1

Hi I am new to snowflake. I am developing an app that displays data in a data frame. The requirements are not too much. A user should have the ability to update the data reflected in the data frame and have the change reflect in the snowflake table. Strictly, nothing else but to update. I use st.dataeditor() function, but does anyone know or have any links on how to make the changes in my data frame reflect in my snowflake table?

2

Hi @david74, welcome to the forum!

I don’t have an article to link for you, but here’s a basic app that does the trick. Here’s some more about the snowflake connection: Getting started with Streamlit in Snowflake | Snowflake Documentation

This isn’t necessary the best or only way to do this, but it’s one way to approach the problem. Especially if you’re just working with small tables, this is probably a reasonable approach. If you’re working with larger tables, there are more efficient ways than dropping and re-creating the whole table.

import streamlit as st

connection = st.connection("snowflake")

table = "public.tables.sync_test"


def create_table():
    # Create initial table
    connection.cursor().execute(
        f"CREATE TABLE IF NOT EXISTS {table} (id INT, name STRING)"
    )

    # Insert data
    connection.cursor().execute(
        f"INSERT INTO {table} (id, name) VALUES (1, 'John'), (2, 'Jane'), (3, 'Jim')"
    )


try:
    current_data = connection.query(f"SELECT * FROM {table}", ttl=0)
except Exception:
    create_table()
    current_data = connection.query(f"SELECT * FROM {table}", ttl=0)

new_data = st.data_editor(current_data)

new_data_list = new_data.to_dict(orient="records")

new_data_list = [f"({row['ID']}, '{row['NAME']}')" for row in new_data_list]
new_data_list = f"{','.join(new_data_list)}"

if st.button("Sync table"):
    # Make these both happen in a transaction
    connection.cursor().execute("BEGIN")
    connection.cursor().execute(f"TRUNCATE TABLE {table}")
    query = f"INSERT INTO {table} (id, name) VALUES {new_data_list}"
    connection.cursor().execute(query)
    connection.cursor().execute("COMMIT")
    st.toast("Table synced")
    st.rerun()