How to update a record in SQLAlchemy Alembic migration?

Question

I'm writing a Database Migration script for my SQLAlchemy application. The migration below works. But it doesn't actually do anything (yet!):

 1: from alembic import op
 2: import sqlalchemy as sa
 3: 
 4: def upgrade():
 5:     my_table = sa.Table('my_table',
 6:                           sa.MetaData(),
 7:                           sa.Column('my_id', sa.Integer, primary_key=True),
 8:                           sa.Column('my_attribute1', sa.Text(), nullable=True),
 9:                           sa.Column('my_attribute2', sa.String(length=128), nullable=True))
10:
11:
12: connection = op.get_bind()
13: for my_record in connection.execute(my_table.select()):
14:     x = my_record.my_id
15:     print x

I want to modify the above migration to do the following things but I don't know how:

In line #13 I want to select only those records where my_attribute1 == 'Hello'
In line #15 Instead of doing a print statement, I want to update my_record such that my_attribute2 is set to my_attribute1[:10] + 'Goodbye'

How can I do it? When I tried to do selects & updates with where clauses, they didn't work. The manual wasn't much help.

You have some indentation issues going on there. The for-loop is not a part of the upgrade function. Please include the selects & updates with where clauses that you tried in the question. Instead of the DML manual you should be reading the [SQL Expression Language Tutorial](http://docs.sqlalchemy.org/en/latest/core/tutorial.html#inserts-updates-and-deletes). — Ilja Everilä, Apr 12 '17 at 06:30
Also, please describe how and why they did not work. Did you get exceptions? If so, include the tracebacks. Did the upgrade run, but no changes were saved? — Ilja Everilä, Apr 12 '17 at 06:39

score 4 · Answer 1 · answered Apr 13 '17 at 14:15

It would be safer for you to bypass ORM in migrations and just do something like

connection = op.get_bind()
connection.execute("UPDATE my_table SET my_attribute2 = SUBSTRING(my_attribute1, 0, 10) + 'Goodbye' WHERE my_attribute1 = 'Hello'")

I assume this is just an example and you're going to do something a bit different, because otherwise, you wouldn't need to take the substring of my_attribute1 as it always has the same value 'Hello' for those records.

How to update a record in SQLAlchemy Alembic migration?

1 Answers1