By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,071 Members | 1,244 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,071 IT Pros & Developers. It's quick & easy.

Cursor navigation

P: n/a
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
Jul 21 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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.

Jul 21 '05 #2

P: n/a
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
Jul 22 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.