Can you copy/move data between columns in a DB migration file?

Question

I have a SqlAlchemy/Flask application. In it, I have an existing model named MyModelA. This is what it looks like:

class MyModelA(db.Model):
    a_id   = db.Column(db.Integer, nullable=False, primary_key=True)
    my_field1 = db.Column(db.String(1024), nullable=True)

Now, I am adding a child model MyModelB. This is what it looks like:

class MyModelB(db.Model):
    b_id   = db.Column(db.Integer, nullable=False, primary_key=True)
    a_id = db.Column(db.Integer, db.ForeignKey(MyModelA.a_id), nullable=False)
    my_field2 = db.Column(db.String(1024), nullable=True)

Then I run python manage.py migrate. This is what shows up in the migration file:

def upgrade():
    op.create_table('my_model_b',
    sa.Column('b_id', sa.Integer(), nullable=False),
    sa.Column('a_id', sa.Integer(), nullable=False),
    sa.Column('my_field2', sa.String(length=1024), nullable=True),
    sa.ForeignKeyConstraint(['a_id'], [u'my_model_a.a_id'], ),
    sa.PrimaryKeyConstraint('b_id')
    )

def downgrade():
    op.drop_table('my_table_b')

I want to edit this migration such that it for every instance of MyModelA, a child record of instance MyModelB should be created with MyModelB.my_field2 set to MyModelA.my_field1. How can I do it?

Please show the code for upgrade and downgrade.

You can write any database code in your migration script, the basic limitation is that you cannot use your ORM models and instead need to use SQLAlchemy core or plain SQL. Here is an article that has some examples: https://www.julo.ch/blog/migrating-content-with-alembic/. The downgrade does not need to change in your example, by the way, only the upgrade. — Miguel Grinberg, Nov 16 '16 at 05:09
Miguel, thanks. How do you do record-insertion using the `connection.execute()` function? It is not shown there. And how do you get the primary key of the record that you have just inserted? — Saqib Ali, Nov 16 '16 at 05:39
SQLAlchemy documentation: http://docs.sqlalchemy.org/en/latest/core/dml.html — Miguel Grinberg, Nov 16 '16 at 07:22

Yacine Filali · Answer 1 · 2016-11-19T21:20:21.503

Edit:

You can do something like this for the one time migration:

db.engine.execute("INSERT INTO model_b (a_id) select a_id from model_a");

of if you really want sqlalschemy code:

for model in db.query(ModelA).all()
    db.session.add(ModelB(a_id=model.id))
db.session.commit()

Previous answer:

What you are describing is not something you typically do in migrations. Migrations change/create the structure of your database. If you need it to happen every time a new MyModelA is created, this sounds more like events: http://docs.sqlalchemy.org/en/latest/orm/events.html#session-events

class MyModelA(db.Model):
    ...


@sqlalchemy.event.listens_for(SignallingSession, 'before_flush')
def insert_model_b(session, transaction, instances):
    for instance in session.new:
        if isinstance(instance, MyModelA):
            model_b = MyModelB(a=instance)
            session.add(model_b)

Also, your schema needs to show that relationship (not just the foreign key) so you can assign the yet uninserted model_a to model_b.a:

class MyModelB(db.Model):
    b_id   = db.Column(db.Integer, nullable=False, primary_key=True)
    a_id = db.Column(db.Integer, db.ForeignKey(MyModelA.a_id), nullable=False)
    a = relationship("MyModelA")
    my_field2 = db.Column(db.String(1024), nullable=True)

Full code example:

import sqlalchemy
from sqlalchemy.orm import relationship
from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy
from flask.ext.sqlalchemy import SignallingSession

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///:memory:'
app.config['SQLALCHEMY_ECHO'] = True

db = SQLAlchemy(app)

class MyModelA(db.Model):
    __tablename__ = 'model_a'
    a_id = db.Column(db.Integer, nullable=False, primary_key=True)
    my_field1 = db.Column(db.String(1024), nullable=True)

class MyModelB(db.Model):
    __tablename__ = 'model_b'

    b_id = db.Column(db.Integer, nullable=False, primary_key=True)
    a_id = db.Column(db.Integer, db.ForeignKey(MyModelA.a_id), nullable=False)
    a = relationship(MyModelA)
    my_field2 = db.Column(db.String(1024), nullable=True)


@sqlalchemy.event.listens_for(SignallingSession, 'before_flush')
def insert_model_b(session, transaction, instances):
    for instance in session.new:
        if isinstance(instance, MyModelA):
            model_b = MyModelB(a=instance)
            session.add(model_b)

db.create_all()
model_a = MyModelA()
db.session.add(model_a)
db.session.commit()

Actually I do want to do it in migration, I don't need to do it every time a new `MyModelA` is created. I already have data in my DB and I need to copy it in this way. Going forward, my application will make sure to create a new `MyModelB` which will be a child record of `MyModelA` and copy the data. I followed instructions in the SQLAlchemy docs that Miguel provided to achieve it. Thanks. — Saqib Ali, Nov 17 '16 at 04:45
Got it. If its a one time migration then yes, querying the db for ModelA records and creating ModelB records is what you need. Leaving my answer for googlers who may have a different need. — Yacine Filali, Nov 17 '16 at 09:23

Can you copy/move data between columns in a DB migration file?

1 Answers1