Pulling Snowflake Worksheet Data into a Dataframe in Snowsight

[st.jfern]

Summary

I have my sql query made up to pull the desired data from Snowflake Worksheets. But I want to call that box of data into a cell in my dataframe.

Steps to reproduce

Code snippet:

#name of input table
st.subheader("User Inputs")

sql_query = "select example from example_table where condition" 

#create dataframe with input data and input columns
df = session.create_dataframe(
    [[0,0,0,0,0,0,0]],
    schema=["COLUMN1","COLUMN2","COLUMN3","COLUMN4","COLUMN5","COLUMN6","COLUMN7"]

# Execute the query and convert it into a Pandas dataframe
queried_data = df.to_pandas()

#display dataframe for editable columns
st.experimental_data_editor(queried_data)

If applicable, please provide the steps we should take to reproduce the error or specified behavior.

Expected behavior:
It should show the box of data from worksheets in the under the cell labeled “COLUMN6”.

Actual behavior:
Right now I just have the editable dataframe and the query I used to select the data in Snowflake Worksheets.

[Caroline]

Hey @st.jfern,

Thanks for sharing this question! How are you connecting to Snowflake? I assume via Snowpark?

It looks like your code snippet might be missing the piece that executes the SQL query, e.g. session.sql(sql_query)

[st.jfern]

Hi Caroline, yes I’m connecting to Snowflake via Snowpark. My code as of now:

import streamlit as st
from snowflake.snowpark.context import get_active_session
st.subheader("User Inputs")

sql_query = "select example from example_table where condition" 

#create dataframe with input data and input columns
df = session.create_dataframe(
    [[0,0,0,0,0,0,0]],
    schema=["COLUMN1","COLUMN2","COLUMN3","COLUMN4","COLUMN5","COLUMN6","COLUMN7"]

queried_data = df.to_pandas()

#display dataframe for editable columns
st.experimental_data_editor(queried_data)


 session.sql('''select CALM_AVG_OFSET_PCT from lqx.stg.VW_NSCC_LQDTY_PRJCTD_FMLY_ND  where bus_dt=20230510 and slice=1 and fmly_id='00950047' 
''').collect()

How would I integrate session.sql into the df dataframe? Appreciate your help!

[Caroline]

Seems like you’re on the right track! I would just add the empty input columns to the Pandas DataFrame rather than via Snowpark. So something like this:

import streamlit as st
from snowflake.snowpark.context import get_active_session
st.subheader("User Inputs")

df = session.sql('''select CALM_AVG_OFSET_PCT from lqx.stg.VW_NSCC_LQDTY_PRJCTD_FMLY_ND  where bus_dt=20230510 and slice=1 and fmly_id='00950047' ''').collect().to_pandas()

# add empty columns to dataframe
df['COLUMN_1'] = 0
df['COLUMN_2'] = 0
df['COLUMN_3'] = 0
df['COLUMN_4'] = 0
df['COLUMN_5'] = 0
df['COLUMN_6'] = 0
df['COLUMN_7'] = 0

# display dataframe in data_editor
st.experimental_data_editor(df)