store_in_DB {disprose}

Store data in SQLite database

Description

Write, read and delete tables from SQLite database.

Usage

list_DB(database)

write_to_DB(
  database,
  data,
  table,
  overwrite = FALSE,
  append = FALSE,
  verbose = TRUE
)

index_DB(database, table, index.unique, index.column.name, verbose = TRUE)

read_from_DB(
  database,
  table,
  choose.columns = FALSE,
  column.names,
  select = FALSE,
  select.column.name,
  select.val,
  unique = FALSE
)

delete_from_DB(database, table, verbose = TRUE)

Arguments

database

character; SQLite database name and path.

data

data frame that should be stored as database table.

table

character; table name.

overwrite

logical; use overwrite = TRUE if you want to overwrite a table that already exists in database

append

logical; append rows to table

verbose

logical; show messages

index.unique

logical; vector of indicators to create unique or not unique indexes

index.column.name

vector of indexed columns' names

choose.columns

logical; return chosen columns only

column.names

character; vector of name of columns that are chosen to be returned

select

logical; return only rows that contain selected values in one column

select.column.name

character; name of column that contains selected values

select.val

vector of values that define rows that should be returned

unique

logical; delete duplicated rows

Details

This functions help to store big data frames in SQLite database which makes it faster to save and read the data.

This function creates SQLlite connection to database, fulfills the task and then disconnects. If no database has been created yet, creates one.

Do not use overwrite = TRUE if table does not exists. Do not use append = TRUE and overwrite = TRUE at the same time, no append is possible while overwriting.

If multiple indexes are created in one table, they are unrelated.

Do not use dots in data frame character variables, use underscore.

Parameters choose.columns=FALSE, column.names, select, select.column.name, select.val, unique are only used with linkread_from_DB function. Those parameters define rows and columns that will be returned.

Value

list_DB returns character vector of names of database tables.

read_from_DB returns a data frame with the content of SQLite table.

Functions

• list_DB: Lists all tables from SQLite database

• write_to_DB: Writes data frame into SQLite database table

• index_DB: Creates SQLite indexes in database table

• read_from_DB: Reads table from SQLite database and writes it into data frame.

• delete_from_DB: Deletes table from SQLite database.

Author(s)

Elena N. Filatova

Examples

mydata <- as.data.frame (matrix(1:10, 2, 5))
database <- tempfile()
write_to_DB (database, data = mydata, table = "table1", overwrite = FALSE)
list_DB (database)
mydata2 <- as.data.frame (matrix(11:20, 2, 5))
write_to_DB (database, data = mydata2, table = "table1", overwrite = TRUE)
mydata3 <- read_from_DB (database, table = "table1")
delete_from_DB (database, table = "table1")
file.remove (database)

# example with reading table with restricted columns and rows.
mydata <- data.frame(ids = c(1:6), titles = c("A", "B", "C", "D", "E", "E"),
                    other = rep("other", 6))
database <- tempfile()
write_to_DB (database, data = mydata, table = "table1", overwrite = FALSE)
read_from_DB(database, "table1", choose.columns = TRUE, column.names = c("ids", "titles", "other"),
            select = TRUE, select.column.name = "ids",  select.val = 3:6, unique = TRUE)
read_from_DB(database, "table1", choose.columns = TRUE, column.names = c("titles", "other"),
            select = TRUE, select.column.name = "ids",  select.val = 3:6, unique = TRUE)
file.remove (database)