Saving Python Pickled objects in MySQL db

Question

I am pickling Python Objects in Django and saving it in MySQL db. So far i have followed these simple rules:

1. cPickle.dumps(object) #to convert python object to pickled object

2. cPickle.loads(pickled_object) # to load back the python object from pickled object

3. My Django Model Field is Text Field

4. MySQL db field Type is longblob Attributes binary

5. MySQL db encoding is utf8_unicode_ci

Unfortunately i am getting following error while loading back python object.

Type Error: ('an integer is required', <type 'datetime.date'>, ('x07xb6x0bx06',))

It seems to me by looking on error value x07xb6x0bx06 this is an encoding problem. Did i miss some important step?? Can any one help me to solve this problem??

Answer 1

If you are trying to store the output of cPickle.dumps in a VARCHAR column, then your issue is that you are trying to store a byte-string in a character column. The fix in that case is to encode your object as unicode(base64.encode(cPickle.dumps(myobject))) and then store it.

Alternatively:

object2varchar = lambda obj: unicode(base64.encode(cPickle.dumps(obj)))
store(object2varchar([1, 'foo']))

Answer 2

Newtover's answer is probably the correct one, but have a look at

https://github.com/bradjasper/django-pickledfield

It really saved me some time, and can store pretty much anything you want.

Answer 3

one more rule: connect to mysql with option charset=utf8?

UPD1: Sometimes it is a good idea to look at the complete SQL query, I usually do it that way:

>>> conn = MySQLdb.connect(**db_params)
>>> "INSERT INTO tbl VALUES (%s)" % conn.literal((your_pickled_item, ))