mysql - 如何获取 R Shiny 响应式(Reactive)下拉框中所选选项的值？

Question

我在 Rshiny 中有一个响应式(Reactive)下拉框，其中包含 2016 年到 2019 年的值。用户选择年份后，必须将年份传递到以年份作为参数的 sql 查询。

所以我尝试了下面给出的行(其中 unique_vales2 保存年份值)

 ---
 title: "Dashboard"
 output: 
 flexdashboard::flex_dashboard:
 orientation: rows
 vertical_layout: fill
 social: [ "twitter", "facebook", "menu"]
 runtime: shiny

 ```{r}
 mydb = dbConnect(MySQL(), user='xxx', password='xxx',dbname='xxx', 
 host='xxx')
 selectInput("year", "Choose year", choices = unique_values2) 
 num <- reactive(as.integer(input$year))
 rs=dbSendQuery(mydb,paste("select * from employees where Year=",num," group 
 by job;"))
 result=fetch(rs,n=-1)

 ```

但是当我尝试在上面给出的 SQL 查询中使用 num 值时，

它显示:

Error: cannot coerce the type 'closure' to vector of type 'character'.

有办法解决这个问题吗？提前致谢。

Answer 1

假设以下数据在您的 mysql 表 employees 中，因为我没有实际数据。

dput(employees)
structure(list(id = 1:12, names = structure(1:12, .Label = c("aa", 
"bb", "cc", "dd", "ee", "ff", "gg", "hh", "ii", "jj", "kk", "ll"
), class = "factor"), year = c(2016, 2016, 2017, 2017, 2018, 
2018, 2016, 2018, 2017, 2019, 2019, 2019)), .Names = c("id", 
"names", "year"), row.names = c(NA, -12L), class = "data.frame")

下面的代码将帮助您满足您的要求

library(shiny)
library(DBI)
library(pool)
pool <- dbPool(drv = RMySQL::MySQL(),dbname = "db_name",host = "localhost",username = "user_name",password = "password", port = 3306, unix.sock = "/var/run/mysqld/mysqld.sock")

ui <- fluidPage(
  uiOutput("years"),
  tableOutput("mytable")
)

server <- function(input, output, session) {
  output$years <- renderUI({
    unique_values2 <- c(2016:2019)
    selectInput("year", "select year", choices = unique_values2)
  })

results <- reactive({
  df <- dbGetQuery(pool, paste0("SELECT * FROM employees WHERE year = ", input$year ," ;"))
  return(df)
})
output$mytable <- renderTable(results())
}

shinyApp(ui, server)

因此表输出将根据我们从下拉列表中选择的年份而变化。