SQLAlchemy error - "Please configure one or more attributes for these same-named columns explicitly."

Question

I am brand new to sqlalchemy. Trying to get a query to work and am having issues with a join.

I have two tables both of which have a column named "Id" and I need to join on that table. My code looks like this:

table1 = server.tab1
table2 = server.tab2
joined = server.join(table1,table2, table1.Id == table2.Id)
where = table1.createDate > start
results = joined.filter(where).all()

This results in the following error message:

Implicitly combining column table1.Id with column table2.Id under attribute 'Id'. Please configure one or more attributes for these same-named columns explicitly.

Question is, how do I configure these attributes?

TIA!

Answer 1

With sql soup

joined = server.session.query(table1).join((table2,table1.id == table2.id))
where = table1.createDate > start
results = joined.filter(where).all()

Answer 2

I had this same issue so I figured I would add the solution I came up with (based on http://www.mail-archive.com/[email protected]/msg23735.html). It's certainly not the cleanest thing I've ever coded, but using your example from above, it would be approximately:

from sqlalchemy import select
aliased_table1 = select([
    table1.c.Id.label("renamed_id_col"),
    table1.c.any_other_columns_you_want_returned_but_not_renamed,
    ...
]).correlate(None).alias()
joined = server.join(aliased_table1, table2, aliased_table1.c.renamed_id_col == table2.Id)
where = aliased_table1.c.createDate > start
results = joined.filter(where).all()