Insert R Data frame as parameter in SQL query WHERE clause-CodePudding

Trying to insert R data frame as parameter in SQL query where clause but I get a SQL error.

This is my code:

df <- data.frame(X=c(12691683,12693928)) %>% paste0(collapse = ",")

dbGetQuery(con2,"
  SELECT * FROM ORDERS WHERE ORDER_ID IN (", df, ")
")

CodePudding user response：

With glue::glue_sql(), you could input multiple values for use in SQL IN statements by putting * at the end of the value and the values will be collapsed and quoted appropriately.

df <- data.frame(X = c(12691683, 12693928))

glue::glue_sql(
  "SELECT * FROM ORDERS WHERE ORDER_ID IN ({df$X*})",
)

# <SQL> SELECT * FROM ORDERS WHERE ORDER_ID IN (12691683, 12693928)

In base you could also use sprintf() to create the SQL query:

sprintf("SELECT * FROM ORDERS WHERE ORDER_ID IN (%s)",
        paste(df$X, collapse = ','))

# [1] "SELECT * FROM ORDERS WHERE ORDER_ID IN (12691683,12693928)"

CodePudding user response：

Use sprintf and toString. No packages are used. We assume this is an internal application where security issues are not relevant.

df <- data.frame(X=c(12691683,12693928))

sql <- sprintf("SELECT * FROM ORDERS WHERE ORDER_ID IN (%s)", toString(df$X))
sql
## [1] "SELECT * FROM ORDERS WHERE ORDER_ID IN (12691683, 12693928)"

A variation of this is to use with

sql <- with(df, 
  sprintf("SELECT * FROM ORDERS WHERE ORDER_ID IN (%s)", toString(X)))