Python DB API - commit() v. execute("commit transaction")?
Pavol Lisy
pavol.lisy at gmail.com
Wed May 31 08:47:44 EDT 2017
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!
More information about the Python-list
mailing list