Sqlalchemy adds unnecessary _1 at the end of labels when using use_labels=True

[Former user]

The issue occurs when you have a table named "test" and you have two columns in the table named "id" and "test_id". Sqlalchemy, when using use_labels=True, will append "_1" to the "id" column when it is not necessary. Here is an example:

from sqlalchemy import *

db = create_engine('sqlite:///tutorial.db')
metadata = MetaData(db)

test = Table('test', metadata,
 Column('id', Integer, primary_key=True),
 Column('test_id', String(40))
)

print select([test](test), from_obj=[test](test), use_labels=True)

# Prints:
# SELECT test.id AS test_id_1, test.test_id AS test_test_id
# FROM test

Since use_labels=True is used, it shouldn't be necessary to append "_1" to "test_id". My temporary solution is this:

select([test](test.c.id.label('test_id'),), from_obj=[test](test), use_labels=True)

It may seem asinine to have a column named "test_id" in a table, but in my case it works. We have a table called "insurance" that has an auto incrementing "id" and a column named "insurance_id" which is the identification number for the actual insurance.

[Mike Bayer]

whats the actual bug ? this behavior is by design.

[Former user]

What would the purpose of appending _1 to test_id? If you have use_labels set to True, there will be no naming conflicts. _1 seems to be added to avoid naming conflicts but it's not necessary. It forces me to have to work around this.

E.g., when I set use_labels=True, I expect to get the columns "test_id" and "test_test_id", not "test_id_1" and "test_test_id".

[Mike Bayer]

to disambiguate if someone happens to say, row"test_id", statement.order_by("test_id"), etc. There was an issue some years ago related to this, however that issue is probably obsolete as we target column objects very closely between a generated statement and the result set today. you can likely find it though searching through trac.

however, "use_labels" was never meant as a "prettying" operation, it is meant to ensure that columns in a select() don't conflict on name, the names themselves are unimportant. the _1 is appended for other reasons, such as if the generated label is too long for the backend's identifier length (very common on some backends, possible on any). I have strongly considered turning down their length to the minimum, i.e. "_1", "_2", "_3" by default (this is adjustable using the label_length dialect parameter) - this is a behavior that at least two other prominent ORMs use.

Fetching values from rows is designed to be most agnostic by using column objects as keys, i.e. rowtable.c.id, or via numerical index.

The use case of "I want these exact labels" is better served by being explicit and using label() as needed:

def pretty_labels(columns):
    return [+ "_" + c.name) for c in columns](c.label(c.table.name)

my_select = select(pretty_labels(test))

where above, if your naming scheme cannot be met (due to identifier too long, name conflicts), at least the database will complain that the given rendering won't work. use_labels OTOH needs to render in all cases.

[Former user]

Fair enough. Thanks for the explanation. Guess I'll just make a convenience function to do just that, application wide.

Feel free to close this.

[Mike Bayer]

Removing milestone: 0.5.xx (automated comment)