group_by()
GROUP BY clause in SQL can be written in SQLAlchemy using the group_by() method. It takes in the entity or column names as the parameter and does grouping based on these columns. The result that we see above is the group by operation done on first_name and last_name columns of students table where score is aggregated by SUM function.
Syntax: sqlalchemy.orm.Query.group_by(*clauses)
Apply one or more GROUP BY criterion to the query and return the newly resulting Query.
Python
from sqlalchemy.orm import sessionmaker import sqlalchemy as db from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() # DEFINE THE ENGINE (CONNECTION OBJECT) engine = db.create_engine( "mysql+pymysql://root:password@localhost/Geeks4Geeks" ) # CREATE THE TABLE MODEL TO USE IT FOR QUERYING class Students(Base): __tablename__ = 'students' first_name = db.Column(db.String( 50 ), primary_key = True ) last_name = db.Column(db.String( 50 ), primary_key = True ) course = db.Column(db.String( 50 )) score = db.Column(db. Float ) # CREATE A SESSION OBJECT TO INITIATE QUERY IN DATABASE Session = sessionmaker(bind = engine) session = Session() # SELECT first_name, last_name, SUM(score) # AS total FROM students GROUP BY first_name, last_name; result = session.query(Students) \ .with_entities( Students.first_name, Students.last_name, db.func. sum (Students.score).label( 'total' ) ).group_by( Students.first_name, Students.last_name ). all () # VIEW THE ENTRIES IN THE RESULT for r in result: print (r.first_name, r.last_name, "| Score =" , r[ 2 ]) |
Output:
SQLAlchemy ORM – Query
In this article, we will see how to query using SQLAlchemy ORM in Python.
To follow along with this article, we need to have sqlalchemy and anyone database installed in our system. We have used the MySQL database for this article’s understanding.
Created a Profile table and a Students table:
Here we are going to cover the following methods:
- add_columns()
- add_entity()
- count()
- delete()
- distinct()
- filter()
- get()
- first()
- group_by()
- join()
- one()