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"

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

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 86 k
## 2 31 l
## 3 19 m
## 4 16 n
## 5 99 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   86 k
## 2   31 l
## 3   19 m
## 4   16 n
## 5   99 o
## 6   54 p
## 7   14 q
## 8    7 r
## 9   15 s
## 10  48 t
## 11  76 e
## 12  48 f
## 13 100 g
## 14  88 h
## 15  45 i
## 16  20 j
## 17  56 k
## 18  54 l
## 19  25 m
## 20  11 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)

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

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)