Search code examples
databasersqlitersqlite

How to find DB created in SQLite

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?

Solution

  • 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.