I am trying to upgrade a database, but the migration script keeps creating a Foreign key constraint of None. I am trying to drop a boolean called kiosk form the user table while adding a many to 1 relationship with a kiosk_id table that backref's kiosk. If I do a script to drop the kiosk boolean it drops it fine, but it refuses to build the many to 1 relationship from the user table to the kiosk_id table.
Here is the migration script
"""
Revision ID: f76e23eb53f4
Revises: 3354e9f7076a
Create Date: 2020-09-02 20:13:22.217435
"""
from alembic import op
import sqlalchemy as sa
# revision identifiers, used by Alembic.
revision = 'f76e23eb53f4'
down_revision = '3354e9f7076a'
branch_labels = None
depends_on = None
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('kiosk_id',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=128), nullable=False),
sa.PrimaryKeyConstraint('id')
)
op.create_table('kiosk_response_id',
sa.Column('id', sa.Integer(), nullable=False),
sa.PrimaryKeyConstraint('id')
)
op.create_table('kiosk_question',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('question', sa.String(length=256), nullable=True),
sa.Column('kiosk_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(['kiosk_id'], ['kiosk_id.id'], ),
sa.PrimaryKeyConstraint('id')
)
op.create_table('kiosk_response',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('response', sa.String(length=256), nullable=False),
sa.Column('response_id', sa.Integer(), nullable=True),
sa.Column('question_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(['question_id'], ['kiosk_question.id'], ),
sa.ForeignKeyConstraint(['response_id'], ['kiosk_response_id.id'], ),
sa.PrimaryKeyConstraint('id')
)
with op.batch_alter_table('user', schema=None) as batch_op:
batch_op.add_column(sa.Column('kiosk_id', sa.Integer(), nullable=True))
#THIS IS THE PROBLEM
batch_op.create_foreign_key(None, 'kiosk_id', ['kiosk_id'], ['id'])
#HERE IS THE ISSUE
batch_op.drop_column('kiosk')
# ### end Alembic commands ###
def downgrade():
# ### commands auto generated by Alembic - please adjust! ###
with op.batch_alter_table('user', schema=None) as batch_op:
batch_op.add_column(sa.Column('kiosk', sa.BOOLEAN(), nullable=True))
batch_op.drop_constraint(None, type_='foreignkey')
batch_op.drop_column('kiosk_id')
op.drop_table('kiosk_response')
op.drop_table('kiosk_question')
op.drop_table('kiosk_response_id')
op.drop_table('kiosk_id')
# ### end Alembic commands ###
I am trying to add the following relationships.
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(32), index=True, unique=True, nullable=False)
password_hash = db.Column(db.String(128), nullable=False)
#Dropping this boolean and replacing with a backref from Kiosk_ID
#kiosk=db.Column(db.Boolean, default=False)
#Adding this foriegn key for a 1 to many from kiosk_id
kiosk_id=db.Column(db.Integer, db.ForeignKey('kiosk_id.id'))
def __repr__(self): # pragma: no cover
return '<user> %r' % (self.username)
class Kiosk_ID(db.Model):
__tablename__='kiosk_id'
id=db.Column(db.Integer, primary_key=True)
name=db.Column(db.String(128), nullable=False)
users=db.relationship('User', backref='kiosk')
questions=db.relationship('Kiosk_Question', backref='kiosk')
def __repr__(self): # pragma: no cover
return '<Kiosk_ID: > %r' % (self.name)
class Kiosk_Question(db.Model):
__tablename__='kiosk_question'
id=db.Column(db.Integer, primary_key=True)
question=db.Column(db.String(256))
kiosk_id=db.Column(db.Integer, db.ForeignKey('kiosk_id.id'))
responses=db.relationship('Kiosk_Response', backref='question')
def __repr__(self): # pragma: no cover
return '<Question: > %r' % (self.question)
class Kiosk_Response_ID(db.Model):
__tablename__='kiosk_response_id'
id=db.Column(db.Integer, primary_key=True)
responses=db.relationship('Kiosk_Response', backref='kiosk_response')
def __repr__(self): # pragma: no cover
return '<Response ID: > %r' % (self.id)
class Kiosk_Response(db.Model):
__tablename__='kiosk_response'
id=db.Column(db.Integer, primary_key=True)
response=db.Column(db.String(256), nullable=False)
response_id=db.Column(db.Integer, db.ForeignKey('kiosk_response_id.id'))
question_id=db.Column(db.Integer, db.ForeignKey('kiosk_question.id'))
def __repr__(self): # pragma: no cover
return '<Response: > %r' % (self.response)
I was having trouble with it dropping the kiosk boolean column, but fixed it by adding "rend_as_batch=True to my init. I don't think it is related, but I can not figure out why this error keeps popping up and I made the change around the same time.
def create_app(config_class=Config):
...
#Fixes flask db upgrade to allow deleting columns
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)
...
Here is what happens when I run flask db upgrade after running flask db migrate.
INFO [alembic.runtime.migration] Running upgrade 3354e9f7076a -> 3433e257dcb1, empty message
Traceback (most recent call last):
File "/home/bob/cat/env/bin/flask", line 8, in <module>
sys.exit(main())
File "/home/bob/cat/env/lib/python3.6/site-packages/flask/cli.py", line 967, in main
cli.main(args=sys.argv[1:], prog_name="python -m flask" if as_module else None)
File "/home/bob/cat/env/lib/python3.6/site-packages/flask/cli.py", line 586, in main
return super(FlaskGroup, self).main(*args, **kwargs)
File "/home/bob/cat/env/lib/python3.6/site-packages/click/core.py", line 782, in main
rv = self.invoke(ctx)
File "/home/bob/cat/env/lib/python3.6/site-packages/click/core.py", line 1259, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File "/home/bob/cat/env/lib/python3.6/site-packages/click/core.py", line 1259, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File "/home/bob/cat/env/lib/python3.6/site-packages/click/core.py", line 1066, in invoke
return ctx.invoke(self.callback, **ctx.params)
File "/home/bob/cat/env/lib/python3.6/site-packages/click/core.py", line 610, in invoke
return callback(*args, **kwargs)
File "/home/bob/cat/env/lib/python3.6/site-packages/click/decorators.py", line 21, in new_func
return f(get_current_context(), *args, **kwargs)
File "/home/bob/cat/env/lib/python3.6/site-packages/flask/cli.py", line 426, in decorator
return __ctx.invoke(f, *args, **kwargs)
File "/home/bob/cat/env/lib/python3.6/site-packages/click/core.py", line 610, in invoke
return callback(*args, **kwargs)
File "/home/bob/cat/env/lib/python3.6/site-packages/flask_migrate/cli.py", line 134, in upgrade
_upgrade(directory, revision, sql, tag, x_arg)
File "/home/bob/cat/env/lib/python3.6/site-packages/flask_migrate/__init__.py", line 96, in wrapped
f(*args, **kwargs)
File "/home/bob/cat/env/lib/python3.6/site-packages/flask_migrate/__init__.py", line 271, in upgrade
command.upgrade(config, revision, sql=sql, tag=tag)
File "/home/bob/cat/env/lib/python3.6/site-packages/alembic/command.py", line 298, in upgrade
script.run_env()
File "/home/bob/cat/env/lib/python3.6/site-packages/alembic/script/base.py", line 489, in run_env
util.load_python_file(self.dir, "env.py")
File "/home/bob/cat/env/lib/python3.6/site-packages/alembic/util/pyfiles.py", line 98, in load_python_file
module = load_module_py(module_id, path)
File "/home/bob/cat/env/lib/python3.6/site-packages/alembic/util/compat.py", line 184, in load_module_py
spec.loader.exec_module(module)
File "<frozen importlib._bootstrap_external>", line 678, in exec_module
File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
File "migrations/env.py", line 96, in <module>
run_migrations_online()
File "migrations/env.py", line 90, in run_migrations_online
context.run_migrations()
File "<string>", line 8, in run_migrations
File "/home/bob/cat/env/lib/python3.6/site-packages/alembic/runtime/environment.py", line 846, in run_migrations
self.get_context().run_migrations(**kw)
File "/home/bob/cat/env/lib/python3.6/site-packages/alembic/runtime/migration.py", line 520, in run_migrations
step.migration_fn(**kw)
File "/home/bob/cat/migrations/versions/3433e257dcb1_.py", line 49, in upgrade
batch_op.drop_column('kiosk')
File "/usr/lib/python3.6/contextlib.py", line 88, in __exit__
next(self.gen)
File "/home/bob/cat/env/lib/python3.6/site-packages/alembic/operations/base.py", line 354, in batch_alter_table
impl.flush()
File "/home/bob/cat/env/lib/python3.6/site-packages/alembic/operations/batch.py", line 114, in flush
fn(*arg, **kw)
File "/home/bob/cat/env/lib/python3.6/site-packages/alembic/operations/batch.py", line 497, in add_constraint
raise ValueError("Constraint must have a name")
ValueError: Constraint must have a name
I fixed it, sort of. I changed the following alembic line:
batch_op.create_foreign_key(None, 'kiosk_id', ['kiosk_id'], ['id'])
to this
batch_op.create_foreign_key('None', 'kiosk_id', ['kiosk_id'], ['id'])
Does it matter what name I give it? ('None') could cause issues later I suppose, but I am not sure why it is asking me to name the relationship in the first place.
