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

Skip Montanaro skip.montanaro at gmail.com
Wed May 31 07:20:49 EDT 2017


Let me try to gently steer the discussion back to my original
question. I know that both databases I'm using support the following
statements:

begin transaction
commit transaction
rollback transaction

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 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. I will pose one question which relates to the broader
discussion which has ensued. On occasion, I have found myself wanting
to perform subsequent queries while processing the result set from an
initial query. Something like:

conn = ... whatever ...

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

I know that given a smart database person, a single (probably pretty
complex) select statement can be written which collapses the nested
for loops into a single result set and corresponding single for loop.
Still, can you do the nested cur1.execute/cur2.execute thing if you
are performing selects on the connection?

Skip



More information about the Python-list mailing list