I used the following sql
code in .Rmd
document. However, I want to use the same SQL code in .Rnw
document.
```{r label = setup, include=FALSE}
knitr::opts_chunk$set(echo = TRUE, max.print = NA)
```
```{r, echo=FALSE, results='hide'}
library(DBI)
db <- dbConnect(RSQLite::SQLite(), dbname = "survey.db")
dbListTables(db)
```
```{sql, label = Q1, connection=db, tab.cap = "Table Caption"}
SELECT *
FROM Person;
```
Would prefer to get code formatting and output printing facility.
Porting the RMarkdown to RNW requires some tweaking:
```{r, echo=FALSE}
is <<echo=FALSE>>=
and RNW chunks end with @
. (See the minimal RNW example.)engine
is set. So ```{r}
becomes simply <<>>=
, but the equivalent of ```{sql}
is <<engine="sql">>=
.For embedding SQL in RMarkdown, note that the SQL connection must be passed to the SQL chunk via the connection
option. The option output.var
can be used to specify the name of the object to which the result of the query will be assigned.
A simple solution (see previous revision) would just assign the SQL result to an object, say res
, using output.var
and add another R chunk that prints res
nicely, e.g. using xtable
. However, there is a more elegant approach using hooks:
The example uses the SQLite sample database from sqlitetutorial.net. Unzip it to your working directory before running the code.
\documentclass{article}
\begin{document}
\thispagestyle{empty}
<<include=FALSE>>=
library(knitr)
library(DBI)
knit_hooks$set(formatSQL = function(before, options, envir) {
if (!before && opts_current$get("engine") == "sql") {
sqlData <- get(x = opts_current$get("output.var"))
max.print <- min(nrow(sqlData), opts_current$get("max.print"))
myxtable <- do.call(xtable::xtable, c(list(x = sqlData[1:max.print, ]), opts_current$get("xtable.args")))
capture.output(myoutput <-do.call(xtable::print.xtable, c(list(x = myxtable, file = "test.txt"), opts_current$get("print.xtable.args"))))
return(asis_output(paste(
"\\end{kframe}",
myoutput,
"\\begin{kframe}")))
}
})
opts_chunk$set(formatSQL = TRUE)
opts_chunk$set(output.var = "formatSQL_result")
opts_chunk$set(max.print = getOption("max.print"))
@
<<echo=FALSE, results="hide">>=
db <- dbConnect(RSQLite::SQLite(), dbname = "chinook.db")
@
<<engine = "sql", connection=db, max.print = 8, xtable.args=list(caption = "My favorite artists?", label="tab:artist"), print.xtable.args=list(comment=FALSE, caption.placement="top")>>=
SELECT * FROM artists;
@
\end{document}
A new chunk hook formatSQL
is added. (Chunk hooks run whenever the corresponding chunk option is not NULL
.) After a chunk with engine="sql"
, it reads the SQL results into sqlData
. Then, it uses xtable
to print the first max.print
rows of the result.
By default, the chunk hook formatSQL
is activated (i.e. it is globally set to TRUE
) and SQL results are stored in formatSQL_result
. The chunk option max.print
controls the number of rows to be printed (set it to Inf
to print all rows, always).
The table produced by xtable
is highly customizable. The chunk option xtable.args
is passed to xtable
and print.xtable.args
is passed to print.xtable
. In the example these options are used to set a caption, a label and to suppress xtable's default comment.
Below the generated PDF. Note that syntax highlighting for non-R code in RNW requires installing highlight and adding the directory to path (Windows).