CODEWITHSUNDEEP
pythonsqlalchemy
Arefe
Arefe

Reputation: 1059

How to do group_by query using SQLAlchemy in Python?

I have 2 tables as provided below which are already populated, 

class Shelter(Base):

    __tablename__ = 'shelter'
    id = Column(Integer, primary_key = True)
    name = Column(String(80), nullable = False)
    address = Column(String(250))
    city = Column(String(80))
    state = Column(String(20))
    zipCode = Column(String(10))
    website = Column(String)


class Puppy(Base):

    __tablename__ = 'puppy'

    id = Column(Integer, primary_key=True)
    name = Column(String(250), nullable=False)
    gender = Column(String(6), nullable = False)
    dateOfBirth = Column(Date)
    picture = Column(String)
    shelter_id = Column(Integer, ForeignKey('shelter.id'))
    shelter = relationship(Shelter)
    weight = Column(Numeric(10))

Now, I would like to write an query for get all the Puppies group by their Shelter name using Python. So, it will need to be printed as puppyName puppyShelterName.

I write the code as following, 

puppies = session.query(Puppy).group_by(Puppy.shelter_id).all()

for puppy in puppies:
    puppy.name

This is obviously not working as I wanted. I would like to print all the puppies name with their shelter name group by in which shelter they are staying. How to correct the code ?

Upvotes: 1

Views: 4343

Answers (1)

Ilja Everilä
Ilja Everilä

Reputation: 52929

From provided additional information it seems like an ORDER BY would suffice. The models also need to be joined in order to print out both the puppy's name and shelter's name:

results = session.query(Puppy.name, Shelter.name)\
        .join(Shelter).order_by(Shelter.name, Puppy.name).all()

for puppy, shelter in results:
    print(puppy, shelter)  # or `print puppy, shelter` if on PY2

You could also do this a bit differently, if you had backrefs in your relationship:

class Puppy(Base):
    ...
    shelter = relationship(Shelter, backref='puppies')

Then you could first select all the shelters and then iterate through the puppies using the backref. Depending on how eager the backref is configured to be, this would either issue a single query, or a select for all the Shelters and a select per shelter to fetch the Puppy instances.

from sqlalchemy.orm import joinedload

shelters = session.query(Shelter).options(joinedload('puppies')).all()
for shelter in shelters:
    for puppy in shelter.puppies:
        print(puppy.name, shelter.name)

Upvotes: 3

Related Questions