python an sqlite objects

[Gerhard Häring]

skip at pobox.com wrote:
>     azrael> is it possible to save a python object into a sqlite database as
>     azrael> an atribute of type BLOB
> 
> Sure.  Just pickle the object and save the resulting string.

Be sure to save it as BLOB, not TEXT.

Suppose you have serialized your object as Python bytestring.

serialized = ...
... .execute("insert into mytable(mycolumn) values (?)",
       (sqlite3.Binary(serialized),))

This way you will get a BLOB in the form of a Python buffer object when
you later select it from the database, which you can then deserialize to
a Python object.

If you don't go the BLOB way, you may get an exception, because SQLite
assumes all text is UTF-8 encoded, which it isn't necessarily when you
put arbitrary serialized strings into the database.

-- Gerhard