1. Michael Bayer
  2. sqlalchemy
  3. Issues


Issue #3116 closed

NoSuchColumnError when creating label on subquery column

lsowen NA
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'))

    __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))\

    ## CASE A   

    ## CASE B

    ## CASE C

    ## CASE D

if __name__ == '__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>
  File "C:\Users\m02332\workspace\test_automation\test_case.py", line 44, in main
  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
sqlalchemy.exc.NoSuchColumnError: 'Could not locate column in row for column \'sum(anon_1."ACTUALMW")\''


  • SQLAlchemy: 0.9.6
  • cx-Oracle: 5.1.2

Comments (5)

  1. lsowen NA reporter


    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