如何在bash中将二进制数据插入sqlite3数据库？

Question

我希望在bash脚本中向sqlite3数据库插入二进制数据(png、jpg、gif等)。
我使用独立的二进制文件sqlite3。我该如何编写SQL语句？
感谢您的帮助。

- kev

---

我不确定命令行工具是否允许您插入任意数据。我猜您可以先对数据进行base64编码。 - larsks

用Python编写的愚蠢绕过方法，将SQLite3的blob写入标准输出：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 ... 通过Python插入blob会类似；）但这很愚蠢，因为它很慢 - undefined

Answer 1

正如我在对 @sixfeetsix 的回答评论中提到的那样，插入数据只是问题的一半。一旦数据被插入，您需要将其取回。我们可以使用 xxd 来完成此操作。

#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 

- David Souther

Answer 2

这里有一种方法可以实现。将文件test.jpg 转换成 sqlite 二进制文本格式，然后将其插入到名为 foo 的表格中，该表格属于数据库 foodb 。

[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

编辑

在这里，我们可以看到数据以“全保真”存储，因为 .jpg 文件可以恢复：

[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

此外，这种方法在空间利用方面非常高效，因为它使用sqlite的BLOB类型来存储.jpg文件。它不会像使用base64编码一样将图像转换成字符串。

[someone@somewhere tmp]$ ll foodb 
-rw-r--r-- 1 someone someone 622592 Apr 28 16:37 foodb

- user610650

---

这行不通 :( 我知道它不会工作，因为我在20分钟前想发布它，但无法让一些测试案例工作。具体来说，如果查询该列，结果将在第一个0x00处被截断（在jpeg中，几乎肯定有一个NULL字节）。如果选择 quote(bar)，它将给出原始的x'...'。 - David Souther

@DavidSouther：在二进制数据中有0x00不是很正常吗？请去了解一下sqlite的BLOB类型。你期望的是以某种人类可读格式存储的数据，这种方式远不如空间效率高；而OP并没有提出这样的要求。 - user610650

我喜欢您的编辑 :) 现在唯一可以宣称的是，xxd比您的perl命令更短！再次看到了Unix方式的强大。 - David Souther

@DavidSouther：我只想指出，OP询问的是插入问题。 - user610650

@kev：作为hexdump的替代品？ - user610650