NoSuchColumnError when creating label on subquery column

When applying an alias to a column (via label) on a subquery, I get a NoSuchColumnError in each of the four cases shown below.

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql.schema import MetaData
from datetime import datetime

import sqlalchemy.types as types
from sqlalchemy.schema import Column

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine, func



BASE = declarative_base(metadata=MetaData(schema='redacted'))

class WS_TABLE(BASE):
    __tablename__ = 'WS_TABLE' 

    STARTDATETIME_CS = Column(types.DATETIME, primary_key = True)
    STOPDATETIME = Column('STOPDATETIME_CS', types.DATETIME, primary_key = True)   
    ACTUALMW = Column(types.INTEGER)



def main():
    username = 'redacted'
    password = 'redacted'

    engine = create_engine('oracle+cx_oracle://{0}:{1}@{2}'.format(username, password, 'redacted'), echo=True)
    session = sessionmaker(bind=engine)()


    sub_query = session.query(WS_TABLE)\
        .filter(WS_TABLE.STARTDATETIME_CS < datetime(2014,6,2))\
        .filter(WS_TABLE.STOPDATETIME > datetime(2014,6,1))\
        .subquery()

    ## CASE A   
    #print(session.query(sub_query.c.STARTDATETIME_CS.label('STARTDATETIME_CS')).all())

    ## CASE B
    #print(session.query(sub_query.c.STOPDATETIME_CS.label('STOPDATETIME')).all())

    ## CASE C
    #print(session.query(sub_query.c.STOPDATETIME_CS.label('STOPDATETIME_CS')).all())

    ## CASE D
    print(session.query(func.sum(sub_query.c.ACTUALMW).label('ACTUALMW')).all())

if __name__ == '__main__':
    main()

The traceback is the same (with different column names) in each of the four cases:

Traceback (most recent call last):
  File "C:\Python33\lib\site-packages\sqlalchemy\engine\result.py", line 70, in __getitem__
    processor, obj, index = self._keymap[key]
KeyError: <sqlalchemy.sql.elements.Label object at 0x000000000364BA20>

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\Users\m02332\workspace\test_automation\test_case.py", line 47, in <module>
    main()
  File "C:\Users\m02332\workspace\test_automation\test_case.py", line 44, in main
    print(session.query(func.sum(sub_query.c.ACTUALMW).label('ACTUALMW')).all())
  File "C:\Python33\lib\site-packages\sqlalchemy\orm\query.py", line 2293, in all
    return list(self)
  File "C:\Python33\lib\site-packages\sqlalchemy\orm\loading.py", line 75, in instances
    labels) for row in fetch]
  File "C:\Python33\lib\site-packages\sqlalchemy\orm\loading.py", line 75, in <listcomp>
    labels) for row in fetch]
  File "C:\Python33\lib\site-packages\sqlalchemy\orm\loading.py", line 74, in <listcomp>
    rows = [util.KeyedTuple([proc(row, None) for proc in process],
  File "C:\Python33\lib\site-packages\sqlalchemy\orm\query.py", line 3469, in proc
    return row[column]
  File "C:\Python33\lib\site-packages\sqlalchemy\engine\result.py", line 72, in __getitem__
    processor, obj, index = self._parent._key_fallback(key)
  File "C:\Python33\lib\site-packages\sqlalchemy\engine\result.py", line 332, in _key_fallback
    expression._string_or_unprintable(key))
sqlalchemy.exc.NoSuchColumnError: 'Could not locate column in row for column \'sum(anon_1."ACTUALMW")\''

Versions:

SQLAlchemy: 0.9.6
cx-Oracle: 5.1.2

Comments (5)