gr1zzly be4r gr1zzly be4r - 4 months ago 9
SQL Question

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.

Answer

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))