Why Does SQLAlchemy Label Columns in Query

Question

When I make a query in SQLAlchemy, I noticed that the queries use the AS keyword for each column. It sets the alias_name = column_name for every column.

For example, if I run the command print(session.query(DefaultLog)), it returns:

Note: DefaultLog is my table object.

SELECT default_log.id AS default_log_id, default_log.msg AS default_log_msg, default_log.logger_time AS default_log_logger_time, default_log.logger_line AS default_log_logger_line, default_log.logger_filepath AS default_log_logger_filepath, default_log.level AS default_log_level, default_log.logger_name AS default_log_logger_name, default_log.logger_method AS default_log_logger_method, default_log.hostname AS default_log_hostname
FROM default_log

Why does it use an alias = original name? Is there some way I can disable this behavior?

Thank you in advance!

Why do you think you need to disable column aliasing? You are using the ORM to run the query, and the ORM is then using the results to build Python objects. The aliases are there to label each column in the result set, making automated mapping from result set to Python object easy to manage, and you are not reading that result set, the ORM is. — Martijn Pieters, Apr 19 '19 at 12:57
@MartijnPieters @Ilja Everilä - I had asked because I thought either I was doing something wrong or the additional `AS` would slow things down. From the answer, I now see that it's intended behavior and is desirable. — Intrastellar Explorer, Apr 20 '19 at 22:13

score 5 · Accepted Answer · answered Apr 19 '19 at 09:04

Query.statement:

The full SELECT statement represented by this Query.

The statement by default will not have disambiguating labels applied to the construct unless with_labels(True) is called first.

Using this model:

class DefaultLog(Base):

    id = sa.Column(sa.Integer, primary_key=True)
    msg = sa.Column(sa.String(128))
    logger_time = sa.Column(sa.DateTime)
    logger_line = sa.Column(sa.Integer)

print(session.query(DefaultLog).statement) shows:

SELECT defaultlog.id, defaultlog.msg, defaultlog.logger_time, defaultlog.logger_line
FROM defaultlog

print(session.query(DefaultLog).with_labels().statement) shows:

SELECT defaultlog.id AS defaultlog_id, defaultlog.msg AS defaultlog_msg, defaultlog.logger_time AS defaultlog_logger_time, defaultlog.logger_line AS defaultlog_logger_line
FROM defaultlog

You asked:

Why does it use an alias = original name?

From Query.with_labels docs:

...this is commonly used to disambiguate columns from multiple tables which have the same name.

So if you want to issue a single query that calls upon multiple tables, there is nothing stopping those tables having columns that share the same name.

Is there some way I can disable this behavior?

Also from the Query.with_labels docs:

When the Query actually issues SQL to load rows, it always uses column labeling.

All of the methods that retrieve rows (get(), one(), one_or_none(), all() and iterating over the Query) route through the Query.__iter__() method:

def __iter__(self):
    context = self._compile_context()
    context.statement.use_labels = True
    if self._autoflush and not self._populate_existing:
        self.session._autoflush()
    return self._execute_and_instances(context)

... where this line hard codes the label usage: context.statement.use_labels = True. So it is "baked in" and can't be disabled.

You can execute the statement without labels:

session.execute(session.query(DefaultLog).statement)

... but that takes the ORM out of the equation.

Thank you for an explicit answer @SuperShoot! This definitely answers my question, and I understand now why SQLAlchemy made this decision. Thank you! — Intrastellar Explorer, Apr 20 '19 at 22:11

kotofos · Answer 2 · 2021-09-12T12:40:04.463

It is possible to hack sqlachemy Query class to not add labels. But one must be aware that this will breaks when a table is used twice in the query. For example, self join or join thought another table.

from sqlalchemy.orm import Query

class MyQuery(Query):
    def __iter__(self):
        """Patch to disable auto labels"""
        context = self._compile_context(labels=False)
        context.statement.use_labels = False
        if self._autoflush and not self._populate_existing:
            self.session._autoflush()
        return self._execute_and_instances(context)

And then use it according to mtth answer

sessionmaker(bind=engine, query_cls=MyQuery)

score 1 · Answer 3 · answered Sep 26 '22 at 18:37

Printing an SQLAlchemy query is tricky and produced not human-friendly output. Not only columns but also bind params are in an odd place. Here's how to do it correctly:

qry = session.query(SomeTable)
compiled = qry.statement.compile(dialect=session.bind.dialect, compile_kwargs={"literal_binds": True})
print(compiled)

Here's how to fix it for all your future work:

from sqlalchemy.orm import Query
class MyQuery(Query):
    def __str__(self):
        dialect = self.session.bind.dialect
        compiled = self.statement.compile(dialect=dialect, compile_kwargs={"literal_binds": True})
        return str(compiled)

To use:

session = sessionmaker(bind=engine, query_cls=MyQuery)()

Why Does SQLAlchemy Label Columns in Query

3 Answers3

Linked