Setting up connection (in R)

This chunk of code will take the in-memory data and allow this data to be queried exactly like a SQL database.

library(dplyr)
library(DBI)
library(tidyverse)
# Create an ephemeral in-memory RSQLite database
con = dbConnect(RSQLite::SQLite(), ":memory:")
# tbl = tibble(
#   numbers = 1:5, 
#   letters = letters[1:5])
dbListTables(con)
## character(0)
# dbWriteTable(con, "tbl", tbl)
# dbListTables(con)
# dbListFields(con, "tbl")

Creating data/tables with data

R

tbl = tibble(
  numbers = 1:3,
  letters = LETTERS[1:3])

print(tbl)
## # A tibble: 3 × 2
##   numbers letters
##     <int> <chr>  
## 1       1 A      
## 2       2 B      
## 3       3 C

SQL

CREATE TABLE tbl (
  numbers INT,
  letters VARCHAR(10)
);
INSERT INTO tbl
 (numbers, letters)
VALUES
 (1, "A"),
 (2, "B"),
 (3, "C");
SELECT *
FROM tbl;
3 records
numbers letters
1 A
2 B
3 C

Inserting

R

tbl = tbl %>% rows_insert(tibble(numbers = 4, letters = "D"))
## Matching, by = "numbers"
print(tbl)
## # A tibble: 4 × 2
##   numbers letters
##     <int> <chr>  
## 1       1 A      
## 2       2 B      
## 3       3 C      
## 4       4 D

SQL

INSERT INTO tbl
 (numbers, letters)
VALUES
 (4, "D");
SELECT *
FROM tbl;
4 records
numbers letters
1 A
2 B
3 C
4 D

Updating rows

R

tbl = tbl %>% rows_update(tibble(numbers = 2:3, letters = "Unknown"))
## Matching, by = "numbers"
print(tbl)
## # A tibble: 4 × 2
##   numbers letters
##     <int> <chr>  
## 1       1 A      
## 2       2 Unknown
## 3       3 Unknown
## 4       4 D

SQL

UPDATE tbl
SET 
  letters = 'Unknown'
WHERE 
  numbers IN (2, 3)
SELECT *
FROM tbl;
4 records
numbers letters
1 A
2 Unknown
3 Unknown
4 D

Deleting rows

R

tbl = tbl %>% 
  filter(letters != "Unknown")

print(tbl)
## # A tibble: 2 × 2
##   numbers letters
##     <int> <chr>  
## 1       1 A      
## 2       4 D

SQL

DELETE FROM tbl WHERE letters = 'Unknown'
SELECT *
FROM tbl;
2 records
numbers letters
1 A
4 D