[R] Serialize data.frame to database

Jeff Newmiller jdnewmil at dcn.davis.CA.us
Tue Jul 16 02:34:50 CEST 2013

I could be wrong, but I would guess that doing what you are describing is very unusual. Most of the time the data frame is mapped to a table in the database so the rows can be searched. Storing data frames as BLOBs really seems odd.

Note that there is an R-sig-db mailing list for questions of this type.
Jeff Newmiller                        The     .....       .....  Go Live...
DCN:<jdnewmil at dcn.davis.ca.us>        Basics: ##.#.       ##.#.  Live Go...
                                      Live:   OO#.. Dead: OO#..  Playing
Research Engineer (Solar/Batteries            O.O#.       #.O#.  with
/Software/Embedded Controllers)               .OO#.       .OO#.  rocks...1k
Sent from my phone. Please excuse my brevity.

Simon Zehnder <szehnder at uni-bonn.de> wrote:

>Dear R-Users,
>I need a very fast and reliable database solution so I try to serialize
>a data.frame (to binary data) and to store this data to an SQLite
>This is what I tried to do:
>con <- dbDriver("SQLite")
>db <- dbConnect(con, "test")
>dbSendQuery(db, 'CREATE TABLE frames("simID" INT, "data" BLOB)')
>data.bin <- serialize(iris, NULL, ascii = FALSE)
>dbSendQuery(db, paste("INSERT INTO frames VALUES(1, X'", data.bin,
>"')", sep = ""))
>data.bin2 <- dbGetQuery(db, "SELECT DATA FROM frames WHERE simID = 1")
>  data
>1   58
>So, only the first entry of data.bin is saved to the database. I tried
>to first convert the binary data to raw data:
>data.raw <- rawToChar(data.bin)
>Error in rawToChar(data.bin) :
>embedded nul in string:
> 0\0
>I don't know what this error should tell me. Then I tried to use the
>ASCII format
>data.ascii <- serialize(iris, NULL, ascii = TRUE)
>data.raw <- rawToChar(data.ascii)
>dbSendQuery(db, "DELETE FROM frames")
>dbSendQuery(db, paste("INSERT INTO frames VALUES(1, X'", data.raw,
>"')", sep = ""))
>Error in sqliteExecStatement(conn, statement, ...) :
>  RS-DBI driver: (error in statement: unrecognized token: "X'A
>This also does not work. It seems the driver does not deal that nicely
>with the regular INSERT query for BLOB objects in SQLite. Then I used a
>simpler way:
>dbSendQuery(db, "DELETE FROM frames")
>dbSendQuery(db, "DROP TABLE frames")
>dbSendQuery(db, 'CREATE TABLE frames("simID" INT, "data" TEXT DEFAULT
>dbSendQuery(db, paste("INSERT INTO frames VALUES(1, '", data.raw, "')",
>sep = ""))
>data.bin2 <- dbGetQuery(db, "SELECT data FROM frames WHERE simID = 1")
>Nice, that worked. Now I want to unserialize the data:
>Error in unserialize(data.bin2) : 'connection' must be a connection
>unserialize(data.bin2[1, 'data'])
>Error in unserialize(data.bin2[1, "data"]) :
>  character vectors are no longer accepted by unserialize()
>I feel a little stuck here, but I am very sure, that converting
>data.frames to binary data and storing them to a database is not that
>unusual. So I hope somebody has already done this and could give me the
>missing piece.
>R-help at r-project.org mailing list
>PLEASE do read the posting guide
>and provide commented, minimal, self-contained, reproducible code.

More information about the R-help mailing list