storing pickles in sql data base

[Chris Mellon]

On 7/11/07, Nick Craig-Wood <nick at craig-wood.com> wrote:
> David Bear <david.bear at asu.edu> wrote:
> >  I need to store pickled objects in postgresql. I reading through the pickle
> >  docs it says to always open a file in binary mode because you can't be sure
> >  if the pickled data is binary or text. So I have 2 question. Can I set the
> >  pickle to be text -- and then store it in a 'text' type field in my sql
> >  table, or should what sql type should I save the pickle as?
>
> You could always encode it into text form, eg
>
>   >>> from cPickle import dumps, loads
>   >>> a = range(10)
>   >>> a
>   [0, 1, 2, 3, 4, 5, 6, 7, 8, 9]
>   >>> b = dumps(a).encode("zip").encode("base64").strip()
>   >>> b
>   'eJzTyCkw5PI04Er0NARiIyA2BmITIDYFYjMgNgdiCyC25ErUAwD5DQqD'
>   >>> loads(b.decode("base64").decode("zip"))
>   [0, 1, 2, 3, 4, 5, 6, 7, 8, 9]
>   >>>
>

Protocol 0 (the default) is a text protocol, it's safe to store in a
text field or write to a text file. base64 encoding will work on
protocol 1 and 2 (which are binary protocols and not text-safe), but
doing that removes the main benefit of the higher protocols, which is
smaller pickle data.