-1

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
zx8754
  • 52,746
  • 12
  • 114
  • 209
kukulkan99
  • 3
  • 1
  • 1
    The package `SQLite` is not installed. Please use `install.package("SQLite")` to install it prior to loading it. – hannes101 Nov 04 '20 at 09:39
  • 1
    @hannes101 There is no package in CRAN named SQLite. The correct package is RSQLite and it is installed, since loading it gives no error. The issue is that table 'name' does not exist in the database 'test.db'. – neilfws Nov 04 '20 at 09:55
  • @neilfws I just looked a the first error in the code, but is `Error: Couldn't find driver RSQLite. Looked in:...` not also an issue? – hannes101 Nov 04 '20 at 10:21
  • 4
    There's a lot of trial and error going on in this code :) but I think the important thing is that eventually, the correct library is loaded (RSQLite) without error, and a connection is established without error. The problem would seem to be simply that the named table is not in the database. – neilfws Nov 04 '20 at 10:23
  • @neilfws yes, there were mistakes in the beginning. but in the end everything worked except reading the table. but it definitely exists. – kukulkan99 Nov 04 '20 at 10:45
  • @hannes101 yes, I downloaded the library. For some reason it's only earned after the second time – kukulkan99 Nov 04 '20 at 10:47
  • Darina, there is a lot of extraneous information in this question that does not seem to be about finding a table in a SQLite file. The first 14 lines of code here are completely unrelated to your difficulty of reading a table; I suggest you start with `con <- dbConnect(...)`. Tangentially, use `library`, or use `require` and check its return value; c.f., https://stackoverflow.com/a/51263513/3358272. – r2evans Nov 05 '20 at 23:50
  • Regardless, the output from `dbListTables` is very clear: there is nothing in that database. This can easily be explained away with the knowledge that it will "connect" to a database, and if the file is not found then a new one is created *with no indication*. My expectation is that had you done `file.exists("test.db")` before doing this, it would have returned `FALSE`, though it will now return `TRUE` (because you created it unknowingly). If you've done nothing further with the database connection, then `file.info("test.db")` might very well say the size is `0`, supporting my suspicion. – r2evans Nov 05 '20 at 23:54

1 Answers1

0

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
r2evans
  • 141,215
  • 6
  • 77
  • 149