R & SQLite

Introduction

Connection

Introduction


Connection Summary

Introduction


Instruction


summary(con)
summary(con)

List Tables

Introduction


Instruction


dbListTables(con)
dbListTables(con)

List Fields

Introduction


Instruction


DBI::dbListFields(con, "ecom")
DBI::dbListFields(con, "ecom")

Querying Data

Introduction


  • dbReadTable(): read entire table
  • dbGetQuery(): read few rows
  • dbSendQuery() & dbFetch(): read data in batches

Query Entire Table

Introduction


Instruction


DBI::dbReadTable(con, 'ecom')
DBI::dbReadTable(con, 'ecom')

Query Few Rows

Introduction


Instruction


DBI::dbGetQuery(con, "select * from ecom limit 10")
DBI::dbGetQuery(con, "select * from ecom limit 10")

Read Data in Batches

Introduction


Instruction


query <- DBI::dbSendQuery(con, 'select * from ecom')
result <- DBI::dbFetch(query, n = 30)
result
query <- DBI::dbSendQuery(con, 'select * from ecom')
result <- DBI::dbFetch(query, n = 30)
result

Query Status

Introduction


Instruction


DBI::dbHasCompleted(query)
DBI::dbHasCompleted(query)

Query Info

Introduction


Instruction


DBI::dbGetInfo(query)
DBI::dbGetInfo(query)

Latest Query

Introduction


Instruction


DBI::dbGetStatement(query)
DBI::dbGetStatement(query)

Rows Fetched

Introduction


Instruction


DBI::dbGetRowCount(query)
DBI::dbGetRowCount(query)

Rows Affected

Introduction


Instruction


DBI::dbGetRowsAffected(query)
DBI::dbGetRowsAffected(query)

Column Info

Introduction


Instruction


DBI::dbColumnInfo(query)
DBI::dbColumnInfo(query)

Clear Results

Introduction


Instruction


DBI::dbClearResult(query)
DBI::dbClearResult(query)

Introduction

Create Table

Introduction


Instructions


x <- 1:10
y <- letters[1:10]
trial <- tibble::tibble(x, y)
DBI::dbWriteTable(con, "trial", trial)
x <- 1:10
y <- letters[1:10]
trial <- tibble::tibble(x, y)
DBI::dbWriteTable(con, "trial", trial)

Check if table is created in database

Instructions


DBI::dbListTables(con)
DBI::dbExistsTable(con, "trial")
DBI::dbListTables(con)
DBI::dbExistsTable(con, "trial")

Query sample data from table

Instructions


DBI::dbGetQuery(con, "select * from trial limit 5")
DBI::dbGetQuery(con, "select * from trial limit 5")

Overwrite Table

Introduction


Instructions


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

Query sample data from table to see if it has been overwritten

Instructions


DBI::dbGetQuery(con, "select * from trial limit 5")
DBI::dbGetQuery(con, "select * from trial limit 5")

Append Table

Introduction


Instructions


DBI::sqlAppendTable(con, "ecom", head(ecom))
DBI::sqlAppendTable(con, "ecom", head(ecom))

Append Data

Introduction


Instructions


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

Query sample data from table to see if new data is appended

Instructions


DBI::dbReadTable(con, "trial")
DBI::dbReadTable(con, "trial")

Insert Rows - Part 1

Introduction


Instructions


DBI::dbExecute(con,
  "INSERT into trial (x, y) VALUES (32, 'c'), (45, 'k'), (61, 'h')"
)
DBI::dbExecute(con,
  "INSERT into trial (x, y) VALUES (32, 'c'), (45, 'k'), (61, 'h')"
)

Insert Rows - Part 2

Introduction


Instructions


DBI::dbSendStatement(con,
  "INSERT into trial (x, y) VALUES (25, 'm'), (54, 'l'), (16, 'y')"
)
DBI::dbSendStatement(con,
  "INSERT into trial (x, y) VALUES (25, 'm'), (54, 'l'), (16, 'y')"
)

Data Type

Introduction


Instructions


DBI::dbDataType(RSQLite::SQLite(), "a")
DBI::dbDataType(RSQLite::SQLite(), 1:5)
DBI::dbDataType(RSQLite::SQLite(), 1.5)

Remove Table

Introduction


Instructions


DBI::dbRemoveTable(con, "trial")
DBI::dbRemoveTable(con, "trial")

Check if table has been removed

Instructions


DBI::dbListTables(con)
DBI::dbListTables(con)

Generate Query

Introduction


Instructions


DBI::sqlCreateTable(con, "new", c(x = "integer", y = "text"))
DBI::sqlCreateTable(con, "new", c(x = "integer", y = "text"))

Close Connection

Introduction


Instructions


DBI::dbDisconnect(con)
DBI::dbDisconnect(con)