6.301
Working with Databases in R

Introduction

R programs can connect to virtually any relational (and many non-relational) databases as well as tabular data in files. This tutorial provides an overview of the different approaches to working with data in databases.

Connect to Database

The first step is to connect to the database. This requires a package for the database to which you connect. In this tutorial we will connect to SQLite so we need the corresponding package: RSQLite. Naturally, as with all packages in R, you must install the package first and then load it with library.

With SQLite, if you attempt to open a database that does not exist, it will be created as a new and empty database. This is not true for other databases: any creation must be done through admin tools. In the code below, we connect to an existing database. If you are working in an R Project, the database file will be created in the project’s root folder, otherwise it will be created in the current working directory (which could be anywhere, so best to work in a project).

library(RSQLite)

dbfile = "CoffeeDB.sqlitedb"
conn <- dbConnect(RSQLite::SQLite(), dbfile)

The RSQLite package has a small set of database meta functions that allow us to enquire about the database itself. For example, the function dbListTables lists all tables in the database. The parameter conn is the database connection object returned from dbConnect().

dbListTables(conn)

## [1] "arx_coffees" "coffees"     "customers"   "order_items" "orders"     
## [6] "salespeople"

To get the list of columns of a specific table, you can use dbListFields().

dbListFields(conn, "coffees")

## [1] "id"          "coffee_name" "price"

Setting Default Connection

If only one SQLite (or other) database connection is used for SQL code chunks, then a default connection can bet configured which means that the connections string connection=dbcon can be omitted. The R code fragment must be included after you made a connection to the database. Naturally, replace the connection variable dbcon below with the variable from your own code, i.e., the variable to which you assigned the return value from dbConnect().

library(DBI)
dbcon <- dbConnect(RSQLite::SQLite(), dbname = "sql.sqlite")
knitr::opts_chunk$set(connection = "dbcon")

Execute SQL Query

There are two common ways to run a SQL query against a database. The approaches are the same for all databases and are not exclusive to SQLite.

1. Embed the SQL statement in a {sql} chunk.
2. Send the query via an R function (dbGetQuery() and dbSendQuery()).

Any embedded SQL code in a {sql} chunk is translated to a dbSendStatement() call during knitting. So, there’s no performance difference and which to use is a matter of preference. Using a {sql} chunk makes database code more explicit and conforms to the principles of literate programming.

dbGetQuery() and dbSendQuery() are used to send SELECT statements, while dbSendStatement() is used for INSERT, UPDATE, DELETE, CREATE TABLE, and any other SQl statement.

If you need to write an R script (rather than an R Notebook) then you must use the R functions and cannot use {sql} chunks.

Simple Embedded Query: {sql} Code Chunks

The {sql} chunk requires specifying the connection to the database to which the SQL query should be send in the connection parameter. This is the return value from dbConnect().

The generic structure of a {sql} chunk is defined below. A SQL statement can be a SELECT, INSERT, UPDATE, DELETE, ALTER, CREATE, or any other valid SQL statement.

The result of the above SQL query is shown below.

select coffee_name as coffee, price
  from coffees
 where price > 8;

Table 1: 8 records

coffee	price
French_Roast	8.99
Espresso	9.99
Colombian_Decaf	8.99
French_Roast_Decaf	9.99
French_Roast	8.99
Espresso	9.99
Colombian_Decaf	8.99
French_Roast_Decaf	9.99

You can only place one SQL statement into a {sql} chunk and thus the semi-colon at the end of statement is optional.

SQL Chunk Parameters

The list below are the most common parameters for a {sql} chunk, although the list is not exhaustive and there are others.

• connection – a connection object to the database to which the SQL statement will be sent for execution; generally the return value from dbConnect()

• eval – if TRUE, then the SQL code is executed; if FALSE, then the code is not executed

• echo – if set to TRUE, then the SQL code is included in any knitted file; if FALSE then the code in the chunk is not in the knitted output

• output.var – the name of a data frame variable in which the output result will be stored and can be used is subsequent R code chunks; the variable must be in quotes

Capture Query Result

Every SQL SELECT query returns a table. In R, the returned table is transformed to a data frame. This data frame can be assigned to a variable for further processing. For a {sql} chunk you specify the data frame to which the result set is saved using the output.var parameter:

{sql simpleSQLwithResult, connection = conn, output.var = ‘rs’}.

