pysql lite transactions question

[Stormbringer]

Hi,

Could anyone shed some light on transactions using pysqlite when
autocommit is off ?

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 :

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

The program works as expected, i.e. seems like this exception is
ignored, but I must do something wrong or not properly as this
exception appear.

I also tried to send a 'COMMIT' after my 'BEGIN TRANSACTION' and I get
_sqlite.DatabaseError: cannot commit - no transaction is active
which does strike me as odd.

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 ?

Thanks in Advance,
Andrei

 

[=?ISO-8859-1?Q?Gerhard_H=E4ring?=]

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

 

[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

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