write2sqlite: Quickly create and write to a SQLite database

In al-obrien/farrago: A random collection of helpful code baubles

Quickly create and write to a SQLite database

Description

write2sqlite is a light wrapper function around various RSQLite and DBI functions to make it easier to quickly create a database, tables, and append to existing tables.

Usage

write2sqlite(
  db_name,
  data,
  tbl_name,
  extended_types = TRUE,
  date2text = TRUE,
  logging = FALSE,
  ...
)

Arguments

db_name	Database name (either path to existing, or name of new database (e.g. name.sqlite, name.db)).
data	Dataset to add to database.
tbl_name	Name to assign to table associated with the provided data.
extended_types	RSQLite parameter for additional data types such as dates (default: TRUE),
date2text	Detect which columns are dates and parse them to characters (instead of numeric) to store in SQLite (default: TRUE),
logging	Boolean, determines if logging table named 'log_book' is used in the writing operation (default: FALSE),
...	Additional parameters to be passed to dbWriteTable.

Details

This function requires some of the more recent features of SQLite, including 'extended types' and date formatting capabilities. To ensure date formatting is preserved, write2sqlite checks column types for the table being added. Using this function also controls the connection open/closing automatically (given the provided DB name).

An optional logging feature is also available. This creates a column called 'log_book' and will track when a table was updated.

See Also

read_sqlite

Examples

## Not run: 
# Use an existing dataset to add data
write2sqlite('path/to/my/dbfile.sqlite', mtcars, 'boringcars')

# Replace an existing dataset
newmtcars <- mtcars[1:10]
write2sqlite('path/to/my/dbfile.sqlite', newmtcars, 'boringcars', overwrite = TRUE)

# Create a new DB and add first table in one swoop
write2sqlite('mydb.sqlite', newmtcars, 'firsttable')

## End(Not run)

al-obrien/farrago documentation built on April 14, 2023, 6:20 p.m.