python - SQLAlchemy db.drop_all() 给出 AttributeError : 'NoneType' object has no attribute 'lower'

Question

我正在使用 Flask 及其各种扩展开发 API。我有一个与本地 MySQL 数据库建立连接的数据库。当我尝试执行 db.drop_all() 时，出现以下错误:

Traceback (most recent call last):
  File "/usr/lib/python3.6/code.py", line 91, in runcode
    exec(code, self.locals)
  File "<input>", line 2, in <module>
  File "/home/zahessi/.local/lib/python3.6/site-packages/flask_sqlalchemy/__init__.py", line 971, in drop_all
    self._execute_for_all_tables(app, bind, 'drop_all')
  File "/home/zahessi/.local/lib/python3.6/site-packages/flask_sqlalchemy/__init__.py", line 955, in _execute_for_all_tables
    op(bind=self.get_engine(app, bind), **extra)
  File "/home/zahessi/.local/lib/python3.6/site-packages/sqlalchemy/sql/schema.py", line 4032, in drop_all
    tables=tables)
  File "/home/zahessi/.local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1940, in _run_visitor
    conn._run_visitor(visitorcallable, element, **kwargs)
  File "/home/zahessi/.local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1549, in _run_visitor
    **kwargs).traverse_single(element)
  File "/home/zahessi/.local/lib/python3.6/site-packages/sqlalchemy/sql/visitors.py", line 121, in traverse_single
    return meth(obj, **kw)
  File "/home/zahessi/.local/lib/python3.6/site-packages/sqlalchemy/sql/ddl.py", line 909, in visit_metadata
    self.traverse_single(fkc)
  File "/home/zahessi/.local/lib/python3.6/site-packages/sqlalchemy/sql/visitors.py", line 121, in traverse_single
    return meth(obj, **kw)
  File "/home/zahessi/.local/lib/python3.6/site-packages/sqlalchemy/sql/ddl.py", line 971, in visit_foreign_key_constraint
    self.connection.execute(DropConstraint(constraint))
  File "/home/zahessi/.local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "/home/zahessi/.local/lib/python3.6/site-packages/sqlalchemy/sql/ddl.py", line 68, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "/home/zahessi/.local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1003, in _execute_ddl
    if not self.schema_for_object.is_default else None)
  File "<string>", line 1, in <lambda>
  File "/home/zahessi/.local/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 442, in compile
    return self._compiler(dialect, bind=bind, **kw)
  File "/home/zahessi/.local/lib/python3.6/site-packages/sqlalchemy/sql/ddl.py", line 26, in _compiler
    return dialect.ddl_compiler(dialect, self, **kw)
  File "/home/zahessi/.local/lib/python3.6/site-packages/sqlalchemy/sql/compiler.py", line 219, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
  File "/home/zahessi/.local/lib/python3.6/site-packages/sqlalchemy/sql/compiler.py", line 245, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/home/zahessi/.local/lib/python3.6/site-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
    return meth(self, **kw)
  File "/home/zahessi/.local/lib/python3.6/site-packages/sqlalchemy/dialects/mysql/base.py", line 1312, in visit_drop_constraint
    const = self.preparer.format_constraint(constraint)
  File "<string>", line 1, in <lambda>
  File "/home/zahessi/.local/lib/python3.6/site-packages/sqlalchemy/sql/compiler.py", line 3151, in format_constraint
    return self.quote(constraint.name)
  File "/home/zahessi/.local/lib/python3.6/site-packages/sqlalchemy/sql/compiler.py", line 3101, in quote
    if self._requires_quotes(ident):
  File "/home/zahessi/.local/lib/python3.6/site-packages/sqlalchemy/sql/compiler.py", line 3072, in _requires_quotes
    lc_value = value.lower()
AttributeError: 'NoneType' object has no attribute 'lower'

唯一有帮助的是删除整个数据库，然后重新创建它。但我只通过 CLI 执行此操作，因此没有传统的方法可以在代码中执行此操作。

编辑:数据库在该工厂中初始化:

def create_app():
    from flask import Flask
    from models import db, ma
    from flask_compress import Compress
    from sqlathanor import initialize_flask_sqlathanor

    # initialization
    app = Flask(__name__)
    app.config.from_object('config.Config')
    db.init_app(app)
    ma.init_app(app)
    initialize_flask_sqlathanor(db)
    Compress(app)

    # blueprints
    from blueprints.users_crud.views import MANAGE_USERS_BLUE
    from blueprints.projects_tasks.views import PROJECTS_BLUE
    app.register_blueprint(MANAGE_USERS_BLUE)
    app.register_blueprint(PROJECTS_BLUE)

    return app, db

正如我所测试的，这个错误并不依赖于这里调用的扩展。我的目标是删除所有表。

Answer 1

如果没有为约束设置名称，SQLAlchemy 不会为其创建一个名称并将约束名称的分配留给数据库。 ^[1]

在以这种方式创建约束的表上执行 drop 或 alter 语句时，此方法会出错。这是因为 SQLAlchemy 不知道要为约束解析什么名称。 ^[2]

虽然通过以下方式删除所有表时应该可以反射(reflect)表及其外键:

@manager.command
def dropdb():
    db.metadata.reflect(bind=db.engine)
    db.metadata.drop_all(db.engine)

对于 MySQL 方言，外键不会反射(reflect)在表中，并且在反射(reflect)时需要指定 ForeignKeyConstraint 名称。 ^[3] 当您查看生成的迁移脚本时，Alembic 就是这样做的。

在回答您的问题时，需要命名约束。例如

class Branch(Base):
    //...
    project_id = Column(Integer, ForeignKey('projects.id', name="fk_branch_project_id_projects"))

为应用程序模型中声明的所有 ForeignKey 执行此操作很痛苦，SQLAlchemy 提供了一种更好的方法来提供一致的命名约定。用于约束的命名约定需要写入models.py。 ^[4]

#...
from sqlalchemy import Column, Integer, DateTime, MetaData

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

除此之外，我强烈建议使用像 Alembic 这样的迁移工具，而不是每次都删除并创建所有表以将更改应用于应用程序模型。