Removing Table Name and Alias from Column in SQLAlchemy Query

I have a Declarative base defined as follows:

class User(Base):
    __tablename__ = "gold.user"
    __table_args__ = {"quote": False}

    user= Column("user", String, primary_key=True)

I'm querying the table using the following line of code:

session.query(User.user).limit(1)

However, the generated query has an undesired format, like this:

SELECT gold.user.`user` as
       gold.user_user
FROM   gold.user
LIMIT 1

I want to remove the table name from the column name and also get rid of the alias to maintain the original naming. This is how the desired query looks like:

SELECT user
FROM   gold.user
LIMIT 1

How can I achieve this?

Solution

There are two parts to this: the alias, or label and the schema.table qualifier.

SQLAlchemy provides label styles that can be applied to the label:

with orm.Session(engine) as s:
    print('Legacy query')
    s.query(User.user).set_label_style(sa.LABEL_STYLE_NONE).limit(1).first()
    print('2.0 style')
    s.scalars(
            sa.select(User.user)
            .set_label_style(sa.SelectLabelStyle.LABEL_STYLE_NONE)
            .limit(1)
            ).first()

The qualifiers are more difficult to adjust. When a column is compiled into SQL, the presence of the qualifier (in the simple case) is governed by the include_table keyword argument. This argument defaults to True, meaning the qualifier is applied. If you want to override it, you could intercept column compilation like this:

import sqlalchemy as sa
from sqlalchemy.ext.compiler import compiles

@compiles(sa.Column)
def format_column(element, compiler, **kw):
    kw['include_table'] = False
    return compiler.visit_column(element, **kw)

This is obviously indiscriminate and could cause problems if a column needs to be qualified.

Finally, user is enclosed in double quotes because it is a reserved word in PostgreSQL - this quoting is unavoidable if you want a syntactically correct statement.