moodymudskipper / easydb Goto Github PK

View Code? Open in Web Editor NEW

0.0 3.0 1.0 12 KB

DBI and dplyr wrappers to write to DB, fetch and run data manipulation operations on server side.

R 100.00%

easydb's Introduction

easydb

DBI and dplyr wrappers to write to DB, fetch and run data manipulation operations on server side.

We define $.DBIConnection(), $<-.DBIConnection() and !.tbl_lazy() to be able to :

write in a data base with con$foo <- my_data_frame (where con is a connection built with DBI::dbConnect() or this package’s db_connect), short for DBI::dbWriteTable(con, "foo", my_data_frame)
build a lazy table with con$foo, short for dplyr::tbl(con, "foo"), so it can be used to do some server side data manipulation using dplyr
collect a lazy table into the R session with !my_lazy_table (or directly !con$foo), equivalent to dplyr::collect(my_lazy_table) or dplyr::collect(dplyr::tbl(con, "foo"))
We can access tables in a schema by using a . separator as we would in an SQL query. In that case con$my_schema.my_table is equivalent to dplyr::tbl(con, dbplyr::in_schema("my_schema", "my_table"))

Installation

Install with :

remotes::install_github("moodymudskipper/easydb")

connect

We offer a simple wrapper around DBI::dbConnect that makes it easy to use specs stored in a list and makes it easier to choose the relevant driver.

library(easydb)
# Used just as DBI::dbConnect()
db <- db_connect(RSQLite::SQLite(), path = ":memory:")
# Using shorthand
db <- db_connect("sqlite", path = ":memory:")
# Using a list of specs
specs <- list(drv = "sqlite", path = ":memory:")
db <- db_connect(specs)
# Or just, for this specific case, just use the defaults and it will create
# an SQLite database in memory
db <- db_connect()

Query

To write or fetch :

db$foo <- head(cars,2)
db$bar <- head(iris,2)
dplyr::db_list_tables(db)
#> [1] "bar"          "foo"          "sqlite_stat1" "sqlite_stat4"
db$foo
#> # Source:   table<foo> [?? x 2]
#> # Database: sqlite 3.22.0 []
#>   speed  dist
#>   <dbl> <dbl>
#> 1     4     2
#> 2     4    10

!db$foo
#> # A tibble: 2 x 2
#>   speed  dist
#>   <dbl> <dbl>
#> 1     4     2
#> 2     4    10

By default one can’t overwrite a table, it would need to be removed first, which you can do by calling db$table_to_delete <- NULL

db$foo <- tail(cars,2)
#> Error: Table `foo` exists in database, and both overwrite and append are FALSE
db$foo <- NULL
db$foo <- tail(cars,2)
!db$foo
#> # A tibble: 2 x 2
#>   speed  dist
#>   <dbl> <dbl>
#> 1    24   120
#> 2    25    85

We can allow overwriting by setting the option easydb.overwrite to TRUE.

options(easydb.overwrite = TRUE)
db$bar <- tail(iris,2)
!db$bar
#> # A tibble: 2 x 5
#>   Sepal.Length Sepal.Width Petal.Length Petal.Width Species  
#>          <dbl>       <dbl>        <dbl>       <dbl> <chr>    
#> 1          6.2         3.4          5.4         2.3 virginica
#> 2          5.9         3            5.1         1.8 virginica

We can attach or create a schema with the following syntax (here creating a schema named "aux" in a temporary file) :

db$aux. <- tempfile()

Then we can use a smilar syntax to what’s already been showed, by prefixing table names with the schema name and a dot.

db$aux.baz <- head(mtcars[1:4], 2)
!db$aux.baz
#> # A tibble: 2 x 4
#>     mpg   cyl  disp    hp
#>   <dbl> <dbl> <dbl> <dbl>
#> 1    21     6   160   110
#> 2    21     6   160   110

using `with()`

We provide a method with.DBIConnection() that can be used to do operators “in” the database. Just as base:::with.default() looks for the symbol of the expression in its first argument first, with.DBIConnection() searches for the tables first in the database using the standard with().

