How to store images in SQLite database in Julia?

Question

I am using Searchlight in Genie Julia framework. How to store a image in SQLite database?

Answer 1

While usually it is better to store the path to the image, a solution is straightforward - just use BLOB and parametrized queries.

Consider the following table (note the BLOB type:

SQLite.execute(db, "CREATE TABLE IF NOT EXISTS Images
                                   (ID INTEGER PRIMARY KEY AUTOINCREMENT,
                                   name TEXT,
                                   imdata BLOB)
                                 ")

Suppose you have some image:

im0 = Images.load(raw"c:\temp\small.png")

This image can be simply stored by using parametrized query:

DBInterface.execute(db, "INSERT INTO Images (name, imdata) VALUES (?, ?)", ["somename", im0])

It can be simply retrieved:

julia> dat = DBInterface.execute(db, "SELECT * FROM Images") |> DataFrame
1×3 DataFrame
 Row ? ID     name      imdata
     ? Int64  String    Matrix
??????????????????????????????????????????????????????????
   1 ?     1  somename  RGB{N0f8}[RGB{N0f8}(1.0,0.498,0.…

And you can check that it has been correctly stored:

julia> dat.imdata[1] == im0
true