SQLite BLOB literals#

I wanted to construct a string of SQL that would return a blob value:

1
select 'binary-data' as content, 'x.jpg' as content_filename

This was while writing a unit test for datasette-media - for issue #19. I used it in the test here.

The SQLite documentation for Literal values explains how to do this:

BLOB literals are string literals containing hexadecimal data and preceded by a single “x” or “X” character. Example: X’53514C697465’

In Python 3 you can generate the hexadecimal representation of any byte string using b'...'.hex()

So my solution looked like this:

1
jpeg_bytes = open("content.jpg", "rb").read()
2
sql = "select X'{}' as content, 'x.jpg' as content_filename".format(jpeg_bytes.hex())