up vote 1 down vote favorite

I am using cPickle and psycopg2 to store some vectors into database. This is my code to store binary data

binary_vec = cPickle.dumps(vec, -1)
db.cur.execute('''
               INSERT INTO feature_vector (vector, id) 
               VALUES (%s, %s);
               ''', (psycopg2.Binary(binary_vec), thread_id)
db.conn.commit()

However when I use fetchall() to load my data back, the type is buffer. I can't find how how to restore this buffer object back to a list (vec).

This is how I fetch the data

db.cur.execute("SELECT * FROM feature_vector;")
m = db.cur.fetchall()

The result looks like this

[(3169187, <read-only buffer for 0x1002b0f10, size 3462, offset 0 at 0x1004a7430>), 
(3169275, <read-only buffer for 0x1002b0f50, size 3462, offset 0 at 0x1004a7570>), 
(3169406, <read-only buffer for 0x1002b0f70, size 3462, offset 0 at 0x10140b0b0>), 
(3169541, <read-only buffer for 0x10141c030, size 3462, offset 0 at 0x10140b2b0>), 
(3169622, <read-only buffer for 0x10141c050, size 3462, offset 0 at 0x10140b3f0>),...

When I try to use cPickle.loads(m[0][1]), it will return the error message

Traceback (most recent call last):
File "<stdin>", line 1, in <module>
TypeError: must be string, not buffer
share|improve this question
40% accept rate
Did you check manually (e.g., mysql -e 'select * from ...') that the data is indeed stored in the database? – Jakub M. Dec 2 '12 at 11:59
Yes, data is stored in the database. It looks like this \x80025d7101284b6a4b6a4b6a4b6a4b6a4b6a4b6a4b6a4b6a4b6a4b6a652e – seed Dec 2 '12 at 20:24
1  
it would help if you pasted the code for fetching.. – Jakub M. Dec 2 '12 at 20:38
1  
Did you try str(the_buffer) or bytes(the_buffer)? – Bakuriu Dec 2 '12 at 21:04
Oh it works now, thanks! – seed Dec 3 '12 at 0:07
feedback

1 Answer

up vote 0 down vote

You can create a customized typecaster to automatically convert pickled values to Python: 

import cPickle
obj = {'a': 10}
data = cPickle.dumps(obj, -1)

import psycopg2

def cast_pickle(data, cur):
    if data is None: return None
    return cPickle.loads(str(psycopg2.BINARY(data, cur)))

psycopg2.extensions.register_type(
    psycopg2.extensions.new_type(
        psycopg2.BINARY.values, 'BINARY-PICKLE', cast_pickle))

cnn = psycopg2.connect('')
cur = cnn.cursor()
cur.execute("select %s::bytea", [psycopg2.Binary(data)])
cur.fetchone()
# ({'a': 10},)
share|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.