Flask SQLAlchemy upgrade failing after updating models. Need an explanation on how my fix works

Question

I'm following along with a book to try and create a Flask blog. I am new to Flask. I updated a model so the code looked like this:

class Entry(db.Model):
    STATUS_PUBLIC = 0
    STATUS_DRAFT = 1
    STATUS_DELETED = 2

    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100))
    slug = db.Column(db.String(100), unique=True)
    body = db.Column(db.Text)
    status = db.Column(db.SmallInteger, default=STATUS_PUBLIC)
    created_timestamp = db.Column(db.DateTime, default=datetime.datetime.now)
    modified_timestamp = db.Column(
        db.DateTime,
        default=datetime.datetime.now,
        onupdate=datetime.datetime.now)
    author_id = db.Column(db.Integer, db.ForeignKey("user.id"))

Adding the last line author_id = db.Column(db.Integer, db.ForeignKey("user.id")) is causing the database upgrade to fail with an error:

  File "manage.py", line 5, in <module>
    manager.run()
  File "/home/devblog/projects/blog/venv/lib/python2.7/site-packages/flask_script/__init__.py", line 412, in run
    result = self.handle(sys.argv[0], sys.argv[1:])
  File "/home/devblog/projects/blog/venv/lib/python2.7/site-packages/flask_script/__init__.py", line 383, in handle
    res = handle(*args, **config)
  File "/home/devblog/projects/blog/venv/lib/python2.7/site-packages/flask_script/commands.py", line 216, in __call__
    return self.run(*args, **kwargs)
  File "/home/devblog/projects/blog/venv/lib/python2.7/site-packages/flask_migrate/__init__.py", line 259, in upgrade
    command.upgrade(config, revision, sql=sql, tag=tag)
  File "/home/devblog/projects/blog/venv/lib/python2.7/site-packages/alembic/command.py", line 254, in upgrade
    script.run_env()
  File "/home/devblog/projects/blog/venv/lib/python2.7/site-packages/alembic/script/base.py", line 425, in run_env
    util.load_python_file(self.dir, 'env.py')
  File "/home/devblog/projects/blog/venv/lib/python2.7/site-packages/alembic/util/pyfiles.py", line 93, in load_python_file
    module = load_module_py(module_id, path)
  File "/home/devblog/projects/blog/venv/lib/python2.7/site-packages/alembic/util/compat.py", line 75, in load_module_py
    mod = imp.load_source(module_id, path, fp)
  File "migrations/env.py", line 87, in <module>
    run_migrations_online()
  File "migrations/env.py", line 80, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/home/devblog/projects/blog/venv/lib/python2.7/site-packages/alembic/runtime/environment.py", line 836, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/home/devblog/projects/blog/venv/lib/python2.7/site-packages/alembic/runtime/migration.py", line 330, in run_migrations
    step.migration_fn(**kw)
  File "/home/devblog/projects/blog/app/migrations/versions/6b49bdaf06ce_.py", line 22, in upgrade
    batch_op.create_foreign_key(None, 'user', ['author_id'], ['id'])
  File "/usr/lib64/python2.7/contextlib.py", line 24, in __exit__
    self.gen.next()
  File "/home/devblog/projects/blog/venv/lib/python2.7/site-packages/alembic/operations/base.py", line 299, in batch_alter_table
    impl.flush()
  File "/home/devblog/projects/blog/venv/lib/python2.7/site-packages/alembic/operations/batch.py", line 80, in flush
    fn(*arg, **kw)
  File "/home/devblog/projects/blog/venv/lib/python2.7/site-packages/alembic/operations/batch.py", line 337, in add_constraint
    raise ValueError("Constraint must have a name")
ValueError: Constraint must have a name

The issue seemed to be with the migration script's upgrade function here:

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table('entry', schema=None) as batch_op:
        #batch_op.create_foreign_key(None, 'user', ['author_id'], ['id'])
        batch_op.create_foreign_key("author_id", 'user', ['author_id'], ['id'])

I "fixed" this by commenting out the original line, removing None, and using 'author_id'.

I used the documentation here http://alembic.zzzcomputing.com/en/latest/ops.html to come up with the fix. Relevant section:

create_foreign_key(constraint_name, source_table, referent_table, local_cols, remote_cols, onupdate=None, ondelete=None, deferrable=None, initially=None, match=None, source_schema=None, referent_schema=None, **dialect_kw)

This internally generates a Table object containing the necessary columns, then generates a new ForeignKeyConstraint object which it then associates with the Table. Any event listeners associated with this action will be fired off normally. The AddConstraint construct is ultimately used to generate the ALTER statement.

Parameters: name – Name of the foreign key constraint. The name is necessary so that an ALTER statement can be emitted. For setups that use an automated naming scheme such as that described at Configuring Constraint Naming Conventions, name here can be None, as the event listener will apply the name to the constraint object when it is associated with the table. source_table – String name of the source table. referent_table – String name of the destination table. local_cols – a list of string column names in the source table. remote_cols – a list of string column names in the remote table. onupdate – Optional string. If set, emit ON UPDATE when issuing DDL for this constraint. Typical values include CASCADE, DELETE and RESTRICT. ondelete – Optional string. If set, emit ON DELETE when issuing DDL for this constraint. Typical values include CASCADE, DELETE and RESTRICT. deferrable – optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when issuing DDL for this constraint. source_schema – Optional schema name of the source table. referent_schema¶ – Optional schema name of the destination table.

I think I've added a constraint name instead of None, which has "fixed" the issue. Is this correct?

Also, it looks like there are 5 arguments necessary for the function to work. How does it work with just 4?

Any advice gratefully appreciated.

Answer 1

Mark Steward's solution worked perfectly for me for the case of adding a simple foreignkey column.

Wherever you declare / define your "db", do the following -

from sqlalchemy import MetaData

naming_convention = {
    "ix": 'ix_%(column_0_label)s',
    "uq": "uq_%(table_name)s_%(column_0_name)s",
    "ck": "ck_%(table_name)s_%(column_0_name)s",
    "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
    "pk": "pk_%(table_name)s"
}
db = SQLAlchemy(metadata=MetaData(naming_convention=naming_convention))

And then, where you initialize your migration app (flask-migrate), pass render_as_batch=True -

migrate.init_app(app, db, render_as_batch=True)