Why Flask-migrate cannot upgrade when drop column

Question

I am using SqlAlchemy and Flask-migrate for DB migration. I have successfully init the DB and upgrade once, but when I deleted one of my table column, I managed to migrate however upgrade gave me the following error:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near "DROP": syntax error [SQL: u'ALTER TABLE posts DROP COLUMN tags'] 

There is part of my models.py

class Post(db.Model):     __tabelname__ = 'posts'     id = db.Column(db.Integer, primary_key=True)     body = db.Column(db.UnicodeText)     # tags = db.Column(db.Unicode(32))     # I deleted this field, upgrade give me error      .... 

And I run python manage.py db upgrade again, the error changed!

(venv)ncp@ubuntu:~/manualscore$ python manage.py db upgrade INFO  [alembic.migration] Context impl SQLiteImpl. INFO  [alembic.migration] Will assume non-transactional DDL. INFO  [alembic.migration] Running upgrade 555b78ffd5f -> 2e063b1b3164, add tag table Traceback (most recent call last):   File "manage.py", line 79, in <module>     manager.run()   File "/home/ncp/manualscore/venv/local/lib/python2.7/site-packages/flask_script/__init__.py", line 405, in run     result = self.handle(sys.argv[0], sys.argv[1:])   File "/home/ncp/manualscore/venv/local/lib/python2.7/site-packages/flask_script/__init__.py", line 384, in handle     return handle(app, *positional_args, **kwargs)   File "/home/ncp/manualscore/venv/local/lib/python2.7/site-packages/flask_script/commands.py", line 145, in handle     return self.run(*args, **kwargs)   File "/home/ncp/manualscore/venv/local/lib/python2.7/site-packages/flask_migrate/__init__.py", line 177, in upgrade     command.upgrade(config, revision, sql=sql, tag=tag)   File "/home/ncp/manualscore/venv/local/lib/python2.7/site-packages/alembic/command.py", line 165, in upgrade     script.run_env()   File "/home/ncp/manualscore/venv/local/lib/python2.7/site-packages/alembic/script.py", line 390, in run_env     util.load_python_file(self.dir, 'env.py')   File "/home/ncp/manualscore/venv/local/lib/python2.7/site-packages/alembic/util.py", line 243, in load_python_file     module = load_module_py(module_id, path)   File "/home/ncp/manualscore/venv/local/lib/python2.7/site-packages/alembic/compat.py", line 79, in load_module_py     mod = imp.load_source(module_id, path, fp)   File "migrations/env.py", line 72, in <module>     run_migrations_online()   File "migrations/env.py", line 65, in run_migrations_online     context.run_migrations()   File "<string>", line 7, in run_migrations   File "/home/ncp/manualscore/venv/local/lib/python2.7/site-packages/alembic/environment.py", line 738, in run_migrations     self.get_context().run_migrations(**kw)   File "/home/ncp/manualscore/venv/local/lib/python2.7/site-packages/alembic/migration.py", line 309, in run_migrations     step.migration_fn(**kw)   File "/home/ncp/manualscore/migrations/versions/2e063b1b3164_add_tag_table.py", line 24, in upgrade     sa.PrimaryKeyConstraint('id')   File "<string>", line 7, in create_table   File "/home/ncp/manualscore/venv/local/lib/python2.7/site-packages/alembic/operations.py", line 944, in create_table     self.impl.create_table(table)   File "/home/ncp/manualscore/venv/local/lib/python2.7/site-packages/alembic/ddl/impl.py", line 198, in create_table     self._exec(schema.CreateTable(table))   File "/home/ncp/manualscore/venv/local/lib/python2.7/site-packages/alembic/ddl/impl.py", line 122, in _exec     return conn.execute(construct, *multiparams, **params)   File "/home/ncp/manualscore/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 914, in execute     return meth(self, multiparams, params)   File "/home/ncp/manualscore/venv/local/lib/python2.7/site-packages/sqlalchemy/sql/ddl.py", line 68, in _execute_on_connection     return connection._execute_ddl(self, multiparams, params)   File "/home/ncp/manualscore/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 968, in _execute_ddl     compiled   File "/home/ncp/manualscore/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context     context)   File "/home/ncp/manualscore/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1339, in _handle_dbapi_exception     exc_info   File "/home/ncp/manualscore/venv/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause     reraise(type(exception), exception, tb=exc_tb)   File "/home/ncp/manualscore/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context     context)   File "/home/ncp/manualscore/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 442, in do_execute     cursor.execute(statement, parameters) sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) table tags already exists [SQL: u'\nCREATE TABLE tags (\n\tid INTEGER NOT NULL, \n\tname VARCHAR(32), \n\tpost_id INTEGER, \n\tPRIMARY KEY (id), \n\tFOREIGN KEY(post_id) REFERENCES posts (id)\n)\n\n'] 

Answer 1

SQLite does not support dropping or altering columns. However, there is a way to work around this by making changes at the table level: https://www.sqlite.org/lang_altertable.html

And more usefully for Alembic/Flask-Migrate users, Alembic's batch_alter_table context manager lets you specify the changes in a natural way, and does a little "make new table - copy data - drop old table - rename new table" dance behind the scenes when using SQLite. See: http://alembic.zzzcomputing.com/en/latest/batch.html

So the upgrade() function in your migration file should contain something like:

with op.batch_alter_table('posts') as batch_op:     batch_op.drop_column('tags') 

I'm afraid I don't know why the error changed the second time you tried the upgrade.

As tkisme points out, you can also configure the EnvironmentContext.configure.render_as_batch flag in env.py so that autogenerated migration scripts will use batch_alter_table by default. See: http://alembic.zzzcomputing.com/en/latest/batch.html#batch-mode-with-autogenerate