r column values in sql where statement

Question

I have a dataset and I am trying to pass the contents of a specific column into the SQL where statement.

For example, assuming iris is my dataset

       data(iris)
       head(iris)

       Sepal.Length Sepal.Width Petal.Length Petal.Width Species
       5.1         3.5          1.4         0.2  setosa
       4.9         3.0          1.4         0.2  setosa
       4.7         3.2          1.3         0.2  setosa
       4.6         3.1          1.5         0.2  setosa
       5.0         3.6          1.4         0.2  setosa
       5.4         3.9          1.7         0.4  setosa

I want to pass the contents of column Species { setosa, setosa, setosa.....setosa} to my sql query where statement

sqlQuery(abcd, paste("Select * from TestTableName1 
                       where WHERE DESCRIPTION 
                          IN (values of Species column from r dataframe)");

Need help here

Answer 1

By prefacing any function call with fn$ from the gsubfn package string interpolation is enabled on its arguments. See ?fn for more info. This is often used with sqldf in the sqldf package but can be used with any function as we show here. In particular inserting $variable into a string argument of the function call substitutes the value of that variable into that string:

library(gsubfn)
lvls <- toString(shQuote(levels(iris$Species)))

fn$sqlQuery(abcd,  "select * from TestTableName1 where DESCRIPTION in ($lvls)")

or if we want to examine the string first:

sql <- fn$identity("select * from TestTableName1 where DESCRIPTION in ($lvls)")
cat(sql, "\n") # look at sql string

sqlQuery(abcd, sql)

The output from the cat statement is:

select * from TestTableName1 where DESCRIPTION in ("setosa", "versicolor", "virginica")