mysql - 如何从mysql到 Shiny 的表中读取数据

Question

我正在尝试构建一个 Shiny 的应用程序。我在数据库中有 3 个表。该应用程序将从 MySQL 检索数据表并在 Web 界面上弹出。在 ui 功能中提到了所有选择。从我的 MySQL 检索数据时，我写了 3 次 readtable 命令。是否有任何选项可以从一个可读语句中检索数据？

library(shiny)

# Define UI for dataset viewer application
ui<-fluidPage(

  # Application title.
  titlePanel("More Widgets"),

  sidebarLayout(
    sidebarPanel(
      selectInput("dataset", "Choose a dataset:", 
                  choices = c("table1", "table2","table3")),

      numericInput("obs", "Number of observations to view:", 10),

      submitButton("Update View")
    ),

    mainPanel(

      h4("Observations"),
      tableOutput("view")
    )
  )
)

# Define server logic required to summarize and view the 
# selected dataset
server<-function(input, output) {

    conn <- dbConnect(drv = RMySQL::MySQL(),dbname = "xxx",host = "localhost",
    username = "root",password = "yyy")
    on.exit(dbDisconnect(conn), add = TRUE)
    table1<- dbReadTable(conn = conn,  name = 'table1', value = as.data.frame(table1))
    table2<- dbReadTable(conn = conn,  name = 'table2', value = as.data.frame(table2))
    table3<- dbReadTable(conn = conn,  name = 'table3', value = as.data.frame(table3))

  # Return the requested dataset
  datasetInput <- reactive({
    switch(input$dataset,
           "table1" = table1,
           "table2" = table2,
        "table3" = table3
           )
  })

  # Show the first "n" observations
  output$view <- renderTable({
    head(datasetInput(), n = input$obs)
  })
        on.exit(dbDisconnect(conn), add = TRUE)

}
shinyApp(ui, server)

Answer 1

for() 函数可能有效:

data <- c('table1', 'table2', 'table3')

list_data <- list()

for(i in 1:length(data)) {list_data[i] <- dbReadTable(conn = conn,  name = data[i], value = as.data.frame(table1))}