How can I prevent sqlalchemy from prefixing CTE column names?

Question

Consider the following query encoded through SQLAlchemy.

# Create a CTE that performs a join and gets some values
x_cte = session.query(SomeTable.col1
                     ,OtherTable.col5
                     ) \
               .select_from(SomeTable) \
               .join(OtherTable, SomeTable.col2 == OtherTable.col3)
               .filter(OtherTable.col6 == 34)
               .cte(name='x')

# Create a subquery that splits the CTE based on the value of col1
# and computes the quartile for positive col1 and assigns a dummy
# "quartile" for negative and zero col1
subquery = session.query(x_cte
                        ,literal('-1', sqlalchemy.INTEGER).label('quartile')
                        ) \
                  .filter(x_cte.col1 <= 0)
                  .union_all(session.query(x_cte
                                          ,sqlalchemy.func.ntile(4).over(order_by=x_cte.col1).label('quartile')
                                          )
                                    .filter(x_cte.col1 > 0)
                            ) \
               .subquery()

# Compute some aggregate values for each quartile
result = session.query(sqlalchemy.func.avg(subquery.columns.x_col1)
                      ,sqlalchemy.func.avg(subquery.columns.x_col5)
                      ,subquery.columns.x_quartile
                      ) \
                .group_by(subquery.columns.x_quartile) \
                .all()

Sorry for the length, but this seems like my real request. In my real code, I gave a more descriptive name for my CTE, and my CTE has a lot more columns for which I have to calculate the average. (This is also actually a weighted average value, weighted by a column in the CTE.)

"" - . (, . , , .) , subquery.columns.x_[column name]; , SQLAlchemy CTE. , SQLAlchemy CTE , , . CTE, ( ) ; , . ?

Python 2.7.3 SQLAlchemy 0.7.10.

Answer 1

, "x_", , label(), , :

row = session.query(func.avg(foo).label('foo_avg'), func.avg(bar).label('bar_avg')).first()
foo_avg = row['foo_avg']  # indexed access
bar_avg = row.bar_avg     # attribute access

: "x_" . :

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class A(Base):
    __tablename__ = "a"

    id = Column(Integer, primary_key=True)

    x = Column(Integer)
    y = Column(Integer)

s = Session()

subq = s.query(A).cte(name='x')

subq2 = s.query(subq, (subq.c.x + subq.c.y)).filter(A.x == subq.c.x).subquery()

print s.query(A).join(subq2, A.id == subq2.c.id).\
        filter(subq2.c.x == A.x, subq2.c.y == A.y)

, , subq2.c.<colname> , "x". SQLAlchemy , , .