Strings with null bytes inside sqlite

[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