没有明显的原因,我们无法创建没有外键的外键

我试图升级一个数据库,但是迁移脚本一直在创建一个无外键约束。我试图从用户表中删除一个名为kiosk的布尔值,同时添加一个与backref的kiosk的kiosk\u id表的多对1关系。如果我编写一个脚本来删除kiosk boolean,它会很好地删除它,但是它拒绝构建从user表到kiosk\u id表的多对1关系。

"""
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 ###

我试图添加以下关系。

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)

我在删除kiosk boolean列时遇到了问题,但是通过在我的列表中添加“rend\u as\u batch=True”来修复它 初始化

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)
    ...

下面是在运行flask db migrate之后运行flask db upgrade时发生的情况。

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

我把它修好了。我更改了以下alembic行:

batch_op.create_foreign_key(None, 'kiosk_id', ['kiosk_id'], ['id'])

为了这个

batch_op.create_foreign_key('None', 'kiosk_id', ['kiosk_id'], ['id'])

我给它起什么名字重要吗?('None')可能会在以后引起问题,但我不知道为什么它首先要我说出这段关系。