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
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
>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
>
>______________________________________________
>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.
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 > > ______________________________________________ > 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.- - - - - Der NSA keine Chance: e-mail verschluesseln! http://www.gpg4win.org/
Seemingly Similar Threads
- virt-customize fail to inject firstboot script when running it from script.
- Reading JPEG file, converting to HEX
- Nil object in E1 capture the order
- [PATCH 1/2] Add type checking, support integers as value
- Re: virt-customize fail to inject firstboot script when running it from script.