Re: cPickled Data submission in Pysqlite

Sajjad Hussain wrote:
> Hi,
> 
> I have a pickled data that I want to submit into a
> column of my pysqlite. The column is of type BLOB (it
> could be anything). I can submit the pickled data
> without any problem, but when I retreive  it, I get
> the following error:
> 
> UnicodeDecodeError: 'utf8' codec can't decode bytes in
> position....

That means that the data is stored as TEXT in SQLite, not as BLOB. To make
sure it gets stored as BLOB, use buffer() or sqlite.Binary() on the string
with the pickled object.

> With certain pickled data I can submit it, but when I
> retrieve it, I don't get the complete pickled data
> that I submitted therefore I get this error:
> 
> x = cPickle.loads(strr)
> EOFError:
> 
> Can you please tell me the safe and correct way of
> submitting cpickled data. Help will be much
> appreciated.

See attached example.

-- Gerhard
from pysqlite2 import dbapi2 as sqlite
import cPickle as pickle

# Our test data
value = (3, 4, {"a": 42})

# Let's pickle the test data
pickled_string = pickle.dumps(value)

# Open a SQLite connection and create a table to store the BLOB in
con = sqlite.connect(":memory:")
cur = con.cursor()
cur.execute("create table data(blobcol)")

# To tell pysqlite/SQLite that we want to store a value as a BLOB, we must pass
# it of type `buffer`. The Python builtin buffer() accepts strings and others,
# this is the best way to create a buffer object. We could also use
# sqlite.Binary, which is just a synonym for the buffer builtin.
cur.execute("insert into data(blobcol) values (?)", (buffer(pickled_string),))

# Now let's fetch the data to see.
cur.execute("select blobcol from data")
received_pickled_buffer = cur.fetchone()[0]

# Test that we really got a buffer object back.
assert type(received_pickled_buffer) is buffer

# Let's create a string out of it, and test that it's equal to the pickled
# string we created above.
received_pickled_string = str(received_pickled_buffer)
assert received_pickled_string == pickled_string

# Unpickle the string and check that we really got equal objects.
unpickled_value = pickle.loads(received_pickled_string)
assert unpickled_value == value
_______________________________________________
pysqlite mailing list
pysqlite-IAPFreCvJWPBWskQ1e/+sw@xxxxxxxxxxxxxxxx
http://lists.initd.org/mailman/listinfo/pysqlite