Source for database backends

These functions have been deprecated; instead please use tbl() directly on an DBIConnection. See https://dbplyr.tidyverse.org/ for more details.

Usage

src_mysql(
  dbname,
  host = NULL,
  port = 0L,
  username = "root",
  password = "",
  ...
)

src_postgres(
  dbname = NULL,
  host = NULL,
  port = NULL,
  user = NULL,
  password = NULL,
  ...
)

src_sqlite(path, create = FALSE)

Arguments

dbname

Database name

host, port

Host name and port number of database

...

for the src, other arguments passed on to the underlying database connector, DBI::dbConnect(). For the tbl, included for compatibility with the generic, but otherwise ignored.

user, username, password

User name and password.

Generally, you should avoid saving username and password in your scripts as it is easy to accidentally expose valuable credentials. Instead, retrieve them from environment variables, or use database specific credential scores. For example, with MySQL you can set up my.cnf as described in RMySQL::MySQL().

path

Path to SQLite database. You can use the special path ":memory:" to create a temporary in memory database.

create

if FALSE, path must already exist. If TRUE, will create a new SQLite3 database at path if path does not exist and connect to the existing database if path does exist.

Value

An S3 object with class src_dbi, src_sql, src.

Examples

con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
copy_to(con, mtcars)

# To retrieve a single table from a source, use `tbl()`
mtcars <- con %>% tbl("mtcars")
mtcars
#> # Source:   table<`mtcars`> [?? x 11]
#> # Database: sqlite 3.46.0 [: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
#>  7  14.3     8  360    245  3.21  3.57  15.8     0     0     3     4
#>  8  24.4     4  147.    62  3.69  3.19  20       1     0     4     2
#>  9  22.8     4  141.    95  3.92  3.15  22.9     1     0     4     2
#> 10  19.2     6  168.   123  3.92  3.44  18.3     1     0     4     4
#> # ℹ more rows

# You can also use pass raw SQL if you want a more sophisticated query
con %>% tbl(sql("SELECT * FROM mtcars WHERE cyl == 8"))
#> # Source:   SQL [?? x 11]
#> # Database: sqlite 3.46.0 [:memory:]
#>      mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb
#>    <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#>  1  18.7     8  360    175  3.15  3.44  17.0     0     0     3     2
#>  2  14.3     8  360    245  3.21  3.57  15.8     0     0     3     4
#>  3  16.4     8  276.   180  3.07  4.07  17.4     0     0     3     3
#>  4  17.3     8  276.   180  3.07  3.73  17.6     0     0     3     3
#>  5  15.2     8  276.   180  3.07  3.78  18       0     0     3     3
#>  6  10.4     8  472    205  2.93  5.25  18.0     0     0     3     4
#>  7  10.4     8  460    215  3     5.42  17.8     0     0     3     4
#>  8  14.7     8  440    230  3.23  5.34  17.4     0     0     3     4
#>  9  15.5     8  318    150  2.76  3.52  16.9     0     0     3     2
#> 10  15.2     8  304    150  3.15  3.44  17.3     0     0     3     2
#> # ℹ more rows