Data engineering and BI courses are free!
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 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")
)
# S4 method for SQLiteConnection
dbConnect(drv, ...)
# S4 method for SQLiteConnection
dbDisconnect(conn, ...)
In previous versions, SQLite() took arguments. These
have now all been moved to dbConnect(), and any arguments here
will be ignored with a warning.
An objected generated by SQLite(), or an existing
'>SQLiteConnection. If an connection, the connection
will be cloned.
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.
When TRUE (default) SQLite3
loadable extensions are enabled. Setting this value to FALSE
prevents extensions from being loaded.
When TRUE (default) the initExtension()
function will be called on the new connection.Setting this value to FALSE
requires calling initExtension() manually.
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 http://www.sqlite.org/pragma.html#pragma_cache_size for details.
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
http://www.sqlite.org/pragma.html#pragma_synchronous for details.
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
Select the SQLite3 OS interface. See
http://www.sqlite.org/vfs.html for details. Allowed values are
"unix-posix", "unix-unix-afp",
"unix-unix-flock", "unix-dotfile", and
"unix-none".
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.
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).
The corresponding generic functions DBI::dbConnect() and DBI::dbDisconnect().
# NOT RUN {
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)
# }
Run the code above in your browser using DataLab