gr1zzly be4r gr1zzly be4r - 1 year ago 72
SQL Question

How combine dbSendQuery with values from DataFrame in R?

I'm looking for a way to include data from an

dataframe in a sql predicate. Ideally, I'd like to use
from the
package to send a query to my database that contains a
conditions that includes values from my database. Is this possible?

Example data frame


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 Source

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 = ","))
# 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))
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download