sqlite-query {RSQLite}R Documentation

Execute a SQL statement on a database connection

Description

To retrieve results a chunk at a time, use dbSendQuery(), dbFetch(), then dbClearResult(). Alternatively, if you want all the results (and they'll fit in memory) use dbGetQuery() which sends, fetches and clears for you. To run the same prepared query with multiple inputs, use dbBind(). For statements that do not return a table, use dbSendStatement() and dbExecute() instead of dbSendQuery() and dbGetQuery(). See sqlite-meta for how to extract other metadata from the result set.

Usage

## S4 method for signature 'SQLiteConnection,character'
dbSendQuery(conn, statement,
  params = NULL, ...)

## S4 method for signature 'SQLiteResult'
dbBind(res, params, ...)

## S4 method for signature 'SQLiteResult'
dbFetch(res, n = -1, ..., row.names = NA)

## S4 method for signature 'SQLiteResult'
dbClearResult(res, ...)

Arguments

conn

an SQLiteConnection object.

statement

a character vector of length one specifying the SQL statement that should be executed. Only a single SQL statment should be provided.

params

A named list of query parameters to be substituted into a parameterised query. The elements of the list can be vectors which all must be of the same length.

...

Unused. Needed for compatibility with generic.

res

an SQLiteResult object.

n

maximum number of records to retrieve per fetch. Use -1 to retrieve all pending records; 0 retrieves only the table definition.

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.

See Also

The corresponding generic functions DBI::dbSendQuery(), DBI::dbFetch(), DBI::dbClearResult(), DBI::dbGetQuery(), DBI::dbBind(), DBI::dbSendStatement(), and DBI::dbExecute().

Examples

library(DBI)
db <- RSQLite::datasetsDb()

# Run query to get results as dataframe
dbGetQuery(db, "SELECT * FROM USArrests LIMIT 3")

# Send query to pull requests in batches
rs <- dbSendQuery(db, "SELECT * FROM USArrests")
dbFetch(rs, n = 2)
dbFetch(rs, n = 2)
dbHasCompleted(rs)
dbClearResult(rs)

# Parameterised queries are safest when you accept user input
dbGetQuery(db, "SELECT * FROM USArrests WHERE Murder < ?", list(3))

# Or create and then bind
rs <- dbSendQuery(db, "SELECT * FROM USArrests WHERE Murder < ?")
dbBind(rs, list(3))
dbFetch(rs)
dbClearResult(rs)

# Named parameters are a little more convenient
rs <- dbSendQuery(db, "SELECT * FROM USArrests WHERE Murder < :x")
dbBind(rs, list(x = 3))
dbFetch(rs)
dbClearResult(rs)
dbDisconnect(db)

# Passing multiple values is especially useful for statements
con <- dbConnect(RSQLite::SQLite())

dbWriteTable(con, "test", data.frame(a = 1L, b = 2L))
dbReadTable(con, "test")

dbExecute(con, "INSERT INTO test VALUES (:a, :b)",
          params = list(a = 2:4, b = 3:5))
dbReadTable(con, "test")

rs <- dbSendStatement(con, "DELETE FROM test WHERE a = :a AND b = :b")
dbBind(rs, list(a = 3:1, b = 2:4))
dbBind(rs, list(a = 4L, b = 5L))
dbClearResult(rs)
dbReadTable(con, "test")

# Multiple values passed to queries are executed one after another,
# the result appears as one data frame
dbGetQuery(con, "SELECT * FROM TEST WHERE a >= :a", list(a = 0:3))

dbDisconnect(con)
[Package RSQLite version 1.1-2 Index]