CODEWITHSUNDEEP
rr-dbirnotebook
Colin
Colin

Reputation: 13

R notebook won't knit when data frame has been assigned from with sql chunk

I have an R notebook where I am reading in data from a database using an sql chunk and then assigning it to a data frame. I would then like to use it in R chunks within the R notebook so am using the output.vars option in the sql chunk.

When I run all and then preview it works perfectly but when I knit it I get the error: "Error in eval(expr, envir, enclos) : object 'x' not found".

The following is some simple code that will reproduce this error:

---
title: "R Notebook"
output:
  html_notebook: default
  html_document: default
---

```{r setup}
library(DBI)
library(RSQLite)
db = dbConnect(SQLite(), dbname = "C:/R/chinook.db")
```

```{sql connection = db, output.vars = 'x'}
SELECT * FROM artists
```

```{r}
x[1:10,]
```

I am using:

The example uses the SQLite sample database from sqlitetutorial.net http://www.sqlitetutorial.net/download/sqlite-sample-database/?wpdmdl=94.

I have also tried different types of databases without any success.

Upvotes: 1

Views: 353

Answers (2)

Sraffa
Sraffa

Reputation: 1668

I think you have to change output.vars to output.var

```{sql connection = db, output.var = 'x'}
SELECT * FROM artists
```

It works for me like that.

Upvotes: 1

amatsuo_net
amatsuo_net

Reputation: 2448

I am not sure your syntax chunk in the middle would work. Why don't you just do like:

```{r}
library(DBI)
library(RSQLite)
db <- dbConnect(SQLite(), dbname = "C:/R/chinook.db")
x <- dbGetQuery(db, 'SELECT * FROM artists')

```

```{r}
x[1:10,]
```

Upvotes: 1

Related Questions