Exporting all blobs from sqlite to a files

Question

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?

Answer 1

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

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

Answer 2

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.