RSQLite Manual
User Manual:
Open the PDF directly: View PDF 
.
Page Count: 12
Package ‘RSQLite’
June 19, 2017
Version 2.0
Date 2017-06-18
Title 'SQLite' Interface for R
Description Embeds the 'SQLite' database engine in R and
provides an interface compliant with the 'DBI' package. The
source for the 'SQLite' engine (version 3.8.8.2) is included.
Depends R (>= 3.1.0)
Suggests DBItest, knitr, rmarkdown, testthat
Imports bit64, blob (>= 1.1), DBI (>= 0.4-9), memoise, methods,
pkgconfig, Rcpp (>= 0.12.7)
LinkingTo Rcpp, BH, plogr
Encoding UTF-8
License LGPL (>= 2)
URL https://github.com/rstats-db/RSQLite
BugReports https://github.com/rstats-db/RSQLite/issues
Collate 'RcppExports.R' 'SQLiteConnection.R' 'SQLiteDriver.R'
'SQLiteResult.R' 'connect.R' 'copy.R' 'datasetsDb.R'
'deprecated.R' 'export.R' 'extensions.R' 'query.R' 'rownames.R'
'table.R' 'transactions.R' 'utils.R' 'zzz.R'
VignetteBuilder knitr
RoxygenNote 6.0.1
NeedsCompilation yes
Author Kirill Müller [aut, cre],
Hadley Wickham [aut],
David A. James [aut],
Seth Falcon [aut],
SQLite Authors [ctb] (for the included SQLite sources),
Liam Healy [ctb] (for the included SQLite sources),
R Consortium [cph],
RStudio [cph]
1
2datasetsDb
Maintainer Kirill Müller <krlmlr+r@mailbox.org>
Repository CRAN
Date/Publication 2017-06-19 11:51:07 UTC
Rtopics documented:
datasetsDb.......................................... 2
dbReadTable,SQLiteConnection,character-method . . . . . . . . . . . . . . . . . . . . 3
dbWriteTable,SQLiteConnection,character,data.frame-method . . . . . . . . . . . . . . 4
initExtension ........................................ 6
rsqliteVersion ........................................ 6
SQLite............................................ 7
sqlite-transaction ...................................... 9
sqliteCopyDatabase..................................... 10
Index 12
datasetsDb A sample sqlite database
Description
This database is bundled with the package, and contains all data frames in the datasets package.
Usage
datasetsDb()
Examples
library(DBI)
db <- RSQLite::datasetsDb()
dbListTables(db)
dbReadTable(db, "CO2")
dbGetQuery(db, "SELECT * FROM CO2 WHERE conc < 100")
dbDisconnect(db)
dbReadTable,SQLiteConnection,character-method 3
dbReadTable,SQLiteConnection,character-method
Read a database table
Description
Returns the contents of a database table given by name as a data frame.
Usage
## S4 method for signature 'SQLiteConnection,character'
dbReadTable(conn, name, ...,
row.names = pkgconfig::get_config("RSQLite::row.names.table", FALSE),
check.names = TRUE, select.cols = NULL)
Arguments
conn aSQLiteConnection object, produced by DBI::dbConnect()
name a character string specifying a table name. SQLite table names are not case
sensitive, e.g., table names ABC and abc are considered equal.
... Needed for compatibility with generic. Otherwise ignored.
row.names Either TRUE,FALSE,NA or a string.
If TRUE, always translate row names to a column called "row_names". If FALSE,
never translate row names. If NA, translate rownames only if they’re a character
vector.
A string is equivalent to TRUE, but allows you to override the default name.
For backward compatibility, NULL is equivalent to FALSE.
check.names If TRUE, the default, column names will be converted to valid R identifiers.
select.cols Deprecated, do not use.
Details
Note that the data frame returned by dbReadTable() only has primitive data, e.g., it does not coerce
character data to factors.
Value
A data frame.
See Also
The corresponding generic function DBI::dbReadTable().
4dbWriteTable,SQLiteConnection,character,data.frame-method
Examples
library(DBI)
db <- RSQLite::datasetsDb()
dbReadTable(db, "mtcars")
dbReadTable(db, "mtcars", row.names = FALSE)
dbDisconnect(db)
dbWriteTable,SQLiteConnection,character,data.frame-method
Write a local data frame or file to the database
Description
Functions for writing data frames or delimiter-separated files to database tables.
Usage
## S4 method for signature 'SQLiteConnection,character,data.frame'
dbWriteTable(conn, name,
value, ..., row.names = pkgconfig::get_config("RSQLite::row.names.table",
FALSE), overwrite = FALSE, append = FALSE, field.types = NULL,
temporary = FALSE)
## S4 method for signature 'SQLiteConnection,character,character'
dbWriteTable(conn, name, value,
..., field.types = NULL, overwrite = FALSE, append = FALSE,
header = TRUE, colClasses = NA, row.names = FALSE, nrows = 50,
sep = ",", eol = "\n", skip = 0, temporary = FALSE)
Arguments
conn aSQLiteConnection object, produced by DBI::dbConnect()
name a character string specifying a table name. SQLite table names are not case
sensitive, e.g., table names ABC and abc are considered equal.
value a data.frame (or coercible to data.frame) object or a file name (character). In
the first case, the data.frame is written to a temporary file and then imported
to SQLite; when value is a character, it is interpreted as a file name and its
contents imported to SQLite.
... Needed for compatibility with generic. Otherwise ignored.
row.names A logical specifying whether the row.names should be output to the output
DBMS table; if TRUE, an extra field whose name will be whatever the R identi-
fier "row.names" maps to the DBMS (see DBI::make.db.names()). If NA will
add rows names if they are characters, otherwise will ignore.
overwrite a logical specifying whether to overwrite an existing table or not. Its default is
FALSE.
dbWriteTable,SQLiteConnection,character,data.frame-method 5
append a logical specifying whether to append to an existing table in the DBMS. Its
default is FALSE.
field.types character vector of named SQL field types where the names are the names of
new table’s columns. If missing, types inferred with DBI::dbDataType()).
temporary a logical specifying whether the new table should be temporary. Its default is
FALSE.
header is a logical indicating whether the first data line (but see skip) has a header
or not. If missing, it value is determined following read.table() convention,
namely, it is set to TRUE if and only if the first row has one fewer field that the
number of columns.
colClasses Character vector of R type names, used to override defaults when imputing
classes from on-disk file.
nrows Number of rows to read to determine types.
sep The field separator, defaults to ','.
eol The end-of-line delimiter, defaults to '\n'.
skip number of lines to skip before reading the data. Defaults to 0.
Details
In a primary key column qualified with AUTOINCREMENT, missing values will be assigned the next
largest positive integer, while nonmissing elements/cells retain their value. If the autoincrement
column exists in the data frame passed to the value argument, the NA elements are overwritten.
Similarly, if the key column is not present in the data frame, all elements are automatically assigned
a value.
See Also
The corresponding generic function DBI::dbWriteTable().
Examples
con <- dbConnect(SQLite())
dbWriteTable(con, "mtcars", mtcars)
dbReadTable(con, "mtcars")
# A zero row data frame just creates a table definition.
dbWriteTable(con, "mtcars2", mtcars[0, ])
dbReadTable(con, "mtcars2")
dbDisconnect(con)
6rsqliteVersion
initExtension Add useful extension functions
Description
These extension functions are written by Liam Healy and made available through the SQLite web-
site (http://www.sqlite.org/contrib).
Usage
initExtension(db)
Arguments
db ASQLiteConnection object to load these extensions into.
Available extension functions
Math functions acos, acosh, asin, asinh, atan, atan2, atanh, atn2, ceil, cos, cosh, cot, coth, degrees,
difference, exp, floor, log, log10, pi, power, radians, sign, sin, sinh, sqrt, square, tan, tanh
String functions charindex, leftstr, ltrim, padc, padl, padr, proper, replace, replicate, reverse, right-
str, rtrim, strfilter, trim
Aggregate functions stdev, variance, mode, median, lower_quartile, upper_quartile
Examples
library(DBI)
db <- RSQLite::datasetsDb()
RSQLite::initExtension(db)
dbGetQuery(db, "SELECT stdev(mpg) FROM mtcars")
sd(mtcars$mpg)
dbDisconnect(db)
rsqliteVersion RSQLite version
Description
RSQLite version
Usage
rsqliteVersion()
SQLite 7
Value
A character vector containing header and library versions of RSQLite.
Examples
RSQLite::rsqliteVersion()
SQLite Connect to an SQLite database
Description
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 signature 'SQLiteDriver'
dbConnect(drv, dbname = "", ...,
loadable.extensions = TRUE, cache_size = NULL, synchronous = "off",
flags = SQLITE_RWC, vfs = NULL)
## S4 method for signature 'SQLiteConnection'
dbConnect(drv, ...)
## S4 method for signature '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.
8SQLite
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 http:
//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 oper-
ating system (not the R application) crashing. See http://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 http://www.sqlite.org/vfs.html for
details. Allowed values are "unix-posix","unix-unix-afp","unix-unix-flock",
"unix-dotfile", and "unix-none".
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).
Value
SQLite() returns an object of class SQLiteDriver.
dbConnect() returns an object of class SQLiteConnection.
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)
# 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
sqlite-transaction 9
dbHasCompleted(rs)
dbClearResult(rs)
# clean up
dbDisconnect(con)
sqlite-transaction SQLite transaction management
Description
By default, SQLite is in auto-commit mode. dbBegin() starts a SQLite transaction and turns auto-
commit off. dbCommit() and dbRollback() commit and rollback the transaction, respectively and
turn auto-commit on. DBI::dbWithTransaction() is a convenient wrapper that makes sure that
dbCommit() or dbRollback() is called.
Usage
## S4 method for signature 'SQLiteConnection'
dbBegin(conn, .name = NULL, ..., name = NULL)
## S4 method for signature 'SQLiteConnection'
dbCommit(conn, .name = NULL, ..., name = NULL)
## S4 method for signature 'SQLiteConnection'
dbRollback(conn, .name = NULL, ...,
name = NULL)
Arguments
conn aSQLiteConnection object, produced by DBI::dbConnect()
.name For backward compatibility, do not use.
... Needed for compatibility with generic. Otherwise ignored.
name Supply a name to use a named savepoint. This allows you to nest multiple
transaction
See Also
The corresponding generic functions DBI::dbBegin(),DBI::dbCommit(), and DBI::dbRollback().
Examples
library(DBI)
con <- dbConnect(SQLite(), ":memory:")
dbWriteTable(con, "arrests", datasets::USArrests)
dbGetQuery(con, "select count(*) from arrests")
10 sqliteCopyDatabase
dbBegin(con)
rs <- dbSendStatement(con, "DELETE from arrests WHERE Murder > 1")
dbGetRowsAffected(rs)
dbClearResult(rs)
dbGetQuery(con, "select count(*) from arrests")
dbRollback(con)
dbGetQuery(con, "select count(*) from arrests")[1, ]
dbBegin(con)
rs <- dbSendStatement(con, "DELETE FROM arrests WHERE Murder > 5")
dbClearResult(rs)
dbCommit(con)
dbGetQuery(con, "SELECT count(*) FROM arrests")[1, ]
# Named savepoints can be nested --------------------------------------------
dbBegin(con, name = "a")
dbBegin(con, name = "b")
dbRollback(con, name = "b")
dbCommit(con, name = "a")
dbDisconnect(con)
sqliteCopyDatabase Copy a SQLite database
Description
Copies a database connection to a file or to another database connection. It can be used to save an
in-memory database (created using dbname = ":memory:" or dbname = "file::memory:") to a
file or to create an in-memory database a copy of another database.
Usage
sqliteCopyDatabase(from, to)
Arguments
from ASQLiteConnection object. The main database in from will be copied to to.
to ASQLiteConnection object pointing to an empty database.
Author(s)
Seth Falcon
References
http://www.sqlite.org/backup.html
sqliteCopyDatabase 11
Examples
library(DBI)
# Copy the built in databaseDb() to an in-memory database
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbListTables(con)
db <- RSQLite::datasetsDb()
RSQLite::sqliteCopyDatabase(db, con)
dbDisconnect(db)
dbListTables(con)
dbDisconnect(con)
Index
datasetsDb,2
dbBegin,SQLiteConnection-method
(sqlite-transaction),9
dbCommit,SQLiteConnection-method
(sqlite-transaction),9
dbConnect(),7
dbConnect,SQLiteConnection-method
(SQLite),7
dbConnect,SQLiteDriver-method (SQLite),
7
dbDisconnect,SQLiteConnection-method
(SQLite),7
DBI::dbBegin(),9
DBI::dbCommit(),9
DBI::dbConnect(),3,4,8,9
DBI::dbDataType(),5
DBI::dbDisconnect(),8
DBI::dbReadTable(),3
DBI::dbRollback(),9
DBI::dbSendQuery(),7
DBI::dbWithTransaction(),9
DBI::dbWriteTable(),5
DBI::make.db.names(),4
dbReadTable,SQLiteConnection,character-method,
3
dbRollback,SQLiteConnection-method
(sqlite-transaction),9
dbWriteTable,SQLiteConnection,character,character-method
(dbWriteTable,SQLiteConnection,character,data.frame-method),
4
dbWriteTable,SQLiteConnection,character,data.frame-method,
4
initExtension,6
read.table(),5
RSQLite (SQLite),7
RSQLite-package (SQLite),7
rsqliteVersion,6
SQLite,7
SQLite(),7
sqlite-transaction,9
SQLITE_RO (SQLite),7
SQLITE_RW (SQLite),7
SQLITE_RWC (SQLite),7
SQLiteConnection,3,4,6–9
sqliteCopyDatabase,10
SQLiteDriver,8
12
