RSQLite v1.1-2

0

Monthly downloads

0th

Percentile

by Kirill Müller

'SQLite' Interface for R

Embeds the 'SQLite' database engine in R and provides an interface compliant with the 'DBI' package. The source for the 'SQLite' engine (version 3.8.8.2) is included.

Readme

RSQLite

Build Status AppVeyor Build Status Coverage Status

Embeds the SQLite database engine in R, providing a DBI-compliant interface. SQLite is a public-domain, single-user, very light-weight database engine that implements a decent subset of the SQL 92 standard, including the core table creation, updating, insertion, and selection operations, plus transaction management.

You can install the latest released version from CRAN with:

install.packages("RSQLite")

Or install the latest development version from GitHub with:

# install.packages("devtools")
devtools::install_github("rstats-db/RSQLite")

To install from GitHub, you'll need a development environment.

Basic usage

library(DBI)
# Create an ephemeral in-memory RSQLite database
con <- dbConnect(RSQLite::SQLite(), ":memory:")

dbListTables(con)
## character(0)
dbWriteTable(con, "mtcars", mtcars)
## [1] TRUE
dbListTables(con)
## [1] "mtcars"
dbListFields(con, "mtcars")
##  [1] "row_names" "mpg"       "cyl"       "disp"      "hp"       
##  [6] "drat"      "wt"        "qsec"      "vs"        "am"       
## [11] "gear"      "carb"
dbReadTable(con, "mtcars")
##                      mpg cyl  disp  hp drat    wt  qsec vs am gear carb
## Mazda RX4           21.0   6 160.0 110 3.90 2.620 16.46  0  1    4    4
## Mazda RX4 Wag       21.0   6 160.0 110 3.90 2.875 17.02  0  1    4    4
## Datsun 710          22.8   4 108.0  93 3.85 2.320 18.61  1  1    4    1
## Hornet 4 Drive      21.4   6 258.0 110 3.08 3.215 19.44  1  0    3    1
## Hornet Sportabout   18.7   8 360.0 175 3.15 3.440 17.02  0  0    3    2
## Valiant             18.1   6 225.0 105 2.76 3.460 20.22  1  0    3    1
## Duster 360          14.3   8 360.0 245 3.21 3.570 15.84  0  0    3    4
## Merc 240D           24.4   4 146.7  62 3.69 3.190 20.00  1  0    4    2
## Merc 230            22.8   4 140.8  95 3.92 3.150 22.90  1  0    4    2
##  [ reached getOption("max.print") -- omitted 23 rows ]
# You can fetch all results:
res <- dbSendQuery(con, "SELECT * FROM mtcars WHERE cyl = 4")
dbFetch(res)
##                 mpg cyl  disp  hp drat    wt  qsec vs am gear carb
## Datsun 710     22.8   4 108.0  93 3.85 2.320 18.61  1  1    4    1
## Merc 240D      24.4   4 146.7  62 3.69 3.190 20.00  1  0    4    2
## Merc 230       22.8   4 140.8  95 3.92 3.150 22.90  1  0    4    2
## Fiat 128       32.4   4  78.7  66 4.08 2.200 19.47  1  1    4    1
## Honda Civic    30.4   4  75.7  52 4.93 1.615 18.52  1  1    4    2
## Toyota Corolla 33.9   4  71.1  65 4.22 1.835 19.90  1  1    4    1
## Toyota Corona  21.5   4 120.1  97 3.70 2.465 20.01  1  0    3    1
## Fiat X1-9      27.3   4  79.0  66 4.08 1.935 18.90  1  1    4    1
## Porsche 914-2  26.0   4 120.3  91 4.43 2.140 16.70  0  1    5    2
##  [ reached getOption("max.print") -- omitted 2 rows ]
dbClearResult(res)
## [1] TRUE
# Or a chunk at a time
res <- dbSendQuery(con, "SELECT * FROM mtcars WHERE cyl = 4")
while(!dbHasCompleted(res)){
  chunk <- dbFetch(res, n = 5)
  print(nrow(chunk))
}
## [1] 5
## [1] 5
## [1] 1
# Clear the result
dbClearResult(res)
## [1] TRUE
# Disconnect from the database
dbDisconnect(con)
## [1] TRUE

Acknowledgements

Many thanks to Doug Bates, Seth Falcon, Detlef Groth, Ronggui Huang, Kurt Hornik, Uwe Ligges, Charles Loboz, Duncan Murdoch, and Brian D. Ripley for comments, suggestions, bug reports, and/or patches.

Functions in RSQLite

Name Description
dbRemoveTable,SQLiteConnection,character-method Remove a table from the database
dbListResults,SQLiteConnection-method dbListResults
dbReadTable,SQLiteConnection,character-method Read a database table
dbBeginTransaction Generic for creating a new transaction
dummy-methods Dummy methods
fetch,SQLiteResult-method Fetch
rsqliteVersion RSQLite version
sqlite-transaction SQLite transaction management
sqlite-query Execute a SQL statement on a database connection
sqlite-meta Result information
dbSendPreparedQuery Deprecated querying tools
isIdCurrent isIdCurrent
make.db.names,SQLiteConnection,character-method Make R/S-Plus identifiers into legal SQL identifiers
SQLiteDriver-class Class SQLiteDriver (and methods)
dbExistsTable,SQLiteConnection,character-method Tables in a database
sqliteQuickColumn Return an entire column from a SQLite database
dbDataType,SQLiteDriver-method Determine the SQL Data Type of an R object
sqliteCopyDatabase Copy a SQLite database
dbWriteTable,SQLiteConnection,character,data.frame-method Write a local data frame or file to the database
SQLiteConnection-class Class SQLiteConnection
SQLite Connect to an SQLite database
sqliteBuildTableDefinition Build the SQL CREATE TABLE definition as a string
dbGetInfo Get metadata about a database object
dbListFields,SQLiteConnection,character-method List fields in a table
datasetsDb A sample sqlite database
reexports Objects exported from other packages
SQLiteResult-class Class SQLiteResult
No Results!

Last month downloads

Details

Date 2017-01-07
LinkingTo Rcpp, BH, plogr
Encoding UTF-8
License LGPL (>= 2)
URL https://github.com/rstats-db/RSQLite
BugReports https://github.com/rstats-db/RSQLite/issues
Collate 'RcppExports.R' 'SQLiteConnection.R' 'SQLiteDriver.R' 'SQLiteResult.R' 'connect.R' 'copy.R' 'datasetsDb.R' 'deprecated.R' 'dummy.R' 'extensions.R' 'query.R' 'rownames.R' 'table.R' 'transactions.R' 'utils.R' 'zzz.R'
VignetteBuilder knitr
RoxygenNote 5.0.1.9000
NeedsCompilation yes
Packaged 2017-01-08 00:12:25 UTC; muelleki
Repository CRAN
Date/Publication 2017-01-08 16:57:09

Include our badge in your README

[![Rdoc](http://www.rdocumentation.org/badges/version/RSQLite)](http://www.rdocumentation.org/packages/RSQLite)