8 min read

Quick Guide: R & SQLite

2018/11/27

Introduction

This is the first post in the series R & Databases. You can find the links to the other two posts of this series below:

In this post, we will learn to:

  • connect to a SQLite database from R
  • display database information
  • list tables in the database
  • query data
    • read entire table
    • read few rows
    • read data in batches
  • create table in database
  • overwrite table in database
  • append data to table in database
  • remove table from database
  • generate SQL query
  • close database connection

Libraries, Code & Data

We will use the following libraries in this post:

All the data sets used in this post can be found here and code can be downloaded from here.

Connection

The first step is to connect to a database. In this post, we will connect to an in memory SQLite databse using dbConnect().

con <- dbConnect(RSQLite::SQLite(), ":memory:")

Connection Summary

We can get the more information about the connection using summary().

summary(con)
##           Length            Class             Mode 
##                1 SQLiteConnection               S4

List Tables

Now that we are connected to a database, let us list all the tables present in it using dbListTables().

dbListTables(con)
## [1] "ecom"         "sqlite_stat1" "sqlite_stat4"

List Fields

Time to explore the ecom table in the database. Use dbListFields() to list all the fields in the table.

dbListFields(con, "ecom")
## [1] "referrer" "device"   "bouncers" "n_visit"  "n_pages"  "duration"

Querying Data

The main objectives of connecting to a database are to:

  • query data from the tables already present
  • create new tables
  • overwrite existing tables
  • delete existing tables

Let us begin with querying data. We can do this in the following ways:

  • read an entire table at once
  • read few rows from a table
  • read data in batches

Entire Table

We can read an entire table from a database using dbReadTable().

dbReadTable(con, 'ecom')
##    referrer device bouncers n_visit n_pages duration
## 1    google laptop        1      10       1      693
## 2     yahoo tablet        1       9       1      459
## 3    direct laptop        1       0       1      996
## 4      bing tablet        0       3      18      468
## 5     yahoo mobile        1       9       1      955
## 6     yahoo laptop        0       5       5      135
## 7     yahoo mobile        1      10       1       75
## 8    direct mobile        1      10       1      908
## 9      bing mobile        0       3      19      209
## 10   google mobile        1       6       1      208
## 11   direct laptop        1       9       1      738
## 12   direct tablet        0       6      12      132
## 13   direct mobile        0       9      14      406
## 14    yahoo tablet        0       5       8       80
## 15    yahoo mobile        0       7       1       19
## 16     bing laptop        1       1       1      995
## 17     bing tablet        0       5      16      368
## 18   google tablet        1       7       1      406
## 19   social tablet        0       7      10      290
## 20   social tablet        0       2       1       28

In some cases, we may not need the entire table but only a specific number of rows. Use dbGetQuery() and supply a SQL statement specifying the number of rows of data to be read from the table. In the below example, we read ten rows of data from the ecom table.

Few Rows

dbGetQuery(con, "select * from ecom limit 10")
##    referrer device bouncers n_visit n_pages duration
## 1    google laptop        1      10       1      693
## 2     yahoo tablet        1       9       1      459
## 3    direct laptop        1       0       1      996
## 4      bing tablet        0       3      18      468
## 5     yahoo mobile        1       9       1      955
## 6     yahoo laptop        0       5       5      135
## 7     yahoo mobile        1      10       1       75
## 8    direct mobile        1      10       1      908
## 9      bing mobile        0       3      19      209
## 10   google mobile        1       6       1      208

In case of very large table, we can read data in batches using dbSendQuery() and dbFetch(). We can mention the number of rows of data to be read while fetching the data using the query generated by dbGetQuery().

Read Data in Batches

query <- dbSendQuery(con, 'select * from ecom')
result <- dbFetch(query, n = 15)
result
##    referrer device bouncers n_visit n_pages duration
## 1    google laptop        1      10       1      693
## 2     yahoo tablet        1       9       1      459
## 3    direct laptop        1       0       1      996
## 4      bing tablet        0       3      18      468
## 5     yahoo mobile        1       9       1      955
## 6     yahoo laptop        0       5       5      135
## 7     yahoo mobile        1      10       1       75
## 8    direct mobile        1      10       1      908
## 9      bing mobile        0       3      19      209
## 10   google mobile        1       6       1      208
## 11   direct laptop        1       9       1      738
## 12   direct tablet        0       6      12      132
## 13   direct mobile        0       9      14      406
## 14    yahoo tablet        0       5       8       80
## 15    yahoo mobile        0       7       1       19

Query

Query Status

To know the status of a query, use dbHasCompleted(). It is very useful in cases of queries that might take a long time to complete.

dbHasCompleted(query)
## [1] FALSE

Query Info

dbGetInfo() will return the following:

  • the sql staement
  • number of rows fetched
  • number of rows modified/affected
  • status of the query
dbGetInfo(query)
## $statement
## [1] "select * from ecom"
## 
## $row.count
## [1] 15
## 
## $rows.affected
## [1] 0
## 
## $has.completed
## [1] FALSE

