RSQLite Manual

User Manual:

Open the PDF directly: View PDF .
Page Count: 12

Download
Open PDF In Browser	View PDF

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

2

datasetsDb

Maintainer Kirill Müller 
Repository CRAN
Date/Publication 2017-06-19 11:51:07 UTC

R topics documented:
datasetsDb . . . . . . . . . . . . . . . . . . . . . . . . . . . .
dbReadTable,SQLiteConnection,character-method . . . . . .
dbWriteTable,SQLiteConnection,character,data.frame-method
initExtension . . . . . . . . . . . . . . . . . . . . . . . . . .
rsqliteVersion . . . . . . . . . . . . . . . . . . . . . . . . . .
SQLite . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
sqlite-transaction . . . . . . . . . . . . . . . . . . . . . . . .
sqliteCopyDatabase . . . . . . . . . . . . . . . . . . . . . . .

.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.

. 2
. 3
. 4
. 6
. 6
. 7
. 9
. 10

Index

datasetsDb

12

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

a SQLiteConnection 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().

4

dbWriteTable,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

a SQLiteConnection 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 identifier "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)

6

rsqliteVersion

initExtension

Add useful extension functions

Description
These extension functions are written by Liam Healy and made available through the SQLite website (http://www.sqlite.org/contrib).
Usage
initExtension(db)
Arguments
db

A SQLiteConnection 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, rightstr, 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

Description
RSQLite version
Usage
rsqliteVersion()

RSQLite version

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.

8

SQLite
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 operating 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 autocommit 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

a SQLiteConnection 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

A SQLiteConnection object. The main database in from will be copied to to.

to

A SQLiteConnection object pointing to an empty database.

Author(s)
Seth Falcon
References
http://www.sqlite.org/backup.html

sqliteCopyDatabase
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)

11

Index
datasetsDb, 2
SQLite(), 7
dbBegin,SQLiteConnection-method
sqlite-transaction, 9
(sqlite-transaction), 9
SQLITE_RO (SQLite), 7
dbCommit,SQLiteConnection-method
SQLITE_RW (SQLite), 7
(sqlite-transaction), 9
SQLITE_RWC (SQLite), 7
dbConnect(), 7
SQLiteConnection, 3, 4, 6–9
dbConnect,SQLiteConnection-method
sqliteCopyDatabase, 10
(SQLite), 7
SQLiteDriver, 8
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
12



---

Source Exif Data:

File Type                       : PDF
File Type Extension             : pdf
MIME Type                       : application/pdf
PDF Version                     : 1.5
Linearized                      : No
Page Count                      : 12
Page Mode                       : UseOutlines
Author                          : 
Title                           : 
Subject                         : 
Creator                         : LaTeX with hyperref package
Producer                        : pdfTeX-1.40.15
Create Date                     : 2017:06:19 13:51:08+02:00
Modify Date                     : 2017:06:19 13:51:08+02:00
Trapped                         : False
PTEX Fullbanner                 : This is pdfTeX, Version 3.14159265-2.6-1.40.15 (TeX Live 2015/dev/Debian) kpathsea version 6.2.1dev

EXIF Metadata provided by EXIF.tools