Strings with null bytes inside sqlite

[Pierre Quentel]

I want to store strings in a sqlite database, but my strings may contain
null bytes and sqlite can't handle this. What is the best way to solve the
problem (the fastest in execution time and / or the one that produces the
least additional bytes) : base64, or is there a better solution ?

Thanks,
Pierre

[Jeff Epler]

It depends on the structure of the string, but using
s.encode("string-escape") / e.decode("string-escape") may give better
performance and better storage characteristics (if your data is mostly
ASCII with a few NULs included). If chr(0) is the only problem value,
then creating your own encoder/decoder may be better, translating
'\0' -> '\\0' and '\\' -> '\\\\'.

Jeff

[Gerhard Häring]

Jeff Epler wrote:

It depends on the structure of the string, but using
s.encode("string-escape") / e.decode("string-escape") may give better
performance and better storage characteristics (if your data is mostly
ASCII with a few NULs included). If chr(0) is the only problem value,
then creating your own encoder/decoder may be better, translating
'\0' -> '\\0' and '\\' -> '\\\\'.

No need to mess around with this yourself, as PySQLite provides native support for
binary data:

import sqlite
cx = sqlite.connect(":memory:")
cu = cx.cursor()
cu.execute("create table test(b binary)")
bindata = "".join([chr(x) for x in range(10)])
cu.execute("insert into test(b) values (%s)", (sqlite.Binary(bindata),))
cu.execute("select b from test")
row = cu.fetchone()
row[0] == bindata

True

The PySQLite binary type uses a highly space efficient algorithm from the SQLite
author to encode chr(0) characters.

Yet another undocumented feature, I suppose. Unfortunately, I'm still offline at
home due to moving to a new appartment so the next release will have to wait even
longer.

-- Gerhard