Re: bytea quoting problems

Greg Landrum wrote:

>Greetings,
>
>I'm just getting starting with pyPgSQL (I'm in the process of switching over from Interbase/Firebird) and I've hit some odd behavior with bytea columns.
>
>I am trying to store pickles in bytea columns of a table and I appear to be encountering problems in the quoting rules.  Here's a session:
>
>-------------------------------
>~ > uname -a
>Linux badger 2.4.18-14smp #1 SMP Wed Sep 4 12:34:47 EDT 2002 i686 i686 i386 GNU/Linux
>~ > python
>Python 2.2.3 (#1, Aug  6 2003, 09:41:11)
>[GCC 3.2 20020903 (Red Hat Linux 8.0 3.2-7)] on linux2
>Type "help", "copyright", "credits" or "license" for more information.
>  
>
>>>>from pyPgSQL import PgSQL
>>>>PgSQL.version
>>>>        
>>>>
>'$Revision: 1.35 $'
>  
>
>>>>import cPickle
>>>>conn = PgSQL.connect()
>>>>curs = conn.cursor()
>>>>curs.execute('create table throwaway (guts bytea)')
>>>>curs.commit()
>>>>        
>>>>
># start adding values:
>  
>
>>>>curs.execute('insert into throwaway values (%s)',(cPickle.dumps(range(64)),))
>>>>        
>>>>
># that worked, but try a binary pickle:
>  
>
>>>>curs.execute('insert into throwaway values (%s)',(cPickle.dumps(range(64),1)))
>>>>        
>>>>
>Traceback (most recent call last):
>  File "<stdin>", line 1, in ?
>  File "/usr/lib/python2.2/site-packages/pyPgSQL/PgSQL.py", line 3047, in execute
>    parms = tuple(map(_quote, parms));
>  File "/usr/lib/python2.2/site-packages/pyPgSQL/PgSQL.py", line 2261, in _quote
>    return PgQuoteString(value)
>TypeError: PgQuoteString() argument 1 must be string without null bytes, not str
># we can clear that up by not starting at 0:
>  
>
>>>>curs.execute('insert into throwaway values (%s)',(cPickle.dumps(range(1,64),1)))
>>>>        
>>>>
># but then we get into trouble again (but a different kind this time) with larger values:
>  
>
>>>>curs.execute('insert into throwaway values (%s)',(cPickle.dumps(range(1,129),1)))
>>>>        
>>>>
>Traceback (most recent call last):
>  File "<stdin>", line 1, in ?
>  File "/usr/lib/python2.2/site-packages/pyPgSQL/PgSQL.py", line 3072, in execute
>    raise OperationalError, msg
>libpq.OperationalError: ERROR:  Bad input string for type bytea
># everything is still fine with text pickles though:
>  
>
>>>>curs.execute('insert into throwaway values (%s)',(cPickle.dumps(range(1,129))))
>>>>
>>>>        
>>>>
>-------------------------------
>
>I did a bit of googling and couldn't find this problem mentioned.  
>
>Is this user error or is there something wrong in pyPgSQL itself?
>  
>
You need to tell pyPgSQL that you are inserting a bytea string. you can 
do this as follows:

curs.execute('insert into throwaway values (%s)', PgBytea(cPickle.dumps(range(1,129))))

I hope this answers your question.

-- 
____       | Billy G. Allie    | Domain....: [email protected]
|  /|      | 7436 Hartwell     | MSN.......: [email protected]
|-/-|----- | Dearborn, MI 48126|
|/  |LLIE  | (313) 582-1540    |