dabr: Database Management with R

Description

Provides functions to manage databases: select, update, insert, and delete records, list tables, backup tables as CSV files, and import CSV files as tables.

Author(s)

Maintainer: Roberto Villegas-Diaz r.villegas-diaz@outlook.com (ORCID)

Other contributors:

• SPECIAL Research Group @ University of Reading [copyright holder]

See Also

Useful links:

• https://github.com/special-uor/dabr/

• https://special-uor.github.io/dabr/

• https://research.reading.ac.uk/palaeoclimate/

• Report bugs at https://github.com/special-uor/dabr/issues/

Pipe operator

Description

See magrittr::%>% for details.

Usage

lhs %>% rhs

Combine attributes

Description

Combine attributes from a vector of strings.

Usage

attributes(...)

Arguments

Value

Combined string with all the attributes.

Examples

dabr::attributes("A", "B", "C")
dabr::attributes(c("A", "B", "C"))
dabr::attributes(c("A", "B", "C"), "D", "E", "F")

Close connection to database

Description

Close connection to database

Usage

close_conn(conn, ...)
## S3 method for class 'MariaDBConnection'
close_conn(conn, ...)
## Default S3 method:
close_conn(conn, ...)

Arguments

See Also

Other DB functions: delete(), insert(), list_tables(), open_conn_mysql(), select_all(), select(), update()

Examples

## Not run: 
conn <- open_conn_mysql("sys", "root")
close_conn(conn)
## End(Not run)

Execute DELETE query

Description

Execute DELETE query

Usage

delete(conn, ...)
## S3 method for class 'MariaDBConnection'
delete(conn, ..., quiet = FALSE)

Arguments

See Also

Other DB functions: close_conn(), insert(), list_tables(), open_conn_mysql(), select_all(), select(), update()

Examples

## Not run: 
conn <- open_conn_mysql("sys", "root")
out <- delete(conn, "DELETE sys_config SET value = 1")
close_conn(conn)
## End(Not run)

Get attributes of a table

Description

Get attributes of a table

Usage

get_attr(conn, ...)
## S3 method for class 'MariaDBConnection'
get_attr(conn, name, ...)

Arguments

Value

List of attributes for table name.

Execute INSERT query

Description

Execute INSERT query

Usage

insert(conn, ...)
## S3 method for class 'MariaDBConnection'
insert(conn, ..., quiet = FALSE)

Arguments

See Also

Other DB functions: close_conn(), delete(), list_tables(), open_conn_mysql(), select_all(), select(), update()

Examples

## Not run: 
conn <- open_conn_mysql("sys", "root")
query <- paste0(
 "INSERT INTO sys_config (variable, value, set_time, set_by) VALUES ",
 "('test_var', 999, '", Sys.time(), "', NULL)"
)
out <- insert(conn, query)
close_conn(conn)
## End(Not run)

Verify connection

Description

Verify if connection object is still valid, is connected to the database server.

Usage

is.connected(conn, ...)
## S3 method for class 'MariaDBConnection'
is.connected(conn, ...)

Arguments

Value

Connection status.

List tables

Description

List tables

Usage

list_tables(conn, ...)
## S3 method for class 'MariaDBConnection'
list_tables(conn, quiet = FALSE, attr = TRUE, ...)

Arguments

Value

If quiet = TRUE returns a list with the tables' names. If attr = TRUE includes each attribute of the tables.

See Also

Other DB functions: close_conn(), delete(), insert(), open_conn_mysql(), select_all(), select(), update()

Connect to database

Description

Uses RMariaDB to open a connection to a MySQL database.

Usage

open_conn_mysql(
 dbname,
 user = "root",
 password = NULL,
 host = "127.0.0.1",
 port = 3306
)

Arguments

Value

MariaDBConnection connection object.

See Also

Other DB functions: close_conn(), delete(), insert(), list_tables(), select_all(), select(), update()

Examples

## Not run: 
conn <- open_conn_mysql("sys")
## End(Not run)

Add single quotes

Description

Add single quotes to string.

Usage

quote(str)

Arguments

Value

String surrounded by single quotes

Examples

dabr::quote("A")
dabr::quote("l'A")

Execute SELECT query

Description

Execute SELECT query

Usage

select(conn, ...)
## S3 method for class 'MariaDBConnection'
select(conn, ..., quiet = FALSE)

Arguments

Value

Data frame containing the selected records.

See Also

Other DB functions: close_conn(), delete(), insert(), list_tables(), open_conn_mysql(), select_all(), update()

Examples

## Not run: 
conn <- open_conn_mysql("sys", "root")
out <- select(conn, "SELECT variable, value FROM sys_config")
close_conn(conn)
## End(Not run)

Select all the records

Description

Select all the records inside a particular table, use the table parameter.

Usage

select_all(conn, ...)
## S3 method for class 'MariaDBConnection'
select_all(conn, table, quiet = FALSE, ...)

Arguments

Value

Data frame with records.

See Also

Other DB functions: close_conn(), delete(), insert(), list_tables(), open_conn_mysql(), select(), update()

Examples

## Not run: 
conn <- dabr::open_conn_mysql("sys", "root")
out <- dabr::select_all(conn, "sys_config")
dabr::close_conn(conn)
## End(Not run)

Execute UPDATE query

Description

Execute UPDATE query

Usage

update(conn, ...)
## S3 method for class 'MariaDBConnection'
update(conn, ..., quiet = FALSE)

Arguments

See Also

Other DB functions: close_conn(), delete(), insert(), list_tables(), open_conn_mysql(), select_all(), select()

Examples

## Not run: 
conn <- open_conn_mysql("sys", "root")
out <- update(conn, "UPDATE sys_config SET value = 1")
close_conn(conn)
## End(Not run)