select() parameter to avoid "Ambiguous column name" for a simple join on?

[nyov]

Would it be possible to have a parameter to sqlalchemy.sql.expression.select() to avoid the "Ambiguous column name" exception for simple joins? I don't care for the use_labels here as I then have to rewrite all the keys again after.

If possible, I'd like a parameter to select that would then simply drop the column name on the right side of the join (where the value is the same for such a join) from the select column list. As in "select all but joined_on_column duplicate".

This'd be a nicer, quicker way than writing out all the many column names manually or working with an alias for a column I still don't need as it's just a duplicate.

[nyov]

Or, now that I thought about it, probably more generic,
a way to "unselect" specific column names in the select()?
Instead of select([<all my column names here>]), select(ignore=[<everything but these>])?

[Mike Bayer]

we've already got this, it is reduce_columns:

from sqlalchemy import table, column, select

t1 = table('t1', column('x'), column('y'))
t2 = table('t2', column('x'), column('z'))

s1 = select([t1, t2]).select_from(t1.join(t2, t1.c.x == t2.c.x)).reduce_columns()
print(s1)

:

SELECT t1.x, t1.y, t2.z 
FROM t1 JOIN t2 ON t1.x = t2.x

in the above example, it's scanning the WHERE/ON clauses to figure that out. It will also take ForeignKey into account in a similar way.

without any ForeignKey or WHERE/ON clause it does not reduce:

s1 = select([t1, t2]).reduce_columns()
print(s1)

:

SELECT t1.x, t1.y, t2.x, t2.z 
FROM t1, t2

you can also build something against with_only_columns if you wanted.

[nyov]

Ooh, how embarrassing. I was certain I skimmed all the documented options, but missed both of these somehow or that they would apply to my problem.
Thank you.

[nyov]

It's not the original issue exactly, but it seems both of these options don't work for removing custom columns, that don't reduce.

Is there a possibility to negate with_only_columns (as if passing custom column names to reduce_columns), or a solution to label selective columns without actually setting the select columns (label column x as y if found)?

I just happen to work with a reflected (3rd party) database where both joined tables do have the same column name with different values, sadly.
(not helpful, I'm sure, but query looks like this: meta.tables['eav_attribute'].join(meta.tables['eav_entity_type']).select().reduce_columns().order_by(meta.tables['eav_attribute'].c.attribute_code), except I'm using use_labels here and rewriting column names of the response with this now.)

Here it would be nice magic if I could just drop a specific column name from the inferred/detected table schema (select all but x) or label/rename such a column in the select.

[Mike Bayer]

well you've got inner_columns that will give you all the columns that it's going to consider, broken out from their tables, then you've got column.name which will give you their names, then you've got with_only_columns().

It's an issue of basic use of these three to provide whatever special labeling / omission rules you'd like.

I'd favor that the inner_columns and with_only_columns attributes should refer to each other in their docstrings to refer to purposes like these.

[nyov]

Ah, I can see how this works in a roundabout way 8)
I was shooting too straight to find this solution.
Thank you very much! And for your patience.

This then is the solution I found for my earlier line;

eav_attribute = metadata.tables['eav_attribute']
eav_entity_type = metadata.tables['eav_entity_type']

columns = eav_attribute.join(eav_entity_type).select().reduce_columns(only_synonyms=True).inner_columns
#for col in columns:
#    print col.name
dropped = ['eav_entity_type.attribute_model',]
columns = [c for c in columns if str(c) not in dropped]
select_ = eav_attribute.join(eav_entity_type).select().reduce_columns(only_synonyms=True).with_only_columns(columns).order_by(eav_attribute.c.attribute_code)

# print select_