[Pypgsql-users] bytea quoting problems

[Greg L. <gre...@ea...>]

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?

Thanks,
-greg
----
greg Landrum (gre...@ea...)
Software Carpenter/Computational Chemist