R help - Jan 2011 - RSQLite - How to express(or save) a dataframe as an output?

Dear R helpers

Suppose following is an output due to some R process. I wish to save it as a
table in 'temp.db'

df <- data.frame(x = c(5, 4, 3, 11), y = c(25, 16, 9, 121))

    library(RSQLite)
    write('** Initializing','')

    drv <- dbDriver("SQLite", shared.cache = TRUE)
    con <- dbConnect(drv, dbname = "temp.db",
 loadable.extensions=TRUE)

    on.exit(dbUnloadDriver(drv))
    on.exit(dbDisconnect(con))

write('** Save output', '')

 dbBeginTransaction(con)

 dbGetPreparedQuery(con, "INSERT INTO output(df) VALUES (?)",
data.frame(output))

 dbCommit(con)
 
 
> 
 dbGetPreparedQuery(con,
                        "INSERT INTO output(df) VALUES (?)",
                         data.frame(output))


#
---------------------------------------------------------------------------------

I get following message

Error in dbGetPreparedQuery(con, "INSERT INTO output(df) VALUES (?)", 
:
  error in evaluating the argument 'bind.data' in selecting a method for
function 'dbGetPreparedQuery'
 


      
	[[alternative HTML version deleted]]

Hi Amelia,

You statement...

dbGetPreparedQuery(con, "INSERT INTO output(df) VALUES (?)",
data.frame(output))

...is the problem.

To insert an entire data.frame into the database use dbWriteTable.

To insert with debSendPreparedQuery or dbGetPreparedQuery, the number
of "?" in the VALUES specifier needs to be the same as the number of
fields that you are inserting values into (= the number of cols in
your data.frame).

For example, if you have a table "Foo" with fields Id, Name, Value,
you could do this...

mydf <- data.frame(id=1:3, name=c("foo1", "foo2",
"foo3"), value=rnorm(3))
dbGetPreparedQuery(con, "insert into Foo (values (?, ?, ?)", mydf)

which is equivalent to this...
dbGetPreparedQuery(con, "insert into Foo (Id, Name, Value) values (?,
?, ?)", mydf)

Hope that helps,
Michael

On 14 January 2011 18:19, Amelia Vettori <amelia_vettori at yahoo.co.nz>
wrote:
> Dear R helpers
>
> Suppose following is an output due to some R process. I wish to save it as
a table in 'temp.db'
>
> df <- data.frame(x = c(5, 4, 3, 11), y = c(25, 16, 9, 121))
>
> ?? ?library(RSQLite)
> ??? write('** Initializing','')
>
> ??? drv <- dbDriver("SQLite", shared.cache = TRUE)
> ??? con <- dbConnect(drv, dbname = "temp.db",
> ?loadable.extensions=TRUE)
>
> ??? on.exit(dbUnloadDriver(drv))
> ??? on.exit(dbDisconnect(con))
>
> write('** Save output', '')
>
> ?dbBeginTransaction(con)
>
> ?dbGetPreparedQuery(con, "INSERT INTO output(df) VALUES (?)",
data.frame(output))
>
> ?dbCommit(con)
>
>
>>
> ?dbGetPreparedQuery(con,
> ??????????????????????? "INSERT INTO output(df) VALUES (?)",
> ???????????????????????? data.frame(output))
>
>
> #
---------------------------------------------------------------------------------
>
> I get following message
>
> Error in dbGetPreparedQuery(con, "INSERT INTO output(df) VALUES
(?)",? :
> ? error in evaluating the argument 'bind.data' in selecting a
method for function 'dbGetPreparedQuery'
>
>
>
>
> ? ? ? ?[[alternative HTML version deleted]]
>
>
> ______________________________________________
> 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.
>
>

Hi.

I'm running R OS X GUI 1.35-dev Leopard build 64-bit.  When I run the
following code (snippet from a larger code) from the GUI I obtain 2 separate
*.pdf files as you would expect from the high-lighted code.  However, when I run
from Rscript (command-line), I only get the first one.  No errors appear in the
console log however I do get a 'null device' message that I don't
understand.  It's probably related but I have no clue how to debug this. 
Perhaps the second output file is not getting initialized?  I've tried a few
variations to see if I can unearth the cause but no joy so far.  Any suggestions
would be appreciated.

Thanks.
...

profiles.spl <- smooth.spline(x, y)
(profiles.spl)
x_pred = seq(1,as.integer(max(x)))
B = data.frame(predict(profiles.spl,x_pred))
pdf(file=paste("/Volumes/SLR_Data_001/USN_SERDP_SLR/data/level1/beach_profiles_Flick/",Filename,".pdf",sep=""))
caption = paste(aLocation," (",
aYear,".",aMonth,".",aDay,")",sep="")
credits = paste("splineWriter.R / hellyj@ucsd.edu / 20110120")
xrng = range(x)
yrng = range(y)
pred = qplot(x,y, data=B, xlab="Distance (m)", ylab = "Elevation
(m)", xlim=c(0,1000), ylim=c(-12,4))
pred + geom_text(aes(700,2,label=caption)) +
geom_text(aes(180,-12,label=credits),size=2.7)
dev.off() 
## Residual (Tukey Anscombe) plot:
pdf(file=paste("/Volumes/SLR_Data_001/USN_SERDP_SLR/data/level1/beach_profiles_Flick/",Filename,"TA.pdf",sep=""))
qplot(fitted(profiles.spl), residuals(profiles.spl))
dev.off()

...

--------------
John Helly, University of California, San Diego / San Diego Supercomputer Center
/ Scripps Institution of Oceanography / stonesteps (Skype) / stonesteps7 (iChat)
/ http://www.sdsc.edu/~hellyj
	[[alternative HTML version deleted]]

John,

The first thing I would do is create a simpler example, i.e., to help isolate
the issue. Here’s a simple example:

The contents of a file are:
--------------------------------------------
#! /usr/bin/Rscript

pdf(''test1.pdf'')
plot(1:10)
dev.off()

pdf(''test2.pdf'')
plot(10:1)
dev.off()
----------------------------------------------

With this file, I get both pdf files either way.

Since you’re using plotting functions from ggplot2, you may need to wrap print()
around the qplot() call in the second one. That is,

  print( qplot(fitted(profiles.spl), residuals(profiles.spl)) )

The ‘null device’ message is what dev.off() returns, as in this example:
> x11()
> dev.off()
null device
          1

So it’s not relevant. However, with my simple example above, I get the ‘null
device’ message twice when I run it as an Rscript.

What’s the first line of your file look like? Try including the     -- restore  
option, if you have not already:

#! /usr/bin/Rscript —restore

Your .RData file is not automatically loaded with Rscript, and the plot that
isn’t happening may depend on some object that is loaded from .RData. Although,
in that case, I would expect an error message.

-Don


On 1/20/11 6:53 PM, "John Helly" <hellyj@ucsd.edu> wrote:

Hi.

I''m running R OS X GUI 1.35-dev Leopard build 64-bit.  When I run the
following code (snippet from a larger code) from the GUI I obtain 2 separate
*.pdf files as you would expect from the high-lighted code.  However, when I run
from Rscript (command-line), I only get the first one.  No errors appear in the
console log however I do get a ''null device'' message that I
don''t understand.  It''s probably related but I have no clue
how to debug this.  Perhaps the second output file is not getting initialized? 
I''ve tried a few variations to see if I can unearth the cause but no
joy so far.  Any suggestions would be appreciated.

Thanks.
...

profiles.spl <- smooth.spline(x, y)
(profiles.spl)
x_pred = seq(1,as.integer(max(x)))
B = data.frame(predict(profiles.spl,x_pred))
pdf(file=paste("/Volumes/SLR_Data_001/USN_SERDP_SLR/data/level1/beach_profiles_Flick/",Filename,".pdf",sep=""))
caption = paste(aLocation," (",
aYear,".",aMonth,".",aDay,")",sep="")
credits = paste("splineWriter.R / hellyj@ucsd.edu / 20110120")
xrng = range(x)
yrng = range(y)
pred = qplot(x,y, data=B, xlab="Distance (m)", ylab = "Elevation
(m)", xlim=c(0,1000), ylim=c(-12,4))
pred + geom_text(aes(700,2,label=caption)) +
geom_text(aes(180,-12,label=credits),size=2.7)
dev.off()
## Residual (Tukey Anscombe) plot:
pdf(file=paste("/Volumes/SLR_Data_001/USN_SERDP_SLR/data/level1/beach_profiles_Flick/",Filename,"TA.pdf",sep=""))
qplot(fitted(profiles.spl), residuals(profiles.spl))
dev.off()

...

--------------
John Helly, University of California, San Diego / San Diego Supercomputer Center
/ Scripps Institution of Oceanography / stonesteps (Skype) / stonesteps7 (iChat)
/ http://www.sdsc.edu/~hellyj
 [[alternative HTML version deleted]]

______________________________________________
R-help@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.

--
Don MacQueen
Environmental Protection Department
Lawrence Livermore National Laboratory
925 423-1062

	[[alternative HTML version deleted]]