delete a record from datatable based on the value of textInput Box

Question

How can I delete a record with an action button? The delete action button executes the command properly if I hardcode what I want deleted but it gets grayed out if I want to delete dynamically by pointing at the "delete2" textInput box. For example, if I want to delete a record in the testing table that has a value of 100, I want to type the 100 value in the "delete2" box and when the action button is clicked, it will execute the command and delete the record.

Currently, the button works when I type the 100 value on my delete statement but it doesn't refresh the table until the next time I open the app. To summarize, assume that I want to delete a record from "value1" of 100, How can I delete it dynamically and refresh the datatable? Hope is clear. Below is what I tried:

library(shiny)
library(RSQLite)

ui = fluidPage(
    shinyUI(fluidPage(
      titlePanel("Enter Data into SQLite"),
      sidebarLayout(
        sidebarPanel(
    textInput("value1", label = "Value 1"),
    textInput("value2", label = "Value 2"),
    
    dateInput("SampleDate", label = "Today",value = ""),  
    actionButton("action", label = "Write to DB"),
    actionButton("delete", label = "Delete Records"),
    textInput("delete2", label = "delete these records"),#type record number to be deleted
    
        ),
    mainPanel(
      dataTableOutput("table")
  )
      )
    )))
  
  server = function(input, output,session){
    session$onSessionEnded(function() {
      stopApp()
    }) 
    
    data <- eventReactive(input$action, {
      con <- dbConnect(SQLite(), dbname="sample.sqlite",extended_types=TRUE) 
     dbWriteTable(con, "testing", data.frame(value1 = input$value1, value2 = input$value2, 
                          SampleDate = input$SampleDate, stringsAsFactors = FALSE), append = TRUE)
      
      data <- dbReadTable(con, "testing")
      dbDisconnect(con)
      #Clear form fields to enter more data
      updateTextInput(session, "value1", "value 1", value="")
      updateTextInput(session, "value2", "value 2", value="")
      updateDateInput(session = session, inputId = "SampleDate", value = NA)  
      return(data)
    })
    
  output$table <- renderDataTable(data())

   #Everything above works as expected but the code below to delete the record is not working   
  
   observeEvent(input$delete, {
    con <- dbConnect(SQLite(), dbname="sample.sqlite",extended_types=TRUE) 
    dbExecute(con,"DELETE from testing WHERE value1 = input$delete2") 
    dbDisconnect(con)
  })
  output$table <- renderDataTable(data()) #datatable is not refreshing
  
    
  } 
  shinyApp(ui = ui, server = server)

score 0 · Accepted Answer · answered Jul 22 '22 at 17:20

One option to achieve your desired result would be to move the "reading" part of your eventReactive into an reactive which make reactive to both input$action and input$delete, i.e. it gets updated if either button is clicked. After doing so you could replace the eventReactive by an observeEvent which now just performs the writing part.

Additionally I made two adjustments. First, I made your example reproducible by adding the code to create a sample database. Second, it's not best practice to connect and disconnect from the database for one action. Instead I use the pool package which connects to the database once when the app is started and disconnects when finished. Third, I use today's Date as the default value for the DateInput as using "" will throw a warning.

library(shiny)
library(RSQLite)
library(DBI)

pool <- pool::dbPool(RSQLite::SQLite(), dbname = "sample.sqlite", extended_types = TRUE)

# Create sql lite df
sample_df <- data.frame(
  value1 = numeric(),
  value2 = numeric(),
  SampleDate = as.Date(x = integer(0), origin = "1970-01-01")
)

# Create responses table in sql database
dbWriteTable(pool, "sample_df", sample_df, overwrite = FALSE, append = TRUE)

ui <- fluidPage(
  shinyUI(fluidPage(
    titlePanel("Enter Data into SQLite"),
    sidebarLayout(
      sidebarPanel(
        textInput("value1", label = "Value 1"),
        textInput("value2", label = "Value 2"),
        dateInput("SampleDate", label = "Today", value = Sys.Date()),
        actionButton("action", label = "Write to DB"),
        actionButton("delete", label = "Delete Records"),
        textInput("delete2", label = "delete these records"), # type record number to be deleted
      ),
      mainPanel(
        dataTableOutput("table")
      )
    )
  ))
)

server <- function(input, output, session) {
  session$onSessionEnded(function() {
    stopApp()
  })

  data <- reactive({
    input$action
    input$delete

    dbReadTable(pool, "testing")
  })

  observeEvent(input$action, {
    dbWriteTable(pool, "testing", data.frame(
      value1 = input$value1, value2 = input$value2,
      SampleDate = as.Date(input$SampleDate), stringsAsFactors = FALSE
    ), append = TRUE)

    updateTextInput(session, "value1", "value 1", value = "")
    updateTextInput(session, "value2", "value 2", value = "")
    updateDateInput(session = session, inputId = "SampleDate", value = Sys.Date())
  })

  observeEvent(input$delete, {
    dbExecute(pool, paste("DELETE from testing WHERE value1 = ", input$delete2))
    
    updateTextInput(session, "delete2", value = "")
  })

  output$table <- renderDataTable(data())
}

shinyApp(ui = ui, server = server)

Thanks @stefan, that works really well. Will try to apply this example to my huge database. I am trying to master the reactive stuff but still can't grasp it. :) — Salvador, Jul 22 '22 at 23:30
Forgot to ask this. Is there a way to hide the table at starup but make the table popup when the action button is clicked? — Salvador, Jul 22 '22 at 23:45

delete a record from datatable based on the value of textInput Box

1 Answers1