Unable to connect to database using RSQLite

6
votes

So I'm trying to access my DB file without success. Here is my script:

library(DBI)

library(sqldf)

drv <- dbDriver("SQLite")

con <- dbConnect(drv, dbname = "database.sqlite")

and here is the error:

drv <- dbDriver("SQLite")

con <- dbConnect(drv, dbname = "database.sqlite")

Error in rsqlite_connect(dbname, loadable.extensions, flags, vfs) : Could not connect to database: unable to open database file

I've checked, of course, and made sure that I've installed the packages correctly and that my working directory is set.

4
rdatabasesqlite
file.exists("database.sqlite") returns TRUE? - RoyalTS
@RoyalTS Doubt it, when I try to connect to a non-existent SQLite DB I find that R silently creates a new one. - Spacedman
> file.exists("database.sqlite") [1] TRUE file exists.. - Ohad_D

4 Answers

7
votes

I have solved my problem, and its a bit embarrassing:

I've saved my file on my desktop. since my OS in installed in my native language (Hebrew) the file path had one Hebrew word in it, and while that doesn't pose a problem with reading tables into R, it does pose a problem to the SQL connection.

solving it was easy - I've saved the file in a new folder on my hard drive (c:\database), set as working dir, and everything worked fine.

4
votes

I can duplicate this error in two ways:

  • The file exists but you don't have permission to open it

This could be because of operating system permissions. Check your permissions.

  • The file doesn't exist and you don't have permission to create it.

If SQLite is asked to open a database file that doesn't exist, it tries to create it. If this fails, you get that error message. This will fail if the path to the DB file (in this case the current working directory) does not let you create files. Check your permissions.

Note that if the file does exist but is corrupted, I get a different error:

> con <- dbConnect(drv, dbname = "database.sqlite")
Error in rsqlite_send_query(conn@ptr, statement) : 
  file is encrypted or is not a database
>

so that's probably not your problem.

1
votes

I also had this issue. I double checked the path and spotted the mistake there. Once I provided the correct path, the connection worked.

Thanks for this post. It draw my attention to double check if the path was correct.

0
votes

I ran into a similar problem and the solution was to use forward slashes when issuing dbConnect. I had a working directory defined as "C:\ ..." which worked fine in the rest of my code but not when I tried to open the file with dbConnect.