Search code examples
mysqlrrmysql

How combine dbSendQuery with values from DataFrame in R?

I'm looking for a way to include data from an R dataframe in a sql predicate. Ideally, I'd like to use dbSendQuery from the RMySQL package to send a query to my database that contains a WHERE ... IN conditions that includes values from my database. Is this possible?

Example data frame

BUR
LAX
LGB

Example query

SELECT * FROM table WHERE airport IN ('BUR', 'LAX', 'LGB')

Is there a way to "pass" the rows of my data frame to a query? This might not be possible, but I'm interested to know.

Solution

  • I typically create a "format" string, then sub in the values using sprintf and paste like below:

    qformat <- "SELECT * FROM table WHERE airport IN (%s)"
vals <- c("BUR", "LAX", "LGB")

qstring <- sprintf(qformat, paste0("\"", vals, "\"", collapse = ","))
cat(qstring)
# SELECT * FROM table WHERE airport IN ("BUR","LAX","LGB")

    If you have to do it a lot, just wrap the messy part in a function:

    someFunc <- function(x) paste0("\"", x, "\"", collapse = ",")
qstring <- sprintf(qformat, someFunc(vals))

    If you're worried about SQL injection take a look at ?dbEscapeStrings.