6.300
SQLite with R: A Primer

Prerequisites

Prior to this lesson, consider working through:

• 70.801 ┆ The SQLite Database

Introduction

This tutorial uses SQL code chunks in R Studio to create a new SQLite database, create several tables, insert sample data, and perform simple data retrievals.

In this quick tutorial with demonstrations, Khoury Boston’s Prof. Schedlbauer explains how to create a new SQLite data using R Studio, how to create tables, inert data into those tables, and show the data in the tables.

The tutorial video below goes through the code interactively and should be viewed in conjunction with the code below.

Let’s take a look at each of the elements that were addressed in the above video…

Create Database

A new SQLite database is created by connecting to a database that does not exist. This forces creation of a new database file. Note that SQLite is not a server-based database management system like MySQL or Oracle, but is a file-based relational database akin to Microsoft Access.

In the example below, the database will be created in the current folder – if the code is in an R Project, then the database file will be created in the project folder. If the database is to be created in a different folder, then a path name must be provided.

By convention, SQLite database files generally have the extension .db or .sqlitedb.

library(RSQLite)

fpath = ""
dbfile = "committeeDB.sqlite"

# if database file already exists, we connect to it, otherwise
# we create a new database
dbcon <- dbConnect(RSQLite::SQLite(), paste0(fpath,dbfile))

The SQL code below is inserted using {sql} code chunks in an R Notebook, although there are also R functions for executing SQL statements, but {sql} chunks are simpler. Each chunk can only contain a single SQL statement, so the semi-colon at the end is optional.

