psycopg, transactions and multiple cursors
alban at magproductions.nl
Tue Nov 9 15:47:38 CET 2004
Steve Holden wrote:
> Yes, most database connections will generate an implicit transaction the
> first time a change is made to the database (in the absence of autocommit).
I quoted you on this in the class-file that is (supposedly) going to
solve my problems.
>> Yes, it does have autoCommit, and thankfully it can be turned off
>> (which I did, of course).
> It certainly isn't too useful if you occasionally need to roll things
> back. Structural changes to the database will frequently cause an
> automatic commit anyway, though - you aren't modifying the database
> structure at all, I take it?
Oh no, there's rarely a need for that and it tends to get you into trouble.
For example, I know a CMS (not Python related, sorry) that creates
database tables for classes it uses for various site objects, but it
apparently doesn't check for the class name being a reserved SQL
> Nested cursors aren;t nested transcations, though, right?
Indeed. My intention is actually to keep them in the same transaction.
>> A cursor is comparable to an iterator over a result set (where the
>> cursor fetches one record from the database at a time).
> B e careful that you don;t confuse the DB API curosrs with the cursors
> you get with DECLARE CURSOR in PL/SQL, for example. The two aren;t
> necessarily the same thing (and I've always felt that "cursor" was, for
> that reason, not the best possible terminological choice for the DB API).
PHP uses 'resource identifier' or something similar. I can't say it's
much clearer, but you won't confuse it with cursors. OTOH, a connection
in PHP is also a 'resource identifier'.
>> However, if you loop through a result set (with a cursor) and you need
>> to do a query based on the record that the cursor is currently
>> positioned at, you can't use the same cursor again for that query, but
>> need to open a new cursor. That's what I meant by "nesting cursors".
> Bear in mind, though, that it will often be *much* more efficient to do
> a fetchall() from the cursor and iterate over that result. This
> typically avoids many round-trips by fetching all the data at once,
> though it's less practical if data sets become huge.
> There's sometimes a middle ground to be found with repeated calls to
> In that way the curosr can be reused with impunity once the data has
> been fetched.
True enough, and I don't use nested cursors that way in my Python code.
It is something I do frequently in PL/SQL, though, so I suppose I used
that as an example.
In Python the problem is different, I used a bad example.
The reason I use multiple cursors in Python is not so much that I nest
them, but that they are often in a local scope (in a method, a class, etc.).
As the cursor has to execute a different query in each scope anyway, I
don't think it really matters whether I go through loops to reuse the
previous cursor object (risking entering a nested loop unknowingly) or
create a new one.
The examples I encountered do the same thing, so I suppose it's alright.
> I think we've already agreed that the psycopg cursors aren't DB API
> compliant anyway, precisely because of the way they handle transactions.
> An API-compliant library shares transaction state across all cursors
> generated from the same connection, which (IMHO) gives the flexibility
> one needs to to handle complex database interactions.
>> The number of questions is increasing...
> Well, the number of answers is, too, but it seems to me you *are* kind
> of wanting to have your cake and eat it. In previous threads you've
I'm not familiar with that expression, but I suppose I would change that
to: "I baked my own cake, and though it doesn't taste as well as I
intended, I'm trying to eat it anyway".
It means a lot of work if some basic assumptions in a large project turn
out to be wrong... I was hoping to get it working the way it is, but it
seems that the "one connection" idea is not possible. Too bad...
> suggested that the psycopg cursor behavior is what you want, but now
> that very behavior might (?) be biting you.
I am indeed starting to have my doubts about the usefulness of being
able to commit a cursor. If it doesn't do anything to a transaction, or
if you can't start a new transaction using a cursor after that commit
(I'm not yet certain which of the two happens, if it isn't a third
option), then it seems kind of pointless and confusing.
The possibility suggested (to me at least) that it would be possible to
do multiple (maybe even parallel) transactions using only one database
connection, by making the cursors handle the transactions. Alas, it's
> Anyway, you couldn't be talking to a better bunch of guys to try and
> solve this problem. c.l.py is sometimes persistent beyond all reasonable
> limits. Good luck!
You guys are great indeed! I'm learning things that I thought I knew
already. And you're quick too, all of my complicated questions so far
have been answered/solved within a day or so.
But I have to admit, the complicated questions are usually the most fun ;)
Thanks for all the help, it's much appreciated.
More information about the Python-list