Connect to an SQLite database
Source:R/SQLite.R, R/dbConnect_SQLiteConnection.R, R/dbConnect_SQLiteDriver.R, and 1 more
SQLite.RdTogether, SQLite() and dbConnect() allow you to connect to
a SQLite database file. See DBI::dbSendQuery() for how to issue queries
and receive results.
Usage
SQLite(...)
# S4 method for class 'SQLiteConnection'
dbConnect(drv, ...)
# S4 method for class '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 class 'SQLiteConnection'
dbDisconnect(conn, ...)Arguments
- ...
In previous versions,
SQLite()took arguments. These have now all been moved toDBI::dbConnect(), and any arguments here will be ignored with a warning.- drv, conn
An objected generated by
SQLite(), or an existingSQLiteConnection. If an connection, the connection will be cloned.- 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:
""will create a temporary on-disk database. The file will be deleted when the connection is closed.":memory:"or"file::memory:"will create a temporary in-memory database.
- loadable.extensions
When
TRUE(default) SQLite3 loadable extensions are enabled. Setting this value toFALSEprevents extensions from being loaded.- default.extensions
When
TRUE(default) theinitExtension()function will be called on the new connection.Setting this value toFALSErequires callinginitExtension()manually.- 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
synchronousare "off" (the default), "normal", or "full". Users have reported significant speed ups usingsychronous = "off", and the SQLite documentation itself implies considerable improved performance at the very modest risk of database corruption in the unlikely case of the operating system (not the R application) crashing. See https://www.sqlite.org/pragma.html#pragma_synchronous for details.- flags
SQLITE_RWC: open the database in read/write mode and create the database file if it does not already exist;SQLITE_RW: open the database in read/write mode. Raise an error if the file does not already exist;SQLITE_RO: open the database in read only mode. Raise an error if the file does not already exist- vfs
Select the SQLite3 OS interface. See https://www.sqlite.org/vfs.html for details. Allowed values are
"unix-posix","unix-unix-afp","unix-unix-flock","unix-dotfile", and"unix-none".- 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
TRUEcolumns of typeDATE,DATETIME/TIMESTAMP, andTIMEare mapped to corresponding R-classes, c.f. below for details. Defaults toFALSE.
Value
SQLite() returns an object of class SQLiteDriver.
dbConnect() returns an object of class SQLiteConnection.
Details
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).
Extended Types
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.
See also
The corresponding generic functions DBI::dbConnect() and DBI::dbDisconnect().
Examples
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)
#> [1] "USArrests"
# Fetch all query results into a data frame:
dbGetQuery(con, "SELECT * FROM USArrests")
#> Murder Assault UrbanPop Rape
#> 1 13.2 236 58 21.2
#> 2 10.0 263 48 44.5
#> 3 8.1 294 80 31.0
#> 4 8.8 190 50 19.5
#> 5 9.0 276 91 40.6
#> 6 7.9 204 78 38.7
#> 7 3.3 110 77 11.1
#> 8 5.9 238 72 15.8
#> 9 15.4 335 80 31.9
#> 10 17.4 211 60 25.8
#> 11 5.3 46 83 20.2
#> 12 2.6 120 54 14.2
#> 13 10.4 249 83 24.0
#> 14 7.2 113 65 21.0
#> 15 2.2 56 57 11.3
#> 16 6.0 115 66 18.0
#> 17 9.7 109 52 16.3
#> 18 15.4 249 66 22.2
#> 19 2.1 83 51 7.8
#> 20 11.3 300 67 27.8
#> 21 4.4 149 85 16.3
#> 22 12.1 255 74 35.1
#> 23 2.7 72 66 14.9
#> 24 16.1 259 44 17.1
#> 25 9.0 178 70 28.2
#> 26 6.0 109 53 16.4
#> 27 4.3 102 62 16.5
#> 28 12.2 252 81 46.0
#> 29 2.1 57 56 9.5
#> 30 7.4 159 89 18.8
#> 31 11.4 285 70 32.1
#> 32 11.1 254 86 26.1
#> 33 13.0 337 45 16.1
#> 34 0.8 45 44 7.3
#> 35 7.3 120 75 21.4
#> 36 6.6 151 68 20.0
#> 37 4.9 159 67 29.3
#> 38 6.3 106 72 14.9
#> 39 3.4 174 87 8.3
#> 40 14.4 279 48 22.5
#> 41 3.8 86 45 12.8
#> 42 13.2 188 59 26.9
#> 43 12.7 201 80 25.5
#> 44 3.2 120 80 22.9
#> 45 2.2 48 32 11.2
#> 46 8.5 156 63 20.7
#> 47 4.0 145 73 26.2
#> 48 5.7 81 39 9.3
#> 49 2.6 53 66 10.8
#> 50 6.8 161 60 15.6
# 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)
#> [1] FALSE
d2 <- dbFetch(rs, n = -1) # extract all remaining data
dbHasCompleted(rs)
#> [1] TRUE
dbClearResult(rs)
# clean up
dbDisconnect(con)