Generate sql query from list names and its values

Question

Basically I am trying to derive the WHERE part of a SELECT statement by unlisting and paste-ing a list where the list names represent the database TABLE Columns and the respective list values equal the parameters for the WHERE clause. Here is a simplified example ...

lst <- list(DATE=as.Date('2015-10-25'), NUM="0001", PROD="SOMETHING")
lst
    $DATE
[1] "2015-10-25"

$NUM
[1] "0001"

$PROD
[1] "SOMETHING"

This would ideally be transformed into (the interesting bit starting in the second line after the WHERE):

"SELECT SOME_COLUMNS WHERE  
DATE = '", lst$DATE, "' AND 
NUM = '", lst$NUM, "' AND 
PROD = '" lst$PROD ,"'")

I am quite sure that someone knows of some fancy combination of apply(),

paste(..,collapse ="' AND ") and/or substitute() that can accomplish that in an elegant form, but I am stuck.

score 5 · Accepted Answer · answered Oct 28 '15 at 18:23

I don't know if this is elegant enough but it should work:

sql <- paste0("SELECT ", 
              paste0(names(lst),collapse=','), 
              " WHERE\n",
              paste(lapply(names(lst),function(x)paste0(x," = '",lst[[x]],"'")),collapse="AND\n"))

> cat(sql)
SELECT DATE,NUM,PROD WHERE
DATE = '2015-10-25'AND
NUM = '0001'AND
PROD = 'SOMETHING'

kliron · Answer 2 · 2015-10-29T09:12:04.897

Are you looking for something like this?

lst2sql <- function(lst) {
    sql <- "SELECT col1, col2 FROM table1 WHERE"

    predicates <- vapply(names(lst), function(n) { paste(n, " = '", lst[[n]], "'", sep="") }, character(length(names)))

    paste(sql, paste(predicates, collapse=" AND "))
}

When called on your example list will produce:

"SELECT col1, col2 FROM table1 WHERE DATE = '2015-10-25' AND NUM = '0001' AND PROD = 'SOMETHING'"

score 3 · Answer 3 · edited May 23 '17 at 12:26

sprintf is generally useful:

lst <- list(DATE=as.Date('2015-10-25'), NUM="0001", PROD="SOMETHING") 
q <- "SELECT SOME_COLUMNS WHERE DATE = '%s' AND NUM = '%s' AND PROD = '%s'"

> sprintf(q,lst[[1]],lst[[2]],lst[[3]])
[1] "SELECT SOME_COLUMNS WHERE DATE = '2015-10-25' AND NUM = '0001' AND PROD = 'SOMETHING'"

Also, see my other answer here for more ideas. If you do this a lot, it pays to build up some specialized tools for it, as I outline in that answer.

Generate sql query from list names and its values

3 Answers3