Strange behavior when using st.form and st.data_editor components

🎈 Using Streamlit
1

Hi, I am having problems with using the st.form and st.data_editor components. I am developing a small application for financial record keeping. For this I use the st.data_editor component for the input of new information such as incoming or outgoing money. Once modified, I proceed to update an excel hosted in my google drive, which contains the complete information of the financial record, which allows me a daily update.

The following code represents part of my application and is also where the problem is located.

import streamlit as st
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
from pydrive.auth import GoogleAuth
from pydrive.drive import GoogleDrive

if 'credentials' not in st.session_state:
  gauth = GoogleAuth()
  directorio_credenciales = 'credentials_module.json'
  gauth.LoadCredentialsFile(directorio_credenciales)
  if gauth.access_token_expired:
      gauth.Refresh()
      gauth.SaveCredentialsFile(directorio_credenciales)
  else:
      gauth.Authorize()
  st.session_state.credentials = GoogleDrive(gauth)

with st.form('form1'):
  data       = st.data_editor(pd.read_excel(pd.ExcelFile(f"https://docs.google.com/spreadsheets/d/1-qpdnIsc6rPTLULLAzg34Bl_pIF9F5XAHrPtVln3Ca4/export?format=xlsx"), sheet_name = "Sheet1"),  num_rows = "dynamic", use_container_width = True)
  submitted1 = st.form_submit_button("Actualizar", use_container_width = True)
  if submitted1:
      st.dataframe(data)
      data.to_excel("Contabilidad.xlsx", index = None)
      idexe = '1-qpdnIsc6rPTLULLAzg34Bl_pIF9F5XAHrPtVln3Ca4'
      gfile = st.session_state.credentials.CreateFile({'id': idexe})
      gfile.SetContentFile(r'Contabilidad.xlsx')
      gfile.Upload()

The problem I have is that when the excel update depends on the intent. I explain it below:

Attempt 1: The excel is updated according to what is registered in the st.data_editor.

Attempt 2: The excel is not updated and the modified in the st.data_editor is lost.

Attempt 3: The excel is updated according to what is registered in the st.data_editor.

Attempt 4: The excel is not updated and the modified in the st.data_editor is lost.

Attempt 5: The excel is updated according to what is registered in the st.data_editor.

.
.
.

I did not understand why this happened, so I tried with the following code, to see the behavior of the table without the update of the excel, and it works normal, what I modified was reflected correctly when I press the button. This left me more confused.

import streamlit as st
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
from pydrive.auth import GoogleAuth
from pydrive.drive import GoogleDrive

if 'credentials' not in st.session_state:
  gauth = GoogleAuth()
  directorio_credenciales = 'credentials_module.json'
  gauth.LoadCredentialsFile(directorio_credenciales)
  if gauth.access_token_expired:
      gauth.Refresh()
      gauth.SaveCredentialsFile(directorio_credenciales)
  else:
      gauth.Authorize()
  st.session_state.credentials = GoogleDrive(gauth)

with st.form('form1'):
  data       = st.data_editor(pd.read_excel(pd.ExcelFile(f"https://docs.google.com/spreadsheets/d/1-qpdnIsc6rPTLULLAzg34Bl_pIF9F5XAHrPtVln3Ca4/export?format=xlsx"), sheet_name = "Sheet1"),  num_rows = "dynamic", use_container_width = True)
  submitted1 = st.form_submit_button("Actualizar", use_container_width = True)
  if submitted1:
      st.dataframe(data)

I attach the following video to make it clearer what I describe.

I hope you can help me, because for the moment, the solution I found is to use st.experimental_rerun, but it does not seem to me the optimal solution. Thank you.

2

Hi @Junior19, i suggest that you place the st.data_editor outside of the form and use a standard button to submit. For example

data = st.data_editor(pd.read_excel(pd.ExcelFile(f"https://docs.google.com/spreadsheets/d/1-qpdnIsc6rPTLULLAzg34Bl_pIF9F5XAHrPtVln3Ca4/export?format=xlsx"), sheet_name = "Sheet1"),  num_rows = "dynamic", use_container_width = True)
submitted1 = st.button("Actualizar", use_container_width = True)
  if submitted1:
      st.dataframe(data)
      data.to_excel("Contabilidad.xlsx", index = None)
      idexe = '1-qpdnIsc6rPTLULLAzg34Bl_pIF9F5XAHrPtVln3Ca4'
      gfile = st.session_state.credentials.CreateFile({'id': idexe})
      gfile.SetContentFile(r'Contabilidad.xlsx')
      gfile.Upload()

Let me know if this works!

3

