Get a table from a database

Source: R/tbl_from_db.R

If your target table is in a database, the db_tbl() function is a handy way of accessing it. This function simplifies the process of getting a tbl_dbi object, which usually involves a combination of building a connection to a database and using the dplyr::tbl() function with the connection and the table name (or a reference to a table in a schema). A better option is to use this function as the read_fn parameter in create_agent() and create_informant(). This can be done by using a leading ~ (e.g,. read_fn = ~db_tbl(...)).

The username and password are supplied though environment variables. If desired, these can be supplied directly by enclosing those values in I().

db_tbl(db, dbname, table, user, password, host = NULL, port = NULL)

Arguments

db

Either an appropriate driver function (e.g., RPostgres::Postgres()) or a shorthand name for the database type. Valid names are: "postgresql", "postgres", or "pgsql" (PostgreSQL, using the RPostgres::Postgres() driver function); "mysql" (MySQL, using RMySQL::MySQL()); "maria" or "mariadb" (MariaDB, using RMariaDB::MariaDB()); "duckdb" (DuckDB, using duckdb::duckdb()); and "sqlite" (SQLite, using RSQLite::SQLite()).
dbname

The database name.
table

The name of the table, or, a reference to a table in a schema (two-element vector with the names of schema and table). Alternatively, this can be supplied as a data table to copy into an in-memory database connection. This only works if: (1) the db is either "sqlite" or "duckdb", (2) the dbname was chosen as ":memory:", and (3) the data_tbl is a data frame or a tibble object.
user, password

The environment variables used to access the username and password for the database.
host, port

The database host and optional port number.

Value

A tbl_dbi object.

Function ID

1-6

See also

Other Planning and Prep: action_levels(), create_agent(), create_informant(), file_tbl(), scan_data(), validate_rmd()