4

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?

RonnyKnoxville
  • 6,166
  • 10
  • 46
  • 75
  • I wouldn't know why it's doing that, but have you tried what it suggested and using "use_labels"? – Matt Nov 14 '11 at 11:27
  • Isnt the use_labels for select statements when you use execute()? – RonnyKnoxville Nov 14 '11 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 Nov 14 '11 at 14:21

1 Answers1

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.

van
  • 74,297
  • 13
  • 168
  • 171
  • 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 Nov 14 '11 at 16:51
  • 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. – RonnyKnoxville Nov 17 '11 at 09:04