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

[Peter Otten]

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.

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