Search code examples
pythonsqlalchemyambiguous

SQLAlchemy and Ambiguous Column name

Ive had a good search on google but I cant seem to find an answer to this error in my case.

Im not making any joins, Im literally just trying to get all from this table.

All of the other queries work fine, but this seems to be messing up with this error:

InvalidRequestError: Ambiguous column name 'INMPTL_WIW_BATAM_STG.pers_no' in result set! try 'use_labels' option on select statement.

Model:

    batamStg = sa.Table("INMPTL_WIW_BATAM_STG", meta.metadata, 
    sa.Column("PERS_NO", sa.types.String(),primary_key=True),
    sa.Column("FIRST_NAME", sa.types.String()),
    sa.Column("LAST_NAME", sa.types.String()),
    sa.Column("KNOWN_AS", sa.types.String()),
    sa.Column("JOB_TITLE", sa.types.String()),
    sa.Column("MANAGER_NAME", sa.types.String()),
    sa.Column("MANAGER_ID", sa.types.String()),
    sa.Column("MANAGER_COST", sa.types.String()),
    autoload=True,
    autoload_with=engine)

View:

btm = meta.Session.query(model.BatamStaging).all();

There is only one column called Pers_no here and all of the primary keys are unique.

The same error also occurs if I try to set LAST_NAME as the primary key.

Has anyone else had this issue?

Solution

  • My conjecture would be the case-sensitivity of column names when overriding columns which are reflected by using autoload=True. To verify, comment out all column definition and leave autoload=True. Then do the opposite.

    See Reflecting Database Objects - Overriding Reflected Columns of SA documentation for more information.