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

[Jon Ribbens]

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