3

I am modifying a model(adding a new column) and I have a corresponding migration file. My goal is to extend this migration by adding some data to the new column.

I tried to modify the migration file by adding my own sql code to achieve my goal. Sample modified almebic file contains code like below:

# below is the only line added by alembic
op.add_column('sample_table', sa.Column('new_column', sa.UnicodeText(), nullable=True))
# below lines are my own
my_obj = MyClass()
table_item = my_obj.get_table_item(some_parameter)
...
...

The issue is that my function "get_table_item" operates on the same table as is operated upon by alembic in "op.add_column" line. Since alembic locks the table during alembic upgrade, my function "get_table_item" gets stuck because of this lock. Is there any way to achieve my goal without modifying my function "get_table_item"?

Haris Muzaffar
  • 404
  • 6
  • 17
  • 1
    Two migrations. One to modify the schema, a second to add the data. – Steve Piercy Aug 03 '19 at 19:33
  • your idea seems good. But how can i run two migrations in one "alembic upgrade head"? – Haris Muzaffar Aug 03 '19 at 19:44
  • 2
    When you use `alembic upgrade head`, Alembic will run any migrations that it has not already run until it reaches the end of the chain. Take a look at the tutorial starting from [Create a Migration Script](https://alembic.sqlalchemy.org/en/latest/tutorial.html#create-a-migration-script) through "Running our Second Migration", noting how the migration scripts are linked. – Steve Piercy Aug 04 '19 at 08:40
  • If that is the case then when i hit "alembic upgrade head", why im getting error that "my_new_colunn" doesnt exist. Currently i have two migrations. The first one is adding a new column and the second(latest one) is adding data to it. – Haris Muzaffar Aug 04 '19 at 08:46
  • Try running each migration in turn instead. Is the result different? – Steve Piercy Aug 05 '19 at 02:21
  • no. It actually works but very messy code. What if my next line code is alembic and then next is custom code, then next is alembic and so on. I will need hell many migrarions! – Haris Muzaffar Aug 05 '19 at 04:01

1 Answers1

1

One important thing you need to keep in mind writing Alembic migration scripts is that they need to work not only with the current application code but with all future versions of it. Your example looks like you're importing a mapped class form your application code and trying to run some methods which depend on the newly added column:

from haris_awesome_app import MyClass
# below is the only line added by alembic
op.add_column('sample_table', sa.Column('new_column', sa.UnicodeText(), nullable=True))
# below lines are my own
my_obj = MyClass()
table_item = my_obj.get_table_item(some_parameter)

this will create problems in the future when you add a new parameter to get_table_item, rename MyClass or even change the import structure.

A better approach is to have the Alembic migration steps completely contained, i.e. everything needed for the migration to succeed should be in the migration file itself. You can use op.execute to run arbitrary SQL in your migration file:

def upgrade():

    op.add_column('sample_table', sa.Column('new_column', sa.UnicodeText(), nullable=True))

    op.execute('''UPDATE sample_table SET new_column = ...''')

Here's some documentation on op.execute with more examples.

Sergey
  • 11,892
  • 2
  • 41
  • 52