gistfile1.txt

Using flask-sqlalchemy 2.0 with sqlalchemy 0.9.6, and cx_Oracle 5.1.3.

The sqlalchemy engine works 100% for the other bind I'm using (a MySQL database). But I'm only using ORM on the Oracle bind.

When declaring a ORM class like this (names are redacted):

class ORMObject(db.Model):                                                                                    
    __bind_key__ = 'bind_name'                                                                                       
    __table_args__ = {'schema': 'SPACE'}
    __tablename__ = 'TABLE_NAME'                                                                         
    column_1 = sa.Column(sa.types.String(32), nullable=False)                                                  
    column_2 = sa.Column(sa.types.Numeric(), sa.schema.Sequence('SCHEMA_NAME'), nullable=False, primary_key=True)
    column_3 = sa.Column(sa.types.Numeric())                                                                  

... And when I query it like so:

db = my_app.get_app().database()
db.session.query(ORMObject).filter_by(column_3=123456789).first()

... I get an error because sqlalchemy is using "SELECT x AS y", and trying to find the columns named 'x' rather than the 'y' alias it used:

 'SELECT "SPACE"."TABLE_NAME"."column_1" AS "SPACE_TABLE_NAME_T_1", "SPACE"."TABLE_NAME"."column_2" AS "SPACE_TABLE_NAME_T_2", 
 "SPACE"."TABLE_NAME"."column_3" AS "SPACE_TABLE_NAME_T_3"
 \nFROM "SPACE"."TABLE_NAME" \nWHERE "SPACE"."TABLE_NAME".column_3 = :column_3_1) \nWHERE ROWNUM <= :ROWNUM_1' 
{'ROWNUM_1': 1, 'column_3_1': 123456789}
sqlalchemy.exc.NoSuchColumnError: NoSuchColumnError: "Could not locate column in row for column 'TABLE_NAME.column_3'

It's not a bug. Apparently this happens when the engine is created with case-sensitive matching, and when you specify an ORM object with an upper-case tablename. Changing to a lower-case table name & schema name fixed the issue. Solution finally found here - http://sourceforge.net/p/sqlalchemy/mailman/sqlalchemy-tickets/thread/073.b9f933ec5ee929ab8cbeabb811d93cda@zzzcomputing.com/