How to pass condition with dynamical values to sqlQuery

[tjcnnl1]

I have a condition list with values of '1349','1350','1351','1352' which derived from data frame df dynamically. How can I make in_list as one string and comma between numbers, like '1349','1350','1351','1352'. So, I can pass in_list to sqlQuery where statement with dynamical values?

df <- data.frame(
  run_id = c(1349L,1349L,1349L,1350L,1350L,1351L,
                 1351L,1349L,1350L,1351L,1349L,1350L,1351L,1349L,1350L,
                 1351L,1349L,1350L,1351L,1349L,1350L,1351L,1349L,1350L,
                 1351L,1349L,1350L,1351L,1349L,1350L,1351L,1349L,1350L,
                 1351L,1349L,1350L,1351L,1349L,1350L,1351L,1349L,1350L,
                 1351L,1349L,1350L,1351L,1349L,1350L,1351L,1349L,1349L,
                 1350L,1349L,1350L,1349L,1350L,1349L,1350L,1349L,1350L,
                 1349L,1350L,1349L,1350L,1349L,1350L,1349L,1350L,1349L,
                 1350L,1349L,1350L,1349L,1350L,1349L,1350L,1349L,1350L,
                 1349L,1350L,1349L,1350L,1351L,1352L,1349L,1350L,1351L,
                 1352L,1349L,1350L,1351L,1352L,1349L,1350L,1351L,1352L,
                 1349L,1350L,1351L,1352L,1349L,1350L,1351L,1349L,1350L,
                 1351L,1349L,1352L,1349L,1350L,1351L,1352L,1349L,1352L,
                 1349L,1349L,1350L,1351L,1349L,1349L,1350L,1351L,1352L,
                 1349L,1350L,1351L,1352L,1349L,1350L,1351L,1352L,1349L,
                 1350L,1351L,1352L,1349L,1351L,1352L,1349L,1350L,1351L,
                 1352L,1349L,1350L,1351L,1352L,1349L,1350L,1351L,1352L,
                 1349L,1350L,1351L,1350L,1351L,1349L,1350L,1351L,1349L,
                 1350L,1351L,1349L,1350L,1351L,1349L,1350L,1351L,1349L,
                 1350L,1351L,1349L,1350L,1351L,1349L,1350L,1351L,1349L,
                 1350L,1351L,1349L)
)
id <- df %>% select(run_id) %>% unique()
in_list <- as.character(as.character(id$run_id))

I've been blocked here. Does anyone have better way to do this? I think something like:

sql_stmt <- glue("select * from df2 where run_id in({in_list})")
sql_tab <- sqlQuery(odbcChannel2, sql_stmt, as.is = TRUE)

Instead of hard-coded

sql_tab <- sqlQuery(odbcChannel2, "select * from df2 where 
                                 run_id in ('1349','1350','1351','1352')", as.is = TRUE)

[EeethB]

Thanks for the great question! I think dbplyr::escape() would do what you need:

library(tidyverse)

df <- data.frame(
  run_id = c(1349L,1349L,1349L,1350L,1350L,1351L,
             1351L,1349L,1350L,1351L,1349L,1350L,1351L,1349L,1350L,
             1351L,1349L,1350L,1351L,1349L,1350L,1351L,1349L,1350L,
             1351L,1349L,1350L,1351L,1349L,1350L,1351L,1349L,1350L,
             1351L,1349L,1350L,1351L,1349L,1350L,1351L,1349L,1350L,
             1351L,1349L,1350L,1351L,1349L,1350L,1351L,1349L,1349L,
             1350L,1349L,1350L,1349L,1350L,1349L,1350L,1349L,1350L,
             1349L,1350L,1349L,1350L,1349L,1350L,1349L,1350L,1349L,
             1350L,1349L,1350L,1349L,1350L,1349L,1350L,1349L,1350L,
             1349L,1350L,1349L,1350L,1351L,1352L,1349L,1350L,1351L,
             1352L,1349L,1350L,1351L,1352L,1349L,1350L,1351L,1352L,
             1349L,1350L,1351L,1352L,1349L,1350L,1351L,1349L,1350L,
             1351L,1349L,1352L,1349L,1350L,1351L,1352L,1349L,1352L,
             1349L,1349L,1350L,1351L,1349L,1349L,1350L,1351L,1352L,
             1349L,1350L,1351L,1352L,1349L,1350L,1351L,1352L,1349L,
             1350L,1351L,1352L,1349L,1351L,1352L,1349L,1350L,1351L,
             1352L,1349L,1350L,1351L,1352L,1349L,1350L,1351L,1352L,
             1349L,1350L,1351L,1350L,1351L,1349L,1350L,1351L,1349L,
             1350L,1351L,1349L,1350L,1351L,1349L,1350L,1351L,1349L,
             1350L,1351L,1349L,1350L,1351L,1349L,1350L,1351L,1349L,
             1350L,1351L,1349L)
)
in_list <- df %>% pluck("run_id") %>% unique() %>% as.character()

dbplyr::escape_ansi(in_list, collapse = ",")
#> <SQL> ('1349','1350','1351','1352')

^{Created on 2021-06-24 by the reprex package (v2.0.0)}

And then I think for safety you should also look into glue::glue_sql(). I don't understand SQL interpolation entirely, but I know that one is supposed to be safer to use for avoiding injection attacks.

https://db.rstudio.com/ has lots of information and guidance on this kind of thing!

[tjcnnl1]

Thank you very much. I use pluck and escape_ansi to get dynamic condition list in SQL syntax; and then use glue to construct sql select/from/where statement. It works perfect!