Skip to content

r-dbi/RSQLite

This branch is up to date with main.

Folders and files

NameName
Last commit message
Last commit date
Feb 16, 2022
Dec 8, 2024
Aug 7, 2024
Nov 30, 2024
Nov 9, 2021
Aug 7, 2024
Dec 1, 2024
Oct 31, 2023
Dec 8, 2024
Dec 7, 2024
Aug 16, 2022
Dec 8, 2024
Sep 7, 2016
Feb 16, 2022
Feb 13, 2023
Dec 6, 2022
Dec 9, 2024
Sep 27, 2019
Jul 18, 2024
Dec 9, 2024
Oct 27, 2023
Jan 20, 2024
Oct 13, 2019
Nov 24, 2016
Apr 15, 2024
Jun 18, 2017
Dec 3, 2024

Repository files navigation

RSQLite

Lifecycle: stable rcc Coverage Status CRAN status CII Best Practices

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("r-dbi/RSQLite")

Discussions associated with DBI and related database packages take place on R-SIG-DB. The website Databases using R describes the tools and best practices in this ecosystem.

Basic usage

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

dbListTables(con)
## character(0)
dbWriteTable(con, "mtcars", mtcars)
dbListTables(con)
## [1] "mtcars"
dbListFields(con, "mtcars")
##  [1] "mpg"  "cyl"  "disp" "hp"   "drat" "wt"   "qsec" "vs"   "am"   "gear"
## [11] "carb"
dbReadTable(con, "mtcars")
##    mpg cyl  disp  hp drat    wt  qsec vs am gear carb
## 1 21.0   6 160.0 110 3.90 2.620 16.46  0  1    4    4
## 2 21.0   6 160.0 110 3.90 2.875 17.02  0  1    4    4
## 3 22.8   4 108.0  93 3.85 2.320 18.61  1  1    4    1
## 4 21.4   6 258.0 110 3.08 3.215 19.44  1  0    3    1
## 5 18.7   8 360.0 175 3.15 3.440 17.02  0  0    3    2
## 6 18.1   6 225.0 105 2.76 3.460 20.22  1  0    3    1
## 7 14.3   8 360.0 245 3.21 3.570 15.84  0  0    3    4
## 8 24.4   4 146.7  62 3.69 3.190 20.00  1  0    4    2
## 9 22.8   4 140.8  95 3.92 3.150 22.90  1  0    4    2
##  [ reached 'max' / 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
## 1 22.8   4 108.0 93 3.85 2.320 18.61  1  1    4    1
## 2 24.4   4 146.7 62 3.69 3.190 20.00  1  0    4    2
## 3 22.8   4 140.8 95 3.92 3.150 22.90  1  0    4    2
## 4 32.4   4  78.7 66 4.08 2.200 19.47  1  1    4    1
## 5 30.4   4  75.7 52 4.93 1.615 18.52  1  1    4    2
## 6 33.9   4  71.1 65 4.22 1.835 19.90  1  1    4    1
## 7 21.5   4 120.1 97 3.70 2.465 20.01  1  0    3    1
## 8 27.3   4  79.0 66 4.08 1.935 18.90  1  1    4    1
## 9 26.0   4 120.3 91 4.43 2.140 16.70  0  1    5    2
##  [ reached 'max' / getOption("max.print") -- omitted 2 rows ]
dbClearResult(res)

# 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)

# Disconnect from the database
dbDisconnect(con)

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.


Please note that the ‘RSQLite’ project is released with a Contributor Code of Conduct. By contributing to this project, you agree to abide by its terms.