Ive asked this question on stack overflow and not had any response so I thought I woud try here for some help. I know this is not done thing but i would like to resolve this issue as I am at a dead end. Ideas or links to any help would be appreciated.
I would like to use Shiny to upload and append CSV files to a MySQL database. I can do this with generic R script and it works fine and thought it would be way better as a shiny app. Below is a simplified case. The CSV has three columns and the sql table has four with the frst being an id primary key that auto increments.
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 four columns as follows:
id - primary key, auto-increment
v1 - varchar(10)
v2 - varchar(10)
v3 - varchar(10)
The R script works just fine (see code below). But I run into problems with the shiny version. Below is th R script that works followed by the Shiny version. The shiny version sort of works but puts the data in the wrong columns and does not display the data just uploaded.
- The data gets uploaded to the schema table but the id column is populated with v1 data rather than autoincrementing , the v1 column gets v2 data, the v2 column gets v3 data and v3 column is NULL
- Data not displayed in a DT table except for the value “TRUE”
#R Script
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)
And here is the Shiny code i am using
# 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)