如何使 Shiny 反应性与 SQL 数据库一起工作? [英] How to make Shiny reactivity work with SQL database?

问题描述

好的，我按照@Pork Chop 的建议修改了脚本:

server.R

图书馆(闪亮)图书馆(DT)库(RMySQL)con <- dbConnect(MySQL(), user="myuser", host="myhost", dbname="mydb")闪亮服务器(功能(输入，输出){sqlOutput <- 反应式({sqlInput <- paste0("select * from mydb.mytable"," where value < ", input$value,";")dbGetQuery(con, sqlInput)})output$table <- DT::renderDataTable(sqlOutput(), server=TRUE, rownames=FALSE, filter="top", options=list(pageLength=10))output$download <- downloadHandler("filtered.data.txt", content = function(file) {行 <- input$table_rows_allwrite.table(sqlOutput()[rows, ], file, sep="\t", quote=FALSE, col.names=TRUE, row.names=FALSE)})})

DataTable 现在可以工作了！

然而，当我尝试下载显示的数据时，我得到一个只有列名而没有数据的文件.根据

如果我将 sqlInput 和 sqlOutput 嵌入到 DT::renderDataTable() 的反应式表达式中，这会按预期工作，但是我'我无法从 downloadHandler() 中引用 sqlOutput(object 'sqlOutput' not found).我认为这是使用 reactive() 的完美用例，但我无法让它工作.

完成这项工作的最佳方法是什么?非常感谢任何帮助，谢谢！

解决方案

1. sqlOutput 是一个函数，所以把它改成 sqlOutput()

2. 试试这个，注意这将导出为 .csv 希望没问题

output$download <- downloadHandler(filename = function() {paste(Sys.time(), 'Fltered_data.csv', sep='')}, content = function(file) {write.csv(sqlOutput()[输入$table_rows_all，]，文件，row.names = FALSE)})

Alright, I modified the script following @Pork Chop advice:

server.R

library(shiny)
library(DT)
library(RMySQL)

con <- dbConnect(MySQL(), user="myuser", host="myhost", dbname="mydb")

shinyServer(function(input, output) { 

                sqlOutput <- reactive({
                    sqlInput <- paste0("select * from mydb.mytable",
                           " where value < ", input$value,
                           ";")
                    dbGetQuery(con, sqlInput)
                })

                output$table <- DT::renderDataTable(sqlOutput(), server=TRUE, rownames=FALSE, filter="top", options=list(pageLength=10))

                output$download <- downloadHandler("filtered.data.txt", content = function(file) {
                                           rows <- input$table_rows_all
                                           write.table(sqlOutput()[rows, ], file, sep="\t", quote=FALSE, col.names=TRUE, row.names=FALSE)
                })

})

The DataTable now works!

However when I try to download the displayed data, I get a file with only column names and no data. According to the DT docs, input$table_rows_all should contain the row indices of the displayed table.

What's wrong?

---

I'm having troubles with Shiny reactivity and a MySQL database.

In short, I get an input value from the user, create an SQL query, capture the output and display it as a DataTable.

The output can be further filtered using the DataTable column filters and the user should be able to download the filtered dataset.

server.R

library(shiny)
library(DT)
library(RMySQL)

con <- dbConnect(MySQL(), user="myuser", host="myhost", dbname="mydb")

shinyServer(function(input, output) {


                sqlInput <- reactive({
                    paste0("select * from mydb.mytable",
                           " where value < ", input$value,
                           ";")
                })

                sqlOutput <- reactive({
                    dbGetQuery(con, sqlInput)
                })

                output$table <- DT::renderDataTable(sqlOutput, server=TRUE, rownames=FALSE, filter="top", options=list(pageLength=10))

                output$download <- downloadHandler("filtered.data.txt", content = function(file) {
                                           rows <- input$table_rows_all
                                           write.table(sqlOutput[rows, ], file)
                })

})

Instead of the DataTable, I get this error:

This works as expected if I embed sqlInput and sqlOutput within a reactive expression in DT::renderDataTable(), but then I'm not able to refer to sqlOutput from within downloadHandler() (object 'sqlOutput' not found). I thought this was the perfect use case for using reactive() but I can't get it to work.

What's the best way to make this work? Any help is much appreciated, thanks!

解决方案

1. sqlOutput is a function so change it to sqlOutput()

2. Try this, note this will export is as .csv hope its ok

output$download <- downloadHandler(filename = function() {paste(Sys.time(), ' Fltered_data.csv', sep='')}, content = function(file) {write.csv(sqlOutput()[input$table_rows_all, ], file, row.names = FALSE)})

这篇关于如何使 Shiny 反应性与 SQL 数据库一起工作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！