InvalidRequestError: Ambiguous column name '***' in result set, while the request is valid to mysqldb?

Question

My code as following:

s = DBSession()
r = s.query(Food, FoodCategory).filter(Food.category_id == FoodCategory.id).first()

This query raise the exception:

sqlalchemy.exc.InvalidRequestError: Ambiguous column name 'food.category_id' in result set

I've tried the query directly in mysql db, and It can work well. I also print the sqlalchey query. Yes, there're same lables, "food.category_id as food_category_id" and "food_category.id as food_category_id".

I doubt that now that this query is valid to mysql, why is it not valid to sqlalchemy

Answer 1

SQLAlchemy uses column names in result sets to identify columns. To avoid collisions, it labels result columns with a table-qualified name. In this case I'm gathering the tables are named "food" and "food_category", so there is still a label overlap for "food_category_id". A workaround for this specific case would be to use an alias of one of the classes:

fa = aliased(FoodCategory)
s.query(Food, fa).filter(Food.category_id == fa.id).all()

Edit: there's actually a regression here from SQLAlchemy 0.7 to 0.8. the behavior in 0.7 is slightly better, you don't get an error but one of the columns doesn't get loaded unless you access it later. this issue is here. However I'd also like to look into adding more auto-aliasing here to overcome the name conflict, that might be tricky.