How to access "count" value as dict/property in sqlalchemy with group_by?

Question

I am making a very simple query using ORM SQLAlchemy, in which I expect to get a column (type) as well as its occurences for each value (count with group by).

I can access the type column by accessing the type property on the server object (as shown in the code provided).

I can also access the count column by accessing the index 0 on the server object.

My question is: is there a simple way to access the count column as a dict/property? For example: server.count

I've extensively inspected the server object using dir(), but I couldn't find any properties that would return the count value.

class Server(Base):
    __tablename__ = 'servers'

    id = Column(Integer, primary_key=True)
    type = Column(String)
    method = Column(String)

    def __repr__(self):
        return "<Server(type='%s', method='%s')>" % (self.type, self.method)

session = Session()

servers = session.query(func.count(Server.type), Server.type).group_by(Server.type).all()

for server in servers:
    print(server[0], server.type)

Reinaldo Gomes · Accepted Answer · 2019-05-18 17:26:29Z

1

Found the answer to my question after re-reading the official ORM tutorial

All I need to do is to set a label for the column for which there's no corresponding property in my class.

So, instead of doing this:

servers = session.query(func.count(Server.type), Server.type).group_by(Server.type).all()

I should do that:

servers = session.query(func.count(Server.type).label('total_count'), Server.type).group_by(Server.type).all()

Then I can access the total_count property (provided by the label function):

for server in servers:
    print(server.type, server.total_count)

answered May 18, 2019 at 17:26

Reinaldo Gomes

19310 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to access "count" value as dict/property in sqlalchemy with group_by?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related