R: sprintf list of variables of mixed type

Question

I am trying to generate an SQL query with multiple embedded variables with sprintf.

I store SQL (it is large one) with parameters as %s as a separate file, and then read it from within r. To improve readability, I've created a helper:

fillSQLQuery <- function(query, params){
  #' fill query with params
  do.call(sprintf, as.list(c(query, params)))
}

My parameters are dates, factors, and strings. When I pass them to the function directly, my dates are replaced with an integer, and my factor - with its index (?). On the other side, when I convert the whole vector with as.character, it tries to process all elements to string as dates, and return NA or wrong results for all non-strings).

do.call(sprintf, 
        as.list(c(query,    
        c(as.character(startDateSequence[1]),  # POSix DATE  
          as.character(endDateSequence[1]),    # POSix DATE    
          lotTypes[1], id_list[1] ))))         # string, factor

The only way to solve that I've found so far is to manually convert dates and types to characters beforehand. However, I wonder if there is any "clear" way to do that, something as simple and robust as pythons:

'query {p1}, {p2}'.format(p1=X, p2=Y)

This is next to impossible to work on without at least a small example of your params vector. Please make a minimal reproducible example. — Gregor Thomas
I mean, in with that function, how are you even passing params in, as a vector? What does the vector look like before you run as.character on it - it probably gets messed up as soon as you use c() on objects of different types. — Gregor Thomas
You're right both times - I added an example of vector to be more clear, and yes - for some reason I've assumed that R's vector support mixed type of data as Lists - but they are not. Simple replace of vector with list solved an issue: fillSQLQuery(query, list(startDateSequence[1], endDateSequence[1], lotTypes[1], id_list[1])) — Philipp_Kats
Perhaps I'm over looking something but where should I be looking for a concrete example of what value query and these "parameters" would have as values? — IRTFM

Gregor Thomas Gregor Thomas · Accepted Answer · 2016-11-06T00:26:13

Okay, you haven't provided a reproducible example but your intent is clear, so here's the code you should have provided to make this a nice example (self-contained and reproducible):

x1 = Sys.Date() # Date
x2 = Sys.time() # POSIX datetime
x3 = "hello"    # character
x4 = factor("world", levels = c("world", "something"))  # factor

qry = "Select %s, %s, %s, %s from blah"

Now that the set-up is established, we can work on a solution. A list is the natural structure for holding items of different classes:

params = list(x1, x2, x3, x4)

And we can modify your function to convert each list element to character before using sprintf:

fillSQLQuery = function(query, params){
  params = lapply(params, as.character)
  do.call(sprintf, c(query, params))
}

fillSQLQuery(qry, params)
# [1] "Select 2016-11-05, 2016-11-05 17:19:29, hello, world from blah"

R: sprintf list of variables of mixed type

1 Answers