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

• python A simple pattern for inlining binary content in a Python script - 2023-08-19
• bash Escaping a SQL query to use with curl and Datasette - 2020-12-08
• sqlite Saving an in-memory SQLite database to a file in Python - 2023-04-08
• sqlite Null case comparisons in SQLite - 2020-04-21
• python Using the sqlite3 Python module in Pyodide - Python WebAssembly - 2021-10-18
• zsh Passing command arguments using heredoc syntax - 2022-07-07
• sqlite Combining CTEs and VALUES in SQLite - 2023-01-29
• python Explicit file encodings using click.File - 2020-10-16
• bash Finding CSV files that start with a BOM using ripgrep - 2021-05-28
• sqlite Fixing broken text encodings with sqlite-transform and ftfy - 2021-01-18

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