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

[Andy Dustman]

TA wrote:

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?

This is untrue: cursor.scroll() is an optional DB-API 2.0 extension.

http://www.python.org/peps/pep-0249.html

MySQLdb supports this, but I do not know about your case.

[TA]

"Andy Dustman" <fa*******@gmail.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

This is untrue: cursor.scroll() is an optional DB-API 2.0 extension.

http://www.python.org/peps/pep-0249.html

MySQLdb supports this, but I do not know about your case.

Yes 'cursor.scroll()' is optional and pySqlite supports it too, but pyPgSQL
does not.

Thanks for replying.

--
TA