Hi @mliu , I tried your suggestion but it is not what I expected. If the st_data_editor is outside the form, then every time I edit a cell of this, the application will be executed, which is not adequate and on the other hand, it seems to me that the same problem of the attempts to update the excel, that is, the first time it works, then the second time it doesn’t, the third time it works, and so on. In the documentation it is indicated that when forms are used, when clicking on their respective button, the application is executed again, but I think it is not like that, it seems to me that sometimes only the part altered by the form is executed and other times everything is executed, which would explain my problem, but I am not sure actually, I just read a little. Greetings !

4

Hi @Junior19
The issue you’re encountering with your Streamlit app may be related to how Streamlit handles data_editor and form submission interactions.

The Streamlit data_editor component is designed to provide an interactive editing interface, but its behavior in combination with form submission might lead to unexpected results.

  1. In your first code example, when you press the “Actualizar” button (submitted1), it updates the Excel file with the data from st.data_editor. However, since st.dataframe(data) is called immediately after the update, it might reload the data from the Excel file, essentially reverting any changes made in st.data_editor.

  2. In your second code example, without the Excel update part, the behavior appears normal because it only reflects the changes made in st.data_editor without any interference from an external data update.

To address this issue and ensure your updates from st.data_editor are reflected in your Excel file, you should avoid immediately reloading the data from the Excel file after an update. Instead, update the Excel file only when you want to save changes.

import streamlit as st
import pandas as pd

# Load your data initially
data = pd.read_excel("Contabilidad.xlsx")

with st.form('form1'):
    # Display the data in the data_editor
    edited_data = st.data_editor(data, num_rows="dynamic", use_container_width=True)
    submitted1 = st.form_submit_button("Actualizar", use_container_width=True)

if submitted1:
    # Update the Excel file with the edited data
    edited_data.to_excel("Contabilidad.xlsx", index=None)

This way, you load the data from the Excel file initially, and when you submit the form, you update the Excel file only if changes were made in st.data_editor. This should prevent the issue of the modified data from being lost.

I hope it will help you, if you face any error then tell me. Thanks

5

Hi @abdulrehman

I tried your code and the problem of the attempts continues, that is, the update occurs intermittently and not continuously. The only difference is that the excel in my code is in google drive, and in your code is a local file, I do not know if this way it would work correctly.

import streamlit as st
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
from pydrive.auth import GoogleAuth
from pydrive.drive import GoogleDrive

if 'credentials' not in st.session_state:
  gauth = GoogleAuth()
  directorio_credenciales = 'credentials_module.json'
  gauth.LoadCredentialsFile(directorio_credenciales)
  if gauth.access_token_expired:
      gauth.Refresh()
      gauth.SaveCredentialsFile(directorio_credenciales)
  else:
      gauth.Authorize()
  st.session_state.credentials = GoogleDrive(gauth)

data = pd.read_excel(pd.ExcelFile(f"https://docs.google.com/spreadsheets/d/1-qpdnIsc6rPTLULLAzg34Bl_pIF9F5XAHrPtVln3Ca4/export?format=xlsx"), sheet_name = "Sheet1")

with st.form('form1'):
    # Display the data in the data_editor
    edited_data = st.data_editor(data, num_rows="dynamic", use_container_width=True)
    submitted1  = st.form_submit_button("Actualizar", use_container_width=True)

if submitted1:
    # Update the Excel file with the edited data
    edited_data.to_excel("Contabilidad.xlsx", index=None)
    idexe = '1-qpdnIsc6rPTLULLAzg34Bl_pIF9F5XAHrPtVln3Ca4'
    gfile = st.session_state.credentials.CreateFile({'id': idexe})
    gfile.SetContentFile(r'Contabilidad.xlsx')
    gfile.Upload()

I attach a new video of my app, which works as I expect …

… but as I mentioned, it is because I am using st.experimental_rerun.

with st.form("form1"):
    data = st.data_editor(
        st.session_state.xls_drive1, num_rows="dynamic", use_container_width=True
    )
    submitted1 = st.form_submit_button("Actualizar", use_container_width=True)
    empty1 = st.empty()
    if submitted1:
        if st.session_state.contraseña == password:
            st.session_state.xls_drive1 = data
            data.to_excel("Contabilidad.xlsx", index=None)
            idexe = "1-qpdnIsc6rPTLULLAzg34Bl_pIF9F5XAHrPtVln3Ca4"
            gfile = st.session_state.credentials.CreateFile({"id": idexe})
            gfile.SetContentFile(r"Contabilidad.xlsx")
            gfile.Upload()
            empty1.success("Actualizacion realizada")
            time.sleep(3)
            empty1.empty()
            st.experimental_rerun()
        else:
            empty1.warning("Actualizacion no realizada")
            time.sleep(3)
            empty1.empty()

Cheers !

6

This topic was automatically closed 180 days after the last reply. New replies are no longer allowed.