pysql lite transactions question

[Gerhard Häring]

Stormbringer wrote:
> Hi,
> 
> Could anyone shed some light on transactions using pysqlite when
> autocommit is off ?

Sure.

> I am using Python 2.3 with latest pysqlite and after I do a
>        m_connection = sqlite.connect(name, autocommit=0)
> and
>        m_cursor = self.m_connection.cursor()
> 
> and then looks like it starts a transaction automaticaly, because if I
> issue a
>        m_cursor.execute('BEGIN TRANSACTION')
> it says that nested transactions are not permitted.
> 
> But : if I issue an 'END TRANSACTION' and then a 'BEGIN TRANSACTION'
> and do the usual db operations and in the end I do a 'END TRANSACTION'
> I get this :

If you want to mess with transactions yourself, use autocommit mode. But 
in normal, transactional mode, please only use .commit() and .rollback() 
on the connection object.

> Exception _sqlite.DatabaseError: 'cannot rollback - no transaction is
> active' in <bound method Connection.__del__ of <sqlite.main.Connection
> instance at 0x0086D288>> ignored

I myself would prefer an ElectrificationException instead. Maybe that 
would reduce such perverted usage :->

> [...] My reasons of trying to use transactions is that it seems to make
> sense in what I do, i.e. I try to populate a database and if I get an
> error halfway I want to cancel the whole chain of operations. Plus I
> noticed the speed is much greater if I put autocommit to off.
> 
> Any ideas ?

Yes.

DON'T MESS WITH TRANSACTIONS MANUALLY!!! Cough.

It's absolutely not needed in 99.99999999 % of all cases. Yet people 
still think they're smarter than the designers of the DB-API and me ;-)

Here's a simple way to achieve what you want:

cx = sqlite.connect(...)
cu = cx.cursor()

try:
     # Do your INSERTs here
     cx.commit()
except DatabaseError:
     cx.rollback()

Seeing this misuse of transactional commands in .execute() I'll change 
PySQLite to throw ProgrammingError when they're used in non-autocommit mode.

-- Gerhard