Latest Query

To get the latest query, use dbGetStatement().

dbGetStatement(query)
## [1] "select * from ecom"

Rows Fetched

To check the number of rows of data returned by a query, use dbGetRowCount().

dbGetRowCount(query)
## [1] 15

Rows Affected

To know the number of rows modified or affected in the table, use dbGetRowsAffected().

dbGetRowsAffected(query)
## [1] 0

Column Info

To know the name of the columns and their data types, use dbColumnInfo().

dbColumnInfo(query)
##       name      type
## 1 referrer character
## 2   device character
## 3 bouncers   integer
## 4  n_visit    double
## 5  n_pages    double
## 6 duration    double

Create Table

So far we have explored querying data from an existing table. Now, let us turn our attention to creating new tables in the database.

Introduction

To create a new table, use dbWriteTable(). It takes the following 3 arguments:

  • connection name
  • name of the new table
  • data for the new table
x <- 1:10
y <- letters[1:10]
trial <- tibble::tibble(x, y)
dbWriteTable(con, "trial", trial)
## Warning: Closing open result set, pending rows

Let us check if the new table has been created.

dbListTables(con)
## [1] "ecom"         "sqlite_stat1" "sqlite_stat4" "trial"
dbExistsTable(con, "trial")
## [1] TRUE

Let us query data from the new table.

dbGetQuery(con, "select * from trial limit 5")
##   x y
## 1 1 a
## 2 2 b
## 3 3 c
## 4 4 d
## 5 5 e

Overwrite Table

In some cases, you may want to overwrite the data in an existing table. Use the overwrite argument in dbWriteTable() and set it to TRUE.

x <- sample(100, 10)
y <- letters[11:20]
trial2 <- tibble::tibble(x, y)
dbWriteTable(con, "trial", trial2, overwrite = TRUE)

Let us see if the trial table has been overwritten.

dbGetQuery(con, "select * from trial limit 5")
##    x y
## 1 48 k
## 2 58 l
## 3 85 m
## 4 99 n
## 5 78 o

Append Data

You can append data to an existing table by setting the append argument in dbWriteTable() to TRUE.

x <- sample(100, 10)
y <- letters[5:14]
trial3 <- tibble::tibble(x, y)
dbWriteTable(con, "trial", trial3, append = TRUE)

Let us quickly check if the new data has been appended to the trial table.

dbReadTable(con, "trial")
##     x y
## 1  48 k
## 2  58 l
## 3  85 m
## 4  99 n
## 5  78 o
## 6   9 p
## 7   6 q
## 8  59 r
## 9  11 s
## 10 38 t
## 11 39 e
## 12 69 f
## 13 43 g
## 14 71 h
## 15 99 i
## 16 56 j
## 17 45 k
## 18 81 l
## 19 93 m
## 20 47 n

We can also use sqlAppendTable() to append data to an existing table.

sqlAppendTable(con, "ecom", head(ecom))
## Warning: Do not rely on the default value of the row.names argument for
## sqlAppendTable(), it will change in the future.
## <SQL> INSERT INTO `ecom`
##   (`referrer`, `device`, `bouncers`, `n_visit`, `n_pages`, `duration`)
## VALUES
##   ('google', 'laptop', TRUE, 10, 1, 693),
##   ('yahoo', 'tablet', TRUE, 9, 1, 459),
##   ('direct', 'laptop', TRUE, 0, 1, 996),
##   ('bing', 'tablet', FALSE, 3, 18, 468),
##   ('yahoo', 'mobile', TRUE, 9, 1, 955),
##   ('yahoo', 'laptop', FALSE, 5, 5, 135)

Insert Rows

Introduction

We can insert new rows into existing tables using:

  • dbExecute()
  • dbSendStatement()

Both the function take 2 arguments:

  • connection name
  • sql statement
# use dbExecute
dbExecute(con,
  "INSERT into trial (x, y) VALUES (32, 'c'), (45, 'k'), (61, 'h')"
)
## [1] 3

# use dbSendStatement
dbSendStatement(con,
  "INSERT into trial (x, y) VALUES (25, 'm'), (54, 'l'), (16, 'y')"
)
## <SQLiteResult>
##   SQL  INSERT into trial (x, y) VALUES (25, 'm'), (54, 'l'), (16, 'y')
##   ROWS Fetched: 0 [complete]
##        Changed: 3

Remove Table

If you want to delete/remove a table from the database, use dbRemoveTable().

dbRemoveTable(con, "trial")
## Warning: Closing open result set, pending rows

SQLite Data Type

If you want to know the data type, use dbDataType().

dbDataType(RSQLite::SQLite(), "a")
## [1] "TEXT"
dbDataType(RSQLite::SQLite(), 1:5)
## [1] "INTEGER"
dbDataType(RSQLite::SQLite(), 1.5)
## [1] "REAL"

Close Connection

It is a good practice to close connection to a database when you no longer need to read/write data from/to it. Use dbDisconnect() to close the database connection.

dbDisconnect(con)