SQLAlchemy: Making a subquery of query.from_statement(text(...)) raising AttributeError

Question

I'm building a tool which relies heavily on SQLAlchemy's query builder, but which allows the user to specify literal text of subqueries to join against in cases where the model is insufficient.

However, when I try something like this:

q = session.query().from_statement(sa.text(subquery_text)).subquery(subquery_name)

...an exception occurs:

  File ".../lib/sqlalchemy/orm/query.py", line 473, in subquery
    return q.alias(name=name)
AttributeError: 'AnnotatedTextClause' object has no attribute 'alias'

Looking at the implementation of .subquery() in SQLAlchemy's codebase raises some clarity on how we got from a Query object to an AnnotatedTextClause:

def subquery(self, name=None, with_labels=False, reduce_columns=False):
    # docstring in the original omitted here for brevity
    q = self.enable_eagerloads(False)
    if with_labels:
        q = q.with_labels()
    q = q.statement
    if reduce_columns:
        q = q.reduce_columns()
    return q.alias(name=name)

...but I'm finding myself unenlightened as to whether what I'm attempting to do is possible, and if so how it would be accomplished.

Answer 1

I have found a valid syntax:

q = sa.text(subquery_text).columns(Table.col_a, Table.col_b).alias(subquery_name)

q can then be used as a standard subquery