dbWriteTable: Write a local data frame or file to the database
In RSQLite: SQLite Interface for R

dbWriteTable_SQLiteConnection_character_character

R Documentation

Write a local data frame or file to the database

Description

Functions for writing data frames or delimiter-separated files to database tables.

Usage

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

## S4 method for signature 'SQLiteConnection,character,data.frame'
dbWriteTable(
  conn,
  name,
  value,
  ...,
  row.names = pkgconfig::get_config("RSQLite::row.names.table", FALSE),
  overwrite = FALSE,
  append = FALSE,
  field.types = NULL,
  temporary = FALSE
)

Arguments

`conn`	a `SQLiteConnection` object, produced by `DBI::dbConnect()`
`name`	a character string specifying a table name. SQLite table names are not case sensitive, e.g., table names `ABC` and `abc` are considered equal.
`value`	a data.frame (or coercible to data.frame) object or a file name (character). In the first case, the data.frame is written to a temporary file and then imported to SQLite; when `value` is a character, it is interpreted as a file name and its contents imported to SQLite.
`...`	Needed for compatibility with generic. Otherwise ignored.
`field.types`	character vector of named SQL field types where the names are the names of new table's columns. If missing, types inferred with `DBI::dbDataType()`).
`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`.
`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.
`colClasses`	Character vector of R type names, used to override defaults when imputing classes from on-disk file.
`row.names`	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 identifier `"row.names"` maps to the DBMS (see `DBI::make.db.names()`). If `NA` will add rows names if they are characters, otherwise will ignore.
`nrows`	Number of rows to read to determine types.
`sep`	The field separator, defaults to `','`.
`eol`	The end-of-line delimiter, defaults to `'\n'`.
`skip`	number of lines to skip before reading the data. Defaults to 0.
`temporary`	a logical specifying whether the new table should be temporary. Its default is `FALSE`.

Details

In a primary key column qualified with AUTOINCREMENT, missing values will be assigned the next largest positive integer, while nonmissing elements/cells retain their value. If the autoincrement column exists in the data frame passed to the value argument, the NA elements are overwritten. Similarly, if the key column is not present in the data frame, all elements are automatically assigned a value.

Examples

con <- dbConnect(SQLite())
dbWriteTable(con, "mtcars", mtcars)
dbReadTable(con, "mtcars")

# A zero row data frame just creates a table definition.
dbWriteTable(con, "mtcars2", mtcars[0, ])
dbReadTable(con, "mtcars2")

dbDisconnect(con)

RSQLite documentation built on May 29, 2024, 3:30 a.m.