The python sqlite module automatically commits open transactions
when it encounters a DDL statement.  This is unnecessary; DDL is
transactional in my testing (see attached).

Attached patch addresses the issue. Patch is against 2.6.1, but
looking at Trunk in svn, it seems like the patch is needed and
would apply. One issue I could foresee is that this behavior might
depend on the sqlite version in use (I'm on 3.6.10).

Patch also allows pragma statement.

Here are some tests.

See also Issue 8145.  It would be nice if someone could sort all this out, but I'm not knowledgeable enough to do so.

For this patch, it would be a significant change it behaviour.  Therefore it would have to be a new feature controlled by a flag of some sort (which is part of the reason for the reference to issue 8145).

I find the way that the sqlite3 module handles transactions pretty
surprising in general, but I agree that someone who got used
to DDL not rolling back could in theory find this patch surprising.

We will apply this patch to our python build because having DDL
covered by a transactions is convenient for certain tasks. If anyone
can think of a problem with this simple patch, I'd like to hear it.

Thanks
Scott

I want transactional DDL too. I was tremendously surprised that I could not duplicate the way sqlite3 behaves on the command line from witin pysqlite.

Instead of this patch, I would rather be able to instruct pysqlite to always begin a transaction for any kind of statement (I hear this is a requirement for DB-API compliance) and never perform an implicit commit for any reason. For example, someone on the google code project had a complaint that 'SAVEPOINT' (create a subtransaction) automatically commits because pysqlite doesn't know about it.

I tried to trick pysqlite into doing what I wanted by prepending /* update */ to every CREATE TABLE statement but it didn't seem to quite work. Instead, I would prefer a pysqlite that does not strcmp(statement) at all.

The attached patch is my take on this issue. I ran into the problem that during schema upgrades dropping a table was not rolled back. In another instance, renaming a table was not rolled back. This greatly increases the risk of data loss for our application.

Because I do not currently foresee which commands might need dropping out of a transaction and because of backwards compatibility woes, I added a new field to the sqlite3.Connection: operation_needs_transaction_callback

This function is called to decide if a running transaction should be implicitly committed (I'd consider this dangerous), if a transaction has to be started if not running (should normally always hold) or if the transaction state should be left alone.

For example, the "pragma foreign_keys = ..." is a no-op inside a transaction, therefore an implicit begin would be possibly harmful. In our application, we enable foreign keys when getting the connection out of the SQLAlchemy pool via a pool listener, which would be disabled if there is an implicit begin triggered.

The patch also adds a bunch of unit tests to cover the new behaviour.

Same patch for Python 3. In fact, this also adds a missing Py_XDECREF.

Torsten basically you are suggesting that PRAGMA would never work at all with my 'do not strcmp() the sql at all, always begin a transaction' approach?

> Torsten basically you are suggesting that PRAGMA would never work at all with my 'do not strcmp() the sql at all, always begin a transaction' approach?

No. Most pragmas should still work and getting the current setting of a pragma should also work.

I was only referring to http://www.sqlite.org/pragma.html#pragma_foreign_keys

Quote:

> This pragma is a no-op within a transaction; foreign key constraint enforcement may only be enabled or disabled when there is no pending BEGIN or SAVEPOINT.

I did not see such a restriction for other pragmas though I admit that I did not reread the list in full.

The attached patch is an updated version which adds a bit of documentation.

I updated the patch for upstream pysqlite2. Available at

http://code.google.com/p/pysqlite/issues/detail?id=24

Patch over there is for Python 2 (tested with our production Python 2.6).

Opened http://bugs.python.org/issue12997 in case there is a way to solve the foreign_key PRAGMA issue with a less disruptive fix.