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)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[[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.
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.

 

[Andy Dustman]

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]

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.