在连接到PostgreSQL的Shiny应用程序中设置数据刷新 [英] Setting up data refreshing in Shiny app connected to PostgreSQL

问题描述

我查看了此和此线程和其他几个线程，但还没有

I've looked at this and this thread and a few others but haven't been able to figure out my solution.

我已经使用R和Shiny构建了一个仪表板，并说仪表板使用 RPostgreSQL 软件包。现在，所有数据提取和分析的代码都在 shinyServer 函数之外完成，只有显示部分（输出和 render 函数）位于 shinyServer 部分中。我想进行设置，以便定期刷新仪表盘数据并更新图表。我研究了 reactivePoll 和 invalidateLater 并理解了它们，但是还不太清楚如何在其中实现我的代码。

I have built a dashboard using R and Shiny, and said dashboard pulls in data from a Postgres db using the RPostgreSQL package. Right now, the code for all the data pulling and analysis is done outside of the shinyServer function, and only the displaying part (the output and render functions) is in the shinyServer portion. I'd like to set it up so that the dashboard data is periodically refreshed and the charts are updated. I've looked into reactivePoll and invalidateLater and understand them, but can't quite figure out how to implement it in my code.

这是一个简化的示例 server.R 代码：

Here's a simplified example server.R code:

library(RPostgreSQL)

drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, host='host', port='12345', dbname='mydb',
                 user='me', password='mypass')

myQuery <- "select * from table"
myTable <- dbGetQuery(con, myQuery)

foo <- nrow(myTable)
bar <- foo * 2

shinyServer(
  function(input, output, session) {
    output$foo <- renderText(foo)
    output$bar <- renderText(bar)

    session$onSessionEnded(function(){
      dbDisconnect(con)
      dbDisconnect(con2)
      dbUnloadDriver(drv)
    })
  }
)

现在，如果我想定期 foo 进行更新，则需要我刷新我也有的 dbGetQuery 命令，但我不知道如何使它们全部协同工作。我是否需要重新格式化并将所有内容放入 shinyServer 函数中？我有大约250行代码，将它们全部放入其中感觉不对，仅将数据提取部分放入其中可能会弄乱事情的顺序。任何帮助表示赞赏。

Now if I want foo to update periodically, that requires me to refresh the dbGetQuery command I have too, and I can't figure out how to make them all work together. Do I need to reformat things and put everything inside the shinyServer function? I have about 250 lines of code and it feels wrong to throw them all in there, and just putting the data pull part in there could mess with the order of things. Any help is appreciated.

推荐答案

我将使用 reactivePoll 代替 invalidateLater ，因为它只会在有新数据的情况下重新获取整个数据。

I would use reactivePoll instead of invalidateLater, because it will only re-fetch the whole data in case there is new data.

但是没有办法放置代码来获取 shinyServer 内部的数据，因为您的后续计算取决于（反应性）数据。

There is however no way around putting the code to fetch the data inside shinyServer, since your subsequent calculations depend on the (reactive) data.

免责声明：我对SQL毫无经验，由于缺少合适的数据库，我无法测试我的代码，但是根据我对闪闪发光的的理解，以下代码应该可以工作。

Disclaimer: I have no experience with SQL whatsoever, and I couldn't test my code due to the lack of a suitable database, but from my understanding of shiny the following code should work.

library(RPostgreSQL)

drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, host='host', port='12345', dbname='mydb',
                 user='me', password='mypass')

check_for_update <- function() {
  dbGetQuery(con, "SELECT MAX(timestamp) FROM table") # edit this part in case
  # the syntax is wrong. the goal is to create an identifier which changes
  # when the underlying data changes
}
get_data <- function() {
  dbGetQuery(con, "select * from table")
}
close_connection <- function() {
  dbDisconnect(con)
  dbUnloadDriver(drv)
}

shinyServer(
  function(input, output, session) {
    # checks for new data every 10 seconds
    data <- reactivePoll(10000, session,
                         checkFunc = check_for_update,
                         valueFunc = get_data)

    # the outputs will only be refreshed in case the data changed
    output$foo <- renderText({
      nrow(data())
    })
    output$bar <- renderText({
      bar <- data() * 2
    })

    session$onSessionEnded(close_connection)
  }
)

根据您应用的结构，它可能会有所帮助将计算结果包装到单独的位置反应性，可以在多个地方重复使用。

Depending on the structure of your app it could be helpful to wrap the calculations into a separate reactive, which you can reuse in several places.

在ShinyApps中执行代码的一些说明可以在此找到。 教程。

Some notes on code execution with shinyApps can be found in this tutorial.

如果您遇到任何问题，请发表评论，我将尝试相应地更新我的帖子。

If you are running into any issues, please leave a comment and I will try to update my post accordingly.

这篇关于在连接到PostgreSQL的Shiny应用程序中设置数据刷新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！