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

[Pavol Lisy]

On 5/31/17, Peter Otten <__peter__ at web.de> wrote:
> Jon Ribbens wrote:
>
>> On 2017-05-31, Skip Montanaro <skip.montanaro at gmail.com> wrote:
>>> I'm kind of stuck with the database API I have. ("Love the child you
>>> have, not the one you wish you had?") Given that I have the choice to
>>> execute those three statements to bound a transaction, is there any
>>> reason not to use them instead of
>>>
>>> (conn or cur).execute("begin transaction")
>>> conn.commit()
>>> conn.rollback()
>>
>> I very much doubt it.
>>
>>> I must say this discussion has been enlightening for me. I'm not a
>>> database guy, so for better or worse, my view of how to interact with
>>> relational databases has always been colored by the Python database
>>> adapters.
>>
>> Indeed. This is where I was coming from too, and when I moved beyond
>> that point I got very confused because it turned out that Python had
>> seriously misled me.
>>
>>> cur1 = conn.cursor()
>>> cur2 = conn.cursor()
>>>
>>> for row in cur1.execute("select something ..."):
>>>     tidbit = ... pick something of interest out of the row ...
>>>     for new_row in cur2.execute("select something else involving the
>>> tidbit ..."):
>>>         ... process new_row ...
>>
>> Well, you can't do that, because you can't iterate over an execute().
>
> You can, if the database is sqlite3.

cx_Oracle too.

But althoug return from execute is undefined (
https://www.python.org/dev/peps/pep-0249/#id16 ), you could iterate
over cursor ( https://www.python.org/dev/peps/pep-0249/#iter )

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

fetchall could be disaster for memory!