python - sqlalchemy.exc.OperationalError : (sqlite3. OperationalError)没有这样的列: false

Question

我已在本地成功运行我的 Flask 应用程序，但是当我将其部署到我的产品环境时，出现此错误:

[2019-08-26 00:15:36,229] ERROR in app: Exception on /formulas [GET]
Traceback (most recent call last):
  File "/home/ubuntu/.local/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context
    cursor, statement, parameters, context
  File "/home/ubuntu/.local/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: no such column: false

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/ubuntu/.local/lib/python3.5/site-packages/flask/app.py", line 1949, in full_dispatch_request
    rv = self.dispatch_request()
  File "/home/ubuntu/.local/lib/python3.5/site-packages/flask/app.py", line 1935, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "app.py", line 96, in get_all_formulas
    for formula in query_results:
  File "/home/ubuntu/.local/lib/python3.5/site-packages/sqlalchemy/orm/query.py", line 3334, in __iter__
    return self._execute_and_instances(context)
  File "/home/ubuntu/.local/lib/python3.5/site-packages/sqlalchemy/orm/query.py", line 3359, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/home/ubuntu/.local/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 988, in execute
    return meth(self, multiparams, params)
  File "/home/ubuntu/.local/lib/python3.5/site-packages/sqlalchemy/sql/elements.py", line 287, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/home/ubuntu/.local/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1107, in _execute_clauseelement
    distilled_params,
  File "/home/ubuntu/.local/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context
    e, statement, parameters, cursor, context
  File "/home/ubuntu/.local/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1466, in _handle_dbapi_exception
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  File "/home/ubuntu/.local/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 398, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
    File "/home/ubuntu/.local/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 152, in reraise
    raise value.with_traceback(tb)
  File "/home/ubuntu/.local/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context
    cursor, statement, parameters, context
  File "/home/ubuntu/.local/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such column: false
