How to dump a file stored in a sqlite database as a blob?

Question

I have a sqlite3 database. One column has the TEXT type, and contains blobs which I would like to save as file. Those are gzipped files.

The output of the command sqlite3 db.sqlite3 ".dump" is:

INSERT INTO "data" VALUES(1,'objects','object0.gz',X'1F8B080000000000000 [.. a few thousands of hexadecimal characters ..] F3F5EF')

How may I extract the binary data from the sqlite file to a file using the command line ?

Acnode

What kind of command line? – Isabellaisabelle

I mean by running sqlite3 in a terminal. – Acnode

Answer 1

sqlite3 cannot output binary data directly, so you have to convert the data to a hexdump, use cut to extract the hex digits from the blob literal, and use xxd (part of the vim package) to convert the hexdump back into binary:

sqlite3 my.db "SELECT quote(MyBlob) FROM MyTable WHERE id = 1;"  \
| cut -d\' -f2                                                   \
| xxd -r -p                                                      \
> object0.gz

With SQLite 3.8.6 or later, the command-line shell includes the fileio extension, which implements the writefile function:

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

Isabellaisabelle

cut -d\' -f2 accomplishes the same task and is a little easier to type than the sed statement above. – Pismire

Answer 2

You can use writefile if using the sqlite3 command line tool:

Example usage: select writefile('blob.bin', blob_column) from table where key='12345';

Cristoforo

This is the perfect answer. – Manuscript

Should be the accepted answer! – Doralin

Answer 3

In my case, I use "hex" instead of "quote" to retrieve image from database, and no need "cut" in the command pipe. For example:

sqlite3 fr.db "select hex(bmp) from reg where id=1" | xxd -r -p > 2.png

Fan

Answer 4

I had to make some minor changes on CL's answer, in order to make it work for me:

• The structure for the command that he is using does not have the database name in it, the syntax that I am using is something like:

  sqlite3 mydatabase.sqlite3 "Select quote(BlobField) From TableWithBlod Where StringKey = '1';" | ...
  

• The way he is using the cut command does not work in my machine. The correct way for me is:

  cut -d "'" -f2
  

So the final command would be something like:

sqlite3 mydatabase.sqlite3 "Select quote(BlobField) From TableWithBlod Where StringKey = '1';" | cut -d "'" -f2 | xxd -r -p > myfile.extension

And in my case:

sqlite3 osm-carto_z14_m8_m.mbtiles "select quote(images.tile_data) from images where images.tile_id = '1';" | cut -d "'" -f2 | xxd -r -p > image.png

Wristlet

BTW, in my case xxd could deal with the hexdump both with quotes or without quotes – Permanganate