python - support - flask sqlalchemy tutorial

Por qué Flask-migrate no puede actualizarse cuando se suelta la columna

Question

Estoy usando SqlAlchemy y Flask-migrate para la migración de la base de datos. He init éxito la base de datos y la upgrade una vez, pero cuando eliminé una de las columnas de mi tabla, logré migrate sin embargo, la upgrade me dio el siguiente error:

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

Hay parte de mis modelos.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
....

Y ejecuto Python manage.py db upgrade de nuevo, ¡el error cambió!

(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

No sé si has resuelto el problema. Pero encuentro una solución muy concisa: puedes hacer esto:

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)

Espero poder ayudarte.

Answer 2

SQLite no admite la eliminación o modificación de columnas. Sin embargo, hay una manera de solucionar este problema haciendo cambios a nivel de tabla: https://www.sqlite.org/lang_altertable.html

Y de manera más útil para los usuarios de Alembic / Flask-Migrate, el administrador de contexto batch_alter_table de Alembic le permite especificar los cambios de una manera natural, y hace un poco "crear una tabla nueva - copiar datos - soltar una tabla antigua - cambiar el nombre de una tabla nueva" bailar detrás de la escena cuando utilizando SQLite. Consulte: http://alembic.zzzcomputing.com/en/latest/batch.html

Así que la función upgrade () en su archivo de migración debe contener algo como:

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

Me temo que no sé por qué el error cambió la segunda vez que intentó la actualización.

Como lo señala tkisme, también puede configurar el indicador EnvironmentContext.configure.render_as_batch en env.py para que los scripts de migración batch_alter_table automáticamente utilicen batch_alter_table de forma predeterminada. Consulte: http://alembic.zzzcomputing.com/en/latest/batch.html#batch-mode-with-autogenerate

Answer 3

cambie las migraciones / env.py add render_as_batch=True en context.configure

def run_migrations_online():
"""Run migrations in ''online'' mode.
In this scenario we need to create an Engine
and associate a connection with the context.
"""
# this callback is used to prevent an auto-migration from being generated
# when there are no changes to the schema
# reference: http://alembic.readthedocs.org/en/latest/cookbook.html
def process_revision_directives(context, revision, directives):
if getattr(config.cmd_opts, ''autogenerate'', False):
script = directives[0]
if script.upgrade_ops.is_empty():
directives[:] = []
logger.info(''No changes in schema detected.'')
engine = engine_from_config(config.get_section(config.config_ini_section),
prefix=''sqlalchemy.'',
poolclass=pool.NullPool)
connection = engine.connect()
context.configure(connection=connection,
target_metadata=target_metadata,
process_revision_directives=process_revision_directives,
render_as_batch=True,# this is new feature
**current_app.extensions[''migrate''].configure_args)
try:
with context.begin_transaction():
context.run_migrations()
finally:
connection.close()