Add2DB: Add Data to a Database

In DECIPHER: Tools for curating, analyzing, and manipulating biological sequences

Description

Adds a data.frame to a database table by its row.names.

Usage

Add2DB(myData,
       dbFile,
       tblName = "Seqs",
       clause = "",
       verbose = TRUE)

Arguments

myData	Data frame containing information to be added to the dbFile.
dbFile	A SQLite connection object or a character string specifying the path to the database file.
tblName	Character string specifying the table in which to add the data.
clause	An optional character string to append to the query as part of a “where clause”.
verbose	Logical indicating whether to display each query as it is sent to the database.

Details

Data contained in myData will be added to the tblName by its respective row.names.

Value

Returns TRUE if the data was added successfully, or FALSE otherwise.

Author(s)

Erik Wright [e-mail]

References

ES Wright (2016) "Using DECIPHER v2.0 to Analyze Big Biological Sequence Data in R". The R Journal, 8(1), 352-359.

See Also

Seqs2DB, SearchDB, BrowseDB

Examples

# Create a sequence database
gen <- system.file("extdata", "Bacteria_175seqs.gen", package="DECIPHER")
dbConn <- dbConnect(SQLite(), ":memory:")
Seqs2DB(gen, "GenBank", dbConn, "Bacteria")

# Identify the sequence lengths
l <- IdLengths(dbConn)

# Add lengths to the database
Add2DB(l, dbConn)

# View the added lengths
BrowseDB(dbConn)

# Change the value of existing columns
ids <- data.frame(identifier=rep("Bacteroidetes", 18), stringsAsFactors=FALSE)
rownames(ids) <- 10:27
Add2DB(ids, dbConn)
BrowseDB(dbConn)

# Add data to a subset of rows using a clause
ids[[1]][] <- "Changed"
nrow(ids) # 18 rows
Add2DB(ids, dbConn, clause="accession like 'EU808318%'")
BrowseDB(dbConn) # only 1 row effected

dbDisconnect(dbConn)

Example output

Loading required package: Biostrings
Loading required package: BiocGenerics
Loading required package: parallel

Attaching package: ‘BiocGenerics’

The following objects are masked from ‘package:parallel’:

    clusterApply, clusterApplyLB, clusterCall, clusterEvalQ,
    clusterExport, clusterMap, parApply, parCapply, parLapply,
    parLapplyLB, parRapply, parSapply, parSapplyLB

The following objects are masked from ‘package:stats’:

    IQR, mad, sd, var, xtabs

The following objects are masked from ‘package:base’:

    anyDuplicated, append, as.data.frame, basename, cbind, colnames,
    dirname, do.call, duplicated, eval, evalq, Filter, Find, get, grep,
    grepl, intersect, is.unsorted, lapply, Map, mapply, match, mget,
    order, paste, pmax, pmax.int, pmin, pmin.int, Position, rank,
    rbind, Reduce, rownames, sapply, setdiff, sort, table, tapply,
    union, unique, unsplit, which.max, which.min

Loading required package: S4Vectors
Loading required package: stats4

Attaching package: ‘S4Vectors’

The following object is masked from ‘package:base’:

    expand.grid

Loading required package: IRanges
Loading required package: XVector

Attaching package: ‘Biostrings’

The following object is masked from ‘package:base’:

    strsplit

Loading required package: RSQLite

Reading GenBank file chunk 1

175 total sequences in table Seqs.
Time difference of 0.25 secs


Lengths counted for 175 sequences.

Time difference of 0.03 secs

Expression:
alter table Seqs add column bases INTEGER

Expression:
update Seqs set bases = :bases where row_names = :row_names

Expression:
alter table Seqs add column nonbases INTEGER

Expression:
update Seqs set nonbases = :nonbases where row_names = :row_names

Expression:
alter table Seqs add column width INTEGER

Expression:
update Seqs set width = :width where row_names = :row_names

Added to table Seqs:  "bases" and "nonbases" and "width".

Time difference of 0.02 secs

Expression:
update Seqs set identifier = :identifier where row_names = :row_names

Added to table Seqs:  "identifier".

Time difference of 0.01 secs

[1] 18
Expression:
update Seqs set identifier = :identifier where row_names = :row_names and
accession like 'EU808318%'

Added to table Seqs:  "identifier".

Time difference of 0.01 secs

DECIPHER documentation built on Nov. 8, 2020, 8:30 p.m.