[R] RSQLite - How to express(or save) a dataframe as an output?

[Michael Bedward]

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