Writing information into PostgreSQL database through Shiny app

Question

need some wisdom from the community.

My objective is to build a primitive Shiny app, where I will insert some values. I am not super familiar with SQL, so I stumbled.

I have a remote PostgreSQL database and use Navicat 11.

My test database has only two columns - "id" and "message". I want to insert id and message through shiny app and store it remotely.

I used a tutorial Persistent data storage in Shiny apps by Dean Attali.

This is my code

# Set libraries
library(RPostgreSQL)
library(shiny)

# Define the fields we want to save from the form
fields <- c("id", "message")

# Shiny app with two fields that the user can submit data for
shinyApp(
ui = fluidPage(
DT::dataTableOutput("responses", width = 300), tags$hr(),
textInput("id", "ID", ""),
textInput("message", "MESSAGE", ""),
actionButton("submit", "Submit")
),

server = function(input, output, session) {

databaseName <- "XXXXX"
table <- "XX_XXX"
psql <- dbDriver("PostgreSQL")

saveData <- function(data) {
  # Connect to the database
  pcon <- dbConnect(psql, dbname = "XXX", host = "XXXXXXX", port = XXXX, user = "XXXX", password = "XXXXX")
  # Construct the update query by looping over the data fields
  query <- sprintf(
    "INSERT INTO id (id) VALUES ('message')",
    table, 
    paste(names(data), collapse = ", "),
    paste(data, collapse = "', '")
  )
  # Submit the update query and disconnect
  dbGetQuery(pcon, query)
  dbDisconnect(pcon)
}

  loadData <- function() {
  # Connect to the database
  pcon <- dbConnect(psql, dbname = "XXX", host = "XXXXXXX", port = XXXX, user = "XXXX", password = "XXXXX")
  # Construct the fetching query
  query <- sprintf("SELECT * FROM id", table)
  # Submit the fetch query and disconnect
  data <- dbGetQuery(pcon, query)
  dbDisconnect(pcon)
  data
}


# Whenever a field is filled, aggregate all form data
formData <- reactive({
  data <- sapply(fields, function(x) input[[x]])
  data
})

# When the Submit button is clicked, save the form data
observeEvent(input$submit, {
  saveData(formData())
})

# Show the previous responses
# (update with current response when Submit is clicked)
output$responses <- DT::renderDataTable({
  input$submit
  loadData()
})

} )

This is my error:

Error in postgresqlExecStatement(conn, statement, ...) :

RS-DBI driver: (could not Retrieve the result : ERROR: relation "id" does not exist

LINE 1: SELECT * FROM id ^ )

Warning in postgresqlQuickSQL(conn, statement, ...) :

Could not create execute: SELECT * FROM id*

I as understand, I do a wrong sql query. Any ideas? Really appreciate you help!

Your INSERT and SELECT are trying to insert/query the id column, when they should be referencing the table. Is the table obfuscated (XX_XXX)? Either way, the SQL would be something like INSERT INTO "XX_XXX" VALUES (...), and the SELECT would be SELECT * FROM "XX_XXX" (substitute the real table name if XX_XXX is not the real name). — bma
According to the link you supplied, the table name should be responses, so maybe try the same code in that article, eg. "INSERT INTO %s (%s) VALUES ('%s')" — bma
@bma, thank you very much for such a fast response. I see, so insert and select should work with both columns ("id" and "message"). I changed to query <- sprintf( "INSERT INTO %s (%s) VALUES ('%s')" for save data.For load data I am writing query <- sprintf("SELECT * FROM (database name)", table). — Anakin Skywalker
The error is the same Listening on 127.0.0.1:5455 Error in postgresqlExecStatement(conn, statement, ...) : RS-DBI driver: (could not Retrieve the result : ERROR: relation "rw_messages" does not exist LINE 1: SELECT * FROM (database name) ^ ) Warning in postgresqlQuickSQL(conn, statement, ...) : Could not create execute: SELECT * FROM (database name). I was thinking maybe I am giving a wrong name to the database. But I tried just table and database.table - nothing — Anakin Skywalker
I've never heard of Shiny so I don't have much advice to offer there, but I suggest you write your basic SELECT (and INSERT) queries and test them against your database using the psql Postgres client, or PGAdmin, or whatever GUI you have at hand to query your Postgres databases. Once you have proven your SQL works, then you can run it in your app, with a better idea of what the failures mean. — bma

Anakin Skywalker Anakin Skywalker · Accepted Answer · 2018-01-18T23:32:28

Resolved.

# Set libraries
library(RPostgreSQL)
library(shiny)

# Define the fields we want to save from the form
fields <- c("id", "message")

# Shiny app with two fields that the user can submit data for
shinyApp(
ui = fluidPage(
DT::dataTableOutput("responses", width = 300), tags$hr(),
textInput("id", "ID", ""),
textInput("message", "MESSAGE", ""),
actionButton("submit", "Submit")
),
server = function(input, output, session) {


psql <- dbDriver("PostgreSQL")

saveData <- function(data) {
  # Connect to the database
  pcon <- dbConnect(psql, dbname = "XXX", host = "XXXXX", port = XXXX, user 
= "UserX", password = "PaswordX")
  # Construct the update query by looping over the data fields
  query <- paste0("INSERT INTO table_name.schema_name (message) VALUES ( $1 
)") 
  # Submit the update query and disconnect
  dbSendQuery(pcon, query, params=data[["message"]]) 
  dbDisconnect(pcon)
}

loadData <- function() {
  # Connect to the database
  pcon <- dbConnect(psql, dbname = "XXX", host = "XXXXX", port = XXXX, user = "UserX", password = "PaswordX")
  # Construct the fetching query
  query <- sprintf("SELECT * FROM table_name.schema_name") 
  # Submit the fetch query and disconnect
  data <- dbGetQuery(pcon, query)
  dbDisconnect(pcon)
  data
}



# Whenever a field is filled, aggregate all form data
formData <- reactive({
  data <- sapply(fields, function(x) input[[x]])
  data
})

# When the Submit button is clicked, save the form data
observeEvent(input$submit, {
  saveData(formData())
})

# Show the previous responses
# (update with current response when Submit is clicked)
output$responses <- DT::renderDataTable({
  input$submit
  loadData()
})     
}
)

Writing information into PostgreSQL database through Shiny app

1 Answers