Note that the output data frame must be in quotes.

We can now use the result in R. So, in the example below we calculate the average price of coffee.

mean.Coffee.Price <- mean(rs$price)

print(paste0("Average price of coffee: $", round(mean.Coffee.Price, 2)))

## [1] "Average price of coffee: $9.49"

Embedded Queries with Parameters

Often, we need to execute a SQL query but use a parameter from R. The code below demonstrates how to “pass a variable” to a {sql} chunk and how to use the parameter in the chunk.

First, here is an R code chunk that defines a variable called thePrice and sets it to the value 8.

thePrice <- 8

In the SQL code chunk below, we refer to the value of that variable using the syntax ?RVariable. So, the ?thePrice is substituted with the value of the R variable thePrice.

select * from coffees where price > ?thePrice 

So, the actual SQL query that is sent to the database is: select * from coffees where price > 8.

To verify, here is the result of the query (we used the output.var=“expCoffees” parameter in the SQL code chunk):

head(expCoffees,4)

##   id        coffee_name price
## 1  2       French_Roast  8.99
## 2  3           Espresso  9.99
## 3  4    Colombian_Decaf  8.99
## 4  5 French_Roast_Decaf  9.99

As an alternative, we could have executed the query fully in an R code chunk using the function dbGetQuery() which returns the result as a data frame. In this example, the parameter is passed to the SQL query through string concatenation.

p <- 8
sql <- paste0("select * from coffees where price > ", p)
df <- dbGetQuery(conn, sql)

Querying via R Code

Rather than embedding the SQL query in a {sql} chunk, it often more convenient, simpler, or necessary to execute the query within an R code block. There is no performance difference.

There are two R functions you can choose from: dbGetQuery() and dbSendQuery().

To execute a query within R code, you need to connect to the database, define the SQL query as a string, run the query, and then inspect the result set. The result set is a data frame. Of course, you only need to connect to the database once and not every time before a query. In the example below, we use the same database connection from above.

sql <- "select coffee_name as coffee, price
        from coffees
        where price > 8;"

rs <- dbGetQuery(conn, sql)

print(rs)

##               coffee price
## 1       French_Roast  8.99
## 2           Espresso  9.99
## 3    Colombian_Decaf  8.99
## 4 French_Roast_Decaf  9.99
## 5       French_Roast  8.99
## 6           Espresso  9.99
## 7    Colombian_Decaf  8.99
## 8 French_Roast_Decaf  9.99

Note that the semi-colon at the end of the query is optional as you can only send a single query. The syntax of the query depends on the database – it is not interpreted by R but rather by the database. For example, MySQL recognizes CREATE SCHEMA as a SQL statement, but SQLite does not.

If you needed to add a parameter, then you can use paste0() to generate the SQL query string.

p <- 9

sql <- "select coffee_name as coffee, price
        from coffees
        where price > "
sql <- paste0(sql, p)

rs <- dbGetQuery(conn, sql)

print(rs)

##               coffee price
## 1           Espresso  9.99
## 2 French_Roast_Decaf  9.99
## 3           Espresso  9.99
## 4 French_Roast_Decaf  9.99

Alternatively, you can use dbSendQuery(), which allows you to add parameters to the query. Note that dbSendQuery() executes the query but does not return a result. For that you will need to explicitly call dbFetch()

p <- 9

sql <- "select coffee_name as coffee, price
        from coffees
        where price > "
sql <- paste0(sql, p)

rs <- dbSendQuery(conn, sql)

dbFetch(rs)

##               coffee price
## 1           Espresso  9.99
## 2 French_Roast_Decaf  9.99
## 3           Espresso  9.99
## 4 French_Roast_Decaf  9.99

print(rs)

## <SQLiteResult>
##   SQL  select coffee_name as coffee, price
##         from coffees
##         where price > 9
##   ROWS Fetched: 4 [complete]
##        Changed: 0

Rather than manipulating the query string, you can add parameters to the query. The parameters are marked with ? in the SQL query string and are then bound to actual values (in order) with a list passed to the parameter params.

minP <- 8
maxP <- 12

sql <- "select coffee_name as coffee, price
        from coffees
        where price between ? and ?"

rs <- dbSendQuery(conn, sql, params = list(minP, maxP))

## Warning: Closing open result set, pending rows

dbFetch(rs)

