python - 使用 alembic 对 flask_sqlalchemy 中新添加的不可空列进行迁移时出现 "ALTER"语法错误

Question

当我向现有表添加新列(nullable=False)时，我需要手动更新迁移修订文件以首先添加 nullable=True 的列，然后更新所有现有记录以设置该列，之后将该列更改为 nullable=False。但是遇到了“ALTER”的错误:syntax error。

这是测试脚本(test.py):

#!/usr/bin/env python
import os

import flask_migrate
from flask import Flask
from flask_script import Manager
from flask_sqlalchemy import SQLAlchemy

basedir = os.path.abspath(os.path.dirname(__file__))

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] =\
    'sqlite:///' + os.path.join(basedir, 'data.sqlite')
app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = True
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

manager = Manager(app)
db = SQLAlchemy(app)

class User(db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)

migrate = flask_migrate.Migrate(app, db)
manager.add_command('db', flask_migrate.MigrateCommand)

if __name__ == '__main__':
    manager.run()

首先我初始化了数据库并将其升级到最新版本:

$ python test.py db init && python test.py db migrate && python test.py db upgrade
  Creating directory /tmp/test/migrations ... done
  Creating directory /tmp/test/migrations/versions ... done
  Generating /tmp/test/migrations/env.pyc ... done
  Generating /tmp/test/migrations/script.py.mako ... done
  Generating /tmp/test/migrations/env.py ... done
  Generating /tmp/test/migrations/alembic.ini ... done
  Generating /tmp/test/migrations/README ... done
  Please edit configuration/connection/logging settings in '/tmp/test/migrations/alembic.ini' before proceeding.
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added table 'users'
  Generating /tmp/test/migrations/versions/86805d015930_.py ... done
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> 86805d015930, empty message

然后我更新了模型以添加新列“email”，它是 nullable=False:

<snip>
...
class User(db.Model):
    __tablename__ = 'users'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), nullable=False)  # this is the new column

然后生成迁移修订文件:

$ python test.py db migrate -m "add name"
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added column 'users.name'
  Generating /tmp/test/migrations/versions/c89371227a53_add_name.py ... done

由于名称列不可为空，需要手动更新迁移文件，更新如下:

$ cat /tmp/test/migrations/versions/c89371227a53_add_name.py

from alembic import op
import sqlalchemy as sa

revision = 'c89371227a53'
down_revision = '45a51b6df68c'
branch_labels = None
depends_on = None

def upgrade():
    op.add_column('users', sa.Column('name', sa.String(length=64), nullable=True))
    op.execute("""
    UPDATE users SET name="foobar"
    """)
    op.alter_column('users', 'name', nullable=False)


def downgrade():
    op.drop_column('users', 'name')

现在运行迁移:

$ python test.py db upgrade

我收到如下错误:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near "ALTER": syntax error [SQL: u'ALTER TABLE users ALTER COLUMN name SET NOT NULL']

我该如何解决这个问题，或者我应该如何为这种情况进行迁移？

我的环境是:

Flask==0.12.1
Flask-Migrate==2.0.3
Flask-Script==2.0.5
Flask-SQLAlchemy==2.2
SQLAlchemy==1.1.9
alembic==0.9.1

Answer 1

刚刚弄清楚原因，因为我使用的是 sqlite，而 sqlite 缺少 ALTER 支持，解决方法是使用批处理操作迁移 http://alembic.zzzcomputing.com/en/latest/batch.html