store_in_DB {disprose} | R Documentation |
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 |
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)