Skip to contents

A convenience function that returns a list of tbl_dbi objects from a DBIConnection object, named as per the database table names.

Usage

db_tables_to_list(conn)

Arguments

conn

A DBIConnection object, as returned by dbConnect.

Value

A named list of tbl_dbi objects

Details

See the introduction to dbplyr vignette for getting started with databases and dplyr.

Examples

# create an SQLite database in memory
con <- DBI::dbConnect(RSQLite::SQLite(), dbname = ":memory:")

# populate with tables
dplyr::copy_to(con, head(iris), "iris_head")
dplyr::copy_to(con, head(mtcars), "mtcars_head")

# create named list of tbl_dbi objects
db_tables <- db_tables_to_list(con)

# view tables
db_tables$iris_head
#> # Source:   table<`iris_head`> [6 x 5]
#> # Database: sqlite 3.45.2 [:memory:]
#>   Sepal.Length Sepal.Width Petal.Length Petal.Width Species
#>          <dbl>       <dbl>        <dbl>       <dbl> <chr>  
#> 1          5.1         3.5          1.4         0.2 setosa 
#> 2          4.9         3            1.4         0.2 setosa 
#> 3          4.7         3.2          1.3         0.2 setosa 
#> 4          4.6         3.1          1.5         0.2 setosa 
#> 5          5           3.6          1.4         0.2 setosa 
#> 6          5.4         3.9          1.7         0.4 setosa 
db_tables$mtcars_head
#> # Source:   table<`mtcars_head`> [6 x 11]
#> # Database: sqlite 3.45.2 [:memory:]
#>     mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb
#>   <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1  21       6   160   110  3.9   2.62  16.5     0     1     4     4
#> 2  21       6   160   110  3.9   2.88  17.0     0     1     4     4
#> 3  22.8     4   108    93  3.85  2.32  18.6     1     1     4     1
#> 4  21.4     6   258   110  3.08  3.22  19.4     1     0     3     1
#> 5  18.7     8   360   175  3.15  3.44  17.0     0     0     3     2
#> 6  18.1     6   225   105  2.76  3.46  20.2     1     0     3     1

# to import to R, use dplyr::collect()
dplyr::collect(db_tables$iris_head)
#> # A tibble: 6 × 5
#>   Sepal.Length Sepal.Width Petal.Length Petal.Width Species
#>          <dbl>       <dbl>        <dbl>       <dbl> <chr>  
#> 1          5.1         3.5          1.4         0.2 setosa 
#> 2          4.9         3            1.4         0.2 setosa 
#> 3          4.7         3.2          1.3         0.2 setosa 
#> 4          4.6         3.1          1.5         0.2 setosa 
#> 5          5           3.6          1.4         0.2 setosa 
#> 6          5.4         3.9          1.7         0.4 setosa