SQLite | R Documentation |
Together, SQLite()
and dbConnect()
allow you to connect to
a SQLite database file. See DBI::dbSendQuery()
for how to issue queries
and receive results.
SQLite(...)
## S4 method for signature 'SQLiteConnection'
dbConnect(drv, ...)
## S4 method for signature 'SQLiteDriver'
dbConnect(
drv,
dbname = "",
...,
loadable.extensions = TRUE,
default.extensions = loadable.extensions,
cache_size = NULL,
synchronous = "off",
flags = SQLITE_RWC,
vfs = NULL,
bigint = c("integer64", "integer", "numeric", "character"),
extended_types = FALSE
)
## S4 method for signature 'SQLiteConnection'
dbDisconnect(conn, ...)
... |
In previous versions, |
drv , conn |
An objected generated by |
dbname |
The path to the database file. 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. There are two exceptions:
|
loadable.extensions |
When |
default.extensions |
When |
cache_size |
Advanced option. A positive integer to change the maximum number of disk pages that SQLite holds in memory (SQLite's default is 2000 pages). See https://www.sqlite.org/pragma.html#pragma_cache_size for details. |
synchronous |
Advanced options. Possible values for |
flags |
|
vfs |
Select the SQLite3 OS interface. See
https://www.sqlite.org/vfs.html for details. Allowed values are
|
bigint |
The R type that 64-bit integer types should be mapped to, default is bit64::integer64, which allows the full range of 64 bit integers. |
extended_types |
When |
Connections are automatically cleaned-up after they're deleted and
reclaimed by the GC. You can use DBI::dbDisconnect()
to terminate the
connection early, but it will not actually close until all open result
sets have been closed (and you'll get a warning message to this effect).
SQLite()
returns an object of class SQLiteDriver.
dbConnect()
returns an object of class SQLiteConnection.
When parameter extended_types = TRUE
date and time columns are directly
mapped to corresponding R-types. How exactly depends on whether the actual
value is a number or a string:
Column type | Value is numeric | Value is Text | R-class |
DATE | Count of days since 1970-01-01 | YMD formatted string (e.g. 2020-01-23) | Date |
TIME | Count of (fractional) seconds | HMS formatted string (e.g. 12:34:56) | hms (and difftime ) |
DATETIME / TIMESTAMP | Count of (fractional) seconds since midnight 1970-01-01 UTC | DATE and TIME as above separated by a space | POSIXct with time zone UTC |
If a value cannot be mapped an NA
is returned in its place with a warning.
The corresponding generic functions DBI::dbConnect()
and DBI::dbDisconnect()
.
library(DBI)
# Initialize a temporary in memory database and copy a data.frame into it
con <- dbConnect(RSQLite::SQLite(), ":memory:")
data(USArrests)
dbWriteTable(con, "USArrests", USArrests)
dbListTables(con)
# Fetch all query results into a data frame:
dbGetQuery(con, "SELECT * FROM USArrests")
# Or do it in batches
rs <- dbSendQuery(con, "SELECT * FROM USArrests")
d1 <- dbFetch(rs, n = 10) # extract data in chunks of 10 rows
dbHasCompleted(rs)
d2 <- dbFetch(rs, n = -1) # extract all remaining data
dbHasCompleted(rs)
dbClearResult(rs)
# clean up
dbDisconnect(con)
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.