Connect to an SQLite database

Together, 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 SQLiteConnection
dbConnect(drv, ...)

# S4 method for 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 SQLiteConnection
dbDisconnect(conn, ...)

Arguments

...

In previous versions, SQLite() took arguments. These have now all been moved to dbConnect(), and any arguments here will be ignored with a warning.

drv, conn

An objected generated by SQLite(), or an existing SQLiteConnection. 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 to FALSE prevents extensions from being loaded.

default.extensions

When TRUE (default) the initExtension() function will be called on the new connection.Setting this value to FALSE requires calling initExtension() 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 synchronous are "off" (the default), "normal", or "full". Users have reported significant speed ups using sychronous = "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 TRUE columns of type DATE, DATETIME / TIMESTAMP, and TIME are mapped to corresponding R-classes, c.f. below for details. Defaults to FALSE.

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)