我使用SqlAlchemy和Flask migrate进行数据库迁移。我已经成功地init
了DB和upgrade
一次,但是当我删除一个表列时,我设法migrate
但是upgrade
给了我以下错误:
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near "DROP": syntax error [SQL: u'ALTER TABLE posts DROP COLUMN tags']
我有一部分模特.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
....
我再次运行python manage.py db upgrade,错误改变了!
(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']
SQLite不支持删除或更改列。但是,有一种方法可以通过在表级别进行更改来解决这个问题:https://www.sqlite.org/lang_altertable.html
对于Alembic/Flask Migrate用户更有用的是,Alembic的batch_alter_table context manager允许您以自然的方式指定更改,并在使用SQLite时在后台执行一些“make new table-copy data-drop old table-rename new table”的动作。见:http://alembic.zzzcomputing.com/en/latest/batch.html
因此,迁移文件中的upgrade()函数应该包含如下内容:
恐怕我不知道你第二次尝试升级时为什么错误改变了。
正如tkisme指出的,您还可以在
env.py
中配置EnvironmentContext.configure.render_as_batch
标志,以便自动生成的迁移脚本默认使用batch_alter_table
。见:http://alembic.zzzcomputing.com/en/latest/batch.html#batch-mode-with-autogenerate更改迁移/env.py 在context.configure中添加
render_as_batch=True
我不知道你已经解决了这个问题。 但我找到了一个非常简洁的解决方案: 你可以这样做:
希望能帮助你。
相关问题 更多 >
编程相关推荐