SQLite 3.28: Should Python’s sqlite3 expose a rollback‑allowed flag for DDL?

Goal

Ensure that schema modifications executed through Python’s sqlite3 module can be safely rolled back, preserving the prior table structure without manual intervention.

Constraints & Uncertainty

SQLite supports transactional DDL in recent releases, but certain ALTER TABLE operations (e.g., dropping a column) are not fully supported, causing OperationalError and making rollback moot. The Python DB‑API does not expose a way to opt out of the module’s default transaction wrapping for DDL statements. Behavior also varies across adapters (psycopg2, MySQLdb) and older SQLite versions (<3.7.9). No documented flag currently exists to disable automatic transaction wrapping, raising questions about future compatibility and developer control.

Unresolved Questions

1. Should the sqlite3 module in future Python releases provide an explicit flag (e.g., connection.set_rollback_allowed(False)) to allow developers to opt out of automatic transaction wrapping for DDL?
2. What reliable mechanism can developers use to detect whether the underlying SQLite version supports fully transactional DDL before executing schema changes?
3. Which best‑practice patterns can be recommended for guaranteeing rollback safety across different DB‑API adapters that may or may not support transactional DDL?