0

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.

gr1zzly be4r
  • 2,072
  • 1
  • 18
  • 33
  • I updated the answer based on a good comment from @hadley. I should have escaped the quotation marks. I also made the code much more concise. – dayne Jul 26 '16 at 00:07

1 Answers1

-1

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.

dayne
  • 7,504
  • 6
  • 38
  • 56
  • I'm accepting this as an answer because I know that it will work. It's unfortunate that this type of functionality isn't already implemented in `dbSendQuery`. Thank you for your answer. – gr1zzly be4r Jul 25 '16 at 19:50
  • Down voting for lack of escaping – hadley Jul 25 '16 at 22:39
  • @hadley Looked back at old code and edited the answer to be more concise and correct. Thank you for keeping me honest. – dayne Jul 26 '16 at 00:06
  • Escaping still isn't correct, sorry. Google little Bobby tables. – hadley Jul 26 '16 at 23:30