Storing reactive data in brilliant form from SQL
This follows this question:
SQL Database Recognition Using Brilliant Responsive Query
I am trying to create a dataframe from data retrieved from a SQL database using a brilliant application. Currently I can query the database and return one set of data. Now I would like to store this data in a dataframe and then add more data from subsequent requests. Here is my code:
interface
library(markdown)
shinyUI(fluidPage(
titlePanel("Generic grapher"),
sidebarLayout(
sidebarPanel(
numericInput("wafer", label = h3("Select wafer ID:"), value = NULL),
actionButton("do", "An action button")
),
mainPanel(
verbatimTextOutput("value"), verbatimTextOutput("que"), dataTableOutput(outputId="pos")
)
)
)
)
Server:
library(RMySQL)
library(DBI)
library(sqldf)
con = dbConnect(RMySQL::MySQL(), dbname="xx", username="pete", password="xx", host="xx", port=3306)
query <- function(...) dbGetQuery(con, ...)
wq = data.frame()
shinyServer(function(input, output){
d <- eventReactive(input$do, { input$wafer })
output$value <- renderPrint({ d() })
a <- reactive({ paste("Select id from wafer where wafer_id=",d(), sep="") })
output$que <- renderPrint({ a() })
wq <- reactive({ query( a() ) })
output$pos <- renderDataTable({ wq() })
})
I am now trying to use the information from these two answers to store data from every search I do on a dataframe:
Add values to reactive table shiny
What is the difference between reactive value and reactive expression?
New server:
library(RMySQL)
library(DBI)
library(sqldf)
con = dbConnect(RMySQL::MySQL(), dbname="xx", username="pete", password="xx", host="xx", port=3306)
query <- function(...) dbGetQuery(con, ...)
wq = data.frame()
shinyServer(function(input, output){
values <- reactiveValues()
values$df <- data.frame()
d <- eventReactive(input$do, { input$wafer })
output$value <- renderPrint({ d() })
a <- reactive({ paste("Select id from wafer where wafer_id=",d(), sep="") })
output$que <- renderPrint({ a() })
wq <- reactive({ query( a() ) })
values$df <- reactive({ rbind(values$df, wq() ) })
output$pos <- renderDataTable({ values$df })
})
However, when I do this, the data table is never shown in my application. I have no error message. Any ideas where I'm going wrong? Any help is appreciated!
source to share
I think the change
values$df <- reactive({ rbind(values$df, wq() ) })
in your new server.R
before
observe({
values$df <- rbind(isolate(values$df), wq())
})
might solve your problem.
EDIT: Here's an example of working with a local connection:
library(markdown)
library(RMySQL)
library(DBI)
library(sqldf)
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "mtcars", mtcars)
query <- function(...) dbGetQuery(con, ...)
wq = data.frame()
ui <- shinyUI(fluidPage(
titlePanel("Generic grapher"),
sidebarLayout(
sidebarPanel(
numericInput("wafer", label = h3("Select number of cylinders:"),
value = NULL),
actionButton("do", "An action button")
),
mainPanel(
verbatimTextOutput("value"),
verbatimTextOutput("que"),
verbatimTextOutput("wq_print"),
dataTableOutput(outputId="pos")
)
)
)
)
server <- shinyServer(function(input, output){
values <- reactiveValues()
values$df <- data.frame()
d <- eventReactive(input$do, { input$wafer })
output$value <- renderPrint({ d() })
a <- reactive({ paste("SELECT * FROM mtcars WHERE cyl = ", d(), sep="") })
output$que <- renderPrint({ a() })
observe({
if (!is.null(d())) {
wq <- reactive({ query( a() ) })
output$wq_print <- renderPrint({ print(str(wq())) })
values$df <- rbind(isolate(values$df), wq())
}
})
output$pos <- renderDataTable({ values$df })
})
shinyApp(ui, server)
The corresponding changes in the source code are a condition !is.null(d())
for handling the initial value NULL
d()
and using it values$df <- rbind(isolate(values$df), wq())
inside the observer. Hope it helps with some code fixes!
source to share