SQLite BLOB literals

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

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:

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

Related

Created 2020-07-29T14:49:43-07:00, updated 2020-07-29T15:07:04-07:00 · History · Edit