help: pypgsql: this code works in Fedora core 1, Not in FC2

[Cliff Wells]

On Sun, 2004-09-26 at 00:41 -0700, J Dubal wrote:
> Hello good people,
> 
> Following works in FC1 (python-2.2.3-7, postgresql-7.3.4-11,
> kernel-2.4.22-1.2194.nptl, pyPgSQL-2.4)
> 
> from pyPgSQL   import PgSQL
> conn  = PgSQL.connect('localhost',database='rop')
> loccur  = conn.cursor()
> loccur.execute("DECLARE itemcursor CURSOR FOR \
>               SELECT * FROM locmf")
> loccur.execute("FETCH ALL FROM itemcursor")
> rec=loccur.fetchall()
> print rec
> 
> However, following errors are produced in FC2 (python-2.3.3-6,
> postgresql-7.4.2-1, kernel-2.6.8-1.521, pyPgSQL-2.4):
> 
> /usr/lib/python2.3/site-packages/pyPgSQL/PgSQL.py:2644: FutureWarning:
> %u/%o/%x/%X of negative int will return a signed string in Python 2.4
> and up
>   name = "PgSQL_%08X" % id(self)
> Traceback (most recent call last):
>   File "tt1.py", line 5, in ?
>     loccur.execute("DECLARE itemcursor CURSOR FOR \
>   File "/usr/lib/python2.3/site-packages/pyPgSQL/PgSQL.py", line 3091,
> in execute
>     self.res = self.conn.conn.query('FETCH 1 FROM "%s"' % self.name)
> libpq.OperationalError: ERROR:  cursor "PgSQL_F6F9906C" does not exist
> 
> Out of this, the first error is produced by conn.cursor() statement.
> 
> We have to use the "declare cursor" statement because we have to use
> "fetch prior" statement later in the program. Is there a better way of
> achiving this?
> 
> Something seems to have changed either python or in postgresql that is
> causing this error. Can someone provide a solution/workaround?

Don't know if anything changed between the versions of the software you
are using, but see

http://www.postgresql.org/docs/current/static/sql-declare.html

particularly the section on "WITH HOLD/WITHOUT HOLD" and the first
paragraph in "Notes".  I'm guessing that perhaps you aren't starting a
new transaction before declaring the cursor and that perhaps pyPgSQL is
autocommitting the DECLARE statement which would result in your declared
cursor going out of scope.  Try adding WITH HOLD or put your stuff
inside of "BEGIN/COMMIT" statements.

Regards,
Cliff

> Thanks in advance.
> J Dubal.
-- 
Cliff Wells <clifford.wells at comcast.net>