Fetching SQL data in R with parameters

Question

I would like to extract some data from a MySQL server. Therefore Im using the following code:

#connect to database
getPlayersHome <- dbGetQuery(con,"SELECT * FROM match_player_home WHERE match_id = 1;")

This works fine, however I would like include it in a function with a parameter. But if I do this:

getData <- function(selector){
   getPlayersHome <- dbGetQuery(con,"SELECT * FROM match_player_home WHERE match_id = selector;")

}

My query does not work (it returns everything). Any thoughts on what goes wrong here?

The query in you function is not quite the same, I think you want something like `paste0("SELECT * FROM match_player_home WHERE match_id = ", selector, ";")` in place of the query. — steveb, Feb 17 '16 at 16:35

score 3 · Accepted Answer · answered Feb 17 '16 at 16:38

You are using the selector parameter incorrectly, as you wrote it selector it's interpreted as a literal. You can use sprintf to create the sql string passing parameters:

getData <- function(selector){
    sql <- sprintf("SELECT * FROM match_player_home WHERE match_id = %s", selector)
    rs = dbSendQuery(con, sql)
}

The string "%s" inside sprintf will be substitute by the value of selector.

Fetching SQL data in R with parameters

1 Answers1