A difference is that one can assign inside of the database directly by using <- in the expression.

with(db,{
   # new table from r data
  baz <- head(mtcars,2)
  # create new table from dbms data, all computed on server side
  qux <- dplyr::union_all(foo,foo) 
})
db$baz
#> # Source:   table<baz> [?? x 11]
#> # Database: sqlite 3.22.0 []
#>     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
db$qux
#> # Source:   table<qux> [?? x 2]
#> # Database: sqlite 3.22.0 []
#>   speed  dist
#>   <dbl> <dbl>
#> 1    24   120
#> 2    25    85
#> 3    24   120
#> 4    25    85

easydb's People

Contributors

Watchers

Forkers

jfontestad

easydb's Issues

explicit error with `$` when table not found but partial matching would have worked

I don't want to support partial matching but partial matching is associated with $.

If column not found it would be nice to check if partial matching would have worked, and if so, have the error inform the user that partial matchng doesn't work and that if he means "this table" he should ask for it explicitly.

with.DBIConnection

list tables in DB
list symbols in expr : https://stackoverflow.com/questions/57670823/how-can-i-list-all-symbols-used-in-a-call
find intersection
substitute foo by con$foo when relevant
eval

The package is broken

because we used this : getFromNamespace("db_has_table.DBIConnection", "dbplyr")

I'm not sure why we used a non exported function there but there should be an exported alternative

describe()

quite convenient :

summary.DBIConnection <- function(object, ...){
  cat(crayon::cyan("OBJECT"), "\n",sep="")
  print(object)
  cat("\n", crayon::cyan("TABLES"), "\n",sep="")
  print(DBI::dbListTables(object))
  cat("\n","use `str(object)` for details", "\n", sep="")
  
  cat("\n", crayon::cyan("DEFAULT summary"), "\n",sep="")
  summary.default(object,...)
}

str.DBIConnection <- function(object, ...){
  cat(crayon::cyan("OBJECT"), "\n",sep="")
  print(object)
  cat("\n", crayon::cyan("TABLES"), "\n",sep="")
  tables <- DBI::dbListTables(object)
  tables <- setNames(tables, tables)
  for(tbl in tables){
    cat("\n", crayon::yellow(tbl), "\n", sep="")
    error <- try(tibble::glimpse(object[[tbl]]),silent = TRUE)
    if(inherits(error,"try-error")) cat(error,"\n")
  }
  
  cat("\n", crayon::cyan("DEFAULT str"), "\n",sep="")
  invisible(utils:::str.default(object,...))
}

view ?

Maybe would be good to have this here if not implemented in tibble. easydb already imports tibble.

view <- function(x, title = NULL, ...){
  UseMethod("view")
}

view.default <- tibble::view()

view.tbl_lazy <- function(x, title = NULL, n = 500, ...){
  if (is.null(title)) title <- rlang::expr_deparse(substitute(x)) # enexpr doesn't work in methods, see https://github.com/r-lib/rlang/issues/368
  View(dplyr::collect(head(x, n)), title, ...)
}

Maybe this could be used for connections instead of
#3

library(easydb)
library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union

## Try 1
drv <- DBI::dbDriver("Oracle")
con <- db_connect(
  drv,
  dbname = "my_db",
  username = "read",
  password = "read",
  schema = "my_schema"
)

con$my_tbl
#> Error in .oci.SendQuery(conn, statement, data = data, prefetch = prefetch,  : 
#>  ORA-00942: table or view does not exist

## Try 2
specs <- list(
  drv = DBI::dbDriver("Oracle"),
  dbname = "my_db",
  username = "read",
  password = "read"
)

con <- db_connect(specs)

my_tbl <- dbplyr::in_schema("my_schema", "my_tbl")

con$my_tbl
#> Error in .oci.SendQuery(conn, statement, data = data, prefetch = prefetch,  : 
#>  ORA-00942: table or view does not exist

# works
dplyr::tbl(con, my_tbl)

^{Created on 2019-09-02 by the reprex package (v0.2.1)}