pysql lite transactions question

[Stormbringer]

Hi Gerhard,

I apologize for my barbaric code, I was under the impression that I
could use the transaction commands directly from sqlite. I wasn't
aware that cursors had rollback and commit functions. Now I understand
how all must be handled.

Thanks for the clarifications,
Andrei


Gerhard Häring <gh at ghaering.de> wrote in message news:<mailman.1.1072009735.684.python-list at python.org>...
> 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