Shiny app upload CSV to MySQL

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.

  1. 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
  2. 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)

I havn't tested, but don't you want to save the result of read.csv(...) in a variable, and then use that variable in dbWriteTable()?
That is:
data<-read.csv(...)
dbWriteTable(...,value=data,...)
And maybe you also need read.csv(...,sep=",") as in your R-script.

I think a stroll back through a few of the Shiny demos might help here.

The reason that your Shiny app is just showing TRUE in the table is because the order of your statements in renderTable. Think of all Shiny render* functions like you are writing your own function - the last value in the expression is what you will see. In your example, you have dbWriteTable last in renderTable.
dbWriteTable returns a logical value based on whether or not the data was committed to the database successfully, not a dataframe, thus you only get TRUE. So to fix it, just switch the order of read.csv and dbWriteTable.

As a side note, putting these kinds of side effects into a render function is IMO not a best practice and can lead to code that is hard to debug. If it were me, I would recommend moving inFile out of renderTable and into a standalone reactive variable, then observe the reactive variable and on change, commit the file to a database.

Hopefully that helps

1 Like

Yes that works. Thanks @tbendsen the upload works fine perfectly which is main goal. The tabulate still pops up a true taher than the data. I will persist with this dwn the track but mu main goal is solved.

I swapped the order to no avail. I get an error as follows:

unable to find an inherited method for function ‘dbWriteTable’ for signature ‘"MySQLConnection", "character", "function"’

I take your point on good practice but im not a programmer im just after and easy tool to help me store CSV files. The table is a nice to have item but for now im happy that it uploads successfully. Thanks for your help @dvetsch75

dbWriteTable always return TRUE or FALSE to indicate if the operation was successful. So if you want renderTable() to return a reactive table, you should probably just do:

data<-read.csv(....)
dbWriteTable(conn=con,value=data,...)
data

inside your rendertable({...}).

1 Like

totally works. Thanks .

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.