UPDATE TABLE with DataGrid editable=True

shiny
, ,
1

I have a shiny application that is linked to a SQLite database.

On page 2, there is an editable table df_articulos and I want that when editing a value in the table, take the edited value and sends an UPDATE table in SQL to edit the value within the db.[df_articulos]


from shiny.express import input, render, ui
from shinyswatch import theme
import transformardatos
from shiny import reactive


# Conectar a la base de datos y obtener los artículos
df_articulos = transformardatos.df_articulos


# Configurar la UI con tema y páginas
ui.page_opts(title="Hello shinyswatch theme", theme=theme.cosmo)
ui.page_opts(title="Gestión de Planificación y Desarrollo")

# Configurar la barra lateral con un menú acordeón


# Configurar las páginas

with ui.navset_pill(id="tab"):
    with ui.nav_panel("Inicio"):
        @render.text
        def valor():
            return f"Valor: {input.slider1() + input.slider2()}"
        
    
            

    with ui.nav_panel("Pagina 2"):       
        with ui.navset_pill(id="tabEdicion"):            
            ui.nav_spacer()
          
            with ui.nav_panel("Articulos"):
                with ui.layout_columns():
                    with ui.card():
                        ui.card_header("Articulos registrados por las áreas") 
                                     
                        @render.data_frame
                        def articulos_df():
                            return render.DataGrid( df_articulos,
                                                   filters=True,
                                                   width="100%",  
                                                   selection_mode="rows", 
                                                   height="600px", 
                                                   editable=True)

The dt_articulos is a sql query:


import sqlite3

# db connect

conexion = sqlite3.connect("GestionPyD")
cursor = conexion.cursor()

cursor.execute("""
CREATE TABLE IF NOT EXISTS articulos (
    ArtID INTEGER NOT NULL PRIMARY KEY,
    Articulo TEXT,
    Precio REAL,
    Rubro TEXT,
    Proceso TEXT,
    Fgk_Objetal TEXT,
    TipoGasto TEXT,
    Fgk_GestionID INTEGER,
    FOREIGN KEY(Fgk_GestionID) REFERENCES gestion(GestionID)
    FOREIGN KEY(Fgk_Objetal) REFERENCES objetales(ObjetalID)
)
""")

tbl_articulos = data.cursor.execute(
    "SELECT Articulo, Precio, Rubro,  Fgk_Objetal FROM articulos"
    ).fetchall()

columnas = [description[0] for description in data.cursor.description]
df_articulos = pd.DataFrame(
    tbl_articulos, 
    columns=columnas)

I am just starting the application, because the structure is still very simple but I want to know if it is possible to do it to continue with Python or if I return to shiny for R.

I tried something like this:


@reactive.Effect
def generate_sql():
    if input.edit():
        selected_row = input.articulos_df_rows_selected()
        if selected_row:
            selected_data = df_articulos.iloc[selected_row[0]]
            articulo_id = selected_data['ArtID']
            articulo_valor = selected_data['Articulo']