How Can I Prevent Sqlalchemy From Prefixing The Column Names Of A Cte?

Consider the following query codified via SQLAlchemy. # Create a CTE that performs a join and gets some values x_cte = session.query(SomeTable.col1 ,OtherTable

Solution 1:

you're not being too specific what "x_" is here, but if that's the final result, use label() to give the result columns whatever name you want:

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

Edit: I'm not able to reproduce the "x_" here. Here's a test:

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

Base = declarative_base()

classA(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)

above, you can see I can refer to subq2.c.<colname> without issue, there is no "x" prepended. If you can please specify SQLAlchemy version information and fill out your example fully, I can run it as is in order to reproduce your issue.