22  R: SQLite DB

22.1 Overview

  • In this chapter, we will manage SQLite database from the R environment

  • Create an SQLite database, and disconnect the connection

  • Set up a connection to read an SQLite database

  • Explores the tables in the database

  • Create and remove tables from the database

  • Execute DML commands on the records of a table: INSERT, UPDATE, DELETE

22.2 Create SQLite RDBMS

  • We will create an SQLite RDBMS containing four tables: tblClass, tblTeacher, tblStudent, tblMarks

  • Choose one of the following three options to create an SQLite RDBMS.

22.2.1 Using SQLite

  • Go to SQLite website, download and install the program for your system.

  • Create the SQLite database on the SQLite environment

SQLite Website

22.2.2 Using DB Browser

  • A nice user interface to create SQLite database is DB Browser. It has a portable version for Windows (no installation required).

  • Run DB Browser and create the SQLite database

SQLite Website

22.2.3 Using RSQLite

  • The easiest way to create an SQLite database from R is through RSQLite library

  • Assuming we have four tables (data.frame) in the R environment, we can create an SQLite database of these four tables.

  • Create the database and disconnect.

Code
# Create a database
conn = dbConnect(RSQLite::SQLite(), "./data/School_DB.sqlite")

# Load tables onto database
dbWriteTable(conn, name = "tblClass", tblClass)
dbWriteTable(conn, name = "tblTeacher", tblTeacher)
dbWriteTable(conn, name = "tblStudent", tblStudent)
dbWriteTable(conn, name = "tblMarks", tblMarks)

# List tables
dbListTables(conn)

# Disconnect
dbDisconnect(conn)

# Clean the environment
rm(list = ls())

22.3 Connect SQLite DB

  • We will use duckdb with DBI to perform the connection and DQL and DML queries

  • In general, dbExecute executes a query without an output

  • the dbGetQuery executes a query with an output table

Code
# Connect to the SQLite database (it will create the file if it doesn't exist)
conn = duckdb::dbConnect(RSQLite::SQLite(), host = NULL, port = NULL, username = NULL,
    password = NULL, dbname = "./data/School_DB.sqlite")

22.4 Explore Tables

Code
# List tables
duckdb::dbListTables(conn)


# List fields of a table
duckdb::dbListFields(conn, name = "tblStudent")


# Read database

sSQL = "SELECT * FROM tblStudent"
dbGetQuery(conn, sSQL)

sSQL = "SELECT * FROM tblClass;"
dbGetQuery(conn, sSQL)

22.5 Add Table

Code
# Write and Remove a table Also: CREATE DATABASE, CREATE TABLE & DROP TABLE in
# SQL

tblDummy = data.frame(ID = 1:5, Name = LETTERS[1:5])

dbWriteTable(conn = conn, name = "tblDummy", value = tblDummy)

dbListTables(conn)

22.6 Remove Table

Code
# Write and Remove a table Also: CREATE DATABASE, CREATE TABLE & DROP TABLE in
# SQL

dbListTables(conn)

dbRemoveTable(conn, name = "tblDummy")
dbListTables(conn)

dbExistsTable(conn, name = "tblDummy")
dbExistsTable(conn, name = "tblClass")

22.7 Insert Record

Code
# Insert the data in tblClass. This will also output a 1

sSQL = "INSERT INTO tblClass VALUES (7, 'BIO1', 4)"
DBI::dbExecute(conn, sSQL)

# Check the modified table
DBI::dbGetQuery(conn, "SELECT * FROM tblClass")

22.8 Update Record

Code
# UPDATE Record

sSQL = "UPDATE tblClass SET Subject = 'ENG2' WHERE ClassID = 7"
DBI::dbExecute(conn, sSQL)
DBI::dbGetQuery(conn, "SELECT * FROM tblClass")

22.9 Delete Record

Code
# DELETE Record

sSQL = "DELETE FROM tblClass WHERE ClassID = 7"
DBI::dbExecute(conn, sSQL)
DBI::dbGetQuery(conn, "SELECT * FROM tblClass")

22.10 Disconnect

Code
# Disconnect

dbDisconnect(conn, shutdown = TRUE)