1. Michael Bayer
  2. sqlalchemy
  3. Issues

Issues

Issue #3116 closed

NoSuchColumnError when creating label on subquery column

Logan Owen
created an issue

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)

  1. Logan Owen reporter

    Mike,

    Thank you for the quick response. That was exactly the issue. Making all the column names lower case in both the model and the queries fixed the issue.

    Thanks for your help!

  2. Log in to comment