Menu
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.
554
sqlite-utils can now insert data from binary files, and datasette-media can serve content over HTTP that originated as binary BLOBs in a database file.
A blob is a SQLite datatype representing a sequence of bytes. It can be zero or more bytes in size. SQLite blobs have an absolute maximum size of 2GB and a default maximum size of 1GB. An alternate approach to using blobs is to store the data in files and store the filename in the database.
In SQLite, the sqlite3 command is used to create a new database. Syntax: sqlite3 DatabaseName. db.
Select SQLite from the list. Give a Connection name for your own internal reference. For Database , click Choose a File and then select the database file on your local machine to which you want to connect. Hit Connect and you're all set!
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
