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

 

[=?ISO-8859-1?Q?Gerhard_H=E4ring?=]

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