I would like to use R Shiny to upload csv data to MySQL table and display

Question

Apologies for the long question. I wrote the following R script to upload/append some csv files i get on a regular basis to a MySQL database. The examples files and schema structure are simplified.

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 tab1 with columns as follows:
id - primary key, auto-increment
v1 - varchar(10)
v2 - varchar(10)
v3 - varchar(10)

The basic R script in the first code snippet works fine and appends the data in the CSV file to the schema table and an auto-incrementing id number is added to the SQL table.

I would like a shiny app to do the same (upload and append to a MySQL table) and then display the data just uploaded in a DT table. The Shiny code in the second snippet sort of works except for the following issues:

1. The data gets uploaded to the schema table but the id column is populated with v1 data, v1 column with v2 data, v2 column with v3 data and v3 column is NULL
2. Data not displayed in a DT table except for the value “TRUE”

Any ideas or links to pages with possible solutions would be greatly appreciated.

The basic R script that works is as follows:

library(RMySQL)

#Read in data to a data frame
data <- read.csv(test.csv", header = TRUE, sep = ",")

# input database access values
user = 'user'
password = 'password'
host = 'host'
dbname='shiny'

#connect to database
con <- dbConnect(MySQL(),
                 user = user,
                 password = password,
                 host = host,
                 dbname = shiny)

# write to database
dbWriteTable(conn = con, name = 'tab1', value = data, append = TRUE, header = TRUE, row.names=FALSE)

# remove data and inputs and disconnect
rm(data)
rm(dbname)
rm(host)
rm(password)
rm(user)
rm(con)
lapply(dbListConnections(MySQL()), dbDisconnect)

The shiny code im trying is as follows:

# Shiny app for SQL append
library(shiny)
library(DT)
library(RMySQL)

# database access information
user = 'root'
password = 'rustymarmot'
host = 'localhost'
dbname='shiny'

#connect to database
con <- dbConnect(MySQL(),
                 user = user,
                 password = password,
                 host = host,
                 dbname = shiny)

## USER INTERFACE
ui <- fluidPage(
  
  ## App title
  titlePanel("SQL Upload"),
  
  ## Sidebar layout 
  sidebarLayout(
    sidebarPanel(
      fileInput("file1", label = "File input", accept = c(".xlsx", ".xls", ".csv", ".ods"))
    ),
    ## End Sidebar layout
    mainPanel(
      tableOutput("contents")
    )
  )
)

# SERVER
server = shinyServer(function(input,output){
  output$contents = renderTable({
    inFile <- input$file1
    
    if (is.null(inFile))
      return(NULL)
    
    read.csv(inFile$datapath, header = TRUE)
    dbWriteTable(conn = con, name = 'tab1', value = inFile$datapath, append = TRUE, header = TRUE, row.names=FALSE)
  })
})

shinyApp(ui, server)

Answer 1

little changes needed. Use like this and you have both the data displayed and uploaded correctly.

data <- read.csv(inFile$datapath, header = TRUE)
dbWriteTable(conn = con, name = 'tab1', value = data, append = TRUE, header = TRUE, row.names=FALSE)
data