##               coffee price
## 1       French_Roast  8.99
## 2           Espresso  9.99
## 3    Colombian_Decaf  8.99
## 4 French_Roast_Decaf  9.99
## 5       French_Roast  8.99
## 6           Espresso  9.99
## 7    Colombian_Decaf  8.99
## 8 French_Roast_Decaf  9.99

print(rs)

## <SQLiteResult>
##   SQL  select coffee_name as coffee, price
##         from coffees
##         where price between ? and ?
##   ROWS Fetched: 8 [complete]
##        Changed: 0

Reading Entire Tables

The convenience function dbReadTable reads an entire table into a data frame. It is equivalent to the query SELECT * FROM table.

rs <- dbReadTable(conn, "coffees")

## Warning: Closing open result set, pending rows

head(rs, 3)

##   id  coffee_name price
## 1  1    Colombian  7.99
## 2  2 French_Roast  8.99
## 3  3     Espresso  9.99

Writing Entire Data Frames

The convenience function dbWriteTable writes an entire data frame to a table. There are two important parameters for the function: overwrite and append. By default, both are FALSE which means that dbWriteTable can only be used to write a data frame to a new table (i.e., one that does not exist in the database). To add rows to an existing table requires append = T.

rs <- dbReadTable(conn, "coffees")

# let's write all of the data to an archive table, but let's delete the old
# table first
dbRemoveTable(conn, "arx_coffees", fail_if_missing = F)
dbWriteTable(conn, "arx_coffees", rs, append = T)

# and read it back to see if it worked
rs <- dbReadTable(conn, "arx_coffees")

tail(rs, 3)

##    id        coffee_name price
## 8  13           Espresso  9.99
## 9  14    Colombian_Decaf  8.99
## 10 15 French_Roast_Decaf  9.99

Complete Example

The code below shows the complete sequence of connect to, creating, inserting, and querying a SQLite database from an R program (download script):

# Load necessary library
library(RSQLite)

# Create a new SQLite database
db <- dbConnect(SQLite(), dbname = "sample.db")

