knitr::opts_chunk$set(connection = "con", max.print = 5)
eval_dbi <- FALSE if(Sys.getenv("GLOBAL_EVAL") != "") eval_dbi <- Sys.getenv("GLOBAL_EVAL")
library(DBI)
DBI
Connecting and adding data to a database
Load the DBI
package
r
library(DBI)
Use dbConnect
to open a database connection
r
con <- dbConnect(RSQLite::SQLite(), "mydatabase.sqlite")
Use dbListTables()
to view existing tables, there should be 0 tables
```r
```
Use dbWriteTable()
to create a new table using mtcars
data. Name it db_mtcars
```r
```
Use dbListTables()
to view existing tables, it should return db_mtcars
```r
```
Use dbGetQuery()
to pass a SQL query to the database
```r
```
Close the database connection using dbDisconnect()
r
dbDisconnect(con)
Understand how certain arguments in dbWriteTable()
work
Use dbConnect()
to open a Database connection again
r
con <- dbConnect(RSQLite::SQLite(), "mydatabase.sqlite")
Use dbWriteTable()
to re-create the db_mtcars table using mtcars
data
```r
```
Error: Table db_mtcars exists in database, and both overwrite and append are FALSE
Use the append
argument in dbWriteTable()
to add to the data in the db_mtcars table
```r
```
Using dbGetQuery()
, check the current record count of db_mtcars with the following query: "select count() from db_mtcars"
```r
```
Use the overwrite
argument to dbWriteTable()
to replace the data in the db_mtcars table
```r
```
Check the record count of db_mtcars
again
```r
```
Understand how to use dbSendStatement()
and dbExecute()
to modify the database
Use dbSendStatement()
to pass a SQL commands that deletes any automatic car from db_mtcars
: "delete from db_mtcars where am = 1". Load the command to a variable called rs
```r
```
Call the rs
variable to view information about the results of the requested change
```r
```
Use dbHasCompleted()
to confirm that the job is complete
```r
```
Use dbGetRowsAffected()
to see the number of rows that were affected by the request
```r
```
Clear the results using dbClearResult()
```r
```
Confirm that the result set has been removed by calling the rs
variable once more
```r
```
Check the record count of db_mtcars again, the new count should be 19 (32 original records - 13 deleted records) ```r
```
Use dbWriteTable()
to overwrite db_mtcars with the value of mtcars
r
dbWriteTable(con, "db_mtcars", mtcars, overwrite = TRUE)
Use dbExeceute()
to delete rows where am = 1 using the same query as before. Load the results in a variable called rs
```r
```
rs
contains the number of rows affected by the statement that was executed
```r
```
Check the record count of db_mtcars again. ```r
```
knitr
SQL engineSee how to run SQL queries as code chunks
Start a new code chunk, but using sql
instead of r
as the first argument of the chunk. Also add connection=con
as another argument of the chunk.
r ''````{sql, connection=con}
select * from db_mtcars
r ''````
```{sql, connection=con, echo = FALSE} select * from db_mtcars
2. Add the `max.print` options to the chunk, and set it to 5 `r ''````{sql, connection=con, max.print = 5} select * from db_mtcars `r ''```` ```{sql, connection=con, echo = FALSE}
Set defaults for the sql
chunks by using the knitr::opts_chunk$set()
command in the setup
at the beginning of the document.
r ''````r
knitr::opts_chunk$set(connection = "con", max.print = 5)
r ''````
Run the same query in a new sql
chunk, but without any other argument
r ''````{sql}
select * from db_mtcars
r ''````
```{sql, echo = FALSE}
5. Store the results of the query into an R object called `local_mtcars` using the `output.var` option. ```{sql}
local_mtcars
dbDisconnect()
dbDisconnect(con)
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.