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?

Are you sure you really need to store images in DB? For large binary blobs, we usually store file paths in DB. — Gnimuc, May 24 '22 at 09:58
usually it's done using BLOB but I am not sure how to use it. You can join Genie discord to ask this from Adrian: https://discord.gg/9zyZbD6J7H — abhimanyuaryan, May 24 '22 at 09:59
alternatively you can also use file paths(path to image in disk) — abhimanyuaryan, May 24 '22 at 09:59

score 2 · Answer 1 · answered May 24 '22 at 21:52

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

How to store images in SQLite database in Julia?

1 Answers1