R: Selecting a subset of a sqlite database based on a character vector

Question

I am trying to extract part of my database using a character vector. To explain, I have used the mtcars data as below:

library(dplyr)
library(sqldf)
library(RSQLite)

df <- cbind(rownames(mtcars),mtcars)
colnames(df)[1] <- "CarName"
CarsToFind <- c("Valiant", "Merc 280", "Lotus Europa", "Volvo 142E")
db <- dbConnect(SQLite(), dbname = 'mtcars_db.sqlite3')
dbWriteTable(conn = db, name = 'mtcars_table', value = df, row.names = TRUE, header = TRUE)

I could find the section of the data frame that I am interested in using:

mini_df <- df[df$CarName %in% CarsToFind,]

but my real data is quite large and I would rather not extract the whole thing into a data frame. I am looking for something similar to :

sqldf("SELECT * FROM mtcars_table WHERE CarName IN CarsToFind")

but this gives me the error: "no such table: CarsToFind". I don't want to create the table 'CarsToFind' in the SQL databank, because I have many different queries that I want to perform on a once off basis. Is it possible to query the SQL using such a character vector?

Answer 1

The query you actually want to execute on SQLite should look like this:

SELECT *
FROM mtcars
WHERE CarName IN ('Valiant', 'Merc 280', 'Lotus Europa', 'Volvo 142E')

So all you need to do is build this string in R:

CarsToFind <- c("Valiant", "Merc 280", "Lotus Europa", "Volvo 142E")
CarsToFind <- paste0("'", CarsToFind, "'", collapse=", ")
whereIn <- paste0("(", CarsToFind, ")")

query <- paste0("SELECT * FROM mtcars WHERE CarName IN ", whereIn)             
sqldf(query)

Answer 2

How about instead of creating a character vector, create a dataframe with only one column? This would work:

CarsToFind <- data.frame(lookup=c("Valiant", "Merc 280", "Lotus Europa", "Volvo 142E"))

sqldf("SELECT * FROM df WHERE CarName IN CarsToFind")

Also, this way you don't have to change/add anything on the SQL side, you can still keep everything on the R side.