HOWTO? Embedding ORM code in an Alembic migration

[gthank]

How do you do it? I'm using Flask-SQLAlchemy, and my current attempt is here: https://gist.github.com/4247141

My basic problem is that the `rendered_html` column has a date embedded in some of the markup, and I want to extract that data into its own column so I can use it in some filtering logic. My approach so far is:

* Update the model object to add the new property

* Write a migration to add the column to power the new property

* Naively import my model class

* Use my model to look up the current values, parse the data that I care about, and persist it back to the DB.

This isn't working; when I attempt to run the migration, the command never returns. I suspect that the ORM logic is using a different session than the alembic logic. Is there an accepted pattern for getting alembic and the ORM on the same page?

Thanks,

Hank

[Michael Bayer]

alembic doesn't use a Session of it's own. Everything Alembic is doing to talk to the database is in the env.py script, which is in your environment for you to alter. In this case you'd want to make sure the Engine that's being used in your env.py is the same Engine that your application uses when you say "from pytips.models import Tip".

[Hank Gay]

On 12/09, Michael Bayer wrote:
>
> alembic doesn't use a Session of it's own. Everything Alembic is doing to talk to the database is in the env.py script, which is in your environment for you to alter. In this case you'd want to make sure the Engine that's being used in your env.py is the same Engine that your application uses when you say "from pytips.models import Tip".
>

Hi Michael.

I was never able to get the ORM and Alembic to co-exist peacefully. Any
attempt to use the ORM after making a structural modification via op (or
an attempt at a structural modification using op after I'd performed
some ORM logic) resulted in a migration that wouldn't complete. I
went back and double-checked that the connection setup in env.py was
compatible with the setup used by pytips.model (I'm actually pulling the
connection info from the same env var, so they were), but it didn't seem
to help.

Eventually, I worked around the issue by creating 3 separate migrations:

* http://bit.ly/W3CdtG
* http://bit.ly/VCIQ5I
* http://bit.ly/V7mdaZ

But I had to execute each of these migrations separately. Simply running
an upgrade to head that ran all 3 migrations as part of the same upgrade
command ran into the same problem as doing them all within the same
migration. If it helps with troubleshooting, I was working against the
psycopg2 backend.

Is this a problem that other people have experienced? Am I
doing something silly in my particular configuration, such as
accidentally using separate connections for the ORM and Alembic
operations? I certainly didn't mean to, but I'm a bit new to
SQLAlchemy/Alembic/Flask-SQLAlchemy. I'm happy to help try to track down
what's going on, but I understand if it's a problem that nobody else is
experiencing, too.

Thanks,

Hank

[Michael Bayer]

