SQLite

0th

Percentile

Instantiate the SQLite engine from the current R session.

This function creates and initializes the SQLite engine. It returns an object that allows you to connect to the SQLite engine embedded in R.

Keywords
interface, database
Usage
SQLite(max.con = 16, fetch.default.rec = 500, force.reload = FALSE, 
       shared.cache=FALSE)
Arguments
max.con
maximum number of connections that may be open at one time. This can be up to 100, a limit defined at compilation time. Note that since the SQLite engine is embedded (i.e., a set of C functions within the R process) connections consume very littl
fetch.default.rec
number of records to fetch at one time from the database. (The fetch method uses this number as a default.)
force.reload
should the package code be reloaded (reinitialized)? Setting this to TRUE allows you to change default settings. Notice that all connections should be closed before re-loading.
shared.cache
logical describing whether shared-cache mode should be enabled on the SQLite driver. The default is FALSE.
Details

This object is a singleton, that is, on subsequent invocations it returns the same initialized object.

This implementation allows the R embedded SQLite engine to work with multiple database instances through multiple connections simultaneously.

SQLite keeps each database instance in one single file. The name of the database is the file name, thus database names should be legal file names in the running platform.

Value

  • An object of class SQLiteDriver which extends dbDriver and dbObjectId. This object is required to create connections to the embedded SQLite database. There can be many SQLite database instances running simultaneously.

Side Effects

The R client part of the database communication is initialized, but note that connecting to database instances needs to be done through calls to dbConnect.

User authentication

SQLite is a single-user database engine, so no authentication is required.

References

See the Omega Project for Statistical Computing http://stat.bell-labs.com/RS-DBI for more details on the R database interface.

See the Adobe PDF file DBI.pdf under the doc subdirectory of the DBI package, i.e., system.file("doc", "DBI.pdf", package = "DBI")

See the documentation at the SQLite Web site http://www.sqlite.org for details.

See Also

On database drivers:

dbDriver, dbUnloadDriver, dbListConnections.

On connections, SQL statements and resultSets:

dbConnect, dbDisconnect, dbSendQuery, dbGetQuery, fetch, dbListResults.

On transaction management:

dbCommit, dbRollback.

On meta-data:

summary, dbGetInfo, dbListTables, dbListFields, dbColumnsInfo, dbGetException, dbGetStatement, dbHasCompleted, dbGetRowCount, dbGetAffectedRows.

Aliases
  • SQLite
  • SQLiteDriver
Examples
# create a SQLite instance and create one connection.
   m <- dbDriver("SQLite")
   
   # initialize a new database to a tempfile and copy some data.frame
   # from the base package into it
   tfile <- tempfile()
   con <- dbConnect(m, dbname = tfile)
   data(USArrests)
   dbWriteTable(con, "USArrests", USArrests)
   
   # query
   rs <- dbSendQuery(con, "select * from USArrests")
   d1 <- fetch(rs, n = 10)      # extract data in chunks of 10 rows
   dbHasCompleted(rs)
   d2 <- fetch(rs, n = -1)      # extract all remaining data
   dbHasCompleted(rs)
   dbClearResult(rs)
   dbListTables(con)

   # clean up
   dbDisconnect(con)
   file.info(tfile)
   file.remove(tfile)
Documentation reproduced from package RSQLite, version 0.6-4, License: LGPL version 2 or newer

Community examples

mark@niemannross.com at Dec 12, 2018 RSQLite v2.1.1

[Example files for LinkedIn Learning:](https://linkedin-learning.pxf.io/rweekly_rsqlite) ```r # Description: Example file for RSQLite # main idea: Use SQLite to store/buffer data. it's persistent and uses SQL # Set up SQLIte ----------------------------------------------------------- # import necessary libraries install.packages(c("DBI","RSQLite")) # SQLite support library(DBI) library(RSQLite) putSQLiteHere <- "myRsqlite.sqlite" # could also be ":memory:" mySQLiteDB <- dbConnect(RSQLite::SQLite(),putSQLiteHere) # Load data into SQLite Database ----------------------------------------------------------- data("ChickWeight") # need some data to play with dbWriteTable(conn=mySQLiteDB,name="SQLChickWeight",value=data.frame(ChickWeight)) # Conn = connection to database # name = name of table to create # value is a data.frame # Use SQL with the database --------------------------------------------- doThisSQL <- "select Chick, weight from SQLChickWeight where weight > 100" dbGetQuery(mySQLiteDB,doThisSQL) # All datasets are loaded with RSQLite ------------------------------------ sqldatasets <- RSQLite::datasetsDb() dbReadTable(sqldatasets,"ChickWeight") dbGetQuery(sqldatasets,"select Chick, weight from ChickWeight where weight > 100") # additional code support ------------------------------------------------- RSQLite::initExtension(mySQLiteDB) # adds: acos, acosh, asin, asinh, atan, atan2, atanh, atn2, ceil, cos, cosh, # cot, coth, degrees, difference, exp, floor, log, log10, pi, power, radians, # sign, sin, sinh, sqrt, square, tan, tanh, charindex, leftstr, ltrim, padc, # padl, padr, proper, replace, replicate, reverse, rightstr, rtrim, strfilter, # trim, stdev, variance, mode, median, lower_quartile, upper_quartile doThisSQL <- "select Chick, median(weight) from SQLChickWeight group by Chick order by cast(Chick as int)" dbGetQuery(mySQLiteDB,doThisSQL) dbDisconnect(mySQLiteDB) # documentation ----------------------------------------------------------- browseURL("https://cran.r-project.org/web/packages/RSQLite/RSQLite.pdf") ```