1

I'm trying some db schema changes to my db, using the sqlalchemy table.create and sqlalchemy-migrate table.rename methods, plus some insert into select statments. I want to wrap all of this in a transaction. I can't figure out how to do this. This is what I tried:

engine = sqlalchemy.engine_from_config(conf.local_conf, 'sqlalchemy.')
trans = engine.connect().begin()
try:
    old_metatadata.tables['address'].rename('address_migrate_tmp', connection=trans)
    new_metatadata.tables['address'].create(connection=trans)
except:
    trans.rollback()
    raise
else:
    trans.commit()

But it errors with:

AttributeError: 'RootTransaction' object has no attribute '_run_visitor'

(I tried using sqlalchemy-migrate column.alter(name='newname') but that errors, and does not work in a transaction, and so leaves my db in a broken state. I also need to rename multiple columns, and so I decide to roll my own code.)

Gary van der Merwe
  • 9,134
  • 3
  • 49
  • 80

1 Answers1

2

Ah - I need to simply use the connection that the transaction was created on.

engine = sqlalchemy.engine_from_config(conf.local_conf, 'sqlalchemy.')
conn = engine.connect()
trans = conn.begin()
try:
    old_metatadata.tables['address'].rename('address_migrate_tmp', connection=conn)
    new_metatadata.tables['address'].create(bind=conn)
except:
    trans.rollback()
    raise
else:
    trans.commit()
Gary van der Merwe
  • 9,134
  • 3
  • 49
  • 80
  • you have to remember to call conn.close() at the end. If you don't, once you introduce more than 10 migration (I think that's the limit sqlalchemy uses internally for pool size) scripts you run the risk of your upgrade script intermittently hanging because it'll get blocked waiting on DB connection pool, but only in those rare cases when python's GC doesn't get a chance to kick in and close existing connections for you. – DXM Jan 22 '14 at 13:44