Updating column contents during Alembic migration

Question

Suppose my db model contains an object User:

Base = declarative_base() 

class User(Base):                                                               
    __tablename__ = 'users'                                                     

    id = Column(String(32), primary_key=True, default=...) 
    name = Column(Unicode(100))                                             

and my database contains a table userswith n rows. At some point, I decided to divide nameby firstnameand lastname, and during alembic upgrade headI would like my data to be transferred as well.

Alembic auto- generation is performed as follows:

def upgrade():
    op.add_column('users', sa.Column('lastname', sa.Unicode(length=50), nullable=True))
    op.add_column('users', sa.Column('firstname', sa.Unicode(length=50), nullable=True))

    # Assuming that the two new columns have been committed and exist at
    # this point, I would like to iterate over all rows of the name column,
    # split the string, write it into the new firstname and lastname rows,
    # and once that has completed, continue to delete the name column.

    op.drop_column('users', 'name')                                             

def downgrade():
    op.add_column('users', sa.Column('name', sa.Unicode(length=100), nullable=True))

    # Do the reverse of the above.

    op.drop_column('users', 'firstname')                                        
    op.drop_column('users', 'lastname')

, . execute() bulk_insert() SQL . () db, , .

Alembic? ?

Answer 1

norbertpys answer , , : , , . (. ), ; , .

(. this Gist). :

def upgrade():

    # Schema migration: add all the new columns.
    op.add_column('users', sa.Column('lastname', sa.Unicode(length=50), nullable=True))
    op.add_column('users', sa.Column('firstname', sa.Unicode(length=50), nullable=True))

    # Data migration: takes a few steps...
    # Declare ORM table views. Note that the view contains old and new columns!        
    t_users = sa.Table(
        'users',
        sa.MetaData(),
        sa.Column('id', sa.String(32)),
        sa.Column('name', sa.Unicode(length=100)), # Old column.
        sa.Column('lastname', sa.Unicode(length=50)), # Two new columns.
        sa.Column('firstname', sa.Unicode(length=50)),
        )
    # Use Alchemy connection and transaction to noodle over the data.
    connection = op.get_bind()
    # Select all existing names that need migrating.
    results = connection.execute(sa.select([
        t_users.id,
        t_users.name,
        ])).fetchall()
    # Iterate over all selected data tuples.
    for id_, name in results:
        # Split the existing name into first and last.
        firstname, lastname = name.rsplit(' ', 1)
        # Update the new columns.
        connection.execute(t_users.update().where(t_users.id == id_).values(
            lastname=lastname,
            firstname=firstname,
            ))

    # Schema migration: drop the old column.
    op.drop_column('users', 'name')                                             

:

• Gist, Alembic .
• DB -. -, MySQL (. ", " ). , .

downgrade() .

Answer 2

alembic - , . . . , :

• firstname lastname, name

• , , , first last. .

  for user in session.query(User).all():
      user.firstname, user.lastname = user.name.split(' ')
  session.commit()
  

• name