Flask+Flask-SQLAlchemy+sqlalchemy_mptt插入根节点报错求助

问题描述 #

使用Flask、Flask-SQLAlchemy和sqlalchemy_mptt构建嵌套集表时，在flask shell中插入根节点触发sqlalchemy.exc.ArgumentError，错误提示为：

期望列表达式、FROM子句或其他列子句元素，却得到了BinaryExpression对象。

报错堆栈 #

Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/Users/pingguo/opt/anaconda3/envs/Flask_Web/lib/python3.8/site-packages/sqlalchemy/orm/scoping.py", line 553, in commit
    return self._proxied.commit()
  File "/Users/pingguo/opt/anaconda3/envs/Flask_Web/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 1903, in commit
    trans.commit(_to_root=True)
  File "<string>", line 2, in commit
  File "/Users/pingguo/opt/anaconda3/envs/Flask_Web/lib/python3.8/site-packages/sqlalchemy/orm/state_changes.py", line 137, in _go
    ret_value = fn(self, *arg, **kw)
  File "/Users/pingguo/opt/anaconda3/envs/Flask_Web/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 1218, in commit
    self._prepare_impl()
  File "<string>", line 2, in _prepare_impl
  File "/Users/pingguo/opt/anaconda3/envs/Flask_Web/lib/python3.8/site-packages/sqlalchemy/orm/state_changes.py", line 137, in _go
    ret_value = fn(self, *arg, **kw)
  File "/Users/pingguo/opt/anaconda3/envs/Flask_Web/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 1193, in _prepare_impl
    self.session.flush()
  File "/Users/pingguo/opt/anaconda3/envs/Flask_Web/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 4155, in flush
    self._flush(objects)
  File "/Users/pingguo/opt/anaconda3/envs/Flask_Web/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 4292, in _flush
    transaction.rollback(_capture_exception=True)
  File "/Users/pingguo/opt/anaconda3/envs/Flask_Web/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 147, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/Users/pingguo/opt/anaconda3/envs/Flask_Web/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 4252, in _flush
    flush_context.execute()
  File "/Users/pingguo/opt/anaconda3/envs/Flask_Web/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 467, in execute
    rec.execute(self)
  File "/Users/pingguo/opt/anaconda3/envs/Flask_Web/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 644, in execute
    util.preloaded.orm_persistence.save_obj(
  File "/Users/pingguo/opt/anaconda3/envs/Flask_Web/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py", line 54, in save_obj
    save_obj(base_mapper, [state], uowtransaction, single=True)
  File "/Users/pingguo/opt/anaconda3/envs/Flask_Web/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py", line 60, in save_obj
    for (
  File "/Users/pingguo/opt/anaconda3/envs/Flask_Web/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py", line 233, in _organize_states_for_save
    mapper.dispatch.before_insert(mapper, connection, state)
  File "/Users/pingguo/opt/anaconda3/envs/Flask_Web/lib/python3.8/site-packages/sqlalchemy/event/attr.py", line 487, in __call__
    fn(*args, **kw)
  File "/Users/pingguo/opt/anaconda3/envs/Flask_Web/lib/python3.8/site-packages/sqlalchemy/orm/events.py", line 889, in wrap
    fn(*arg, **kw)
  File "/Users/pingguo/opt/anaconda3/envs/Flask_Web/lib/python3.8/site-packages/sqlalchemy_mptt/events.py", line 574, in before_insert
    mptt_before_insert(mapper, connection, instance)
  File "/Users/pingguo/opt/anaconda3/envs/Flask_Web/lib/python3.8/site-packages/sqlalchemy_mptt/events.py", line 96, in mptt_before_insert
    select(
  File "/Users/pingguo/opt/anaconda3/envs/Flask_Web/lib/python3.8/site-packages/sqlalchemy/sql/_selectable_constructors.py", line 493, in select
    return Select(*entities)
  File "/Users/pingguo/opt/anaconda3/envs/Flask_Web/lib/python3.8/site-packages/sqlalchemy/sql/selectable.py", line 5161, in __init__
    self._raw_columns = [
  File "/Users/pingguo/opt/anaconda3/envs/Flask_Web/lib/python3.8/site-packages/sqlalchemy/sql/selectable.py", line 5162, in <listcomp>
    coercions.expect(
  File "/Users/pingguo/opt/anaconda3/envs/Flask_Web/lib/python3.8/site-packages/sqlalchemy/sql/coercions.py", line 413, in expect
    resolved = impl._literal_coercion(
  File "/Users/pingguo/opt/anaconda3/envs/Flask_Web/lib/python3.8/site-packages/sqlalchemy/sql/coercions.py", line 652, in _literal_coercion
    self._raise_for_expected(element, argname)
  File "/Users/pingguo/opt/anaconda3/envs/Flask_Web/lib/python3.8/site-packages/sqlalchemy/sql/coercions.py", line 1143, in _raise_for_expected
    return super()._raise_for_expected(
  File "/Users/pingguo/opt/anaconda3/envs/Flask_Web/lib/python3.8/site-packages/sqlalchemy/sql/coercions.py", line 711, in _raise_for_expected
    super()._raise_for_expected(
  File "/Users/pingguo/opt/anaconda3/envs/Flask_Web/lib/python3.8/site-packages/sqlalchemy/sql/coercions.py", line 536, in _raise_for_expected
    raise exc.ArgumentError(msg, code=code) from err
sqlalchemy.exc.ArgumentError: Column expression, FROM clause, or other columns clause element expected, got [<sqlalchemy.sql.elements.BinaryExpression object at 0x7fee25170d90>]. Did you mean to say select(<sqlalchemy.sql.elements.BinaryExpression object at 0x7fee25170d90>)?

代码示例 #

from flask import Flask, render_template
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy_mptt.mixins import BaseNestedSets
import os

app = Flask(__name__)
url = 'sqlite:////' + os.path.join(app.root_path, 'data.db')
app.config['SQLALCHEMY_DATABASE_URI'] = url
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False 

db = SQLAlchemy(app)

class Category(db.Model, BaseNestedSets):
    __tablename__ = 'category'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50), nullable=False)

    def __repr__(self):
        return '<Category {}>'.format(self.name)

解决方法 #

原因 #

官方sqlalchemy_mptt库未适配SQLAlchemy 2.0+版本的语法变更，插入根节点时查询最大tree_id的代码使用了旧版SQLAlchemy的select写法，导致在2.0+环境中触发参数类型错误。

方案1：降级SQLAlchemy版本 #

安装兼容的旧版SQLAlchemy和Flask-SQLAlchemy：

pip install sqlalchemy==1.4.49 flask-sqlalchemy==2.5.1

方案2：修改sqlalchemy_mptt源码 #

找到环境中sqlalchemy_mptt/events.py文件，定位到mptt_before_insert函数中查询最大tree_id的代码段（约第96行），将原来的列表式select调用修改为单个表达式：

• 原代码（错误写法）：

  select(
      [func.max(node_class.tree_id) + 1]
  ).where(
      node_class.tree_id is not None
  )
  

• 修改后：

  select(
      func.max(node_class.tree_id) + 1
  ).where(
      node_class.tree_id is not None
  )
  

方案3：使用适配SQLAlchemy 2.0的分支版本 #

安装第三方维护的sqlalchemy-mptt2库，该库已适配SQLAlchemy 2.0+：

pip install sqlalchemy-mptt2

同时修改代码中的导入语句：

# 替换原导入
from sqlalchemy_mptt2.mixins import BaseNestedSets

内容的提问来源于stack exchange，提问作者Plain