After inserting a SQL chunk, the parameter connection= must be set to the database connection object returning from dbConnect(). So, given our connection code above, we need to write a SQL chunk starting with ```{sql connection=dbcon}.

The remaining sections are independent of SQLite and also apply to other relational databases, such as MySQL.

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")

Create New Tables

Creating Tables with {sql} Chunks

The DROP statement deletes any tables that might already exist.

DROP TABLE IF EXISTS Faculty

Now that we know that the table Faculty does not exist, we will create it usign the CREATE TABLE statement, listing each column and tagging the primary key column(s).

CREATE TABLE Faculty(
  fid INTEGER NOT NULL,
  fname TEXT NOT NULL,
  lname TEXT NOT NULL,
  rank TEXT,
  PRIMARY KEY (fid)
);

We will now create another table and link that table with a foreign key to the Faculty table.

DROP TABLE IF EXISTS Committee

CREATE TABLE Committee (
  name TEXT NOT NULL,
  level TEXT,
  chair INTEGER NOT NULL,
  PRIMARY KEY (name),
  FOREIGN KEY (chair) REFERENCES Faculty(fid)
)

Creating Tables with R Functions

Let’s redo the same SQL statements as above but this time using R functions. Assume that the tables have been deleted (dropped). We will use the same database connection object. The most common function to sending a SQL statement to the database is dbExecute(). It submits and synchronously executes the SQL data manipulation statement (_e.g., UPDATE, DELETE, INSERT, CREATE TABLE, DROP TABLE, etc.). To submit SQL SELECT statements use either dbExecute() or dbGetQuery(). dbExecute() returns the number of rows that were affected by the statement. An alternative function is dbSendStatement() which is often used to build prepared statements.

Note how we build the SQL command as a character string using paste0() so it can span multiple lines and is easier to read.

It is common to not get the SQL statement correct. So, it’s best to test the SQL statement using either a {sql} chunk or an interactive SQL query tool such as SQLite Console or MySQL Workbench.

sql <- paste0(
  "CREATE TABLE Faculty (",
    "fid INTEGER NOT NULL,",
    "fname TEXT NOT NULL,",
    "lname TEXT NOT NULL,",
    "rank TEXT,",
    "PRIMARY KEY (fid)",
  ")"
)

dbExecute(dbcon, sql)

sql <- paste0(
  "CREATE TABLE Committee (",
    "name TEXT NOT NULL,",
    "level TEXT,",
    "chair INTEGER NOT NULL,",
    "PRIMARY KEY (name),",
    "FOREIGN KEY (chair) REFERENCES Faculty(fid)",
  ")"
)

dbExecute(dbcon, sql)

dbExecute() is also used to call a stored procedure.

Foreign Key Constraints

Unlike other databases, SQLite does not check foreign key constraints, by default. To enable foreign key constraints, include the following PRAGMA command before creating any tables:

PRAGMA foreign_keys = ON

Insert Data

Now that we have the tables, we will insert sample data for testing. The code below uses {sql} chunks and is most commonly used in R Notebooks. After this, we will show how to use R functions to do the same operations. That approach is required for R Scripts (aka R programs).

Insert Using {sql} Chunks

INSERT INTO Faculty (fid,fname,lname,rank) VALUES 
  (100,"Jose","Annunziato","Assistant"),
  (200,"Dan","Feinberg","Adjunct"),
  (300,"Martin","Schedlbauer","Full"),
  (400,"Kathleen","Durant","Associate");

INSERT INTO Committee VALUES 
  ("Hiring","College",300),
  ("Merit","College", 300),
  ("Teaching","Department",400)

Insert Using R Functions

Let’s insert some additional data using the R function dbexecute(). An alternative would be to use dbSendStatement(). Recall that dbexecute() returns the number of rows affected, so for an INSERT that should be the number of rows inserted. If it it less than what was stated or 0, then some error occurred. We can check for that in the application logic.

Note the use of single quotes so that double quotes can be embedded.

sql <- paste0(
  'INSERT INTO Committee VALUES', 
  '("Integrity","College",100),',
  '("Research","College", 300),',
  '("CATLR","University",400)'
  )

n <- dbExecute(dbcon, sql)

if (n < 1)
  print("Error during insert")

Other Data Manipulations

Other SQL operations such as DELETE, DROP TABLE, UPDATE, ALTER TABLE, etc. can also be executed using {sql} chunks or dbExecute().

Retrieve Data

The database is now complete. Verify for yourself that the data is in the database by executing some SELECT statements. Again, we will first do a retrieval using a {sql} chunks and then using R functions.

Select in {sql} Chunks

SELECT * FROM Faculty LIMIT 3;

Table 1: 3 records

fid	fname	lname	rank
100	Jose	Annunziato	Assistant
200	Dan	Feinberg	Adjunct
300	Martin	Schedlbauer	Full

The SQL keywords (e.g., SELECT and INSERT) can be upper or lower case, but the columns should follow the capitalization in the schema definition.

The semi-colon (;) at the end of a SQL statement is optional in a {sql} chunk.

Select Using R Functions

While there are several functions for retrieving data from a database. The most common, and simplest, is dbGetQuery(). It returns a result set in the form of a data frame.

sql <- "SELECT * FROM Faculty"

rs <- dbGetQuery(dbcon, sql)

print(rs)

  fid    fname       lname      rank
1 100     Jose  Annunziato Assistant
2 200      Dan    Feinberg   Adjunct
3 300   Martin Schedlbauer      Full
4 400 Kathleen      Durant Associate

As the result set is a data frame, it can be processed like any other data frame. Note that dbGetQuery() attempts to match the data types for the columns of the data frame to the database data types.

lnames <- rs$lname

print(lnames)

[1] "Annunziato"  "Feinberg"    "Schedlbauer" "Durant"     

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(dbcon)

Tutorial

In this video tutorial, Khoury Boston’s Prof. Durant explains in detail how to create tables in a relational databases using SQL Data Definition Language constructs, particularly CREATE TABLE. Although the tutorial uses examples from MySQL, the same SQL statements apply to SQLite, SQL Server, Informix, DB2, and all other relational databases as SQL is a standard.

Example R Program

The code below shows the complete sequence of connect to, creating, inserting, and querying a SQLite database from an R program, rather than using SQL code chunks within an R Notebook. Of course, in an R Notebook, the SQL code chunks are translated into R function calls when the notebook is knitted, so there’s no difference ultimately between an R Notebook or an R program – the difference is that an R Notebook produces a document whereas an R program is an actual program.

# 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 IF NOT EXISTS customers (
    cid INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT
  )
")

## [1] 0

# Insert 3 rows of synthetic data into the 'customers' table
dbExecute(db, "INSERT INTO customers (cid, name, email) VALUES (1, 'Alice', 'alice@example.com')")

## [1] 1

dbExecute(db, "INSERT INTO customers (cid, name, email) VALUES (2, 'Bob', 'bob@example.com')")

## [1] 1

dbExecute(db, "INSERT INTO customers (cid, name, email) VALUES (3, 'Charlie', 'charlie@example.com')")

## [1] 1

# 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)

Summary

Now that you have completed this primer, consider Lesson 6.301 - Working with Databases in R.

---

Files & Resources

All Files for Lesson 6.300

---

See Also

• 70.801 - The SQLite Database
• 6.104 - Quick Guide to R For Programmers
• 6.301 - Working with Databases in R

References

SQLite CREATE TABLE SQLite INSERT

Errata

Let us know.