alembic migrations with ORM

[kgk]

I have 2.1.5 project that is using alembic for DB migrations..

I originally used the standard alembic migrations/env.py to setup the migration environment.

This worked well for adding indexes and new tables.

However, recently I needed to do some data massaging and was hoping that I could use 

the ORM.. Micheal Bayer on the alembic list said this was possible by configuring the session

correctly..   However, I have been have some trouble doing that with zope.sqlalchemy

Basically I cannot get a transaction commit at the end of the migration no matter how

I try to use the transaction package.

Here is the relevent section of the env.py 

============================================

def run_migrations_online():

    """Run migrations in 'online' mode.

    In this scenario we need to create an Engine

    and associate a connection with the context.

    """

    import transaction

    from bq.core.model import DBSession, init_model

    

    engine = engine_from_config(

                config.get_section(config.config_ini_section), 

                prefix='sqlalchemy.', 

                poolclass=pool.NullPool)

    init_model (engine)

    connection = DBSession.connection()

    context.configure(

        connection=connection, 

        target_metadata=target_metadata

        )

    try:

        print "BEGIN"

        with transaction:

            context.run_migrations()

        #transaction.commit()

    except Exception, e:

        print "EXCEPTION", e

        transaction.abort()

    #finally:

    #    connection.close()

=======================================

And the run ..

--------------------------------------------------------------------------------

alembic -c config/alembic.ini  downgrade 156205cd1d39

2013-05-01 16:04:03,290 INFO sqlalchemy.engine.base.Engine select version()

INFO  [sqlalchemy.engine.base.Engine] select version()

2013-05-01 16:04:03,290 INFO sqlalchemy.engine.base.Engine {}

INFO  [sqlalchemy.engine.base.Engine] {}

2013-05-01 16:04:03,293 INFO sqlalchemy.engine.base.Engine select current_schema()

INFO  [sqlalchemy.engine.base.Engine] select current_schema()

2013-05-01 16:04:03,293 INFO sqlalchemy.engine.base.Engine {}

INFO  [sqlalchemy.engine.base.Engine] {}

2013-05-01 16:04:03,296 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)

INFO  [sqlalchemy.engine.base.Engine] BEGIN (implicit)

INFO  [alembic.migration] Context impl PostgresqlImpl.

INFO  [alembic.migration] Will assume transactional DDL.

BEGIN

2013-05-01 16:04:03,298 INFO sqlalchemy.engine.base.Engine select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=current_schema() and relname=%(name)s

INFO  [sqlalchemy.engine.base.Engine] select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=current_schema() and relname=%(name)s

2013-05-01 16:04:03,298 INFO sqlalchemy.engine.base.Engine {'name': u'alembic_version'}

INFO  [sqlalchemy.engine.base.Engine] {'name': u'alembic_version'}

2013-05-01 16:04:03,301 INFO sqlalchemy.engine.base.Engine SELECT alembic_version.version_num 

FROM alembic_version

INFO  [sqlalchemy.engine.base.Engine] SELECT alembic_version.version_num 

FROM alembic_version

2013-05-01 16:04:03,301 INFO sqlalchemy.engine.base.Engine {}

INFO  [sqlalchemy.engine.base.Engine] {}

INFO  [alembic.migration] Running downgrade 306c5eb91bac -> 156205cd1d39, resource uniq

2013-05-01 16:04:03,304 INFO sqlalchemy.engine.base.Engine UPDATE alembic_version SET version_num='156205cd1d39'

INFO  [sqlalchemy.engine.base.Engine] UPDATE alembic_version SET version_num='156205cd1d39'

2013-05-01 16:04:03,304 INFO sqlalchemy.engine.base.Engine {}

INFO  [sqlalchemy.engine.base.Engine] {}

---------------------------------------------------------------

Note no.. COMMIT in the log and in fact the database has not been changed.   

If anybody has some pointers .. they would be appreciated.  

Thx

[Moritz Schlarb]

I recently wanted to do data migrations, too and ended up with this:

https://github.com/moschlar/SAUCE/blob/develop/migration/versions/6bceed82300_multiteachers.py

(Without using transaction, but I think the main difference is that I do it in the migration script itself and not in the env, although that should be possible, too...)

Feel free to get inspired ;)

[kgk]

Thanks for the inspiration .. worked well.