# Create a table named 'customers'
dbExecute(db, "
  CREATE TABLE customers (
    cid INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT
  )
")

## [1] 0

# Insert 3 rows of synthetic data into the 'customers' table
s <- dbExecute(db, "INSERT INTO customers (cid, name, email) VALUES (1, 'Alice', 'alice@example.com')")
s <- dbExecute(db, "INSERT INTO customers (cid, name, email) VALUES (2, 'Bob', 'bob@example.com')")
s <- dbExecute(db, "INSERT INTO customers (cid, name, email) VALUES (3, 'Charlie', 'charlie@example.com')")

# Retrieve all rows in the 'customers' table and display them
customers <- dbGetQuery(db, "SELECT * FROM customers")
print(customers)

##   cid    name               email
## 1   1   Alice   alice@example.com
## 2   2     Bob     bob@example.com
## 3   3 Charlie charlie@example.com

# Close the connection to the database
dbDisconnect(db)

The return value of dbExecute() is always the number of rows affected. So, if the return value is 0 it means that the SQL statement failed. Consequently, it is always a good idea to check the return value of dbExecute() and dbSendStatement().

Other Convenience Functions

In addition to the convenience functions mentioned above, you also have the ones below, among others:

• dbAppendTable()
• dbCreateTable()
• dbRemoveTable()

Querying Data Frames with sqldf

sqldf is a package that allows data frames to be queried with SQL as if they were tables in a database. Many queries, while generally doable in Base R or with tidyverse, are often simpler with a SQL query – albeit a bit slower, but that reduction in performance is often not perceptible. However, when the data frame is large, base R functions are much faster as sqldf actually works by copying the data frame to a temporary in-memory SQLite database and runs a SQL query on that table in the database – that can be slow. Use sqldf primarily for grouping and extracting unique values – use which() and any() as well as logical vector operations otherwise.

We will demonstrate the use of sqldf by querying the data in a large CSV file. The file is loaded into a data frame and then treated as if it were a table in a database.

txns <- read.csv("customertxndata.csv", 
               header = F, 
               col.names = c("visits","numtxns","os","gender","total"))

The CSV file contains data on visits to an e-commerce site.

head(txns, 4)

##   visits numtxns      os gender     total
## 1      7       0 Android   Male    0.0000
## 2     20       1     iOS   <NA>  576.8668
## 3     22       1     iOS Female  850.0000
## 4     24       2     iOS Female 1050.0000

Execute sqldf SQL Query

sqldf executes a SQL query against an in-memory data frame and returns the query result as a data frame. The data frame can be captured and then processed further with sqldf or R.

library(sqldf)

sqlq <- "select gender, sum(total) as total from `txns` group by gender"
df <- sqldf(sqlq)

head(df, 3)

##   gender   total
## 1   <NA> 2522332
## 2 Female 2790500
## 3   Male 5059692

library(sqldf)

# imagine you need to ask user for OS, perhaps by choosing from a menu
theOS <- "' and 1=1 or gender <>'"

sqlq <- paste0("select gender, sum(total) as total from `txns` where os='",
                theOS, "' group by gender")
df <- sqldf(sqlq)

head(df, 4)

##   gender   total
## 1 Female 2790500
## 2   Male 5059692

A potential issue with sqldf can occur when you connect to MySQL or a non-SQLite database as sqldf attempts to use your existing database connection as a backing store for its data; this will often not work due to security constraints. So, you need to add the R code options(sqldf.driver = ‘SQLite’) which forces sqldf to use SQLite as its backing store.

Columns containing dot (.): A column containing a dot (period) in its name confuses SQL as the dit is a separator between table name and column name. So, T.x refers to column x in the table T. That is useful when you have queries that involve multiple tables and columns with the same name are in several tables; it allows disambiguation. However, if you have a column in a CSV or data frame named “year.before” then SQL would interpret incorrectly; to “escape” the meaning of the dot, you need to enclose the column in backticks, i.e., `year.before`.

Creating a Database

Let’s create a new (and empty) database.

library(RSQLite)

db.file <- "customerDB.db"

unlink(db.file)
custDB <- dbConnect(RSQLite::SQLite(), db.file)

We can create as well as drop tables in three ways:

1. embed SQL CREATE TABLE statements in a {sql} chunk
2. send SQL CREATE TABLE statements with dbExecute()
3. call the convenience functions dbCreateTable() or dbRemoveTable()

Which to use is preference; there is little difference in performance.

Dropping a Table

sqlCode <- "drop table if exists txns"
dbExecute(custDB, sqlCode)

## [1] 0

Create Tables in {sql}

Create new tables using {sql} chunks:

create table txns (
  id INTEGER PRIMARY KEY,
  visits INTEGER NOT NULL,
  os TEXT
)

Create Table in R

Create new tables using dbExecute():

sqlCode <- "create table if not exists txns (
              id INTEGER PRIMARY KEY,
              visits INTEGER NOT NULL,
              os TEXT
            )"
dbExecute(custDB, sqlCode)

## [1] 0

Adding Data to the Database

Adding new data to the database can be done in several ways. Insert data row by row using SQL INSERT statements or add all rows from a data frame to a table in batch mode using dbWriteTable().

The code below loads a CSV that we will load into the a newly created database.

txns <- read.csv("customertxndata.csv", 
               header = F,
               col.names = c("visits","numtxns","os","gender","total"))

txns$id <- seq(from = 1, to = nrow(txns)) + 100

Bulk Loading

To get data from a data frame into a table in the database, you can either insert the data row by row in a loop or (better) use dbWriteTable() which inserts an entire data frame into a new or an existing table.

dbWriteTable(custDB, "txns", txns[,c(6,1,3)], overwrite=F, append=T)

dbWriteTable() requires that “bulk/batch loading” is enables for the database. This is not the default for MySQL but is for SQLite. For MySQL it must be configured first. Some cloud services for MySQL do not enable batch loading (e.g., db4free.net).

If your table has row names and you end up with an extra column in the database, then specify row.names = F as a parameter.

Prepared Statements

Prepared statements are a special form of a SQL statement that includes parameters. It is preferable to use prepared statements rather than build a SQL string use concatenation. However, some database may not support statements (notably MySQL hosted on db4free.net).

id <- 102
numVisits <- 4
os <- 'iOS'

# sets up prepared statement code
sqlCode <- "insert into txns values (:id, :n, :os)"

# create a prepared statement with parameters
ps <- dbSendStatement(custDB, sqlCode)

# bind parameters
dbBind(ps, params = list(id = id, n = numVisits, os = os))

# run the query
dbClearResult(ps)

Insert Data Row by Row

If bulk loading is not an option because either the database does not support it or the data has to be processed prior to insertion, then adding the data row by row in a loop using a prepared statement is an alternative. However, this process is much slower and should only be used as a last resort.

nrows <- nrow(txns)

for (i in 1:nrows)
{
  id <- txns[i,6]
  numVisits <- txns[i,1]
  os <- txns[i,3]
  
  # sets up prepared statement code
  sqlCode <- "insert into txns values (:id, :n, :os)"
  
  # create a prepared statement with parameters
  ps <- dbSendStatement(custDB, sqlCode)
  
  # bind parameters
  dbBind(ps, params = list(id = id, n = numVisits, os = os))
}

The code below is a performance improvement where the loop generates a single large SQL INSERT statement that inserts multiple rows in a single call to the database. Some database may restrict the number of values that can be included in a single INSERT statement, so this may have to be done in batches.

delete from txns

nrows <- nrow(txns)
sqlCode <- "insert into txns values "

for (i in 1:nrows)
{
  id <- txns[i,6]
  numVisits <- txns[i,1]
  os <- txns[i,3]
  
  # add values to the list of values
  sqlCode <- paste0(sqlCode, "(",id,",", numVisits, ",'", os, "')")
  
  # add comma at the end except for the last row
  if (i < nrows)
    sqlCode <- paste0(sqlCode, ",")
}

print(sqlCode)

# execute the insert statement
r <- dbExecute(custDB, sqlCode)

If the data frame is too large and the subsequent SQL INSERT statement would have too many values and exceed a database limit, then insert the data in stages. For example, read rows in batches from the CSV and inserts them into the database. read.csv has parameters skip and nrows to help do that.

Clearing Result Sets

A common warning message is received (“Warning: Closing open result set, pending rows”) when working with SQLite databases in R using the DBI or RSQLite package. It indicates that a previous query or statement execution was not properly finalized or closed, leaving an open “result set” that hasn’t been fully handled. This typically happens when you:

1. Execute a query or statement that returns rows (e.g., a SELECT statement) but do not fetch or clear the results.
2. Forget to explicitly clear or finalize a result set after running dbSendStatement() or dbSendQuery().

Understanding the Problem

The function dbSendStatement() (from the DBI package) executes a SQL statement, such as an INSERT, UPDATE, or DELETE, which does not return a result set. However, the function returns a DBIResult object that must be cleared explicitly using dbClearResult().

If you don’t clear the DBIResult object, it remains open, and when you close the connection or perform another database operation, RSQLite automatically clears the open result, leading to the warning.

Resolving the Warning

To resolve the warning, you should explicitly clear the result set after executing a statement with dbSendStatement(). Here’s an example:

# Load required packages
library(DBI)
library(RSQLite)

# Create a connection to the SQLite database
con <- dbConnect(SQLite(), "example.db")

# Example: Insert data into a table
stmt <- dbSendStatement(con, "INSERT INTO my_table (column1, column2) VALUES (?, ?)")

# Bind values to the statement
dbBind(stmt, list("value1", "value2"))

# Clear the result explicitly to avoid the warning
dbClearResult(stmt)

# Disconnect from the database
dbDisconnect(con)

Key Points to Avoid the Warning

1. Always Clear the Result: Use dbClearResult() after executing a query or statement with dbSendQuery() or dbSendStatement().

2. Use dbExecute() for Simpler Cases: If you do not need to bind parameters or fetch rows, prefer using dbExecute(), which handles result clearing automatically:

   dbExecute(con, "INSERT INTO my_table (column1, column2) VALUES ('value1', 'value2')")

3. Review Your Code for Open Results: Check if you have any dbSendQuery() or dbSendStatement() calls without corresponding dbClearResult().

Explanation of the Warning

SQLite enforces that all result sets must be closed before executing new statements. RSQLite warns you when it detects an open result set because this could lead to resource leaks or undefined behavior.

By adhering to proper resource management (clearing results and closing connections), you can avoid such warnings and ensure robust interaction with your SQLite database.

Disconnect from Database

When a database connection is no longer needed, it must be closed by disconnecting from the database. This frees up database and other resources.

dbDisconnect(custDB)

---

Files & Resources

All Files for Lesson 6.301

---

References

• The sqldf Package. R Documentation.
• Copy Data Frame to Tables: dbWriteTable

Errata

Let us know.