This could leave your database in an inconsistent state, where some changes from the migration script have been applied, but because of the error the version information is still pointing to the previous migration. There are situations in which batch mode alone does not solve upgrade errors.Ī nasty type of issue occurs when the ALTER TABLE error occurs in the middle of a migration, after some operations were already applied. With op.batch_alter_table('user', schema=None) as batch_op:īatch_op.add_column(sa.Column('about_me', sa.VARCHAR(length=140), nullable=True)) Nothing appears to be different, but if you look at the new migration script, you will see differences: """remove a column Now that you have batch mode enabled, try to generate the migration again: (venv) $ flask db migrate -m "remove a column" Migrate.init_app(app, db, render_as_batch=True) If you use the two-step initialization: migrate = Migrate() If you use the direct method of initialization: migrate = Migrate(app, db, render_as_batch=True) You can enable batch mode in Flask-Migrate right when you initialize the extension. Alembic includes support for migrating a table in this way with a feature called "batch mode". The only way to do this is to generate a brand new table with the new schema and copy all the data. Unfortunately there is no simple way to perform these operations that SQLite has not implemented. You will learn how to generate migrations that work better in the next section. If you tried this on your database, delete the broken migration script before continuing. : (sqlite3.OperationalError) near "DROP": syntax error The above exception was the direct cause of the following exception: Sqlite3.OperationalError: near "DROP": syntax error INFO Running upgrade 834b1a697901 -> ec813e760b53, remove a columnįile "/Users/mgrinberg/Documents/dev/python/microblog/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1283, in _execute_contextįile "/Users/mgrinberg/Documents/dev/python/microblog/venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 590, in do_execute The problem occurs when you try to upgrade the database with this migration: (venv) $ flask db upgrade Op.add_column('user', sa.Column('about_me', sa.VARCHAR(length=140), nullable=True)) # commands auto generated by Alembic - please adjust! # Below you can see the migration that was generated after I removed a column named about_me from the User model: """remove a column If you open the generated migration script everything will look correct. Then generate a migration: (venv) $ flask db migrate -m "remove a column" Take any Flask-SQLAlchemy application (you can use one of mine) and after making sure your database is up to date, remove or comment out a column in one of the models. Any other change to columns or constraints is going to be rejected with an error. With SQLite, however, the ALTER TABLE command only supports adding or renaming columns. If you are using MySQL, Postgres or most other database servers besides SQLite, this isn't a problem. SQLite's ALTER TABLE ImplementationĬhanges that you make to the fields in your model or to the constraints associated with them will end up as an ALTER TABLE statement sent to the database. In this article I'm going to discuss this limitation of the SQLite database, and show you a workaround that is specific to Flask-Migrate and Alembic. When working with a migration framework such as Flask-Migrate, it is common to end up with migration scripts that fail to upgrade or downgrade just because they need to remove or modify a column in a table, something that SQLite does not support. If you've done any work with SQLite databases you surely know that this database is very limited in terms of making changes to the database schema.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |