Must COMMIT after SELECT

[Steve Holden]

Paul Boddie wrote:
> 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.
> 
Clearly you need multiple cursors if you want to be reading from the 
database on both cursors simultaneously. Like you, though, I tend to use 
a single cursor whenever I can get away with it (and now I think about 
it, that's probably because of the greater likelihood of seeing db 
consistency due to the read repeatability, though I can honestly say I 
hadn't really formalized that principle).

> 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.
> 
Yes, unfortunately the nomenclature of the DB API conflicts with that of 
SQL'S DECLARE CURSOR, where the named cursor is effectively a sequence 
of query results that (under certain isolation levels and patterns of 
usage) can reflect database changes as they occur. Different 
implementers have chosen different relationships between DB API cursors 
and SQL cursors since it was introduced in the SQL 92 standard.

I believe, without documentary justification, that named cursors were 
introduced into SQL to support stored procedures, and therefore weren't 
intended to be used for queries whose results were communicated outside 
the server.

MySQL (never the most reliable indication of standardized SQL), 
restricts them to use inside stored procedures and triggers. PostgreSQL 
makes them more generally available, and I suppose it is possible to use 
a DB API cursor to execute a FETCH statement to retrieve data from a SQL 
CURSOR though I have never tried to do it.

SQL Server provides two types of cursor, one for use in the standard 
manner and one that conforms more closely with the ODBC cursor concept.

I tend to treat the database as a purely relational store, and not use 
stored procedures, but this is a luxury not everyone can afford - it's 
just necessary to use them for efficiency reasons sometimes, though not 
in the applications I am mostly involved with. Consequently I haven't 
made much use of SQL CURSORs.

>> 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.
> 
That's true, and your remarks clarify cursor usage in the DB API very 
well. Most people most of the time tend to ignore the existence of 
cursor.fetchmany() in the DB API, despite the fact that it can provide 
huge efficiency gains over both .fetchone() (can slow processing by 
requiring too many database interactions per query) and .fetchmany() 
(which can generate large memory overhead in the case of huge result sets).

regards
  Steve
-- 
Steve Holden        +1 571 484 6266   +1 800 494 3119
Holden Web LLC              http://www.holdenweb.com/