SQL 数据库通常对标识符和字符串具有自定义引用语法,这使得编写 SQL 查询容易出错且繁琐。 glue_sql()
和 glue_data_sql()
类似于处理 SQL 引用的 glue()
和 glue_data()
。 glue_sql_collapse()
可用于折叠DBI::SQL()
对象。
用法
glue_sql(..., .con, .envir = parent.frame(), .na = DBI::SQL("NULL"))
glue_data_sql(.x, ..., .con, .envir = parent.frame(), .na = DBI::SQL("NULL"))
参数
- ...
-
[
expressions
]
未命名的参数被视为要格式化的表达式字符串。多个输入在格式化之前连接在一起。命名参数被视为可用于替换的临时变量。 - .con
-
[
DBIConnection
]:从DBI::dbConnect()
获取的DBI连接对象。 - .envir
-
[
environment
:parent.frame()
]
计算每个表达式的环境。表达式从左到右计算。如果.x
是一个环境,表达式在该环境中求值并且.envir
被忽略。如果NULL
通过了,就相当于emptyenv()
. - .na
-
[
character(1)
:‘不适用’]
要替换的值NA
值与.如果NULL
缺失值被传播,即NA
结果会导致NA
输出。否则该值将被替换为.na
. - .x
-
[
listish
]
用于查找值的环境、列表或 DataFrame 。
值
具有给定查询的 DBI::SQL()
对象。
细节
它们会自动引用字符结果,如果粘合表达式被反引号“`
”包围,则引用标识符,并且不引用非字符(例如数字)。如果数字数据存储在字符列(应加引号)中,则将数据作为字符传递给glue_sql()
。
如果需要给定值,使用 DBI::SQL()
返回结果将抑制引用。
注意 parameterized queries 通常是在查询中传递用户定义值的最安全、最有效的方法,但并非每个数据库驱动程序都支持它们。
如果将 *
放在粘合表达式的末尾,则值将用逗号折叠。例如,这对于 SQL IN Operator 很有用。
也可以看看
glue_sql_collapse()
折叠 DBI::SQL()
对象。
例子
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)
相关用法
- R glue glue_safe 安全地插入字符串
- R glue glue_col 用颜色构造字符串
- R glue glue_collapse 折叠字符向量
- R glue glue 格式化并插入字符串
- R glue trim 修剪字符向量
- R glue quoting 引用运算符
- R SparkR glm用法及代码示例
- R ggplot2 annotation_logticks 注释:记录刻度线
- R axisTicks 计算漂亮的轴刻度
- R googledrive drive_cp 复制云端硬盘文件
- R legend 将图例添加到绘图中
- R googledrive drive_mime_type 查找 MIME 类型
- R ggplot2 vars 引用分面变量
- R grid.curve 在位置之间绘制曲线
- R googledrive drive_reveal 添加新的云端硬盘文件信息列
- R googledrive drive_rm 从云端硬盘删除文件
- R ggplot2 position_stack 将重叠的对象堆叠在一起
- R ggplot2 geom_qq 分位数-分位数图
- R hcl HCL 颜色规格
- R legendGrob 构建一个图例 Grob
- R quartzFonts 石英字体
- R ggplot2 geom_spoke 由位置、方向和距离参数化的线段
- R googlesheets4 sheet_rename 重命名(工作)表
- R googledrive expose 暴露对象
- R grid.draw 画一个网格
注:本文由纯净天空筛选整理自Jim Hester等大神的英文原创作品 Interpolate strings with SQL escaping。非经特殊声明,原始代码版权归原作者所有,本译文未经允许或授权,请勿转载或复制。