R connection to sqlite – Stackify

SQLite is a file level database, hence to reference it requires a full directory path. No where do you specify the working directory or a full path in the file name.

By default, R will use the current working directory contained in getwd(). If database is not contained in this folder, then connection error will emerge. You can change working directory with setwd().

By the way, you reference both packages but are connecting to SQLite with the dplyr package using src_sqlite, not with RSQLite.

RSQLite Connection

library(RSQLite)

setwd("/Path/To/Database/Folder")
sqlite <- dbDriver("SQLite")
conn <- dbConnect(sqlite,"my_db.sqlite3")

DPLYR Connection

library(dplyr)

setwd("/Path/To/Database/Folder")
db <- src_sqlite("my_db.sqlite3", create = TRUE)

You might not want to call both libraries together to avoid conflict of same named functions.

As stated in RSQLite’s vignette:

RSQLite is a DBI-compatible interface which means you primarily use functions defined in the DBI package, so you should always start by loading DBI, not RSQLite:

After loading DBI in your environment, you can connect to an existing database or create a new one:

dbh <- dbConnect(RSQLite::SQLite(), "mydb.sqlite")
dbDisconnect(dbh)
#> [1] TRUE
unlink("mydb.sqlite")

You will find more examples in the above link to RSQLite’s vignette. I have also some examples in my blog. I hope this helps! 🙂

Read more here: Source link