InvalidRequestError: Query.limit() being called on a Query with an existing full statement - can't apply criterion.

[Xander Cage]

hi all.

got this error while playing around whith flask-admin and trying to fire a sql query.

class AixEditForm(ModelView):
    def get_query(self):
        stmt = text("select `n`.`id` AS `NODE_ID`,`n`.`name` AS `NODE`,"
                    "max(if((`a`.`name` = 'LOCATION'),`e`.`value`,NULL)) AS `LOCATION`,"
                    "max(if((`a`.`name` = 'IS_CLUSTER'),`e`.`value`,NULL)) AS `IS_CLUSTER`,"
                    "max(if((`a`.`name` = 'MAN_SYS_NAME'),`e`.`value`,NULL)) AS `MAN_SYS_NAME`,"
                    "max(if((`a`.`name` = 'OS_LEVEL'),`e`.`value`,NULL)) AS `OS_LEVEL`,"
                    "max(if((`a`.`name` = 'IS_LPM'),`e`.`value`,NULL)) AS `IS_LPM`,"
                    "max(if((`a`.`name` = 'CPU_VALUE'),`e`.`value`,NULL)) AS `CPU_VALUE`,"
                    "max(if((`a`.`name` = 'CLUSTER_RGS'),`e`.`value`,NULL)) AS `CLUSTER_RGS`,"
                    "max(if((`a`.`name` = 'HA_LEVEL'),`e`.`value`,NULL)) AS `HA_LEVEL`,"
                    "max(if((`a`.`name` = 'CLUSTER_NODES'),`e`.`value`,NULL)) AS `CLUSTER_NODES`,"
                    "max(if((`a`.`name` = 'CLUSTER_NAME'),`e`.`value`,NULL)) AS `CLUSTER_NAME`,"
                    "max(if((`a`.`name` = 'CPU_POOL'),`e`.`value`,NULL)) AS `CPU_POOL`,"
                    "max(if((`a`.`name` = 'AME_FACTOR'),`e`.`value`,NULL)) AS `AME_FACTOR`,"
                    "max(if((`a`.`name` = 'SYS_PROFILE'),`e`.`value`,NULL)) AS `SYS_PROFILE`,"
                    "max(if((`a`.`name` = 'INFO_MAIL'),`e`.`value`,NULL)) AS `INFO_MAIL`,"
                    "max(if((`a`.`name` = 'HOSTNAME'),`e`.`value`,NULL)) AS `HOSTNAME`,"
                    "max(if((`a`.`name` = 'OS_TYPE'),`e`.`value`,NULL)) AS `OS_TYPE`,"
                    "max(if((`a`.`name` = 'UPTIME'),`e`.`value`,NULL)) AS `UPTIME`,"
                    "max(if((`a`.`name` = 'IP'),`e`.`value`,NULL)) AS `IP`,"
                    "max(if((`a`.`name` = 'IP_LONG'),`e`.`value`,NULL)) AS `IP_LONG`,"
                    "max(if((`a`.`name` = 'CLUSTER_NODENAME'),`e`.`value`,NULL)) AS `CLUSTER_NODENAME`,"
                    "max(if((`a`.`name` = 'RG_SERVICE_IP_LONG'),`e`.`value`,NULL)) AS `RG_SERVICE_IP_LONG`"
                    "from ((`entries` `e` left join `nodes` `n` on((`n`.`id` = `e`.`node_id`)))"
                          "left join `attribs` `a` on((`a`.`id` = `e`.`attrib_id`)))"
                          "where (`a`.`parent` = 'NODE') group by `n`.`name` order by `n`.`id`")
        return self.session.query().from_statement(stmt)

sqlalchemy.exc.InvalidRequestError: Query.limit() being called on a Query with an existing full statement - can't apply criterion.

an idea what might be the problem here..i am clueless.

[Mike Bayer]

the stack trace would show you where it's going wrong. Are you
calling first() on the query? that applies limit(). Stack trace
will show this. Can't apply limit() to a textual query because the
text is already written.

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
> ---
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/62dc9635-d617-4e80-9376-767bd0d7c559%40googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

[Xander Cage]

here is the stack trace and there is indeed some gibberish about limit(). i did not set any limit or something so i have no idea where this comes from?

Traceback (most recent call last):
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/flask/app.py", line 2309, in __call__
    return self.wsgi_app(environ, start_response)
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/flask/app.py", line 2295, in wsgi_app
    response = self.handle_exception(e)
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/flask/app.py", line 1741, in handle_exception
    reraise(exc_type, exc_value, tb)
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/flask/_compat.py", line 35, in reraise
    raise value
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/flask/app.py", line 2292, in wsgi_app
    response = self.full_dispatch_request()
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/flask/app.py", line 1815, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/flask/app.py", line 1718, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/flask/_compat.py", line 35, in reraise
    raise value
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/flask/app.py", line 1813, in full_dispatch_request
    rv = self.dispatch_request()
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/flask/app.py", line 1799, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/opt/rh/rh-python36/root/usr/lib64/python3.6/site-packages/flask_admin/base.py", line 69, in inner
    return self._run_view(f, *args, **kwargs)
  File "/opt/rh/rh-python36/root/usr/lib64/python3.6/site-packages/flask_admin/base.py", line 368, in _run_view
    return fn(self, *args, **kwargs)
  File "/opt/rh/rh-python36/root/usr/lib64/python3.6/site-packages/flask_admin/model/base.py", line 1960, in index_view
    view_args.search, view_args.filters, page_size=page_size)
  File "/opt/rh/rh-python36/root/usr/lib64/python3.6/site-packages/flask_admin/contrib/sqla/view.py", line 1066, in get_list
    query = self._apply_pagination(query, page, page_size)
  File "/opt/rh/rh-python36/root/usr/lib64/python3.6/site-packages/flask_admin/contrib/sqla/view.py", line 995, in _apply_pagination
    query = query.limit(page_size)
  File "<string>", line 2, in limit

  File "/opt/rh/rh-python36/root/usr/lib64/python3.6/site-packages/sqlalchemy/orm/base.py", line 219, in generate
    assertion(self, fn.__name__)
  File "/opt/rh/rh-python36/root/usr/lib64/python3.6/site-packages/sqlalchemy/orm/query.py", line 483, in _no_statement_condition
    % meth
sqlalchemy.exc.InvalidRequestError: Query.limit() being called on a Query with an existing full statement - can't apply criterion.

On Thursday, May 16, 2019 at 5:36:12 PM UTC+2, Mike Bayer wrote:

the stack trace would show you where it's going wrong.  Are you
calling first() on the query?  that applies limit().   Stack trace
will show this.    Can't apply limit() to a textual query because the
text is already written.

> To unsubscribe from this group and stop receiving emails from it, send an email to sqlal...@googlegroups.com.

[Mike Bayer]

you're using a Flask extension that is attempting to apply pagination,
this plugin is not compatible with a textual query:

File "/opt/rh/rh-python36/root/usr/lib64/python3.6/site-packages/flask_admin/contrib/sqla/view.py",
line 995, in _apply_pagination

> To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.

> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/a1c3cc45-ea4f-40ad-aabe-eea3db6c2641%40googlegroups.com.