Must COMMIT after SELECT (was: Very weird behavior in MySQLdb "execute")

[Paul Boddie]

On 7 Feb, 08:52, Frank Aune <Frank.A... at broadpark.no> wrote:
> On Wednesday 06 February 2008 16:16:45 Paul Boddie wrote:
>
> > Really, the rule is this: always (where the circumstances described
> > above apply) make sure that you terminate a transaction before
> > attempting to read committed, updated data.
>
> How exactly do you terminate a transaction then?Do you terminate a transaction
> by closing the cursor?

No, the transaction is controlled using the connection object in the
DB-API, specifically by the commit and rollback methods.

> Would this indicate that for each query you perform
> against the db, you should:
>
> - Open cursor
> - Perform query
> - Close cursor

I tend to open a separate cursor for each query whose result set I
want to keep around. In other words, if I'm doing a number of queries
whose results will not be manipulated via the DB-API (using fetchone,
fetchmany, fetchall) after the next query is executed, then I only
open one cursor - it's like this:

  cr = c.cursor()
  try:
    cr.execute(query1)
    do stuff with cr.fetchone/many/all
    cr.execute(query2)
    ...
  finally:
    cr.close()

As the DB-API indicates, if you want to manipulate more than one
result set, you need more than one cursor.

With PostgreSQL, my impression is that the intended way of using
cursors is not entirely compatible with the DB-API: you declare
cursors only when you know what the query will be, not in advance, and
they can only be used with certain kinds of operations. As far as I
recall, pyPgSQL supports cursors fairly transparently, albeit through
various ad-hoc measures, whereas psycopg2 only does so for "named
cursors" - a concept missing from the DB-API as far as I can see.

> The concept of cursor in MySQL is apparantly very different from what I
> originally thought. I always thought the cursor is the "handler" for a
> certain connection, and that you needed to commit before closing down this
> handler - else changes were automatically rolled back.

It's easy to believe this, given the "hierarchical" nature of the API.
However, cursors are just things which keep track of result sets, and
I suppose that they are most useful when you perform a query which
produces a large number of result rows, but where you only want to
read a limited number of those rows at a time.

Paul