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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
