python an sqlite objects

[Bryan Olson]

Gerhard Häring wrote:
> 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  [...]

Good advice for now, with Python 2.X. Python 3 resolves most of 
confusion with its distinction between the string type and the bytes 
types. The 3.x standard library sqlite3 module understands the 'bytes' 
and 'bytearray' types, and treats them appropriately.

Here's a runnable Python 3 demo:

     # Ensure that we're running Python 3 or later.
     import sys
     assert int(sys.version.split()[0].split('.')[0]) >= 3
     # If there's a better way to chek, please tell.

     # sqlite3 became part of the standard library as of Python 2.5.
     import sqlite3

     # Create an in-memory database and one table.
     db = sqlite3.connect(':memory:')
     db.execute('CREATE TABLE demo (num INTEGER, txt TEXT, bin BLOB)')

     # Create a bytes object containing every possible byte value.
     input_blob = bytes(range(256))

     # Demo the bytes object.
     assert len(input_blob) == 256
     for i in range(256):
         assert input_blob[i] == i

     # Insert a test record.
     # The input blob goes into both a TEXT field and a BLOB field
     db.execute('INSERT INTO demo (num, txt, bin) VALUES (?, ?, ?)',
             (42537, input_blob, input_blob))

     # Fetch back the one and only record.
     cursor = db.execute('SELECT num, txt, bin FROM demo')
     num, txt, blob = cursor.fetchone()

     # Verify that we got back what we stored.
     assert num == 42537
     assert type(txt) == type(blob) == bytes
     assert txt == blob == input_blob


If we change the input type from 'bytes' to 'bytearray', the demo still 
works in that the value selected back from the database is of type 
bytes. The database understands that the value is binary and not 
unicode, and he retrieved value is still the immutable bytes type, not 
bytearray.


Why doesn't the column's declared type, 'TEXT' versus 'BLOB', matter?

sqlite is type-loose, which its author considers a valuable feature and 
some us consider a defect. sqlite does not generally require that the 
value stored in a field (column) be of the field's declared type. When 
an inserted value's type does not match the field's declared type, most 
other database systems will try to convert the given object to the 
field's declared type. sqlite3 is different; it will try to make an 
exception to the field's declared type and store the object as the type 
with which it came in.


-- 
--Bryan