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.
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()
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With