I am developing a shinydashboard to upload/append some csv files i get on a regular basis to a MySQL database. The app so far does three things.

1. allows nominating the csv file to be uploaded.
2. allows a text input of a project number that is added as new column to the data.
3. an action button to initiate the upload and display the data uploaded.

PROBLEM The first upload works fine. When I go to upload a second file by browsing or dropping the file, the App uploads the new file before i press the action button and so it will upload before I can change the project number. It will also start a new upload if I make any changes to the text input

The examples files and schema structure are simplified and shown below. Any clues on code I've missed or misused would be appreciated.

test.csv
v1,v2,v3
33,78,44
4,49,15
87,132,98

The database schema is called shiny and the table is named table1 with columns as follows:
id - primary key, auto-increment.
pn - varchar(10).
v1 - varchar(10).
v2 - varchar(10).
v3 - varchar(10).

Shiny code as follows

## app.R ##
library(shiny)
library(shinydashboard)
library(DT)
library(RMySQL)

# Database access information
user = 'root'
password = 'password'
host = 'host'
dbname='shiny'

ui <- dashboardPage(
  
  
  dashboardHeader(title = "Shiny SQL Upload App"),
  
  #### Main Sidebar Menu Items ####
  dashboardSidebar(width = 120,
                   sidebarMenu(
                     menuItem("Upload", tabName = "aa", icon = icon("dashboard")),
                     menuItem("Menu2", tabName = "bb", icon = icon("dashboard")),
                     menuItem("Menu3", tabName = "cc", icon = icon("dashboard")),
                     menuItem("Menu4", tabName = "dd", icon = icon("dashboard"))
                   ) # End sidebarMenu
  ), #End dashboardSidebar
  
  #### Main Body Content ####
  dashboardBody(
    shinyDashboardThemes(theme = "poor_mans_flatly"),
    tabItems(
      
      tabItem(tabName = "aa", # Start of Menu Item 1 aa
              fluidRow(
                tabBox(
                  width = 6,
                  id = "tabset1",
                  tabPanel("Up Load File", # Start of 1st subtab to first menu item
                           { 
                             sidebarLayout( # Sidebar style for subtab
                               sidebarPanel( # Sidebar panel details and content
                                 width = 7,
                                 fileInput("file1", label = "File input", accept = c(".xlsx", ".xls", ".csv", ".ods")),
                                 textInput("pn", "Project Number or Name", value = "", width = NULL, placeholder = NULL),
                                 actionButton("go", "Upload")
                               ), # End of sidebarPanel
                               mainPanel( # Main panel details and content
                                 width = 3,
                                 tableOutput("contents")
                                 
                               ) # End of mainPanel
                             ) # End of sidebarLayout
                           }
                  ), # End of tabPanel
                  
                  tabPanel("Instructions for Upload", # Start of 2nd subtab to first menu item
                           {
                             mainPanel(
                               "Insert instructions for upload here..." 
                             ) # End of mainPanel
                           }
                  ) # End of tabPanel
                ) # End of tabBox
              ) # End of fluidRow
      ), # End of tabItem menu Item 1
      
      #secondtab contents  menu bb
      tabItem(tabName = 'bb',
              h2("Menu 2 Tab Heading"),
              "text2 text2 text2 text2 text2"
      ),
      
      #third tab contents  menu cc
      tabItem(tabName = "cc",
              h2("Menu 3 Tab Heading"),
              "text3 text3 text3 text3 text3"
      ),
      
      #third tab contents menu dd
      tabItem(tabName = "dd",
              h2("Menu 4 Tab Heading"),
              "text4 text4 text4 text4 text4"
      )
    )
  )
)


server <- function(input, output) {
  observeEvent(input$go, {
    output$contents = 
      renderTable({
        inFile <- input$file1
        
        if (is.null(inFile))
          return(NULL)
        #connect to database 
        con <- dbConnect(MySQL(),
                         user = user,
                         password = password,
                         host = host,
                         dbname = dbname)
        data = read.csv(inFile$datapath, header = TRUE) # csv file contents to data dataframe
        data = mutate(data, pn = input$pn) # add project name to dataframe
        data <- data[,c(ncol(data),1:(ncol(data)-1))]  # move last col to first col
        dbWriteTable(conn = con, name = 'table1', value = data, append = TRUE, header = TRUE, row.names=FALSE) # upload dataframe to MySQL database
        lapply(dbListConnections(MySQL()), dbDisconnect) # close database connection
        data # Show data uploaded
      }
      )
  }
  )
}

shinyApp(ui, server)

CodePudding user response：

Try this

server <- function(input, output) {
 
  mydata <- eventReactive(input$go, {
    inFile <- input$file1
    if (is.null(inFile)) return(NULL)
    
    data = read.csv(inFile$datapath, header = TRUE) # csv file contents to data dataframe
    data = mutate(data, pn = input$pn) # add project name to dataframe
    data <- data[,c(ncol(data),1:(ncol(data)-1))]  # move last col to first col
    data
  })
  output$contents <- renderTable({
    req(mydata())
    #connect to database 
    con <- dbConnect(MySQL(),
                     user = user,
                     password = password,
                     host = host,
                     dbname = dbname)
    dbWriteTable(conn = con, name = 'table1', value = mydata(), append = TRUE, header = TRUE, row.names=FALSE) # upload dataframe to MySQL database
    lapply(dbListConnections(MySQL()), dbDisconnect) # close database connection
    mydata() # Show data uploaded
  })
}