Shiny应用中用户输入传入PostgreSQL查询失败问题排查

我长期浏览论坛，首次发帖。正在开发一个简单Shiny应用，让用户通过下拉菜单选择物种，生成该物种分布范围的地图。之前主要在预处理数据框场景用Shiny，但物种空间范围数据量大且复杂，存在PostgreSQL数据库里。

能正常连接数据库，不管是控制台还是Shiny脚本里，用dbGetQuery(con, "SELECT * FROM range WHERE sp_id = 664")或者st_read(con, query = "SELECT * FROM range WHERE sp_id = 580")都能得到预期结果。

但把用户输入（物种选择）传入PostgreSQL查询时出错，报错信息：

Warning: Error in : Failed to prepare query : ERROR: syntax error at or near "{"
LINE 1: SELECT * FROM range WHERE sp_id = {input$species_choice}

我以为{}是合法写法，但系统不支持，之前复刻过相关示例没问题。

全局代码 #

library(shiny)
#library(glue)
library(DBI)
library(RPostgres)
library(RPostgreSQL)
library(sf)
library(dplyr)
library(ggplot2)

library(ozmaps)
con <- dbConnect(
  RPostgres::Postgres(),
  dbname = "db",
  host = "localhost",
  port = 5432,
  password = "password",
  user = "username")

species_list <- data.frame(sp_id  = as.integer(c(580,581)),
                            taxon_name = c("Species1","Species2")) ## example short list to populate dropdown

UI部分 #

ui <- fluidPage(

    # Application title
    titlePanel("Range Layers"),

    # Sidebar with a slider input for number of bins 
    sidebarLayout(
        sidebarPanel(
          selectizeInput(inputId = 'species_choice', 'Select species or start typing', choices = c("Choose Species" = "",species_list$sp_id), selected = NULL, multiple = FALSE,
                         options = NULL),
        ),

        # Show a plot of the distribution layer at continental scale
        mainPanel(
           plotOutput("distPlot")
        )
    )
)

Server部分 #

server <- function(input, output, session) {
  data <- reactive({
    req(input$species_choice)
    
    # Get the data
    species <- st_read(con, query = "SELECT * FROM range WHERE sp_id = {input$species_choice}")

    species
})

output$distPlot <- renderPlot({ggplot(data()) +
              geom_sf(ozmap_states,mapping = aes()) +
             geom_sf(mapping = aes(fill = taxon_id_r)) + 
              ggtitle(species$taxon_name)
    
})

shinyApp(ui = ui, server = server)

解决方案 #

1. 恢复glue包的加载：你注释掉了glue包，而字符串中{}的插值语法是由glue包提供的，直接写在查询语句里PostgreSQL无法识别。取消#library(glue)的注释，加载该包。

2. 用glue()生成查询语句：修改Server部分的查询代码，用glue()包裹字符串，实现变量插值：

species <- st_read(con, query = glue("SELECT * FROM range WHERE sp_id = {input$species_choice}"))

3. 修复标题变量引用问题：Server里的ggtitle(species$taxon_name)存在错误，species是reactive函数内部的变量，外部无法直接访问，应该替换为data()$taxon_name：

ggtitle(data()$taxon_name)

内容的提问来源于stack exchange，提问作者JayDee