2

I had a User and channel class in my model.py. They had many-to-many relationships. I decided to remove the many-to-many relationships between User and channel and add the many-to-many relationship between Usertemp class and channel class.

After this, I issue: 

flask db migrate
flask db upgrade

However I received the error message that SQLite cannot perform this operation consider batch migration. I looked at other familiar topics such as https://github.com/miguelgrinberg/Flask-Migrate/issues/61#issuecomment-208131722 or Why Flask-migrate cannot upgrade when drop column

I have come across this code in both of the above links:

with app.app_context():
    if db.engine.url.drivername == 'sqlite':
        migrate.init_app(app, db, render_as_batch=True)
    else:
        migrate.init_app(app, db)

However maybe there is more do to since I still receive the SQLite error.

More Info: After looking at my version files in migration folder I had none as the name of some tables:

This is the first version of my migration file that I use batch migration to drop the column I guess the None is creating the problems:

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('enroll', sa.Column('usertemp_id', sa.Integer(), nullable=True))
    op.drop_constraint(None, 'enroll', type_='foreignkey')
    op.create_foreign_key(None, 'enroll', 'usertemp', ['usertemp_id'], ['id'])
    with op.batch_alter_table('enroll') as batch_op:
        batch_op.drop_column('user_id')

Then I added the following to my code: 

convention = {
    "ix": 'ix_%(column_0_label)s',
    "uq": "uq_%(table_name)s_%(column_0_name)s",
    "ck": "ck_%(table_name)s_%(constraint_name)s",
    "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
    "pk": "pk_%(table_name)s"
}

metadata = MetaData(naming_convention=convention)
db = SQLAlchemy(app, metadata=metadata)

And the run the flask db upgrade but again I receive error: Following is my updated migration version: 

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint(None, 'enroll', type_='foreignkey')
    op.create_foreign_key(op.f('fk_enroll_usertemp_id_usertemp'), 'enroll', 'usertemp', ['usertemp_id'], ['id'])
    op.drop_column('enroll', 'user_id')
    op.create_unique_constraint(op.f('uq_user_name'), 'user', ['name'])

I suspect the solution given for render_as_batch=true can fix the issue since some people have posted it is working for them. However can anyone explain how to add it to my files, where I should add it? I suspect it should be added env.py file. However then what else I should change after? Is adding the above code including render_as_batch fix the problem by itself.

I would appreciate it if anyone can help since right now anytime I make a mistake I issuedb.drop_all() and create the tables again, which is very inefficient.

math
  • 341
  • 4
  • 14
  • You need to have the `render_as_batch=True` option enabled when you generate the migration script. – Miguel Grinberg May 22 '20 at 18:21
  • Thank you. Do I only need to enable this and issue flask db migrate and flask db upgrade? Is there any need for naming convention or anything similar. I am referring to the documentation here https://alembic.sqlalchemy.org/en/latest/batch.html – math May 22 '20 at 21:16
  • Yes, all those potential problems with unnamed constraints need to be considered, unfortunately. – Miguel Grinberg May 23 '20 at 15:39

0 Answers0