this is all about configuration. When your alembic migration runs, the entire sequence is controlled by what you have in your env.py script. env.py's job is to get a hold of an Engine and Connection to use within a migration. If you're sharing this migration with an application that has a scoped_session() in use (which I'm assuming is the case here as I see the SomeClass.query pattern in use), then your env.py needs to use the connection used by that scoped_session - that way everyone uses the same DBAPI connection and the series of migrations all occur within the same transaction.

Just like this it should work:

env.py
--------

# this is your scoped_session from your app
from myapplication.mymodel import Session

# ...

def run_migrations_online():

# get the Connection from it
connection = Session.connection()

# run the migrations with it
context.configure(
connection=connection,
target_metadata=target_metadata
)
try:
context.run_migrations()

# finish or rollback the transaction
Session.commit()
except:
Session.rollback()

[kristian kvilekval]

I tried reconfiguring as you suggest and now my scripts that use the ORM are running correctly.

   from bq.core.model import DBSession,

    engine = engine_from_config(

                config.get_section(config.config_ini_section), 

                prefix='sqlalchemy.', 

                poolclass=pool.NullPool)

    #connection = engine.connect()

    DBSession.configure(bind=engine)

    connection = DBSession.connection()

    context.configure(

                connection=connection, 

                target_metadata=target_metadata

                )

    import transaction

    try:

        #with context.begin_transaction():

        context.run_migrations()

        transaction.commit()

    finally:

       transaction.abort()

       #connection.close()

However, simply downgrades do not seem to update alembic_version table.

If I switch it back to the default, then I can do downgrade correctly.

Unfortunate situation.. as of now I need to edit env.py depending on what I need to do.

[Michael Bayer]

a combination of logging SQL output and using pdb.set_trace() should lead to the cause of this issue.

[Michael Bayer]

OK, "import transaction" looks a lot like you're using zope.transaction (which is unusual, with Flask?) .  If you're integrating with zope.transaction you'd need to make sure your DBSession and all that is configured correctly, for help with that library I'd check on the Pyramid list.    The example I gave assumed straight ORM Session usage along with Session.commit().   I don't know if zope.transaction needs special instructions to start it's work.

On Apr 4, 2013, at 7:29 PM, kristian kvilekval <kkvil...@gmail.com> wrote:

Here's the sql output..while the update is occurring, 

seems like the transaction is never committed..

$ alembic -c config/alembic.ini downgrade 156205cd1d39

2013-04-04 16:22:51,589 INFO sqlalchemy.engine.base.Engine select version()

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

2013-04-04 16:22:51,589 INFO sqlalchemy.engine.base.Engine {}

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

2013-04-04 16:22:51,592 INFO sqlalchemy.engine.base.Engine select current_schema()

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

2013-04-04 16:22:51,592 INFO sqlalchemy.engine.base.Engine {}

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

2013-04-04 16:22:51,595 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.

2013-04-04 16:22:51,597 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-04-04 16:22:51,597 INFO sqlalchemy.engine.base.Engine {'name': u'alembic_version'}

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

2013-04-04 16:22:51,599 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-04-04 16:22:51,600 INFO sqlalchemy.engine.base.Engine {}

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

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

2013-04-04 16:22:51,603 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-04-04 16:22:51,603 INFO sqlalchemy.engine.base.Engine {}

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

$

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

Now with configuration in env.py, we definitely see the commit.

    engine = engine_from_config(

                config.get_section(config.config_ini_section), 

                prefix='sqlalchemy.', 

                poolclass=pool.NullPool)

    connection = engine.connect()

    #DBSession.configure(bind=engine)

    #connection = DBSession.connection()

    context.configure(

                connection=connection, 

                target_metadata=target_metadata

                )

    import transaction

    try:

        with context.begin_transaction():

            context.run_migrations()

        #transaction.commit()

    finally:

       #transaction.abort()

       connection.close()

$ alembic -c config/alembic.ini downgrade 156205cd1d39

2013-04-04 16:26:33,222 INFO sqlalchemy.engine.base.Engine select version()

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

2013-04-04 16:26:33,222 INFO sqlalchemy.engine.base.Engine {}

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

2013-04-04 16:26:33,224 INFO sqlalchemy.engine.base.Engine select current_schema()

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

2013-04-04 16:26:33,225 INFO sqlalchemy.engine.base.Engine {}

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

INFO  [alembic.migration] Context impl PostgresqlImpl.

INFO  [alembic.migration] Will assume transactional DDL.

2013-04-04 16:26:33,228 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)

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

2013-04-04 16:26:33,229 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-04-04 16:26:33,229 INFO sqlalchemy.engine.base.Engine {'name': u'alembic_version'}

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

2013-04-04 16:26:33,231 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-04-04 16:26:33,231 INFO sqlalchemy.engine.base.Engine {}

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

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

2013-04-04 16:26:33,234 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-04-04 16:26:33,234 INFO sqlalchemy.engine.base.Engine {}

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

2013-04-04 16:26:33,235 INFO sqlalchemy.engine.base.Engine COMMIT

INFO  [sqlalchemy.engine.base.Engine] COMMIT

--
You received this message because you are subscribed to the Google Groups "sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alem...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.