25

I have an SQLite database file exported from Scraperwiki with .sqlite file extension. How do I import it into R, presumably mapping the original database tables into separate data frames?

user4157124
  • 2,809
  • 13
  • 27
  • 42
psychemedia
  • 5,690
  • 7
  • 52
  • 84

3 Answers3

46

You could use the RSQLite package.

Some example code to store the whole data in data.frames:

library("RSQLite")

## connect to db
con <- dbConnect(drv=RSQLite::SQLite(), dbname="YOURSQLITEFILE")

## list all tables
tables <- dbListTables(con)

## exclude sqlite_sequence (contains table information)
tables <- tables[tables != "sqlite_sequence"]

lDataFrames <- vector("list", length=length(tables))

## create a data.frame for each table
for (i in seq(along=tables)) {
  lDataFrames[[i]] <- dbGetQuery(conn=con, statement=paste("SELECT * FROM '", tables[[i]], "'", sep=""))
}
sgibb
  • 25,396
  • 3
  • 68
  • 74
  • Thanks - I seem to have a problem now w/ my SQLite export files, seeming broken as far as the import goes (I see the tables but lDataFrames shows NULL items... I need to go and do some tests I think.. Sqlite src is at https://scraperwiki.com/scrapers/export_sqlite/f1_timing/ – psychemedia Mar 21 '12 at 15:42
  • @psychemedia Some of your table names contains "-". You have to quote the name of the table `SELECT * FROM 'TABLE-ONE'`. I updated my example code. – sgibb Mar 22 '12 at 06:22
  • Thanks... just spotted that now myself... Doh! – psychemedia Mar 23 '12 at 11:22
  • you may wanna change `con <- dbConnect(drv="SQLite", dbname="YOURSQLITEFILE")` to `con <- dbConnect(drv=SQLite(), dbname="YOURSQLITEFILE")` – four-eyes Sep 10 '15 at 09:42
  • furthermore, the for loop you wrote there does not store the data as `data.frames` but as a large list. Thats enough (under the for i in seq line) `lDataFrames[[i]]<- data.frame(dbGetQuery(con, "SELECT * FROM '", tables[[i]))` – four-eyes Sep 10 '15 at 10:22
  • @Chrissl: Mh, seems fine for me: `class(dbGetQuery(conn=con, statement=paste("SELECT * FROM '", tables[[1]], "'", sep="")))` shows `data.frame` – sgibb Sep 11 '15 at 09:59
  • did not work for me, but then I was not in a loop neither. Just suggesting :) – four-eyes Sep 11 '15 at 10:21
  • Remember to call `dbDisconnect()` when you finish working with a connection – robertspierre Jun 22 '22 at 10:44
6

To anyone else that comes across this post, a nice way to do the loop from the top answer using the purr library is:

lDataFrames <- map(tables, ~{
  dbGetQuery(conn=con, statement=paste("SELECT * FROM '", .x, "'", sep=""))
})

Also means you don't have to do:

lDataFrames <- vector("list", length=length(tables))
primaj
  • 93
  • 1
  • 3
1

Putting together sgibb's and primaj's answers, naming tables, and adding facility to retrieve all tables or a specific table:

getDatabaseTables <- function(dbname="YOURSQLITEFILE", tableName=NULL){
  library("RSQLite")
  con <- dbConnect(drv=RSQLite::SQLite(), dbname=dbname) # connect to db
  tables <- dbListTables(con) # list all table names

  if (is.null(tableName)){
    # get all tables
    lDataFrames <- map(tables, ~{ dbGetQuery(conn=con, statement=paste("SELECT * FROM '", .x, "'", sep="")) })
    # name tables
    names(lDataFrames) <- tables
    return (lDataFrames)
  }
  else{
    # get specific table
    return(dbGetQuery(conn=con, statement=paste("SELECT * FROM '", tableName, "'", sep="")))
  }
}

# get all tables
lDataFrames <- getDatabaseTables(dbname="YOURSQLITEFILE")

# get specific table
df <- getDatabaseTables(dbname="YOURSQLITEFILE", tableName="YOURTABLE")
HackJob99
  • 89
  • 5