dbplyr

Using Databases using R.

See the RSQLite website.

library(tidyverse)
library(dplyr)
library(dbplyr)
library(RSQLite)
library(DBI)
library(beepr)
library(nycflights13)

In memory databases

mtcars data in an sqlite db

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

Error in dbConnect(RSQLite::SQLite(), ":memory:") : 
  could not find function "dbConnect"

iris

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

dbListTables(con2)

dbWriteTable(con2, "iris", iris)
dbListTables(con2)
dbListFields(con2, "iris")
dbReadTable(con2, "iris")
res <- dbSendQuery(con2, "SELECT * FROM iris")
dbFetch(res)
dbClearResult(res)
dbDisconnect(con2)

flights

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

dbListTables(con3)

copy_to(con3, nycflights13::flights, "flights",
  temporary = FALSE, 
  indexes = list(
    c("year", "month", "day"), 
    "carrier", 
    "tailnum",
    "dest"
  )
)

dbListTables(con3)
dbListFields(con3, "flights")
dbReadTable(con3, "flights")
res <- dbSendQuery(con3, "SELECT * FROM flights WHERE day=1")
dbFetch(res)
dbClearResult(res)
dbDisconnect(con3)

Using dplyr.

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

dbListTables(con4)

copy_to(con4, nycflights13::flights, "flights",
  temporary = FALSE, 
  indexes = list(
    c("year", "month", "day"), 
    "carrier", 
    "tailnum",
    "dest"
  )
)

flights_db <- tbl(con4, "flights")

flights_db

flights_db %>% select(year:day, dep_delay, arr_delay)

flights_db %>% filter(dep_delay > 240)

flights_db %>% 
  group_by(dest) %>%
  summarise(delay = mean(dep_time))

tailnum_delay_db <- flights_db %>% 
  group_by(tailnum) %>%
  summarise(
    delay = mean(arr_delay),
    n = n()
  ) %>% 
  arrange(desc(delay)) %>%
  filter(n > 100)

tailnum_delay_db

tailnum_delay_db %>% show_query()

dbDisconnect(con4)

nycflights13 in a sqlite db saved to your harddrive in the data directory Write the nycflights to a db.

my_db_file <- "data/nycflights13_new.sqlite"
my_db <- src_sqlite(my_db_file, create = TRUE)

con <- dbConnect(RSQLite::SQLite(), "data/nycflights13_new.sqlite")

dbListTables(con)

dbWriteTable(con, "flights", nycflights13::flights)
dbWriteTable(con, "planes", nycflights13::planes)
dbWriteTable(con, "airlines", nycflights13::airlines)
dbWriteTable(con, "airports", nycflights13::airports)
dbWriteTable(con, "weather", nycflights13::weather)


dbListTables(con)
dbListFields(con, "flights")
dbReadTable(con, "flights")
res <- dbSendQuery(con, "SELECT * FROM flights WHERE origin = 'JFK'")
dbFetch(res)
dbClearResult(res)
dbDisconnect(con)

mtcars

my_db_file <- "data/mtcars.sqlite"
my_db <- src_sqlite(my_db_file, create = TRUE)

con <- dbConnect(RSQLite::SQLite(), "data/mtcars.sqlite")

dbListTables(con)

dbWriteTable(con, "mtcars", mtcars)
dbListTables(con)
dbListFields(con, "mtcars")
dbReadTable(con, "mtcars")
res <- dbSendQuery(con, "SELECT * FROM mtcars WHERE cyl = 4")
dbFetch(res)
dbClearResult(res)
dbDisconnect(con)

iris

my_db_file <- "data/iris.sqlite"
my_db <- src_sqlite(my_db_file, create = TRUE)

con <- dbConnect(RSQLite::SQLite(), "data/iris.sqlite")

dbListTables(con)

dbWriteTable(con, "iris", iris)
dbListTables(con)
dbListFields(con, "iris")
dbReadTable(con, "iris")
res <- dbSendQuery(con, " SELECT * FROM iris WHERE Species = 'setosa' ")
dbFetch(res)
dbClearResult(res)
dbDisconnect(con)

gapmider

library(gapminder)

my_db_file <- "data/gapminder.sqlite"
my_db <- src_sqlite(my_db_file, create = TRUE)

con <- dbConnect(RSQLite::SQLite(), "data/gapminder.sqlite")

dbListTables(con)

dbWriteTable(con, "gapminder", gapminder)
dbListTables(con)
dbListFields(con, "gapminder")
dbReadTable(con, "gapminder")
res <- dbSendQuery(con, " SELECT * FROM gapminder WHERE Country = 'United States' ")
dbFetch(res)
dbClearResult(res)
dbDisconnect(con)

All three tables in one database

my_db_file <- "data/all_4.sqlite"
my_db <- src_sqlite(my_db_file, create = TRUE)

con <- dbConnect(RSQLite::SQLite(), "data/all_4.sqlite")

dbListTables(con)

dbWriteTable(con, "mtcars", mtcars)
dbWriteTable(con, "iris", iris)
dbWriteTable(con, "gapmider", gapminder)
dbWriteTable(con, "flights", flights)

dbListTables(con)

dbListFields(con, "iris")
dbListFields(con, "flights")

dbDisconnect(con)