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]

Re: Strings with null bytes inside sqlite

It depends on the structure of the string, but using
s.encode("strin g-escape") / e.decode("strin g-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]

Re: Strings with null bytes inside sqlite

Jeff Epler wrote:[color=blue]
> It depends on the structure of the string, but using
> s.encode("strin g-escape") / e.decode("strin g-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 '\\' -> '\\\\'.[/color]

No need to mess around with this yourself, as PySQLite provides native support for
binary data:
[color=blue][color=green][color=darkred]
>>> import sqlite
>>> cx = sqlite.connect( ":memory:")
>>> cu = cx.cursor()
>>> cu.execute("cre ate table test(b binary)")
>>> bindata = "".join([chr(x) for x in range(10)])
>>> cu.execute("ins ert into test(b) values (%s)", (sqlite.Binary( bindata),))
>>> cu.execute("sel ect b from test")
>>> row = cu.fetchone()
>>> row[0] == bindata[/color][/color][/color]
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