DBI-try

library(DBI)
library(RSQLite)

Create and write tables

# Create an ephemeral in-memory RSQLite database
con <- dbConnect(RSQLite::SQLite(), dbname = ":memory:")

dbListTables(con)
#> character(0)
dbWriteTable(con, "mtcars", mtcars)
dbListTables(con)
#> [1] "mtcars"

Get query results

# You can fetch all results:
res <- dbSendQuery(con, "SELECT * FROM mtcars WHERE cyl = 4")
dbFetch(res)
#>     mpg cyl  disp  hp drat    wt  qsec vs am gear carb
#> 1  22.8   4 108.0  93 3.85 2.320 18.61  1  1    4    1
#> 2  24.4   4 146.7  62 3.69 3.190 20.00  1  0    4    2
#> 3  22.8   4 140.8  95 3.92 3.150 22.90  1  0    4    2
#> 4  32.4   4  78.7  66 4.08 2.200 19.47  1  1    4    1
#> 5  30.4   4  75.7  52 4.93 1.615 18.52  1  1    4    2
#> 6  33.9   4  71.1  65 4.22 1.835 19.90  1  1    4    1
#> 7  21.5   4 120.1  97 3.70 2.465 20.01  1  0    3    1
#> 8  27.3   4  79.0  66 4.08 1.935 18.90  1  1    4    1
#> 9  26.0   4 120.3  91 4.43 2.140 16.70  0  1    5    2
#> 10 30.4   4  95.1 113 3.77 1.513 16.90  1  1    5    2
#> 11 21.4   4 121.0 109 4.11 2.780 18.60  1  1    4    2
dbClearResult(res)

# Or a chunk at a time
res <- dbSendQuery(con, "SELECT * FROM mtcars WHERE cyl = 4")
while (!dbHasCompleted(res)) {
  chunk <- dbFetch(res, n = 5)
  print(nrow(chunk))
}
#> [1] 5
#> [1] 5
#> [1] 1
dbClearResult(res)

It is good practice to register a call to dbClearResult() via on.exit() right after calling dbSendQuery() or dbSendStatement() . Release the resource

dbDisconnect(con)

Bind values to a parameterized/prepared statement

con <- dbConnect(RSQLite::SQLite(), ":memory:")

dbWriteTable(con, "iris", iris)

# Using the same query for different values
iris_result <- dbSendQuery(con, "SELECT * FROM iris WHERE [Petal.Width] > ?")
iris_result # status before bind
#> <SQLiteResult>
#>   SQL  SELECT * FROM iris WHERE [Petal.Width] > ?
#>   ROWS Fetched: 0 [incomplete]
#>        Changed: NA

dbBind(iris_result, list(2.3))
iris_result # status after bind
#> <SQLiteResult>
#>   SQL  SELECT * FROM iris WHERE [Petal.Width] > ?
#>   ROWS Fetched: 0 [incomplete]
#>        Changed: 0
dbFetch(iris_result, n = 2)
#>   Sepal.Length Sepal.Width Petal.Length Petal.Width   Species
#> 1          6.3         3.3          6.0         2.5 virginica
#> 2          7.2         3.6          6.1         2.5 virginica
dbHasCompleted(iris_result)
#> [1] FALSE
dbFetch(iris_result, n = -1)
#>   Sepal.Length Sepal.Width Petal.Length Petal.Width   Species
#> 1          5.8         2.8          5.1         2.4 virginica
#> 2          6.3         3.4          5.6         2.4 virginica
#> 3          6.7         3.1          5.6         2.4 virginica
#> 4          6.7         3.3          5.7         2.5 virginica
dbHasCompleted(iris_result)
#> [1] TRUE
iris_result # status after fetch
#> <SQLiteResult>
#>   SQL  SELECT * FROM iris WHERE [Petal.Width] > ?
#>   ROWS Fetched: 6 [complete]
#>        Changed: 0

# Bind with another parameter
dbBind(iris_result, list(3))
dbFetch(iris_result)
#> [1] Sepal.Length Sepal.Width  Petal.Length Petal.Width  Species     
#> <0 rows> (or 0-length row.names)

# Release resource
dbClearResult(iris_result)
iris_result
#> <SQLiteResult>
#> EXPIRED

# Executing the same statement with different values at once
iris_result <- dbSendStatement(con, "DELETE FROM iris WHERE [Species] = $species")
dbBind(iris_result, list(species = c("setosa", "versicolor", "unknown")))
dbGetRowsAffected(iris_result)
#> [1] 100

dbClearResult(iris_result)

nrow(dbReadTable(con, "iris"))
#> [1] 50


dbDisconnect(con)