[SQL: SELECT all_formulas.id AS all_formulas_id, all_formulas.name AS all_formulas_name, all_formulas.abbreviation AS all_formulas_abbreviation, all_fo
rmulas.category_name AS all_formulas_category_name, all_formulas.category_id AS all_formulas_category_id, all_formulas.parent_id AS all_formulas_parent
_id, all_formulas.has_children AS all_formulas_has_children, all_formulas.function AS all_formulas_function 
FROM all_formulas 
WHERE all_formulas.parent_id IS NULL]
(Background on this error at: http://sqlalche.me/e/e3q8)

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/ubuntu/.local/lib/python3.5/site-packages/flask/app.py", line 2446, in wsgi_app
    response = self.full_dispatch_request()
  File "/home/ubuntu/.local/lib/python3.5/site-packages/flask/app.py", line 1951, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/home/ubuntu/.local/lib/python3.5/site-packages/flask_api/app.py", line 96, in handle_user_exception
    app_handlers = self.error_handler_spec[None].get(None, ())
KeyError

如您所见，主要错误是sqlite3.OperationalError: no such column: false。但是，SQLAlchemy 生成的 SQL 中没有 false 。我也在没有 SQLAlchemy 的情况下尝试过此操作并得到了相同的结果。

我所有的研究似乎都表明这些错误是由查询中不存在的某些表或列引起的。但是，我的错误有所不同，因为 false 不是表、列，甚至不是 SQL 中的。

有人有什么建议吗？

编辑#1

这是 python SQLAlchemy 模型类:

class AllFormulas(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String, unique=True, nullable=False)
    abbreviation = db.Column(db.String, unique=True, nullable=False)
    category_name = db.Column(db.String, nullable=False)
    category_id = db.Column(db.Integer, nullable=False)
    parent_id = db.Column(db.Integer, nullable=True)
    has_children = db.Column(db.Boolean, nullable=True)
    function = db.Column(db.String, nullable=True)

    def as_dict(self):
        return {
            'id': self.id,
            'name': self.name,
            'abbreviation': self.abbreviation,
            'category': self.category_name,
            'parentId': self.parent_id,
            'hasChildren': self.has_children,
            'function': self.function
        }

这是 python SQLAlchemy 查询调用(请参见下面注释后的行):

@app.route('/formulas', methods=['GET', 'OPTIONS'])
    def get_all_formulas():
        if request.method == 'OPTIONS':
            return _build_cors_preflight_response()
        else:
            category = request.args.get('category')
            search = request.args.get('search')

            if category:
                query_results = AllFormulas.query.filter(AllFormulas.category_id == category)
            elif search:
                query_results = AllFormulas.query.filter(or_(AllFormulas.name.ilike('%{search}%'.format(search)),
                                                             AllFormulas.abbreviation.ilike('%{search}%'.format(search))))
            else:
                # This next line is being run and throws the SQLite error.
                query_results = AllFormulas.query.filter(AllFormulas.parent_id == None)

            json = []
            for formula in query_results:
                json.append(formula.as_dict())

            if IS_PROD:
                return json
            else:
                return _corsify_actual_response(json)

编辑#2

我在生产环境中直接使用sqlite3命令行查询SQLite数据库。这是我得到的:

ubuntu@ip-xxx-xx-x-xx:~/apps/my-app/data$ sqlite3 math.db
SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
sqlite> select *
   ...> from all_formulas;
Error: no such column: false

但是，相同的查询在我的本地环境中运行良好:

sm7chrisjones:data chris.jones$ sqlite3 math.db
SQLite version 3.24.0 2018-06-04 14:10:15
Enter ".help" for usage hints.
sqlite> select * from all_formulas;
0|Kilograms|kg|0|Medical|2|0|
0|Kilograms|kg|0|Medical|9|0|
0|Kilograms|kg|0|Medical|12|0|
0|Kilograms|kg|0|Medical|13|0|
0|Kilograms|kg|0|Medical|14|0|
0|Kilograms|kg|0|Medical|15|0|
1|Liters|l|0|Medical|2|0|
sqlite>

部署到 prod 时，我所做的就是将项目上传到 S3，登录到我的 Ubuntu prod 环境，然后将项目文件从 S3 复制到 prod 环境。有谁知道为什么这些步骤会产生此错误？

谢谢!

Answer 1

感谢@furas的建议，我发现应用程序的SQLite数据库中的表正确返回查询结果，但all_formulas(这是一个 View )却没有。经过进一步调查，我的本地 Mac 环境和 Ubuntu 产品环境中的 all_formulas View SQL 定义的行为似乎有所不同。 all_formulas View SQL定义为:

CREATE VIEW all_formulas as
select f.id,
       f.name, 
       f.abbreviation,
       c.id as category_id,
       c.name as category_name,
       fr.parent_id,
       case when (select count(*)
                  from formula_relationships
                  where parent_id = f.id) = 0
       then false -- THIS WAS CAUSING THE ERROR IN UBUNTU.
       else true
       end as has_children,
       f.function
from formula f
left join formula_relationships fr on f.id = fr.child_id
left join category c on f.category_id = c.id
where f.category_id = c.id;

我能够通过使用代表 false (0) 和 true (1) 的 SQLite 整数来修复该错误。因此，更正后的 View SQL 定义为:

CREATE VIEW all_formulas as
select f.id,
       f.name, 
       f.abbreviation,
       c.id as category_id,
       c.name as category_name,
       fr.parent_id,
       case when (select count(*)
                  from formula_relationships
                  where parent_id = f.id) = 0
       then 0 --IS EQUIVALENT TO FALSE IN SQLITE
       else 1 --IS EQUIVALENT TO TRUE IN SQLITE
       end as has_children,
       f.function
from formula f
left join formula_relationships fr on f.id = fr.child_id
left join category c on f.category_id = c.id
where f.category_id = c.id;

感谢大家帮助解决此问题!!