Thanks for another great reply.
Yes, the Python sqlite3 wrapper sometimes fiddles with transactions automatically. I actually started this whole process by innocently wondering how to start and stop transactions and ended up much further down the rabbit hole than I intended! At some point I hope to document my findings in a StackOverflow answer which, no doubt, no one will ever read :-) I'll include a note about closing cursors for incompletely exhausted SELECT statements.
As a result, I've spent a lot of time reading the source code to sqlite3 and pysqlite recently. Sadly, I can tell you that the logic for when to automatically start a transaction in sqlite3 *does* still depend on textual matching: if the statement starts with INSERT, UPDATE, DELETE or REPLACE (intended to catch DML statements) then a transaction is started just before executing the statement itself. This comparison skips over whitespace but not comments (e.g. `"/*foo*/INSERT ..."` is not considered to start with INSERT). This only happens if isolation_mode is not None, as you said, and only if a transaction is not already started (as determined with `sqlite3_get_autocommit()`, so it will notice transactions started by the developer manually executing BEGIN).
It's true that there was a move in the past to away from statement parsing. Here's how the situation got (back) to this point:
In the past (before Python 3.6 and before pysqlite 2.8.0), some statements would cause an implicit COMMIT beforehand (if a transaction was open, as determined with `!sqlite3_get_autocommit()`). Again, this only happened if isolation_mode is not None. The rule was: if a statement started with INSERT, UPDATE, DELETE or REPLACE (intended to catch DML), BEGIN a transaction; if it started with SELECT (i.e. DQL), do nothing; otherwise (this case is meant to catch DDL like `CREATE TABLE`), COMMIT a transaction.
Then pysqlite (commit [94eae50](https://github.com/ghaering/pysqlite/commit/94eae5002967a51782f36ce9b7b81bba5b4379db)) got rid of all the above logic, and replaced it with simply: if `!sqlite3_stmt_readonly()` then start a transaction. That got rid of all the statement parsing, and the removal of the implicit transaction COMMIT meant you could include DDL like `CREATE TABLE` in a transaction implicitly started by pysqlite. But this exact version of the code never made it into a release.
The next day, the logic was changed (commit [796b3af](https://github.com/ghaering/pysqlite/commit/796b3afe38cfdac5d7d5ec260826b0a596554631)) to if `!sqlite3_stmt_readonly() && !is_ddl` then start a transaction, where `is_ddl` was defined as a statement starting with CREATE, DROP or INDEX, so there was still text matching in there. This was done to improve backwards compatibility with existing code. This version still never implicitly committed transactions. This was released as pysqlite 2.8.0. That is still how it stands to this day, and is unlikely to change given that its readme says "This project is not actively maintained any more: You are better off using the sqlite3 module in the Python standard library".
This was initially merged into Python 3.6 beta, but [issue 28518](https://bugs.python.org/issue28518) was raised about backwards compatibility: `conn.execute("BEGIN IMMEDIATE")`, which had previously worked, would raise an exception "cannot start a transaction within a transaction". That's because this is *not* a read only statement (as explained [in a comment by D. Richard Hipp](https://bugs.python.org/issue28518#msg281738)), so a transaction was now being started automatically just before it was executed. Such statements are better suited to `isolation_mode = None`, but the issued showed the change would've broken existing code, so before Python 3.6 was released the code was changed to the current logic. Compared to Python 3.5, that meant that only the "otherwise" branch had really changed, and the [release notes](https://docs.python.org/3/whatsnew/3.6.html#changes-in-the-python-api) simply said "sqlite3 no longer implicitly commits an open transaction before DDL statements".
Funnily enough, the DB API spec - which as you said is the motivation for all this funkiness - says that a transaction should be opened before any statement at all, even a SELECT. This isn't clear from the document itself but I came across an archived email from the PEP author that says this is what they intended (but I can't find the link at the moment). Given that sqlite3 doesn't satisfy the spec, all this automagic behaviour seems like wasted effort!