Chapter 6 RPostgreSQL in Shiny Applications
Here we will show you how to use RPostgreSQL within your R-Shiny or Shiny application. This can be somewhat frustrating as you will need to take advantage of the paste() and paste0() base commands in R to send your text or numeric input data to the query itself.
6.1 Insert Query from Shiny Application
For us to send a query to the PostgreSQL database we will need to paste the query together. We will begin by constructing the usual INSERT INTO TABLENAME and then insert the respective values into the query as well. We will need to utilize the input\(value1** replacing whatever **input\)name we have for the value we want to insert into the databale when the query is sent. We can simply paste this into a new variable called qry and assign it and pass this qry to the dbSendQuery function in our application. As the number of columns increase so will the number of values as well.
qry = paste0("INSERT INTO table (column1,column2)",
"VALUES ('",paste(input$value1,"'",",","'",input$value2,"')"))
dbSendQuery(conn = con, statement = qry)You will want to have the query inside of an observeEvent. In the code below we are updating the table and also sending a shinyModal to the user to reflect what has just taken place.
library(shiny)
library(RPostgreSQL)
###UI side of RPostgresql app
shinyApp(
ui <- fluidPage(
sidebarPanel(
wellPanel(
textInput(inputId = "value1",label = "Value 1",width = "200px"),
textInput(inputId = "value2",label = "Value 2",width = "200px")
),
hr(),
wellPanel(
actionButton(inputId = "update_table_sql",label = "Update Table to RPostgreSQL")
)
),
mainPanel(tableOutput("data"))
),
###Server side of RPostgresql app
server <- function(input, output) {
##Update data in Rpostgresql table
observeEvent(input$update_sql,{
###Construct query to insert values into database table
qry = paste0("INSERT INTO <nameoftablehere> (column1,column2)",
"VALUES ('",paste(input$value1,"'",",","'",input$value2,"')"))
###Query to send to database
dbSendQuery(conn = con, statement = qry)
###shinyModal to show to user when the update to the database table is successful
showModal( modalDialog( title=paste0("RPostgreSQL table data Inserted"),
br(),
div(tags$b(paste0("You have Inserted the data into your RPostgresSQL table"), style = "color: green;"))
))
})
output$data <- renderTable(mtcars[1:10,1:8])
}
)Note: We have each value surrounded by the single back ticks ’ and also we have the values seperated by a comma as well.
6.2 Write Table Query from Shiny Application to the Database
Here you can take a dataframe and write it directly to the database using the dbWriteTable command. This allows us to write a dataframe directly to the table in question. You will need the dataframe column names to match the ones that are in the table in the database. The number of columns must match as well. You will not be able to write a dataframe with more columns than are in the table, but you will be able to write a dataframe that has less columns than are in the table in the database.
dbWriteTable(conn = con,name = 'table_name',value = table_value)
RPostgreSQL::postgresqlWriteTable(con = con,name = 'table_name',value = table_value,overwrite = TRUE)
RPostgreSQL::postgresqlWriteTable(con = con,name = 'table_name',value = table_value,append = TRUE)Write table object to RPostgreSQL:
###UI side of RPostgresql app
ui <- fluidPage(
actionButton(inputId = "write_table_sql",label = "Write Table to RPostgreSQL"),
)
###Server side of RPostgresql app
server <- function(input, output) {
##Update data in Rpostgresql table
observeEvent(input$write_table_sql,{
RPostgreSQL::postgresqlWriteTable(con = con,name = 'table_name',value = table_value,overwrite = TRUE)
###shinyModal to show to user when the table is written to the database is successful
showModal( modalDialog( title=paste0("RPostgreSQL table Updated"),
br(),
div(tags$b(paste0("You have written your table to the RPostgresSQL database"), style = "color: green;"))
))
})Above we have 3 distinct ways to write the data to the table in the database. The last two allow us to either overwrite or append the data to the table. Depending on our application needs we will be able to do one or the other.
6.3 Update Table Query from Shiny Application to the Database
This allows us to UPDATE one column at a time or several columns at once. Depending on the structure of the table we have.
qry = paste0("UPDATE table SET column1 = '",paste(input$value1,"')"))
dbSendQuery(conn = con, statement = qry)Update query to update data in RPostgreSQL table:
###UI side of RPostgresql app
ui <- fluidPage(
textInput(inputId = "value1",label = "Value 1"),
hr(),
actionButton(inputId = "update_table_sql",label = "Update Table to RPostgreSQL")
)
###Server side of RPostgresql app
server <- function(input, output) {
##Update data in Rpostgresql table
observeEvent(input$update_table_sql,{
###Construct Update Query to send to RPostgreSQL table
qry = paste0("UPDATE table SET column1 = '",paste(input$value1,"')"))
dbSendQuery(conn = con, statement = qry)
###shinyModal to show to user when the table is written to the database is successful
showModal( modalDialog( title=paste0("RPostgreSQL table Updated"),
br(),
div(tags$b(paste0("You have UPDATED your data in the RPostgresSQL database"), style = "color: green;"))
))
})
})