How to store R objects (lists) in SQLite database?

I'm just wondering if it is possible to store R lists directly in a SQLite database (and of course retrieve them) via sqldf or RSQLite.


source to share

2 answers

Look at my other, better answer here: / ...

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")


Possibly a simpler alternative to the RDS method.

bits <- charToRaw(paste0(deparse(l), collapse = ""))

# And the reverse:
eval(parse(text = rawToChar(bits)))




Ok, a little wrong search before submitting, cf

Storing R Objects in a Relational Database

Lets just develop a minimal working example:

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!



All Articles