Skip to main content
 首页 » 编程设计

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

2024年12月31日8duanxz

我正在使用 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 

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

请您参考如下方法:

如果没有为约束设置名称,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 这样的迁移工具,而不是每次都删除并创建所有表以将更改应用于应用程序模型。