[R] importing data to SQLite database with sqldf

[Stephen Tucker]

Thanks yet another time, Gabor -
I think I am slowly understanding - particularly I was confused by persistence of connections.

So starting with some parts of your example 12, 

##
gc()
rm(list=ls())
unlink("mydb")
sqldf("attach 'mydb' as new") 
irishead <- file("irishead.dat")
iristail <- file("iristail.dat")

If I just wanted to merge the two files within SQL and return some part of the result, I would do

sqldf('select count(*) from (select * from irishead 
union 
select * from iristail)',dbname="mydb")

and the tables exist in mydb only for the duration of the computation
> sqldf("select * from sqlite_master",dbname="mydb")$name
NULL
(but why is the size of mydb > 0 afterwards, if it contains no tables...?)

...is the above the same as 
sqldf('select count(*) from (select * from irishead 
union 
select * from iristail)',dbname=tempfile())

except that I don't create 'mydb'?

If I wanted to save the merged table (for use in a later session):

sqldf('create table fulliris as select * from irishead 
union 
select * from iristail',dbname="mydb")

> sqldf("select * from sqlite_master",dbname="mydb")$name
[1] fulltable
Levels: fulltable

If I want copies of all three tables,
sqldf(dbname="mydb")
sqldf('create table fulltable as select * from irishead 
union 
select * from iristail')
sqldf()

> sqldf("select * from sqlite_master",dbname="mydb")$name
[1] irishead  iristail  fulltable
Levels: fulltable irishead iristail

? ...I'll try to go figure a few more things out in the in the meantime (like using sep="\t" ?) and using connections with sqldf().

But thanks for the help!

Stephen

----- Original Message ----
From: Gabor Grothendieck <ggrothendieck at gmail.com>
To: Stephen Tucker <brown_emu at yahoo.com>
Cc: R-help <r-help at stat.math.ethz.ch>
Sent: Friday, February 20, 2009 5:22:09 AM
Subject: Re: [R] importing data to SQLite database with sqldf

Have just added an example 12 on the home page:

http://sqldf.googlecode.com

that shows an example.  Note use of notation
main.mytable to refer to an existing table in the
main database (as opposed to a data frame in R).

On Thu, Feb 19, 2009 at 11:55 PM, Stephen Tucker <brown_emu at yahoo.com> wrote:
> Hi all,
>
> I am attempting to learn SQL through sqldf...
>
> One task I am particularly interested in is merging separate
> (presumably large) files into a single table without loading these
> files into R as an intermediate step (by loading them into SQLite and
> merging them there).
>
> Taking a step back, I've considered these alternatives:
>
> 1) I know if I use straight SQLite commands I might use the 'IMPORT'
> or 'INSERT INTO' command, which is not terribly flexible...  (I can
> read large files line-by-line in Python and use the 'INSERT INTO'
> command, which is reasonably fast; I could do this in R as well but my
> experience with R's input/output is that it's much slower...? and
> sometimes setting up the table column definitions can be tedious if
> there are many variables).
>
> 2) dbWriteTable() with append=TRUE is very convenient except that it
> requires I load the data into R first...
>
> 3) sqldf's capability to put data directly into a database is
> something I'd like to work out.
>
> So in this case I have a series of tab-delimited text file with the
> first line being a header.
>
> For some reason I cannot seem to get it working.  Combining examples 6
> and 9 from the Google Code page (and R-help archives), I tried
>
> source("http://sqldf.googlecode.com/svn/trunk/R/sqldf.R")
> (do I need it for SQLite?)
> ##
> sqldf("attach 'mydb.db' as new")
> f <- file("myexample.txt")
> attr(f,"file.format") <- list(header=TRUE,sep="\t")
> sqldf("create table myexample as select * from f",
>      stringsAsFactors=FALSE,
>      dbname="mydb.db")
> ## or
> f <- file(fi)
> sqldf("create table myexample as select * from f",
>      stringsAsFactors=FALSE,file.format=list(header=TRUE,sep="\t"),
>      dbname="mydb.db")
> ##
> sqldf("select * from myexample",dbname="mydb.db")
> gives me tables with 0 rows and 0 columns...
>
> So in any case I have a few questions:
>
> === 1 ====
> Would this be scalable to files with few GBs of data in them (I guess
> I am uncertain of the underlying mechanism for transporting data from
> the .txt file to the .db file... I see there is a call the
> dbWriteTable() internally in sqldf but through the connection)?  And
> is there anything obviously doing wrong above?
>
> === 2 ===
> Since I cannot 'append' rows to existing tables in SQLite (or any SQL
> database), I think what I would have to do is to load each of the
> files into the database and then 'rbind' them into a new table using
> 'union all'? The following code is what I have (the part where I read
> in each file before dumping into the database file would hopefully be
> replaced by the method above, if it can be made to work).
>
> ## (1) create a database file
> sqldf("attach 'alltables.db' as new")
> ## (2) convenience function
> sql <- function(...) sqldf(...,dbname="alltables.db")
> ## (3) load data as separate tables
> for( fi in list.files(".","txt$") ) {
>  mydata <- read.delim(fi)
>  sql(sprintf("create table %s as select * from mydata",sub("\\.txt","",fi)))
> }
> rm(fi,mydata)
> ## (4) merge tables
> tablenames <- as.character(sql("select * from sqlite_master")$name)
> sql(paste("create table myfulltable as",
>          paste(sprintf("select * from %s",tablenames),
>                collapse=" union all ")))
> ## (5) delete separate tables since we have a merged one
> for( nm in tablenames ) sql(sprintf("drop table %s",nm))
>
> === 3 ===
> The following command
> sqldf("attach 'mydb.db' as new")
> DF <- read.delim(fi)
> sqldf("create table myexample as select * from DF",dbname="mydb.db")
>
> will usually create a .db file twice the size of the .txt file (for
> now I am playing with a files ~500KB so the .db files are around
> ~1MB). When I create a .db file using SQLite's 'import' command,
> RSQLite's dbWriteTable(), or inserting values from the same .txt file
> from Python's SQLite interface, I get .db files that are approximately
> the same size as the .txt file (~500KB). Is the larger file size for
> sqldf's method expected?
>
> Many thanks in advance!
>
> Stephen Tucker
>
> ______________________________________________
> R-help at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>