CODEWITHSUNDEEP
rpostgresqlshiny
Anakin Skywalker
Anakin Skywalker

Reputation: 2520

Writing information into PostgreSQL database through Shiny app

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!

Upvotes: 2

Views: 2774

Answers (1)

Anakin Skywalker
Anakin Skywalker

Reputation: 2520

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()
})     
}
)

Upvotes: 1

Related Questions