Transaction-handling issues

Dear all,

One thing of the PySQLite interface that I find confusing is the  
transaction handling. It is convenient to have automatic transaction- 
begin but the way it combines with explicit transactions is  
confusing. Following are my issues, and a change proposal.

When automatic transactions are enabled, as per the default, PySQLite  
automatically issues a begin statement before it first executes an  
update or other dml-statement. In this mode, begin should never be  
added programmaticaly as an SQL-statement. Doing so will raise an  
error as soon as PySQLite does the automatic begin. The error mesage  
in this case is: "SQL Logic error or missing database".

The type of begin statement that is automaticly inserted can be any  
of the supported SQLite types (DEFERRED, IMMEDIATE, EXCLUSIVE). By  
default it is "" which is equivalent to DEFERRED. What confuses here  
is IMMEDIATE. As it is only inserted at the first dml, it actually  
becomes equivalent to DEFERRED.

Commit should, with the default operation mode, only be programmed as  
a call to the corresponding method. Commit as an SQL-statement should  
not be used. Doing so is punished by an error: "cannot commit - no  
transaction is active". While the changes really have been committed.

With automatic transaction-begin disabled (isolation_level=None), the  
other way around only commit as an SQL-statement may be used. Using  
the commit method in this mode, does not cause an error. But neither  
does it really commit changes.

Now I like to know if the following proposal makes sense.

To make PySQLite handle commit as an SQL-statement equally to the  
method. The only distinction would be that the method nay be called  
also if there are no changes to commit.

Secondly, but this may be more dificult, to allow a transaction to be  
explicitly started using BEGIN also when in the default operating  
mode. The interface should then be aware of the new transaction state.

I assume that this proposal is not against the dbapi v.2  
specification and that it would be "downwards compatible".

Best regards, Edzard Pasma