dbc_init: Create functions for accessing a remote database

In dgrtwo/dbcooper: Access Databases Through Autogenerated Functions

Create functions for accessing a remote database

Description

Create and assign functions that make accessing a database easy. These include tbl_ functions for each table in the database, as well as query_[id] and execute_[id] functions for querying and executing SQL in the database.

Usage

dbc_init(con, con_id, env = parent.frame(), ...)

## Default S3 method:
dbc_init(
  con,
  con_id,
  env = parent.frame(),
  tables = NULL,
  table_prefix = NULL,
  table_formatter = snakecase::to_snake_case,
  table_post = identity,
  ...
)

## S3 method for class 'src_sql'
dbc_init(con, con_id, env = parent.frame(), ...)

Arguments

con	A DBI-compliant database connection object, or a src_dbi.
con_id	A short string that identifies the database. This is used to create functions query_, tbl_ and execute_ with appropriate names, as well as to cache the connection globally.
env	Environment in which to create the table accessors, such as the global environment or a package namespace.
...	Arguments passed on to dbc_init.default.
tables	Optionally, a vector of tables. Useful if dbcooper's table listing functions don't work for a database, or if you want to use only a subset of tables.
table_prefix	Optionally, a prefix to append to each table, usually a schema.
table_formatter	Optionally, a function to clean the table name before turning it into a function name, such as removing prefixes. By default, to_snake_case.
table_post	Optionally, post-processing to perform on each table before returning it.

Examples

library(dplyr)
library(dbplyr)

# Initialize based on a SQL src or connection object
src <- lahman_sqlite()
dbc_init(src, "lahman")

## Tables

# Access each table using autocompleted functions
lahman_batting()

# Can also pass the name of a table as a string to lahman_tbl
lahman_tbl("Pitching")

# Pass no argument to get a vector of all the tables
lahman_list()

# Run a SQL query
lahman_query("SELECT COUNT(*) FROM Managers")

# Execute queries that change the database
lahman_execute("CREATE TABLE Players AS
  SELECT playerID, sum(AB) as AB FROM Batting GROUP BY playerID"
)

lahman_tbl("Players")

lahman_execute("DROP TABLE Players")

dgrtwo/dbcooper documentation built on Nov. 18, 2023, 6:24 p.m.