dbReadTable-methods {RMySQL} | R Documentation

## Convenience functions for Importing/Exporting DBMS tables

### Description

These functions mimic their R/S-Plus counterpart `get`, `assign`, `exists`, `remove`, and `objects`, except that they generate code that gets remotely executed in a database engine.

### Value

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

### Methods

conn
  an `MySQLConnection` database connection object.
name
  a character string specifying a table name.
value
  a data.frame (or coercible to data.frame).
row.names
  in the case of `dbReadTable`, this argument can be a string or an index specifying the column in the DBMS table to be used as `row.names` in the output data.frame (a `NULL`, `""`, or 0 specifies that no column should be used as `row.names` in the output).

  In the case of `dbWriteTable`, this argument should be a logical specifying whether the `row.names` should be output to the output DBMS table; if `TRUE`, an extra field whose name will be whatever the R/S-Plus identifier `"row.names"` maps to the DBMS (see `make.db.names`).

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. Its default is `FALSE`.
allow.keywords
  `dbWriteTable` accepts a logical `allow.keywords` to allow or prevent MySQL reserved identifiers to be used as column names. By default it is `FALSE`.
dots
  optional arguments.

  When `dbWriteTable` is used to import data from a file, you may optionally specify `header=`, `row.names=`, `col.names=`, `sep=`, `eol=`, `field.types=`, `skip=`, and `quote=`.

  `header` is a logical indicating whether the first data line (but see `skip`) has a header or not. If missing, it value is determined following `read.table` convention, namely, it is set to TRUE if and only if the first row has one fewer field that the number of columns.

  `row.names` is a logical to specify whether the first column is a set of row names. If missing its default follows the `read.table` convention.

  `col.names` a character vector with column names (these names will be filtered with `make.db.names` to ensure valid SQL identifiers. (See also `field.types` below.)

  `sep=` specifies the field separator, and its default is `','`.

  `eol=` specifies the end-of-line delimiter, and its default is `'\n'`.

  `skip` specifies number of lines to skip before reading the data, and it defaults to 0.

  `field.types` is a list of named field SQL types where `names(field.types)` provide the new table's column names (if missing, field types are inferred using `dbDataType`).

### Note

Note that data.frames are only approximately analogous to tables (relations) in relational DBMS, and thus you should not expect complete agreement in their semantics. Tables in RDBMS are best thought of as relations with a number of constraints imposed by the relational database model, and data.frames, with their roots in statistical modeling, as self-contained "sequence of observations on some chosen variables" (Chambers and Hastie (1992), p.46). In particular the `data.frame` returned by `dbReadTable` only has primitive data, e.g., it does not coerce character data to factors. Also, column names in a data.frame are not guaranteed to be equal to the column names in a MySQL exported/imported table (e.g., by default MySQL reserved identifiers may not be used as column names — and with 218 keywords like `"BEFORE"`, `"DESC"`, and `"FROM"` the likelihood of name conflicts is not small.) Use `isSQLKeyword(con, names(value))` to check whether the data.frame names in `value` coincide with MySQL reserver words.

MySQL table names are not case sensitive, e.g., table names `ABC` and `abc` are considered equal.

### References

See the Database Interface definition document `DBI.pdf` in the base directory of this package or http://stat.bell-labs.com/RS-DBI.

### See Also

`MySQL`, `mysqlImportFile`, `isSQLKeyword`, `dbDriver`, `dbConnect`, `dbSendQuery`, `dbGetQuery`, `fetch`, `dbCommit`, `dbGetInfo`, `dbListTables`, `dbReadTable`.

### Examples

    ## Not run:
    conn <- dbConnect("MySQL", group = "wireless")
    if(dbExistsTable(con, "fuel_frame")){
       dbRemoveTable(conn, "fuel_frame")
       dbWriteTable(conn, "fuel_frame", fuel.frame)
    }
    if(dbExistsTable(conn, "RESULTS")){
       dbWriteTable(conn, "RESULTS", results2000, append = T)
    else
       dbWriteTable(conn, "RESULTS", results2000)
    }
    ## End(Not run)

[Package RMySQL version 0.6-0 Index]