21.5 DBI basics

dbListTables(con)
## [1] "diamonds" "mpg"
con |> 
  dbReadTable("diamonds") |> 
  as_tibble()
## # A tibble: 53,940 × 10
##    carat cut       color clarity depth table price     x     y     z
##    <dbl> <fct>     <fct> <fct>   <dbl> <dbl> <int> <dbl> <dbl> <dbl>
##  1  0.23 Ideal     E     SI2      61.5    55   326  3.95  3.98  2.43
##  2  0.21 Premium   E     SI1      59.8    61   326  3.89  3.84  2.31
##  3  0.23 Good      E     VS1      56.9    65   327  4.05  4.07  2.31
##  4  0.29 Premium   I     VS2      62.4    58   334  4.2   4.23  2.63
##  5  0.31 Good      J     SI2      63.3    58   335  4.34  4.35  2.75
##  6  0.24 Very Good J     VVS2     62.8    57   336  3.94  3.96  2.48
##  7  0.24 Very Good I     VVS1     62.3    57   336  3.95  3.98  2.47
##  8  0.26 Very Good H     SI1      61.9    55   337  4.07  4.11  2.53
##  9  0.22 Fair      E     VS2      65.1    61   337  3.87  3.78  2.49
## 10  0.23 Very Good H     VS1      59.4    61   338  4     4.05  2.39
## # ℹ 53,930 more rows
  • SQL Syntax
sql <- "
  SELECT carat, cut, clarity, color, price 
  FROM diamonds 
  WHERE price > 15000
"
as_tibble(dbGetQuery(con, sql))
## # A tibble: 1,655 × 5
##    carat cut       clarity color price
##    <dbl> <fct>     <fct>   <fct> <int>
##  1  1.54 Premium   VS2     E     15002
##  2  1.19 Ideal     VVS1    F     15005
##  3  2.1  Premium   SI1     I     15007
##  4  1.69 Ideal     SI1     D     15011
##  5  1.5  Very Good VVS2    G     15013
##  6  1.73 Very Good VS1     G     15014
##  7  2.02 Premium   SI2     G     15014
##  8  2.05 Very Good SI2     F     15017
##  9  1.5  Very Good VS1     F     15022
## 10  1.82 Very Good SI1     G     15025
## # ℹ 1,645 more rows