Python DB API - commit() v. execute("commit transaction")?

[Peter Otten]

Jon Ribbens wrote:

> On 2017-05-31, Peter Otten <__peter__ at web.de> wrote:
>> Jon Ribbens wrote:
>>> You would do:
>>> 
>>>     cur.execute("SELECT ...")
>>>     for row1 in cur.fetchall():
>>>         cur.execute("SELECT ...")
>>>         for row2 in cur.fetchall():
>>>             ....
>>> 
>>> and as far as the database is concerned, the query is over and done
>>> with as soon as you call fetchall(), so there is no possible overlap
>>> between the different queries.
>>
>> It's easy to simulate an iterable cursor with
>>
>> iter(cursor.fetchone, None)
>>
>> and then two cursors instead of one start making sense again.
> 
> Indeed. I think this would not work, in general. For example, I think
> with MySQLdb it would work if you use a standard Cursor class, as that
> downloads the entire result set as soon as execute() is called, but it
> would not work with the SSCursor class which downloads results as
> needed. Again, this is because DB-API "cursors" are not SQL cursors.

ere's an excerpt of PEP 249:

"""
.cursor()
Return a new Cursor Object using the connection.

If the database does not provide a direct cursor concept, the module will 
have to emulate cursors using other means to the extent needed by this 
specification. [4]

Cursor Objects

These objects represent a database cursor, which is used to manage the 
context of a fetch operation. Cursors created from the same connection are 
not isolated, i.e., any changes done to the database by a cursor are 
immediately visible by the other cursors. Cursors created from different 
connections can or can not be isolated, depending on how the transaction 
support is implemented (see also the connection's .rollback() and .commit() 
methods).
"""

I don't see how a cursor class that messes up interleaved fetchone() calls 
can be compliant with the DB-API.