How to do group_by query using SQLAlchemy in Python?

Question

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 ?

What DB? For example with PostgreSQL you could aggregate with array_agg(). — Ilja Everilä
– Ilja Everilä, Commented May 23, 2016 at 12:18
Using sqlite and I initiated as following, engine = create_engine('sqlite:///puppyshelter.db') — Arefe
– Arefe, Commented May 23, 2016 at 12:20
Another question, if you really just have to print them as name, shelter pairs, you really don't need a group by. Is this just a simplification of your real problem? — Ilja Everilä
– Ilja Everilä, Commented May 23, 2016 at 12:31
I need as name and same shelter as pair as far the goes, for example, puppy1 shelter1 puppy2 shelter1 puppy3 shelter1 puppy4 shelter2 puppy5 shelter2 puppy6 shelter2 puppy7 shelter2 puppy8 shelter3 puppy9 shelter3 puppy10 shelter3 puppy11 shelter3 puppy1 shelter3 — Arefe
– Arefe, Commented May 23, 2016 at 12:42
That looks like a job for order by, but I might still be missing your true goal. — Ilja Everilä
– Ilja Everilä, Commented May 23, 2016 at 12:48

Ilja Everilä · Accepted Answer · 2016-05-23 13:20:03Z

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)

Collectives™ on Stack Overflow

How to do group_by query using SQLAlchemy in Python?

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