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
--
http://mail.python.org/mailman/listinfo/python-list
