Menu
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 ?
958
A BLOB (large binary object) is an SQLite data type that stores large objects, typically large files such as images, music, videos, documents, pdf, etc. We need to convert our files and images into binary data (byte array in Python) to store it into SQLite database.
To dump a database into a file, you use the . dump command. The . dump command converts the entire structure and data of an SQLite database into a single text 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.
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" 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
