Re: [R] Serialize data.frame to database
Hi Jeff, I think you are more right than me. I have not much experience with R-specific objects and Databases. I just know, that for languages like Java, C, etc. this process is a usual one for storing for example matrices (in case of course that I do not have to access specific elements inside of the matrix but only the matrix as a whole). Thank you for the tip with the R-sig-DB list. I switch over to this list. Best Simon On Jul 16, 2013, at 2:34 AM, Jeff Newmiller jdnew...@dcn.davis.ca.us wrote: 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 NewmillerThe . . Go Live... DCN:jdnew...@dcn.davis.ca.usBasics: ##.#. ##.#. Live Go... Live: OO#.. Dead: OO#.. Playing Research Engineer (Solar/BatteriesO.O#. #.O#. with /Software/Embedded Controllers) .OO#. .OO#. rocks...1k --- Sent from my phone. Please excuse my brevity. Simon Zehnder szehn...@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@\024ff@\023\x99\x99\x99\x99\x99\x9a@\022\xcc\xcc\xcc\xcc\xcc\xcd@\022ff@\024\0\0\0\0\0\0@\025\x99\x99\x99\x99\x99\x9a@\022ff@\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@\02333@\02333@\02133@\02733@\026\xcc\xcc\xcc\xcc\xcc\xcd@\025\x99\x99\x99\x99\x99\x9a@\024ff@\026\xcc\xcc\xcc\xcc\xcc\xcd@\024ff@\025\x99\x99\x99\x99\x99\x9a@\024ff@\022ff@\024ff@\02333@\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@\02333@\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@\024ff@\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@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. __ R-help@r-project.org mailing list
Re: [R] 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@\024ff@\023\x99\x99\x99\x99\x99\x9a@\022\xcc\xcc\xcc\xcc\xcc\xcd@\022ff@\024\0\0\0\0\0\0@\025\x99\x99\x99\x99\x99\x9a@\022ff@\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@\02333@\02333@\02133@\02733@\026\xcc\xcc\xcc\xcc\xcc\xcd@\025\x99\x99\x99\x99\x99\x9a@\024ff@\026\xcc\xcc\xcc\xcc\xcc\xcd@\024ff@\025\x99\x99\x99\x99\x99\x9a@\024ff@\022ff@\024ff@\02333@\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@\02333@\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@\024ff@\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@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/ __ 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.
Re: [R] Serialize data.frame to database
Hi Rainer, dbWriteTable is a nice function but in my case I need something that can actually save a dataframe in one row of a table. That is why I want to serialize my data.frame. Best Simon On Jul 16, 2013, at 3:05 PM, Rainer Schuermann rainer.schuerm...@gmx.net wrote: 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@\024ff@\023\x99\x99\x99\x99\x99\x9a@\022\xcc\xcc\xcc\xcc\xcc\xcd@\022ff@\024\0\0\0\0\0\0@\025\x99\x99\x99\x99\x99\x9a@\022ff@\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@\02333@\02333@\02133@\02733@\026\xcc\xcc\xcc\xcc\xcc\xcd@\025\x99\x99\x99\x99\x99\x9a@\024ff@\026\xcc\xcc\xcc\xcc\xcc\xcd@\024ff@\025\x99\x99\x99\x99\x99\x9a@\024ff@\022ff@\024ff@\02333@\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@\02333@\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@\024ff@\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@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/ __ 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. __ 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.
[R] Serialize data.frame to database
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@\024ff@\023\x99\x99\x99\x99\x99\x9a@\022\xcc\xcc\xcc\xcc\xcc\xcd@\022ff@\024\0\0\0\0\0\0@\025\x99\x99\x99\x99\x99\x9a@\022ff@\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@\02333@\02333@\02133@\02733@\026\xcc\xcc\xcc\xcc\xcc\xcd@\025\x99\x99\x99\x99\x99\x9a@\024ff@\026\xcc\xcc\xcc\xcc\xcc\xcd@\024ff@\025\x99\x99\x99\x99\x99\x9a@\024ff@\022ff@\024ff@\02333@\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@\02333@\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@\024ff@\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@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.
Re: [R] Serialize data.frame to database
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 NewmillerThe . . Go Live... DCN:jdnew...@dcn.davis.ca.usBasics: ##.#. ##.#. Live Go... Live: OO#.. Dead: OO#.. Playing Research Engineer (Solar/BatteriesO.O#. #.O#. with /Software/Embedded Controllers) .OO#. .OO#. rocks...1k --- Sent from my phone. Please excuse my brevity. Simon Zehnder szehn...@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@\024ff@\023\x99\x99\x99\x99\x99\x9a@\022\xcc\xcc\xcc\xcc\xcc\xcd@\022ff@\024\0\0\0\0\0\0@\025\x99\x99\x99\x99\x99\x9a@\022ff@\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@\02333@\02333@\02133@\02733@\026\xcc\xcc\xcc\xcc\xcc\xcd@\025\x99\x99\x99\x99\x99\x9a@\024ff@\026\xcc\xcc\xcc\xcc\xcc\xcd@\024ff@\025\x99\x99\x99\x99\x99\x9a@\024ff@\022ff@\024ff@\02333@\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@\02333@\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@\024ff@\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@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. __ 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.
