CODEWITHSUNDEEP
linuxmacosbashsqlite
Jim
Jim

Reputation: 19552

Exporting all blobs from sqlite to a files

Using this command: 

sqlite3 my.db "SELECT writefile('object0.jpg', MyBlob) FROM MyTable WHERE id = 1"

I can save an image stored as blob in a table in my db.
How can I alter this so that all the images are saved instead of just 1?

Upvotes: 4

Views: 2766

Answers (2)

CL.
CL.

Reputation: 180020

Use SQL string concatenation (||) to construct the file names:

sqlite3 my.db "SELECT writefile('object' || id || '.jpg', MyBlob) FROM MyTable"

Upvotes: 7

nsilent22
nsilent22

Reputation: 2863

If a two-step, naive solution is an option, you could do:

sqlite3 my.db "SELECT id from MyTable" > /tmp/id.list

for getting the list of all ids, then

cat /tmp/id.list | while read id; do echo SELECT writefile\(\'object$id.jpg\', MyBlob\) FROM MyTable WHERE id = $id\;; done | sqlite3 my.db

for creating and executing commands for storing all the blobs.

Upvotes: 3

Related Questions