r does not read tables from sqlite

First, the code in the question can be reduced to many fewer lines, since most of it is “trying to find the package”. I’ll work this this relevant subset of the code:

con <- dbConnect(drv, dbname= 'test.db')
dbListTables(con)
# character(0)
dbReadTable(con, 'name')
# Error: no such table: name

While the output itself is fairly self-explanatory (character(0) indicates that there are no user tables in the database/file), the reason “why” is perhaps unintuitive. While we don’t know for certain, there is one quite believable reason why I think this is happening to you:

The file is not in R’s current working directory.

I think it is instructive to know that SQLite is the only “database type” (well, duckdb as well …) that when you connect, if the file does not already exist, it will be created for you as an empty database. It is further vexing that it does this completely silently; in hindsight, especially for new users of the package, it might be informative to have a create=FALSE option to RSQLite‘s implementation of dbConnect that errors (or at least warns) the user when this is a new file and therefore no tables exist.

Hindsight is 20/20, had you checked for the file-existence before trying to open it, you might have found that it did not exist, suggesting you should find it first. For instance, this might have been what you would have seen (but now will not see, for reasons I’ll say in a moment):

file.exists("test.db")
# [1] FALSE

The reason it will now likely return TRUE is that by connecting to a non-existent file, as I said earlier, it defaults to creating a new (empty) file. However, if you’ve done nothing more with this database connection (specifically inserting any data into any tables), then you might find this to be true:

file.info("test.db")
#        size isdir mode               mtime               ctime               atime
# test.db    0 FALSE  666 2020-11-05 15:53:21 2020-11-05 15:53:21 2020-11-05 15:53:21
#         exe
# test.db  no

Specifically, size is 0, indicating the file is empty. (Empty SQLite databases are truly zero-length files, though the moment you add anything it grows quite a bit for the standard SQLite admin tables and such.)

Ultimately, the problem in this case (I’m guessing) is that you are looking at "test.db" in one directory (or copied it there from a browser download directory), but your R is running in a different directory. While this answer is not the best place to address questions about working directories, you can find a little bit of information with

getwd()
# [1] "C:/Users/r2/StackOverflow"

and perhaps try to correlate that with the actual directory to which you saved that "test.db". For instance, one could use one of the following:

con <- dbConnect(drv, dbname= 'path/to/test.db')        # relative to this directory
con <- dbConnect(drv, dbname= '../../path/to/test.db')  # also relative, but not "below"
con <- dbConnect(drv, dbname= '/path/to/test.db')       # absolute path to that file

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top