SQLAlchemy and Ambiguous Column name

Question

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?

I wouldn't know why it's doing that, but have you tried what it suggested and using "use_labels"? — Matt
– Matt, Commented Nov 14, 2011 at 11:27
Isnt the use_labels for select statements when you use execute()? — RonnyKnoxville
– RonnyKnoxville, Commented Nov 14, 2011 at 11:58
I believe you can also enable verbose debugging to see exactly what SQL statements SQLAlchemy is generating, which might help you figure out exactly why you're getting this error message. — larsks
– larsks, Commented Nov 14, 2011 at 14:21

van · Accepted Answer · 2011-11-14 14:34:53Z

4

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.

answered Nov 14, 2011 at 14:34

van

77.5k13 gold badges174 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

RonnyKnoxville Over a year ago

This seems like a likely case, although I cant seem to get them to work properly without autoload. If I manage to get it working Ill give you an update, thanks

RonnyKnoxville Over a year ago

Turns out you were right. This table was unlike all of the other tables in the database as it didnt stick to the naming schema and even though SQL Developer was showing the column names as capitalised, they must have been in lower case. I had assumed that sql developer showed them as they actually were and that sqlalchemy was case insensitve when mapping tables.

Collectives™ on Stack Overflow

SQLAlchemy and Ambiguous Column name

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related