How to store R objects (lists) in SQLite database?
Look at my other, better answer here:
fooobar.com/questions/733104 / ...
Torn objects like lists don't fit well with the rectangular nature of database tables. You have several ways to work around this limitation.
Convert the list to text with dput
and save it in the text column. Then dget
this is when you return it from the database.
You can also store your data as XML or JSON in a text column and parse it as you retrieve it.
If you are not interested in reading the content, you can also store it as a binary blob using saveRDS
(and retrieve it with readRDS
).
All of these methods have a drawback that you cannot do in analyzing the database on them. They are only suitable for using a database as a data carrier.
If you want to work with your data inside a database, you need to make it rectangular .
Learn more about the RDS method. This is actually more pain than I thought, since yes, you need to write to a file.
l <- list(x = 1:5, y = letters)
saveRDS(l, "tmp")
bits <- readBin("tmp", "raw", 1000)
# Then send the raw data to the DB
# On retrieval reverse the process
writeBin(x, "tmp")
readRDS("tmp")
Possibly a simpler alternative to the RDS method.
bits <- charToRaw(paste0(deparse(l), collapse = ""))
# And the reverse:
eval(parse(text = rawToChar(bits)))
source to share
Ok, a little wrong search before submitting, cf
Storing R Objects in a Relational Database
Lets just develop a minimal working example:
library(RSQLite)
db <- dbConnect(SQLite(), dbname = "test.sqlite")
dbSendQuery(conn = db, "CREATE TABLE test (ID INTEGER, DATA STRING)")
l <- list("super", "important", "stuff")
dbSendQuery(conn = db, sprintf("INSERT INTO test VALUES (1, '%s')",
rawToChar(serialize(l, NULL, TRUE))))
unserialize(charToRaw(dbReadTable(db, "test")$DATA))
happy coding!
source to share