Can you help me? Tell me where the error is. I am trying to pull tables from SQLite to R. At the beginning, he did not read the DB, but then he opened it, but he still does not see the table.
> library('RSQLite')
> require(SQLite)
Loading required package: SQLite
Warning message:
In library(package, lib.loc = lib.loc, character.only = TRUE, logical.return = TRUE, :
there is no package called ‘SQLite’
> drv <- dbDriver("SQLite")
> require('RSQLite')
> dvr <- dbDriver('RSQLite')
Error: Couldn't find driver RSQLite. Looked in:
* global namespace
* in package called RSQLite
* in package called RRSQLite
> View(drv)
> con <- dbConnect(drv, dbname= 'test.db')
> dbListTables(con)
character(0)
> dbReadTable(con, 'name')
Error: no such table: name
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