CODEWITHSUNDEEP
rlistrodbc
GWD
GWD

Reputation: 1464

Generate sql query from list names and its values

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.

Upvotes: 2

Views: 1389

Answers (3)

kliron
kliron

Reputation: 4663

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'"

Upvotes: 3

joran
joran

Reputation: 173567

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.

Upvotes: 3

digEmAll
digEmAll

Reputation: 57210

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'

Upvotes: 5

Related Questions