Skip to content
Advertisement

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

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)

Advertisement

Answer

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)
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement