Serialize data.frame to database

Maybe a simple

dbWriteTable( db, "frames", iris )

does what you want?



On Monday 15 July 2013 23:43:18 Simon Zehnder 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 database. 

This is what I tried to do:

library(RSQLite)
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.bin2
 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: 'X\n\0\0\0\002\0\003\0\001\0\002\003\0\0\0\003\023\0\0\0\005\0\0\0\016\0\0\0\x96@\024ffffff@\023\x99\x99\x99\x99\x99\x9a@\022\xcc\xcc\xcc\xcc\xcc\xcd@\022ffffff@\024\0\0\0\0\0\0@\025\x99\x99\x99\x99\x99\x9a@\022ffffff@\024\0\0\0\0\0\0@\021\x99\x99\x99\x99\x99\x9a@\023\x99\x99\x99\x99\x99\x9a@\025\x99\x99\x99\x99\x99\x9a@\023333333@\023333333@\021333333@\027333333@\026\xcc\xcc\xcc\xcc\xcc\xcd@\025\x99\x99\x99\x99\x99\x9a@\024ffffff@\026\xcc\xcc\xcc\xcc\xcc\xcd@\024ffffff@\025\x99\x99\x99\x99\x99\x9a@\024ffffff@\022ffffff@\024ffffff@\023333333@\024\0\0\0\0\0\0@\024\0\0\0\0\0\0@\024\xcc\xcc\xcc\xcc\xcc\xcd@\024\xcc\xcc\xcc\xcc\xcc\xcd@\022\xcc\xcc\xcc\xcc\xcc\xcd@\023333333@\025\x99\x99\x99\x99\x99\x9a@\024\xcc\xcc\xcc\xcc\xcc\xcd@\026\0\0\0\0\0\0@\023\x99\x99\x99\x99\x99\x9a@\024\0\0\0\0\0\0@\026\0\0\0\0\0\0@\023\x99\x99\x99\x99\x99\x9a@\021\x99\x99\x99\x99\x99\x9a@\024ffffff@\024\0\0\0\0\0\0@\022\0\0\0\0\0\0@\021\x99\x99\x99\x99\x99\x9a@\024\0\0\0\!

0\!

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 NULL)')
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:

unserialize(data.bin2)
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.


Best

Simon

- - - - -

Der NSA keine Chance: e-mail verschluesseln!
http://www.gpg4win.org/