store_in_DB: Store data in SQLite database

In disprose: Discriminating Probes Selection

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)

disprose documentation built on March 19, 2022, 2:15 a.m.