Must COMMIT after SELECT

[M.-A. Lemburg]

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