Re: psycopg, transactions and multiple cursors

From: Alban Hertroys (alban_at_magproductions.nl)
Date: 11/08/04 

Date: Mon, 08 Nov 2004 16:07:21 +0100

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

Relevant Pages

  • Re: Huge memory comsumption of ADODB Connection object
    ... Why I said the Connection ... to nothing, the memory used by my app doesn't decrease, only when I close the ... I take the same opinion as you do, if the cursor doesn't return ... resultset to client, no impact will be imposed to client. ...
    (microsoft.public.data.ado)
  • Re: VB6 ADO DB2 - Update Problem
    ... Jeweils ein Feld der beiden ... Was veranlasst Dich zu der Annahme, dass Daten eines Recordsets mit ... Man kann eine Connection auch ohne ein solches Ungetüm öffnen, ... Warum serverseitiger Cursor? ...
    (microsoft.public.de.vb.datenbank)
  • RE: :Oracle question
    ... Are you closing your cursor once they are no longer needed? ... > What seems to happen is that when we open a connection to the database and run ... > some query, after a number of query that we have run seems that we fill the ... > number of possible cursor per connection. ...
    (perl.dbi.users)
  • Re: sqlite3 question
    ... In my real app the connection is kept inside a singleton object and I ... > This should not work because `r` should not be a `Cursor` object. ... cursor, executes a query on that cursor, and returns that cursor. ...
    (comp.lang.python)
  • Re: psycopg, transactions and multiple cursors
    ... >> opening a connection thus the app doesn't suffer so much. ... first time a change is made to the database. ... >> what you want is a nested and not a new cursor. ... B e careful that you don;t confuse the DB API curosrs with the cursors ...
    (comp.lang.python)