psycopg, transactions and multiple cursors

[Alban Hertroys]

Diez B. Roggisch wrote:
>>AFAIK, you can't open a transaction w/o using a cursor; You need a query
>>that says "BEGIN;".
>>You can commit a connection object, however. It would be nice to be able
>>to start a transaction on a connection object, but then you still could
>>only have one transaction per connection... :(
> 
> 
> thats actually the case for all DBs I know including e.g. oracle with jdbc -
> so the abstraction layers usually use connection pooling to speed up
> opening a connection thus the app doesn't suffer so much.

Ok, that means I won't get away with a single connection object (unless 
psycopg puts a connection pool in a single connection object).

> No idea why thats happening - on jdbc, one can set an "autocommit"
> connection property that will do exactly that: enforce a commit if a
> statement was successful. Maybe psycopg has that too?

Yes, it does have autoCommit, and thankfully it can be turned off (which 
I did, of course).

>>I know PostgreSQL can do transactions on nested cursors, I have used
>>that now and then in stored procedures (pl/pgsql).

> I didn't find much on nested cursors on google, so I don't know how they
> work - but I assume if they are part of psycopg, they somehow have to be
> created using an existing cursor, as otherwise how should psycopg know that
> what you want is a nested and not a new cursor.

Actually, nesting of cursors is something that PL/PgSQL can do. And so 
can PL/SQL in Oracle.
It's something that's possible on a low database API level, and (to my 
understanding) the DBAPI 2.0 uses them for queries. It's one of Pythons' 
advantages over eg. PHP, and one of the reasons I chose to use Python 
for this project.

> So is there something on cursor objecst to get a new cursor, or at least the
> connection so you can get a cursor on the very same connection?

A cursor is comparable to an iterator over a result set (where the 
cursor fetches one record from the database at a time).

You use one for every query, and it can often be reused for the next 
query as well.

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

That shouldn't matter for the state of a transaction, though...

Maybe there's a difference between database level cursors and DB API 2.0 
level cursors in Python?

The number of questions is increasing...