I was just wondering whether it is possible to store R lists directly in a SQLite database (and of course retrieve them) via sqldf or RSQLite.
Asked
Active
Viewed 2,505 times
3
-
@agstudy Yes we can! However I am a bit worried about the performance of this hacky way as soon as the data gt bigger... – jack Sep 28 '14 at 14:19
-
@Jack I mean you can't store a list as it is in a classic data base. Ritchie kindly detail more this in his answer. – agstudy Sep 28 '14 at 14:31
-
This is not really a duplicate as it is `sqlite`-specific. – Davor Josipovic Oct 31 '17 at 00:50
2 Answers
4
See my other, better answer here:
https://stackoverflow.com/a/26098290/134830
Ragged objects like lists don't fit well with the rectangular nature of database tables. You have a few options to work around this limitation.
Convert your list to text using dput, and store that in a text column. Then dget it when you retrieve it back from the database.
You can also store your data as XML or JSON in a text column, and parse it when you retrieve it.
If you don't care about the contents being readable, you can also store it as a binary blob, using saveRDS (and retrieve it using readRDS).
All these methods have the downside that you can't do in-database analytics on them. They are only suitable for using the database as a data storage medium.
If you want to work with your data inside the database, you'll have to make it rectangular.
More detail on the RDS method. This is actually more of a pain than I first anticipated, since yes, you need to write to 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")
A possibly easier alternative to the RDS method.
bits <- charToRaw(paste0(deparse(l), collapse = ""))
# And the reverse:
eval(parse(text = rawToChar(bits)))
Community
- 1
- 1
Richie Cotton
- 118,240
- 47
- 247
- 360
-
Cool, thanks for the answer - readability is not an issue. I only need the DB as organized storage medium with concurrent access by many subprocesses. However I would need to save a file first before I can store it in the DB would I not? Could you give me a minimal example for the saveRDS approach with RSQLite? – jack Sep 28 '14 at 14:32
2
Okay, slightly wrong search before posting, c.f.
Storing R Objects in a relational database
Lets just elaborate 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!