Python SQLAlchemy Query using labeled OVER clause with ORM

Question

This other question says how to use the OVER clause on sqlalchemy:

Using the OVER window function in SQLAlchemy

But how to do that using ORM? I have something like:

q = self.session.query(self.entity, func.count().over().label('count_over'))

This fails when I call q.all() with the following message:

sqlalchemy.exc.InvalidRequestError:
Ambiguous column name 'count(*) OVER ()' in result set! try 'use_labels' option on select statement

How can I solve this?

Answer 1

You have the over syntax almost correct, it should be something like this:

import sqlalchemy
q = self.session.query(
    self.entity,
    sqlalchemy.over(func.count()).label('count_over'),
)

Example from the docs:

from sqlalchemy import over
over(func.row_number(), order_by='x')

Answer 2

SQLAlchemy Query object has with_entities method that can be used to customize the list of columns the query returns:

Model.query.with_entities(Model.foo, func.count().over().label('count_over'))

Resulting in following SQL:

SELECT models.foo AS models_foo, count(*) OVER () AS count_over FROM models