Using dplyr::tbl inside function

Question

I now there are various answers on using dplyr inside function but i tried all methods and neither worked.

Here is my simple function:

loadData_gfi_zse <- function(table_name = "mytable_name"){
  con <- DBI::dbConnect(RMySQL::MySQL(), 
                        host = "xxxx",
                        user = "xxxx",
                        password = "xxxx",
                        dbname = "xxxxs")
  table_name <- substitute(table_name)
  df <- tbl(con, lazyeval::interp(table_name))
  dbDisconnect(con)
  df_clean
}

I tried wit rlang::!! and other methods but neither work. This should be straightforward, but I can't get it work.

Please share sample of your data using `dput()` (not `str` or `head` or picture/screenshot) so others can help. See more here https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example?rq=1 — Tung, Jul 06 '18 at 13:43
Have you tried `rlang::sym(table_name)` then unquote it inside `tbl` using `!!`? — Tung, Jul 06 '18 at 13:44
this return an error: ``!!` can only be used within a quasiquoted argument ` — Mislav, Jul 06 '18 at 13:54
data comes from connection to database. This is the point. Maybe it would work if I just use datafram, but that is not my example. — Mislav, Jul 06 '18 at 13:57

score 2 · Accepted Answer · answered Jul 13 '18 at 10:13

2

library(dplyr)

con <- DBI::dbConnect(RSQLite::SQLite(), dbname = "dummydb.sqlite")
DBI::dbWriteTable(con, "iris", head(iris))
DBI::dbDisconnect(con)

loadData_gfi_zse <- function(table_name = "mytable_name") {
  con <- DBI::dbConnect(RSQLite::SQLite(), dbname = "dummydb.sqlite")
  on.exit(DBI::dbDisconnect(con))
  collect(tbl(con, table_name))
}

loadData_gfi_zse(table_name = "iris")

# # A tibble: 6 x 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 

unlink("dummydb.sqlite")

answered Jul 13 '18 at 10:13

Aurèle

12,545
1
31
49

I accept the answer, but I recognize one shortcoming. Collect reads data into the memroy from the remote connection. But using just tible(con, tablename) doesn't do that. So it is not really the same. – Mislav Jul 17 '18 at 21:26
@Mislav You're absolutely right. Though if you want to return a `tbl(con, table_name)`, you'd need a different approach to connecting to the database (since you'd need to leave the connection open after the function returns) – Aurèle Jul 18 '18 at 08:37
do you have any idea hoe to do that? Lets's say I will do some other dplyr operations and return final table in the and. How to do that? – Mislav Jul 18 '18 at 08:40
It depends a lot on the context. If it's one long analysis script, you could open a connection at the beginning, pass the open connection as a function argument, instead of opening and closing a dedicated connection at each function call. An interesting approach for more robust setups is the `pool` package: https://github.com/rstudio/pool – Aurèle Jul 18 '18 at 09:06
It' simple to do everything outside a funciton, but have a problem when using inside function. Inconvenient for such popular package. – Mislav Jul 18 '18 at 09:29

Using dplyr::tbl inside function

1 Answers1