0

I'm using this code to load the data from DB ontime into the data frame in R.

library(RSQLite)
library(DBI)

ontime <- dbConnect(RSQLite::SQLite(), dbname = "ontime.sqlite3")
from_db <- function(sql) {
  dbGetQuery(ontime, sql)
}

from_db("select count(*), tailnum from ontime group by tailnum")

tails <- from_db("select distinct tailnum from ontime")

However, it seems that R cannot find DB ontime that I created from SQLite shell. 

Error in sqliteSendQuery(con, statement, bind.data) : 
  error in statement: no such table: ontime

I tried to search ontime on disk, but I didn't find it. I also double-checked that this DB exists by using select * from ontime command. So, where is this DB stored on disk and how can I find it?

Klausos Klausos
  • 15,308
  • 51
  • 135
  • 217

1 Answers1

1

SQLite databases are a single file on disk. In this case you already named it in your connection: "ontime.sqlite3".

The error message occurs because your query

select count(*), tailnum from ontime group by tailnum

is asking for data in a table named ontime (inside the ontime DB), and presumably there is no table with that name.

Richie Cotton
  • 118,240
  • 47
  • 247
  • 360
  • That's really strange. I created a table in SQLite shell, then closed the shell and reopened it again. Now I cannot find the table. Should I save all the tables with some "save" command before exiting the shell? – Klausos Klausos Mar 10 '15 at 11:47
  • Not sure what happened to you there, but I find the easiest way to work with SQLite is with Firefox SQLite manager. https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/ – Richie Cotton Mar 10 '15 at 11:58