Geek Answers Handbook

Using SQLAlchemy ORM inside Alembic migration: how do I do this?

I currently have a column containing HTML markup. Inside this markup there is a timestamp that I want to save in a new column (so I can query it). My idea was to do the following in one go:

  • Create a new column with a null value for the data
  • Use ORM to pull off the HTML I need for parsing
  • For each row
    • parse the HTML to pull the timestamp
    • update ORM object

But when I try to start my migration, it seems to get stuck in an endless loop. Here is what I have so far:

def _extract_publication_date(html): root = html5lib.parse(html, treebuilder='lxml', namespaceHTMLElements=False) publication_date_string = root.xpath("//a/@data-datetime")[0] return parse_date(publication_date) def _update_tip(tip): tip.publication_date = _extract_publication_date(tip.rendered_html) tip.save() def upgrade(): op.add_column('tip', sa.Column('publication_date', sa.DateTime(timezone=True))) tips = Tip.query.all() map(tips, _update_tip) def downgrade(): op.drop_column('tip', 'publication_date')
+6
source share
3 answers

What worked for me was to get the session by doing the following:

 connection = op.get_bind() Session = sa.orm.sessionmaker() session = Session(bind=connection)
+4
source

After a little experiment using @velochy's answer, I settled on something like the following template for using SqlAlchemy inside Alembic. This works fine for me and probably can serve as a general solution to the OP question:

 from sqlalchemy.orm.session import Session from alembic import op def upgrade(): # Attach a sqlalchemy Session to the env connection session = Session(bind=op.get_bind()) # Perform arbitrarily-complex ORM logic instance1 = Model1(foo='bar') instance2 = Model2(monkey='banana') # Add models to Session so they're tracked session.add(instance1) session.add(instance2) def downgrade(): # Attach a sqlalchemy Session to the env connection session = Session(bind=op.get_bind()) # Perform ORM logic in downgrade (eg clear tables) session.query(Model2).delete() session.query(Model1).delete()

This approach seems to handle transactions correctly. Often while working on this, I generated database exceptions, and they would roll back everything as expected.

+4
source

Keep reading the comments, you can try something like this:

 import sqlalchemy as sa tip = sa.sql.table( 'tip', sa.sql.column('id', sa.Integer), sa.sql.column('publication_date', sa.DateTime(timezone=True)), ) def upgrade(): mappings = [ (x.id, _extract_publication_date(x.rendered_html)) for x in Tip.query ] op.add_column('tip', sa.Column('publication_date', sa.DateTime(timezone=True))) exp = sa.sql.case(value=tip.c.id, whens=( (op.inline_literal(id), op.inline_literal(publication_date)) for id, publication_date in mappings.iteritems() )) op.execute(tip.update().values({'publication_date': exp})) def downgrade(): op.drop_column('tip', 'publication_date')
+1
source

More articles:


All Articles