SQL databases often have custom quotation syntax for identifiers and strings
which make writing SQL queries error prone and cumbersome to do. glue_sql()
and
glue_data_sql()
are analogs to glue()
and glue_data()
which handle the
SQL quoting. glue_sql_collapse()
can be used to collapse DBI::SQL()
objects.
glue_sql(..., .con, .envir = parent.frame(), .na = DBI::SQL("NULL"))
glue_data_sql(.x, ..., .con, .envir = parent.frame(), .na = DBI::SQL("NULL"))
[expressions
]
Unnamed arguments are taken to be expression
string(s) to format. Multiple inputs are concatenated together before formatting.
Named arguments are taken to be temporary variables available for substitution.
[DBIConnection
]: A DBI connection object obtained from
DBI::dbConnect()
.
[environment
: parent.frame()
]
Environment to evaluate each expression in. Expressions are
evaluated from left to right. If .x
is an environment, the expressions are
evaluated in that environment and .envir
is ignored. If NULL
is passed, it is equivalent to emptyenv()
.
[character(1)
: ‘NA’]
Value to replace NA
values
with. If NULL
missing values are propagated, that is an NA
result will
cause NA
output. Otherwise the value is replaced by the value of .na
.
[listish
]
An environment, list, or data frame used to lookup values.
A DBI::SQL()
object with the given query.
They automatically quote character results, quote identifiers if the glue
expression is surrounded by backticks '`
' and do not quote
non-characters such as numbers. If numeric data is stored in a character
column (which should be quoted) pass the data to glue_sql()
as a
character.
Returning the result with DBI::SQL()
will suppress quoting if desired for
a given value.
Note parameterized queries are generally the safest and most efficient way to pass user defined values in a query, however not every database driver supports them.
If you place a *
at the end of a glue expression the values will be
collapsed with commas. This is useful for the SQL IN Operator
for instance.
glue_sql_collapse()
to collapse DBI::SQL()
objects.
con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
iris2 <- iris
colnames(iris2) <- gsub("[.]", "_", tolower(colnames(iris)))
DBI::dbWriteTable(con, "iris", iris2)
var <- "sepal_width"
tbl <- "iris"
num <- 2
val <- "setosa"
glue_sql("
SELECT {`var`}
FROM {`tbl`}
WHERE {`tbl`}.sepal_length > {num}
AND {`tbl`}.species = {val}
", .con = con)
#> <SQL> SELECT `sepal_width`
#> FROM `iris`
#> WHERE `iris`.sepal_length > 2
#> AND `iris`.species = 'setosa'
# If sepal_length is store on the database as a character explicitly convert
# the data to character to quote appropriately.
glue_sql("
SELECT {`var`}
FROM {`tbl`}
WHERE {`tbl`}.sepal_length > {as.character(num)}
AND {`tbl`}.species = {val}
", .con = con)
#> <SQL> SELECT `sepal_width`
#> FROM `iris`
#> WHERE `iris`.sepal_length > '2'
#> AND `iris`.species = 'setosa'
# `glue_sql()` can be used in conjuction with parameterized queries using
# `DBI::dbBind()` to provide protection for SQL Injection attacks
sql <- glue_sql("
SELECT {`var`}
FROM {`tbl`}
WHERE {`tbl`}.sepal_length > ?
", .con = con)
query <- DBI::dbSendQuery(con, sql)
DBI::dbBind(query, list(num))
DBI::dbFetch(query, n = 4)
#> sepal_width
#> 1 3.5
#> 2 3.0
#> 3 3.2
#> 4 3.1
DBI::dbClearResult(query)
# `glue_sql()` can be used to build up more complex queries with
# interchangeable sub queries. It returns `DBI::SQL()` objects which are
# properly protected from quoting.
sub_query <- glue_sql("
SELECT *
FROM {`tbl`}
", .con = con)
glue_sql("
SELECT s.{`var`}
FROM ({sub_query}) AS s
", .con = con)
#> <SQL> SELECT s.`sepal_width`
#> FROM (SELECT *
#> FROM `iris`) AS s
# If you want to input multiple values for use in SQL IN statements put `*`
# at the end of the value and the values will be collapsed and quoted appropriately.
glue_sql("SELECT * FROM {`tbl`} WHERE sepal_length IN ({vals*})",
vals = 1, .con = con)
#> <SQL> SELECT * FROM `iris` WHERE sepal_length IN (1)
glue_sql("SELECT * FROM {`tbl`} WHERE sepal_length IN ({vals*})",
vals = 1:5, .con = con)
#> <SQL> SELECT * FROM `iris` WHERE sepal_length IN (1, 2, 3, 4, 5)
glue_sql("SELECT * FROM {`tbl`} WHERE species IN ({vals*})",
vals = "setosa", .con = con)
#> <SQL> SELECT * FROM `iris` WHERE species IN ('setosa')
glue_sql("SELECT * FROM {`tbl`} WHERE species IN ({vals*})",
vals = c("setosa", "versicolor"), .con = con)
#> <SQL> SELECT * FROM `iris` WHERE species IN ('setosa', 'versicolor')
# If you need to reference variables from multiple tables use `DBI::Id()`.
# Here we create a new table of nicknames, join the two tables together and
# select columns from both tables. Using `DBI::Id()` and the special
# `glue_sql()` syntax ensures all the table and column identifiers are quoted
# appropriately.
iris_db <- "iris"
nicknames_db <- "nicknames"
nicknames <- data.frame(
species = c("setosa", "versicolor", "virginica"),
nickname = c("Beachhead Iris", "Harlequin Blueflag", "Virginia Iris"),
stringsAsFactors = FALSE
)
DBI::dbWriteTable(con, nicknames_db, nicknames)
cols <- list(
DBI::Id(table = iris_db, column = "sepal_length"),
DBI::Id(table = iris_db, column = "sepal_width"),
DBI::Id(table = nicknames_db, column = "nickname")
)
iris_species <- DBI::Id(table = iris_db, column = "species")
nicknames_species <- DBI::Id(table = nicknames_db, column = "species")
query <- glue_sql("
SELECT {`cols`*}
FROM {`iris_db`}
JOIN {`nicknames_db`}
ON {`iris_species`}={`nicknames_species`}",
.con = con
)
query
#> <SQL> SELECT `iris`.`sepal_length`, `iris`.`sepal_width`, `nicknames`.`nickname`
#> FROM `iris`
#> JOIN `nicknames`
#> ON `iris`.`species`=`nicknames`.`species`
DBI::dbGetQuery(con, query, n = 5)
#> sepal_length sepal_width nickname
#> 1 5.1 3.5 Beachhead Iris
#> 2 4.9 3.0 Beachhead Iris
#> 3 4.7 3.2 Beachhead Iris
#> 4 4.6 3.1 Beachhead Iris
#> 5 5.0 3.6 Beachhead Iris
DBI::dbDisconnect(con)