How to Pass Input Variable to SQL Statement in R Shiny

How to pass input variable to SQL statement in R shiny?

The data query needs to be evaluated in a reactive context.

One way would be to move the data query itself into the renderPlot() context e.g.

--server.R--

shinyServer(function(input, output) {

 database <- dbConnect(MySQL(), group= "zugangsdaten", dbname= 'database')

 output$main_plot <- renderPlot({

   table <- dbGetQuery(database, statement = 
              paste("
                SELECT a,b FROM table1
                WHERE id = ",input$segment,"
                AND created_at>='2015-08-01'
              "))

    plot(table$a,table$b)

 })

})

However, it's better to construct a reactive conductor for the data which can be evaluated once when any updates happen and re-used in multiple reactive end-points (see here for details).

This would look something like:

--server.R--

shinyServer(function(input, output) {

 database <- dbConnect(MySQL(), group= "zugangsdaten", dbname= 'database')

 table <- reactive({
            dbGetQuery(database, statement = 
              paste("
                SELECT a,b FROM table1
                WHERE id = ",input$segment,"
                AND created_at>='2015-08-01'
              ")
            )
          })

 output$main_plot <- renderPlot({

    plot(table()$a,table()$b)

 })

}) 

how to save a sql database in a real time dataframe? shiny

I think you're probably better off using invalidateLater instead of reactivePoll. The latter is intended to use a

relatively cheap "check" function with a more expensive value retrieval function

In your case, you intend to run your query every 2 seconds, regardless. There is no "check" needed.

Additionally, reactive* functions in shiny tend to be lazy (if nothing depends on it, it will never fire), whereas observe* functions are more greedy (and fire regardless of dependencies).

How about this?

dat <- reactiveValue(NULL)
observe({
  invalidateLater(2000)
  QUERY1 = "SELECT * FROM table"
  ret <- dbGetQuery(storiesDb, QUERY1)
  dat(ret)
})
output$tbl <- renderDataTable({
  dat()
})

You might want to consider only downloading recent data, depending on the schema of that table. For instance, if there is a timestamp (e.g., Created which indicates when the data was inserted into the table), then perhaps you can use something like

dat <- reactiveValue(NULL)
observeEvent(invalidateLater(2000), {
  olddat <- dat()
  latest <- max(c(as.POSIXct("1900-01-01"), olddat$Created), na.rm = TRUE)
  QUERY1 = "SELECT * FROM table t where t.Created > ?"
  newdat <- dbGetQuery(storiesDb, QUERY1, params = list(latest))
  if (NROW(newdat) > 0) {
    newdat <- rbind(olddat, newdat)
    dat(newdat)
  }
})
output$tbl <- renderDataTable({
  dat()
})

Depending on your schema, you may also have a Modified field in the table, where Created indicates when it was first added and Modified indicates when it was last changed. In this case, you'll likely need to check both fields for recency. (Perhaps I'm getting ahead of myself now.)

---

However, you can use reactivePoll to do a "fast" check function and a "slower" value function, perhaps something like:

df <- reactivePoll(2000, session,
  checkFunc = function() {
    dbGetQuery(storiesDb, "select count(*) as n from table")
  },
  valueFunc = function() {
    dbGetQuery(storiesDb, "select * from table")
  }  
})
output$tbl <- renderDataTable({
  df()
})

This idea (of using valueFunc for counting rows) can also be adapted to take advantage of Created and Modified ... in fact, if you have one or both of those fields, most tables will be index/optimized so that querying for the max(Created) is pretty fast. Granted, getting the row-count of a table is often optimized into a near-immediate query, so you might benefit from some internal benchmarking and/or a discussion with your DBA.

r shiny date range input to sql query

I use sub function to do that, by simply using regular expressions like so:

my_date1 <- "08/01/2014"
my_date2 <- "08/31/2014"

my_query <- 'Select * from dbo.Employee where StartDate between DATE1 and DATE2 order by StartDate'
my_query <- sub("DATE1",my_date1,my_query);my_query <- sub("DATE2",my_date2,my_query)
# the result of your query is below
noquote(my_query)

# if you want the quotes for dates leave them there
my_query <- 'Select * from dbo.Employee where StartDate between "DATE1" and "DATE2" order by StartDate'
my_query <- sub("DATE1",my_date1,my_query);my_query <- sub("DATE2",my_date2,my_query)
# the result of your query is below
noquote(my_query)

# Now sub the inputs into those variables like so
my_query <- 'Select * from dbo.Employee where StartDate between DATE1 and DATE2 order by StartDate'
my_query <- sub("DATE1",input$daterange[1],my_query);my_query <- sub("DATE2",input$daterange[2],my_query)
noquote(my_query)

How to use SQL query result as shiny drop down input?

You almost done just want to load the table data into your selectinput area. like shown below ,

R Server:

server <- shinyServer(function(input, output) {

output$hist <- renderPlot({

table1 <- dbGetQuery(con, statement = 
          paste("
            SELECT sum(sales_amount) as sum, sum(sales_amount) as sum2
    from table s
    join table2 c on c.key = s.key
    join table3 d on d.date = s.date
            WHERE year_number = ",input$segment,"

          "))
plot(table1$sum, table1$sum2)
})

})

ui :

table2 <- dbGetQuery (con1, "select year_number from yourtable")

ui <-(fluidPage(
selectInput("segment", "segment", sort(unique(table2$year_number)),selected = NULL),    
plotOutput("hist")

))

shinyApp(ui = ui, server = server)

R Shiny: Is it possible to build Shiny app where user can choose SQL:s where condition variables and export the query results

It's very possible (I've done this in many apps). Your question is hard to answer, as it's not a specific coding question. Also you've not indicated which database application you're using, so its hard to advise on anything related to the R drivers. Generally, use a DBI compliant driver and strongly recommend to pass user-input as a parameter list (to protect sql-injection attacks).

library(your DBI compliant package)

selections <- input$my_selections #input from shiny
selections <- as.vector(selections) #here may encounter driver/database compliance issues as you may or may not be able to pass-in a vector/array object.  If not you can pass-in a string and split it on the database end
selections <- paste0(as.vector(selections),collapse=",") #alternative input

conn <- #your connection to the database
result <- dbGetQuery(conn,"select * from Animaldata where animal_type in ( $1 )",
                     params=list(selections))

Passing reactive input/output to another .R script in Shiny App

You'll just need to run that command inside a reactive expression. So if you already have a function defined in your external .R file that wraps your query, you can call that like so:

output$query <- reactive({
  df <- runQuery(datasetInputNumber())

  # whatever you want to do to your data here.
  output$avg <- mean(df[,2])
  ...
})

where runQuery is the function defined in the external R file that accepts a number that then gets used in the SQL query.

---

Related Topics