Steve said:
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.
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
keyword. Oops...
Nested cursors aren;t nested transcations, though, right?
Indeed. My intention is actually to keep them in the same transaction.
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'.
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
fetchmany().
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.
Indeed.
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
not so.
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.
Alban.