library(DBI)
mydb <- dbConnect(RSQLite::SQLite(), "my-db.sqlite")
dbDisconnect(mydb)
mydb <- dbConnect(RSQLite::SQLite(), "")
dbDisconnect(mydb)
mydb <- dbConnect(RSQLite::SQLite(), "")
dbWriteTable(mydb, "mtcars", mtcars)
dbWriteTable(mydb, "iris", iris)
dbListTables(mydb)
dbGetQuery(mydb, 'SELECT * FROM iris ')
dbGetQuery(mydb, 'SELECT * FROM mtcars LIMIT 5')
dbGetQuery(mydb, 'SELECT * FROM iris WHERE "Sepal.Length" < 4.6')
dbGetQuery(mydb, 'SELECT * FROM iris WHERE "Sepal.Length" < :x',
params = list(x = 4.6))
rs <- dbSendQuery(mydb, 'SELECT * FROM mtcars')
while (!dbHasCompleted(rs)) {
df <- dbFetch(rs, n = 10)
print("-----------------")
#print(nrow(df))
print(df)
}
dbClearResult(rs)
rs <- dbSendQuery(mydb, 'SELECT * FROM iris WHERE "Sepal.Length" < :x')
dbBind(rs, params = list(x = 4.5))
nrow(dbFetch(rs))
#> [1] 4
dbBind(rs, params = list(x = 4))
nrow(dbFetch(rs))
#> [1] 0
dbClearResult(rs)
rs <- dbSendQuery(mydb, 'SELECT * FROM iris WHERE "Sepal.Length" = :x')
dbBind(rs, params = list(x = seq(4, 4.4, by = 0.1)))
nrow(dbFetch(rs))
#> [1] 4
dbClearResult(rs)
dbExecute(mydb, 'DELETE FROM iris WHERE "Sepal.Length" < 4')
#> [1] 0
rs <- dbSendStatement(mydb, 'DELETE FROM iris WHERE "Sepal.Length" < :x')
dbBind(rs, params = list(x = 4.5))
dbGetRowsAffected(rs)
#> [1] 4
dbClearResult(rs)
https://cran.r-project.org/web/packages/RSQLite/vignettes/RSQLite.html
RSQLite
RSQLite is the easiest way to use a database from R because the package itself contains SQLite; no external software is needed. This vignette will walk you through the basics of using a SQLite database. RSQLite is a DBI-compatible interface which means you
cran.r-project.org
DB Browser for SQLite
DB Browser for SQLite The Official home of the DB Browser for SQLite Screenshot What it is DB Browser for SQLite (DB4S) is a high quality, visual, open source tool to create, design, and edit database files compatible with SQLite. DB4S is for users and dev
sqlitebrowser.org