On 2008-02-07 14:29, Steve Holden wrote: > Paul Boddie wrote: >> With PostgreSQL, my impression is that the intended way of using >> cursors is not entirely compatible with the DB-API: you declare >> cursors only when you know what the query will be, not in advance, and >> they can only be used with certain kinds of operations. As far as I >> recall, pyPgSQL supports cursors fairly transparently, albeit through >> various ad-hoc measures, whereas psycopg2 only does so for "named >> cursors" - a concept missing from the DB-API as far as I can see. >> > Yes, unfortunately the nomenclature of the DB API conflicts with that of > SQL'S DECLARE CURSOR, where the named cursor is effectively a sequence > of query results that (under certain isolation levels and patterns of > usage) can reflect database changes as they occur. Different > implementers have chosen different relationships between DB API cursors > and SQL cursors since it was introduced in the SQL 92 standard. > > I believe, without documentary justification, that named cursors were > introduced into SQL to support stored procedures, and therefore weren't > intended to be used for queries whose results were communicated outside > the server.
Cursors defined using DECLARE CURSOR usually live in the scope of the database engine. They are different from the cursors defined and used with the database APIs. MS even warns against mixing them: http://msdn2.microsoft.com/en-us/library/aa172576(SQL.80).aspx The Python DB-API is defined at the database API level, so the same applies in the context of DB-API cursors. In practice, I've not had problems with accessing named cursors using DB-API cursors. The main reason for the MS warning is that cursors can be used for lots of interesting optimizations such as auto-updating result sets and scrolling, positioned updates or deletions, etc. The DB-API doesn't expose all these nifty features, so doesn't easily run into trouble. mxODBC has support for named cursors that you can later use for positioned updates. You declare the name of the cursor when creating it: cursor1 = connection.cursor('mycursor') cursor1.execute('select id, name, value from mytable') # Position the mycursor on row 10 cursor1.fetchmany(10) # Update row 10 cursor2 = connection.cursor() cursor2.execute('update mytable set value = ? where current of mycursor') cursor1.close() cursor2.close() However, it's usually better to do updates in the classical way, ie. by referencing a primary key. -- Marc-Andre Lemburg eGenix.com Professional Python Services directly from the Source (#1, Feb 07 2008) >>> Python/Zope Consulting and Support ... http://www.egenix.com/ >>> mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/ >>> mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/ ________________________________________________________________________ :::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,MacOSX for free ! :::: eGenix.com Software, Skills and Services GmbH Pastor-Loeh-Str.48 D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg Registered at Amtsgericht Duesseldorf: HRB 46611 -- http://mail.python.org/mailman/listinfo/python-list
