7

I am unable to list tables for the sqlite database I am connecting to from R.

I setup the tables in my database (WBS_test1.db) using "DB Browser" https://sqlitebrowser.org/

Looking at this db in the command window, I am able to list the tables via .tables and view the data headers via .schema so I know they are (can also preview in DB Browser of course).

In R however..., I set my working directory, etc.

library(DBI)
library(RSQLite)
setwd(dir = "C:/here/there/folder")
sqlite <- dbDriver("SQLite")

I then connect to the database and attempt to list the tables and fields in one of the tables specifically

DBtest <- dbConnect(sqlite,"WBS_Test1.db")
dbListTables(DBtest)
dbListFields(DBtest, "WBS_CO2")

I get a "character(0)" returned, which from searching around looks like it's saying the tables are temporary.

I've also tried using the dplyr package

library(dplyr)

# connect to the sqlite file
test_db <- src_sqlite("C:/SQLite/WBS_test.db", create = TRUE)
src_tbls(test_db)

This again returns a "character(0)"

I have no prior experience with SQLite and modest experience in R so I'm probably missing something simple but I can't figure it out. Suggestions??? Maybe I'm not directing my wd in the correct location for the RSQLite package?

Thanks!

dphuber
  • 71
  • 1
  • 3
  • 1
    FYI: you can remove `library(RSQLite)` and use of `dbDriver` if you do `cbDonnect(RSQLite::SQLite(), ...)`. No difference in performance, intent, safety, etc, just reduced code. – r2evans Jul 16 '18 at 22:25
  • 1
    You are using the `DBI` and `dplyr` functions correctly, so your database is suspect. How confident are you that the sqlite file contains tables and your data? For instance, I did `con <- dbConnect(RSQLite::SQLite(), "foo.sqlite")`, `dbWriteTable(con, "mt", mtcars)`, and `dbListTables(con)` (returned `[1] "mt"`) without issue, effectively what you are doing above. – r2evans Jul 16 '18 at 22:32
  • I tested your code and it is working correctly. Something wrong with your `.db`. `closeAllConnections()` `rm(list=ls())` `ls()` `library(DBI)` `con <- dbDriver("SQLite")` `DBtest <- dbConnect(con,"survey.db")` `dbListTables(DBtest)` `[1] "Person" "Site" "Survey" "Visited"` `dbListFields(DBtest, "Site")` `[1] "name" "lat" "long"` – flamenco Jul 16 '18 at 22:39
  • 1
    Check the database without R. In shell or cmd go to the directory containng the SQLite database and then run `sqlite3 WBS_test.db` (assuming you have installed the sqlite3 command line program) and then when you get the sqlite prompt try `select * from sqlite_master;` . – G. Grothendieck Jul 17 '18 at 00:01
  • @r2evans et al., thanks for double checking my R code. Glad to see I mostly have that correct. It does appear my .db is the issue but I still can's see where (see response to G. Grothendieck) – dphuber Jul 17 '18 at 15:30
  • @G.Grothendieck I had checked in cmd but double checked doing exactly what you said and I get 5 tables with headers listed, don't see an issue there. Could admin-privileges be interfering? Or again, am is the wd defaulting to somewhere else? – dphuber Jul 17 '18 at 15:36
  • SQLite doesn't require or even benefit from admin privileges (except for whether you have write-access to the directory, which is fs-based, not db-based). Does the file look like it contains data? That is, is the file size representative of the data volume and does it increase while building it? – r2evans Jul 17 '18 at 16:08
  • @r2evans yep, .db is about 37MB. Here is the code for one of my tables: CREATE TABLE `WBS_CO2` ( `Recod_No` NUMERIC, `Year` NUMERIC, `Jday` NUMERIC, `Hour` NUMERIC, `Minute` NUMERIC, `Datetime` TEXT, `Site_ID` TEXT, `cfx006s` NUMERIC, `cfx006i` NUMERIC, `cfx026s` NUMERIC, `cfx026i` NUMERIC, `cfx041s` NUMERIC, `cfx044i` NUMERIC, `cfx063s` NUMERIC, `cfx063i` NUMERIC, PRIMARY KEY(`Recod_No`) ); – dphuber Jul 17 '18 at 17:52
  • I think SQLite deals with multi-access just fine, but do you have anything else using the database that might be locking it? You can try a "clean" test by rebooting or copying the db to another computer and trying it in R there. (I know nothing about the app you're using, perhaps it is locking the file?) – r2evans Jul 17 '18 at 18:31

0 Answers0