I want to insert binary data(png,jpg,gif,etc) into a sqlite3 database within a bash script.
I use the standalone binary sqlite3. How can I write the SQL statement?
Thanks for your help.
2 Answers
As I mentioned in the comment on @sixfeetsix's answer, inserting the data is only half the problem. Once it's in, you'll need to get it back out. We can use xxd for this.
#A nice hubble image to work with.
echo 'http://asd.gsfc.nasa.gov/archive/hubble/Hubble_20th.jpg' > imageurl.txt
image=imageurl.txt
curl $image > image.jpg
#Insert the image, using hexdump to read the data into SQLite's BLOB literal syntax.
echo "create table images (image blob);" | sqlite3 images.db
echo "insert into images (image) values(x'$(hexdump -ve '1/1 "%0.2X"' image.jpg)');" | sqlite3 images.db 2>&1
#Select just the one image, then use xxd to convert from hex back to binary.
echo "select quote(image) from images limit 1 offset 0;" | sqlite3 images.db | tr -d "X'" | xxd -r -p > newimage.jpg
eog newimage.jpg
Comments
Here is one way to do it. The file test.jpg is inserted in the table foo of the database foodb after being hexdumped to the binary literal format of sqlite:
[someone@somewhere tmp]$ sqlite3 foodb "create table foo (bar blob);"
[someone@somewhere tmp]$ echo "insert into foo values (X'`hexdump -ve '1/1 "%.2x"' test.jpg`');" | sqlite3 foodb
EDIT
And here we see that the data is store in "full-fidelity" as the .jpg file can be restored:
[somneone@somewhere tmp]$ sqlite3 foodb "select quote(bar) from foo;" | perl -ne 's/([0-9a-f]{2})/print chr hex $1/gie' > bla.jpg
[somneone@somewhere tmp]$ ll *.jpg
-rw-rw-r-- 1 someone someone 618441 Apr 28 16:59 bla.jpg
-rw-rw-r-- 1 someone someone 618441 Apr 28 16:37 test.jpg
[someone@somewhere tmp]$ md5sum *.jpg
3237a2b76050f2780c592455b3414813 bla.jpg
3237a2b76050f2780c592455b3414813 test.jpg
Furthermore, this approach is space efficient as it store the .jpg using sqlite's BLOB type. It doesn't stringify the image using for example base64 encoding.
[someone@somewhere tmp]$ ll foodb
-rw-r--r-- 1 someone someone 622592 Apr 28 16:37 foodb
8 Comments
David Souther
This won't work :( I know it won't work, because I was going to post it 20 minutes ago, but can't get some test cases to work. Specifically, if you query that column, the results will be truncated at the first 0x00 (in jpeg, you're almost guaranteed to have a NULL byte somewhere). If you select
quote(bar) it will give you the original x'...'David Souther
It is a very normal thing. I am expecting, and indeed am correct in that sqlite correctly stores 0x00 in a blob column. The problem is when you run a
select on that column- the select will only return up to the first 0x00. That is the problem I was pointing out, and the fix is what we included in our edits by using select quote() and converting that hex back to the binary output.David Souther
I like your edits :) Now the only thing I can claim is that xxd is shorter than your perl command! Once again, we see the power of the Unix way.
|
python -c "import sys, sqlite3; sys.stdout.buffer.write(sqlite3.connect(sys.argv[1]).execute('select some_blob_column from some_table limit 1').fetchone()[0])" some_sqlite_database.db... inserting a blob via python would be similar ; ) but this is stupid, because its slow