mariadb-tables: Read and write MariaDB tables.

In r-dbi/RMariaDB: Database Interface and MariaDB Driver

Read and write MariaDB tables.

Description

These methods read or write entire tables from a MariaDB database.

Usage

## S4 method for signature 'MariaDBConnection'
dbAppendTable(conn, name, value, ..., row.names = NULL)

## S4 method for signature 'MariaDBConnection,character'
dbExistsTable(conn, name, ...)

## S4 method for signature 'MariaDBConnection'
dbListObjects(conn, prefix = NULL, ...)

## S4 method for signature 'MariaDBConnection'
dbListTables(conn, ...)

## S4 method for signature 'MariaDBConnection,character'
dbReadTable(conn, name, ..., row.names = FALSE, check.names = TRUE)

## S4 method for signature 'MariaDBConnection,character'
dbRemoveTable(conn, name, ..., temporary = FALSE, fail_if_missing = TRUE)

## S4 method for signature 'MariaDBConnection,character,character'
dbWriteTable(
  conn,
  name,
  value,
  field.types = NULL,
  overwrite = FALSE,
  append = FALSE,
  header = TRUE,
  row.names = FALSE,
  nrows = 50,
  sep = ",",
  eol = "\n",
  skip = 0,
  quote = "\"",
  temporary = FALSE,
  ...
)

## S4 method for signature 'MariaDBConnection,character,data.frame'
dbWriteTable(
  conn,
  name,
  value,
  field.types = NULL,
  row.names = FALSE,
  overwrite = FALSE,
  append = FALSE,
  ...,
  temporary = FALSE
)

Arguments

conn	a MariaDBConnection object, produced by DBI::dbConnect()
name	a character string specifying a table name.
value	A data frame.
...	Unused, needed for compatibility with generic.
row.names	Either TRUE, FALSE, NA or a string. If TRUE, always translate row names to a column called "row_names". If FALSE, never translate row names. If NA, translate rownames only if they're a character vector. A string is equivalent to TRUE, but allows you to override the default name. For backward compatibility, NULL is equivalent to FALSE.
prefix	A fully qualified path in the database's namespace, or NULL. This argument will be processed with dbUnquoteIdentifier(). If given the method will return all objects accessible through this prefix.
check.names	If TRUE, the default, column names will be converted to valid R identifiers.
temporary	If TRUE, creates a temporary table that expires when the connection is closed. For dbRemoveTable(), only temporary tables are considered if this argument is set to TRUE.
fail_if_missing	If FALSE, dbRemoveTable() succeeds if the table doesn't exist.
field.types	Optional, overrides default choices of field types, derived from the classes of the columns in the data frame.
overwrite	a logical specifying whether to overwrite an existing table or not. Its default is FALSE.
append	a logical specifying whether to append to an existing table in the DBMS. If appending, then the table (or temporary table) must exist, otherwise an error is reported. Its default is FALSE.
header	logical, does the input file have a header line? Default is the same heuristic used by read.table(), i.e., TRUE if the first line has one fewer column that the second line.
nrows	number of lines to rows to import using read.table from the input file to create the proper table definition. Default is 50.
sep	field separator character
eol	End-of-line separator
skip	number of lines to skip before reading data in the input file.
quote	the quote character used in the input file (defaults to ⁠\"⁠.)

Details

When using load_data_local_infile = TRUE in DBI::dbConnect(), pass safe = FALSE to dbAppendTable() to avoid transactions. Because ⁠LOAD DATA INFILE⁠ is used internally, this means that rows violating primary key constraints are now silently ignored.

Value

A data.frame in the case of dbReadTable(); otherwise a logical indicating whether the operation was successful.

Note

The data.frame returned by dbReadTable() only has primitive data, e.g., it does not coerce character data to factors. Temporary tables are ignored for dbExistsTable() and dbListTables() due to limitations of the underlying C API. For this reason, a prior existence check is performed only before creating a regular persistent table; an attempt to create a temporary table with an already existing name will fail with a message from the database driver.

Examples

if (mariadbHasDefault()) {
  con <- dbConnect(RMariaDB::MariaDB(), dbname = "test")

  # By default, row names are written in a column to row_names, and
  # automatically read back into the row.names()
  dbWriteTable(con, "mtcars", mtcars[1:5, ], temporary = TRUE)
  dbReadTable(con, "mtcars")
  dbReadTable(con, "mtcars", row.names = FALSE)
}

r-dbi/RMariaDB documentation built on Dec. 15, 2024, 12:12 p.m.