Cursor navigation

[TA]

Hi,

This might be a silly question, but I was wondering how you would navigate
backwards in a PostgreSQL cursor when the Python DB-API 2.0 allows records
to be fetched in a forward-only manner? I have tried different solutions -
two of which are included here.

The first solution uses the cursor declared implicitly by the pyPgSQL
interface, but for some reason this does not work as expected.

(RH-8.0, Python-2.2.1, pyPgSQL-2.4.0, PostgreSQL-7.2.2)
>>> # First solution
>>> from pyPgSQL import PgSQL
>>> con = PgSQL.connect()
>>> con.conn.toggleShowQuery
'On'
>>> cur = con.cursor()
QUERY: BEGIN WORK
>>> cur.execute("select * from test")
QUERY: DECLARE "PgSQL_081B3B64" CURSOR FOR select * from test
QUERY: FETCH 1 FROM "PgSQL_081B3B64"
QUERY: SELECT typname, -1 , typelem FROM pg_type WHERE oid = 23
QUERY: SELECT typname, -1 , typelem FROM pg_type WHERE oid = 1043
>>> cur.execute("fetch 1 in \"%s\" " % cur.name)
QUERY: fetch 1 in "PgSQL_081B3B64"
>>> r = cur.fetchall()
QUERY: FETCH ALL FROM "PgSQL_081B3B64"
>>> r
[[2, 'name2'], [3, 'name3'], [4, 'name4'], [5, 'name5'], [6, 'name6']]

The 'fetchall()' returns the remaining five rows in the table and not just a
single row as expected.


The other solution explicitly declares another cursor.

>>> # Second solution
>>> from pyPgSQL import PgSQL
>>> con = PgSQL.connect()
>>> con.conn.toggleShowQuery
'On'
>>> cur = con.cursor()
QUERY: BEGIN WORK
>>> cur.execute("declare acursor cursor for select * from test")
QUERY: declare acursor cursor for select * from test
>>> cur.execute("fetch 1 in acursor")
QUERY: fetch 1 in acursor
QUERY: SELECT typname, -1 , typelem FROM pg_type WHERE oid = 23
QUERY: SELECT typname, -1 , typelem FROM pg_type WHERE oid = 1043
>>> r = cur.fetchall()
>>> r
[[1, 'name1']]
>>> cur.execute("fetch 1 in acursor")
QUERY: fetch 1 in acursor
>>> r = cur.fetchall()
>>> r
[[2, 'name2']]
>>> cur.execute("fetch -1 in acursor")
QUERY: fetch -1 in acursor
>>> r = cur.fetchall()
>>> r
[[1, 'name1']]

This works as expected. Only a single row is returned and it is possible to
fetch rows forwards and backwards in the cursor, but is this really the way
to do this?

If anyone can explain what I'm doing wrong or suggest another approach, it
would be much appreciated.

--
TA
Denmark