如何让glue::glue_sql返回仅含单引号的合法SQL查询字符串？

我需要通过glue::glue_sql生成仅包含单引号的SQL查询字符串，尝试了两种方法都未得到预期结果：

方法1 #

con <- RAthena::dbConnect(RAthena::athena(),
                 s3_staging_dir = s3_staging_dir)

column_values <- c("thing1","thing2","thing3")

query1 <- glue::glue_sql("SELECT COUNT (*) as venues FROM schema.table WHERE column IN ({column_values*})", .con = con)
query1

返回结果：

'SELECT COUNT (*) as venues FROM schema.table WHERE column IN (\'thing1\', \'thing2\', \'thing3\')'

方法2 #

con <- RAthena::dbConnect(RAthena::athena(),
                 s3_staging_dir = s3_staging_dir)

column_values <- c("thing1","thing2","thing3")

query2 <- glue::glue_sql("SELECT COUNT (*) as venues FROM schema.table WHERE column IN ({`column_values`*})", .con = con)
query2

返回结果：

'SELECT COUNT (*) as venues FROM schema.table WHERE column IN ("thing1", "thing2", "thing3")'

期望输出：

"SELECT COUNT (*) as venues FROM schema.table WHERE column IN ('thing1', 'thing2', 'thing3')"

解决方法 #

方案1：移除数据库连接参数 #

如果不需要数据库连接的SQL语法适配，直接去掉.con参数，glue_sql默认会用单引号包裹字符串值，最后转换为字符类型即可得到无转义符的结果：

column_values <- c("thing1","thing2","thing3")

query <- glue::glue_sql("SELECT COUNT (*) as venues FROM schema.table WHERE column IN ({column_values*})")
as.character(query)

执行后输出：

[1] "SELECT COUNT (*) as venues FROM schema.table WHERE column IN ('thing1', 'thing2', 'thing3')"

方案2：手动指定单引号转义（保留数据库连接） #

如果必须保留数据库连接，可以使用glue::sql_quote手动指定单引号作为包裹符，再拼接成查询语句：

con <- RAthena::dbConnect(RAthena::athena(), s3_staging_dir = s3_staging_dir)
column_values <- c("thing1","thing2","thing3")

# 为每个值添加单引号
quoted_values <- purrr::map_chr(column_values, ~ glue::sql_quote(., con = con, quote = "'"))
# 拼接成查询字符串
query <- glue::glue("SELECT COUNT (*) as venues FROM schema.table WHERE column IN ({glue::glue_collapse(quoted_values, sep = ', ')})")
as.character(query)

执行后同样会得到符合预期的带单引号的SQL字符串。

内容的提问来源于stack exchange，提问作者Douglas