Question: How do I pass a variable in the RPostgreSQL query?
Example: In the example below I try to pass the date '2018-01-03' to the query
library(RPostgreSQL)
dt <- '2018-01-03'
connect <- dbConnect(PostgreSQL(),
dbname="test",
host="localhost",
port=5432,
user="user",
password="...")
result <- dbGetQuery(connect,
"SELECT * FROM sales_tbl WHERE date = @{dt}")
You can use paste0 to generate your query and pass it to dbGetQuery:
library(RPostgreSQL)
dt <- '2018-01-03'
connect <- dbConnect(PostgreSQL(),
dbname="test",
host="localhost",
port=5432,
user="user",
password="...")
query <- paste0("SELECT * FROM sales_tbl WHERE date='", dt, "'")
result <- dbGetQuery(connect, query)
dbQuoteString() or sqlInterpolate() functions see RStudio Best Practices. As far as I know, there is no way to do this with the dbBind function that is called by dbGetQuery(con, sql, params), at least for the RPostgreSQL implementation of DBI.
dbBind. Didn't realize I didn't respond to the second part of of your comment previously.The safest way is to parameterize the query as mentioned here
Example:
library(RPostgreSQL)
dt <- '2018-01-03'
connect <- dbConnect(drv = PostgreSQL(),
dbname ="test",
host = "localhost",
port = 5432,
user = "user",
password = "...")
query <- "SELECT * FROM sales_tbl WHERE date= ?"
sanitized_query <- dbSendQuery(connect, query)
dbBind(sanitized_query, list(dt))
result <- dbFetch(sanitized_query)
Here by passing ? you are sanitizing your query to avoid SQL injection attacks.
Another thing I like to do is to create .Renviron file to store my credintials. For example, for the connection above, the .Renviron file will look like this.
dbname = test
dbuser = me
dbpass = mypass
dbport = 5432
dbhost = localhost
save the file, restart RStudio (to load the .Renviron file at startup). Then access the credentials using the Sys.getenv(variable)
#example:
connect <- dbConnect(drv = PostgreSQL(),
dbname = Sys.getenv("dbname"),
host = Sys.getenv("dbhost"),
port = Sys.getenv("dbport"),
user = Sys.getenv("dbuser"),
password = Sys.getenv("dbpass"))
SELECT * from table WHERE date between "?" and "?". Then send the parameter for those "?" placeholders on second parameter like dbBind(query, list(date1, date2). Let me know if that worked for you or not.