5

I am unable to connect to sqlite from R. Sqlite3 is installed on the linux server and am able to create/modify. But R isn't connecting:

library(dplyr)
library(RSQLite)

> db <- src_sqlite("my_db.sqlite3", create = TRUE)
Error in .local(drv, ...) : Could not connect to database:
unable to open database file

I am able to connect to SQLite from the command line:

@ubuntu:~$ sqlite3
SQLite version 3.8.2 2013-12-06 14:53:30
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

Here is the session Info:

> sessionInfo()
R version 3.2.2 (2015-08-14)
Platform: x86_64-pc-linux-gnu (64-bit)
Running under: Ubuntu 14.04.1 LTS

locale:
 [1] LC_CTYPE=en_US.UTF-8       LC_NUMERIC=C              
 [3] LC_TIME=en_US.UTF-8        LC_COLLATE=en_US.UTF-8    
 [5] LC_MONETARY=en_US.UTF-8    LC_MESSAGES=en_US.UTF-8   
 [7] LC_PAPER=en_US.UTF-8       LC_NAME=C                 
 [9] LC_ADDRESS=C               LC_TELEPHONE=C            
[11] LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C       

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] RSQLite_1.0.0 DBI_0.3.1     dplyr_0.4.3  

loaded via a namespace (and not attached):
[1] magrittr_1.5   R6_2.1.1       assertthat_0.1 parallel_3.2.2 tools_3.2.2   
[6] Rcpp_0.12.1   
>
Νίκος Αντωνετσής
  • 65
  • 7
Antex
  • 1,364
  • 4
  • 18
  • 35

2 Answers2

6

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.

Parfait
  • 104,375
  • 17
  • 94
  • 125
  • Thanks for the explanation and responding. I am using the dplyr connection. although I didn't specifically set setwd(), am ok with the R picking the default path. I am experiencing this on Linux, on OS-X, I don't see this issue. I also hardwired the path with setwd(), just to make sure setting the path is not the issue, with the same result. db1 <- src_sqlite("db1.sqlite3", create = T) Error in .local(drv, ...) : Could not connect to database: unable to open database file – Antex Sep 13 '15 at 13:20
  • 1
    In Linux, you have to grant [read/write access](https://www.linux.com/learn/tutorials/760276-how-to-manage-file-and-folder-permissions-in-linux) to individual files and folders. Be sure to do so prior to R connection. – Parfait Sep 13 '15 at 14:00
1

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] 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! :)

Antonio
  • 11
  • 2