Re: [Pypgsql-users] Binary data with postgress

[Billy G. A. <Bil...@mu...>]

Gerhard =?iso-8859-1?Q?H=E4ring?= wrote:
> I was trying to answer this question, too. In the process of which I was
> constructing an example using BYTEA. Unfortunately, there seems to be a
> bug in pyPgSQL that we should eliminate before (finally, *cough*)
> releasing 2.3:
> 
> # Database schema used:
> # create table test(ba bytea, lo oid);
> 
> from pyPgSQL import PgSQL
> 
> bindata = "".join([chr(x) for x in range(256)] * 10)
> 
> con = PgSQL.connect()
> 
> cursor = con.cursor()
> 
> # I) BYTEA
> 
> # Storing BYTEA:
> cursor.execute("insert into test(ba) values (%s)", (PgSQL.PgBytea(bindata),))
> last_oid = cursor.oidValue
> 
> # Retrieving BYTEA
> cursor.execute("select ba from test where oid=%s", (last_oid,))
> row = cursor.fetchone()
> 
> # Check if input is the same as output
> assert bindata == row.ba
> 
> con.close()
> 
> 
> It looks like the quoting of BYTEAs is wrong, at least when the BYTEA
> starts with a chr(0). But I haven't got a real clue where the problem
> lies. Though I vaguely remember touching that piece of code once when I
> was working on the ARRAY stuff. I hope I didn't introduce the bug then
> *blush*.

Gerhard,

The problem is with the PgBytea class. It is missing the hidden methods for 
determining length, comparisons, concatenation (+), repetition (*), etc.  I 
will be very happy when version 3.0 gets out the door and we can just 
sub-class the string object :-)  I will fix the class by this weekend.

>>> from pyPgSQL import PgSQL
>>> a = '\000ab\000'  # Create a string with NUL characters in it.
>>> len(a)
4
>>> a
'\x00ab\x00'
>>> PgSQL.PgQuoteBytea(a)  # Check how it's quoted by pyPgSQL
"'\\\\000ab\\\\000'"
>>> cx = PgSQL.connect()   # Lets put it into the database
>>> cu = cx.cursor()
>>> cu.execute('create table byteatest (a bytea, b oid)')
>>> cx.commit()
>>> cu.execute('insert into byteatest values(%s, %s)', PgSQL.PgBytea(a), 0)
>>> cx.commit()
>>> cu.execute('select * from byteatest')
>>> rs = cu.fetchone()
>>> rs
['\x00ab\x00', 0]
>>> cu.execute('insert into byteatest(a) values(%s)', PgSQL.PgBytea(a))
>>> cx.commit()
>>> cu.execute('select * from byteatest');
>>> rs = cu.fetchall()
>>> for i in rs:
..     print i
.. 
['\x00ab\x00', 0]
['\x00ab\x00', None]
>>> cu.execute('insert into byteatest(a) values(%s)', (PgSQL.PgBytea(a),))
>>> cx.commit()
>>> cu.execute('select * from byteatest');
>>> rs = cu.fetchall()
>>> for i in rs:
..     print i
.. 
['\x00ab\x00', 0]
['\x00ab\x00', None]
['\x00ab\x00', None]
>>> len(rs[0].a)	# len() doesn't work on PgBytea types
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
AttributeError: PgBytea instance has no attribute '__len__'
>>> len(rs[0].a.value)
4
>>> assert a == rs[0].a  # Comparison doesn't work either
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
AssertionError
>>> assert a == rs[0].a.value
>>>

-- 
____       | Billy G. Allie    | Domain....: Bil...@mu...
|  /|      | 7436 Hartwell     | MSN.......: B_G...@em...
|-/-|----- | Dearborn, MI 48126|
|/  |LLIE  | (313) 582-1540    |



-- 
____       | Billy G. Allie    | Domain....: Bil...@mu...
|  /|      | 7436 Hartwell     | MSN.......: B_G...@em...
|-/-|----- | Dearborn, MI 48126|
|/  |LLIE  | (313) 582-1540    |