R connection to sqlite
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:
library(DBI)
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] TRUEunlink("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! :)