如何在 R Markdown SQL 块中使用 SQL 参数 [英] How to use SQL parameters in an R markdown SQL chunk

问题描述

在 Rmd SQL 代码块中包含参数的正确方法是什么?这是一个示例 Rmd 文件:

---标题:Rmd 示例"输出:html_document:df_print:分页pdf_document: 默认---`r Sys.Date()`这是 Rmd 文件的示例.```{r}图书馆(tidyverse)图书馆(DBI)图书馆(odbc)图书馆(knitr)选项(connectionObserver = NULL)con = dbConnect(odbc::odbc(),dsn = "myDsn",uid = "我的用户名",pwd = "我的密码")d1=as.Date(2021-03-15")``在 `r d1` 之后寻找所有遭遇```{sql, connection=con, output.var = 遭遇"}选择 *来自相遇日期>?d1```r d1` 之后有 `r nrow(encounters)`，它们是```{r}kable(遭遇)``文档到此结束.

SQL 代码块中的 ?d1 部分不起作用，因为它不是字符串.假设我从其他来源获得 d1 ，它无法硬编码到 SQL 查询本身中.与数据库交互的正常方法是参数化这个值，据我所知，它有两件非常重要的事情:

1. 确保参数值具有正确的数据类型，并且
2. 通过正确转义字符来防止 SQL 注入

R 确实有能力支持参数化，例如

图书馆(tidyverse)图书馆(DBI)图书馆(odbc)选项(connectionObserver = NULL)con = dbConnect(odbc::odbc(),dsn = "myDSN",uid = "我的用户名",pwd = "我的密码")d1=as.Date(2021-03-15")查询=选择*从遇到日期>:d1"；遭遇=dbGetQuery(con，查询，参数=d1)

此 R 代码运行良好.如何让 R markdown SQL 代码块接受这样的 SQL 参数??d1 语法显然只插入文本，它不处理数据类型，虽然文档不清楚，但我怀疑它是否也能正确转义字符.

明确地说，我不是在寻找一个时髦的黑客来运行代码.我可以使用直接的 R 代码而不是 SQL 块——失去语法突出显示比失去对参数的正确评估更可取.我正在寻找将 SQL 参数放入 SQL 块的正确方法.

解决方案

也许这对你来说是一个解决方案，使用glue_sql，它在我的电脑上工作

Rmd 块:

库(DBI)图书馆(胶水)图书馆(RSQLite)con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")dbWriteTable(con, mtcars", mtcars, overwrite = TRUE)cyl_int <- c(4L, 6L)cyl_sql <-glue_sql("{cyl_int*}")

 SELECT * FROM mtcars哪里 cyl 在 (?cyl_sql)限制 3

对于日期列，您可能必须根据您的数据库对其进行正确格式化或使用 to_date(etc, '%d%...')在 sqlite 中，使用 ISO-8601 日期很容易，例如:

库(DBI)图书馆(胶水)图书馆(RSQLite)con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")mtcars$DATE_1 <- seq(from = Sys.Date() - 15, to = Sys.Date() + nrow(mtcars) -16, by = 1) %>% as.character()dbWriteTable(con, mtcars", mtcars, overwrite = TRUE)cyl_int <- c(4L, 6L)cyl_sql <-glue_sql("{cyl_int*}")d_date_sql <-glue_sql("{Sys.Date()*}", .con = con)

请注意，您必须为字符参数指定 .con 参数，如

What's the right way to include parameters in an Rmd SQL code chunk? Here's an example Rmd file:

---
title: "Rmd Example"
output:
  html_document:
    df_print: paged
  pdf_document: default
---
`r Sys.Date()`
This is an example of an Rmd file.
```{r}
library(tidyverse)
library(DBI)
library(odbc)
library(knitr)
options(connectionObserver = NULL)
con = dbConnect(odbc::odbc(), 
                dsn = "myDsn", 
                uid = "myUsername", 
                pwd = "myPassword")
d1=as.Date("2021-03-15")
```
Look for all encounters after `r d1`
```{sql, connection=con, output.var = "encounters"}
select *
from encounters
where date>?d1
```
There are `r nrow(encounters)` after `r d1` and they are
```{r}
kable(encounters)
```
This ends the document.

The ?d1 part in the SQL code chunk doesn't work because it's not a string. Pretend I'm getting d1 from some other source, it is something that can't be hard-coded into the SQL query itself. The normal way to interact with databases would be to parameterize this value, which as I understand it does two pretty important things:

1. ensure the parameter value has the correct datatype, and
2. prevent SQL injection by properly escaping characters

R does have the ability to support parameterization, for example

library(tidyverse)
library(DBI)
library(odbc)
options(connectionObserver = NULL)
con = dbConnect(odbc::odbc(), 
    dsn = "myDSN",
    uid = "myUsername", 
    pwd = "myPassword")
d1=as.Date("2021-03-15")
query="select * from encounters where date>:d1"
encounters=dbGetQuery(con,query,params=d1)

This R code runs fine. How can I get the R markdown SQL code chunk to accept an SQL parameter like this? The ?d1 syntax apparently only inserts text, it doesn't handle data types, and although the documentation isn't clear I'm doubtful it is properly escaping characters either.

To be clear I'm not looking for a funky hack to get the code to run. I can use straight R code instead of the SQL chunk--losing the syntax highlighting would be preferable to losing correct evaluation of parameters. I'm looking for the correct way to put SQL parameters in an SQL chunk.

解决方案

Maybe this is a solution for you, using glue_sql, it works on my computer

https://community.rstudio.com/t/using-multiple-r-variables-in-sql-chunk/2940/13

Rmd chunks:

library(DBI)
library(glue)
library(RSQLite)

con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "mtcars", mtcars, overwrite = TRUE)

cyl_int <- c(4L, 6L)
cyl_sql <- glue_sql("{cyl_int*}")

  SELECT * FROM mtcars
  WHERE cyl IN (?cyl_sql)
  LIMIT 3

For a date column, you may have to format it correctly according to your database or use to_date(etc, '%d%...') Here in sqlite it's easy with ISO-8601 dates, an example:

library(DBI)
library(glue)
library(RSQLite)

con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
mtcars$DATE_1 <- seq(from = Sys.Date() - 15, to = Sys.Date() + nrow(mtcars) -16, by = 1) %>% as.character()
dbWriteTable(con, "mtcars", mtcars, overwrite = TRUE)

cyl_int <- c(4L, 6L)
cyl_sql <- glue_sql("{cyl_int*}")

d_date_sql <- glue_sql("{Sys.Date()*}", .con = con)

Note that you have to specify .con parameter for character parameters, as said here

  SELECT cyl, mpg, DATE_1 FROM mtcars
  WHERE DATE_1 >= ?d_date_sql
  ORDER BY DATE_1

这篇关于如何在 R Markdown SQL 块中使用 SQL 参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！