How to set a NULL value in an update statement using streamlit data editor

[William_Garrard]

I am running Streamlit v 1.46.1 on Snowflake.

I have a simple app that grabs the data in a table and displays it in a data editor widget. Most of the values in the table are NULL to begin with, except for one column which is the PK. The user can then enter data in some or all of the cells. The form submit button connected to the data editor loops through the updated rows and sends off an update statement for each one.

editedHistory=st.data_editor(dfHistory, key="editor");
submitChanges = st.form_submit_button("Submit Changes");
if(submitChanges):
        try:
            with st.spinner(text="Saving Changes..."):    
                #Get edited rows.
                dict=st.session_state.editor["edited_rows"];
                #For each edited row,
                for key in dict:
                    #Compose and execute an update statement.
                    sqlUpdate="""UPDATE MY_TABLE SET 
                                 COLUMN_A=?,
                                 COLUMN_B=?,
                                 COLUMN_C=?,
                                 COLUMN_D=?,
                                 WHERE COLUMN_PK=?""";
                    #Extract binding variables from current row of edited dataframe.
                    sqlArgs=[editedHistory.loc[int(key),'COLUMN_A'],
                             editedHistory.loc[int(key),'COLUMN_B'],
                             editedHistory.loc[int(key),'COLUMN_C'],
                             editedHistory.loc[int(key),'COLUMN_D'],
                             editedHistory.loc[int(key),'COLUMN_PK']];                            
                    st.connection("snowflake").cursor().execute(sqlUpdate,sqlArgs);
                    #End for.
                st.success("Update successful");
        except Exception as e:
            st.write(e); 

This works for any cell with a value in it. But if the user leaves a value empty, the editedHistory.loc call returns a NULL value, and this is, for some unknown reason, translated into the string literal “None” and saved to the database during the execution of the update statement. This is undesirable behavior.

For additional information, here’s what I see when I print out the sqlArgs right before the execution call if I only update one column:

And then in the database for that row, I see the string literal “None” for all the cells I did not edit.

How can I get snowflake to understand that a NULL value in the arguments should be a NULL in the database?

Thank you

[Prakash14]

To handle this, you’ll want to make sure your update logic only includes the columns that actually have values. A practical approach is to loop through each edited row, check each column, and add it to the UPDATE statement only if it’s not None, NaN, or empty. This way, Snowflake will only update the fields the user has changed, while any untouched cells will remain as NULL in the database instead of being saved as the string "None".

[William_Garrard]

Thank you. This is a fine workaround. However, I think its less than ideal that we have to do it. Streamlit/python/snowflake should be able to natively handle a None value in an update statement and have it result as a NULL in the database. I can’t be the only person with this use case.