RSQLite v1.1
Monthly downloads
'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
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 | |
dbExistsTable,SQLiteConnection,character-method | Tables in a database | |
isIdCurrent | isIdCurrent | |
rsqliteVersion | RSQLite version | |
fetch,SQLiteResult-method | Fetch | |
make.db.names,SQLiteConnection,character-method | Make R/S-Plus identifiers into legal SQL identifiers | |
dummy-methods | Dummy methods | |
sqlite-query | Execute a SQL statement on a database connection | |
sqlite-meta | Result information | |
reexports | Objects exported from other packages | |
dbSendPreparedQuery | Deprecated querying tools | |
sqlite-transaction | SQLite transaction management | |
sqliteQuickColumn | Return an entire column from a SQLite database | |
sqliteCopyDatabase | Copy a SQLite database | |
SQLiteDriver-class | Class SQLiteDriver (and methods) | |
SQLiteConnection-class | Class SQLiteConnection | |
SQLiteResult-class | Class SQLiteResult | |
SQLite | Connect to an SQLite database | |
sqliteBuildTableDefinition | Build the SQL CREATE TABLE definition as a string | |
datasetsDb | A sample sqlite database | |
dbReadTable,SQLiteConnection,character-method | Read a database table | |
dbBeginTransaction | Generic for creating a new transaction | |
dbListFields,SQLiteConnection,character-method | List fields in a table | |
dbDataType,SQLiteDriver-method | Determine the SQL Data Type of an R object | |
dbListResults,SQLiteConnection-method | dbListResults | |
dbGetInfo | Get metadata about a database object | |
dbRemoveTable,SQLiteConnection,character-method | Remove a table from the database | |
dbWriteTable,SQLiteConnection,character,data.frame-method | Write a local data frame or file to the database | |
No Results! |
Last month downloads
Details
Date | 2016-11-24 |
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 | 2016-11-25 14:49:05 UTC; muelleki |
Repository | CRAN |
Date/Publication | 2016-11-27 16:31:52 |
linkingto | BH , plogr |
imports | DBI (>= 0.4-9) , memoise , methods , Rcpp (>= 0.12.7) |
suggests | DBItest , knitr , rmarkdown , testthat |
depends | R (>= 3.1.0) |
Contributors | RStudio, Hadley Wickham, Seth Falcon, SQLite Authors, Liam Healy, Kirill Müller, David A. James, R Consortium |
Include our badge in your README
[](http://www.rdocumentation.org/packages/RSQLite)