I'm trying to do an in-place database "upgrade" -- if I detect that a column is missing, I use ALTER TABLE to add the column and then compute values for the new column for each row. I'd like this to all happen within a transaction, so the column doesn't get added unless the new values are successfully set.
So my current code looks like this (new_parent_for() computes my value for the new column): try: session.execute("ALTER TABLE nodes ADD COLUMN parent_id INTEGER DEFAULT 0;") for node in session.query(Node): session.query(Node).update({"parent_id": new_parent_for(node)}) session.commit() except Exception as e: session.rollback() raise To test the rollback, I add a line to throw an exception during the update loop. It catches the exception, and does the rollback, but the new 'parent_id' column is left in the table. Here's the echoed SQL results: 2010-05-29 14:34:18,746 INFO sqlalchemy.engine.base.Engine.0x...eb10 ALTER TABLE nodes ADD COLUMN parent_id INTEGER DEFAULT 0; 2010-05-29 14:34:18,746 INFO sqlalchemy.engine.base.Engine.0x...eb10 [] 2010-05-29 14:34:18,759 INFO sqlalchemy.engine.base.Engine.0x...eb10 SELECT nodes.id AS nodes_id, nodes.parent_id AS nodes_parent_id, nodes.kind AS nodes_kind, nodes.title AS nodes_title, nodes.created AS nodes_created, nodes.jconfig AS nodes_jconfig FROM nodes WHERE nodes.kind = ? 2010-05-29 14:34:18,759 INFO sqlalchemy.engine.base.Engine.0x...eb10 ['menu'] 2010-05-29 14:34:18,762 INFO sqlalchemy.engine.base.Engine.0x...eb10 SELECT nodes.id AS nodes_id FROM nodes 2010-05-29 14:34:18,770 INFO sqlalchemy.engine.base.Engine.0x...eb10 [] 2010-05-29 14:34:18,772 INFO sqlalchemy.engine.base.Engine.0x...eb10 UPDATE nodes SET parent_id=? 2010-05-29 14:34:18,772 INFO sqlalchemy.engine.base.Engine.0x...eb10 [3] then the test exception occurs, and we get 2010-05-29 14:34:18,788 INFO sqlalchemy.engine.base.Engine.0x...eb10 ROLLBACK At the end of this operation, the parent_id column is still in the table, but the values are all zero. The UPDATE part didn't get committed, but the ALTER TABLE did. This is a sqlite3 database, by the way... is there some limitation in SQLite that doesn't roll back an ALTER TABLE, or am I doing something wrong? Thanks for any help or pointers anybody can offer! -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.