In the past week, I’ve be working on a small, lightweight database migration tool using Python. I chose this path instead of using existing tools such as South or PyMigrate because I thought it would be a good learning experience and we would be able to customize the tool to our liking later if need be.
Our tool currently takes in raw .sql files and works with PostgreSQL. Many of the alternatives use [up] and [down] tags to designate forward and background migrations. I originally started out wanting to support PostgreSQL, MySQL, and sqlite, but the problem with this approach was that using the the pyodbc and sqlite3 wrappers do not support transactions. Thus, I can’t rollback in the middle of a transaction. I learned that MySQL doesn’t support transactional Data Definition Language and that the sqlite3 wrapper doesn’t support turning on transactions for DDL (only DML).
Anybody have experience working with these modules that could help?