SQLAlchemy - Aggregate Functions
Last Updated : 21 Feb, 2022
In this article, we will see how to select the count of rows using SQLAlchemy using Python.
Before we begin, let us install the required dependencies using pip:
pip install sqlalchemy
Since we are going to use MySQL in this post, we will also install a SQL connector for MySQL in Python. However, none of the code implementations changes with change in the database except for the SQL connectors.
pip install pymysql
There are 5 SQL aggregate functions used often as shown below:
SQL Aggregate FunctionsIn this article, we will cover the examples for each of the above aggregate functions. In both examples, we will count the number of records present in the payment table within the sakila database. The sample record from the payment table looks like:
Payment TableIf you do not have sakila database and want to follow along with this article without installing it then use the SQL script present in the link mentioned below to create the required schema and payment table along with the records. Sakila Payment Table Script
The `func` function in SQLAlchemy is used to implement these aggregate functions. The below table summarizes the method used for each of the aggregate functions.
| SQL Aggregate Function | SQLAlchemy Method |
|---|
| MIN() | sqlalchemy.func.min() |
| MAX() | sqlalchemy.func.max() |
| SUM() | sqlalchemy.func.sum() |
| AVG() | sqlalchemy.func.avg() |
| COUNT() | sqlalchemy.func.count() |
MIN()
Here we will use sqlalchemy.func.min() function to get minimum element for rows.
Syntax: sqlalchemy.select([sqlalchemy.func.min(sqlalchemy.DeclarativeMeta)])
Code:
Python # IMPORT THE REQUIRED LIBRARY import sqlalchemy as db # DEFINE THE ENGINE (CONNECTION OBJECT) engine = db.create_engine("mysql+pymysql://\ root:password@localhost/sakila") # CREATE THE METADATA OBJECT TO ACCESS THE TABLE meta_data = db.MetaData(bind=engine) db.MetaData.reflect(meta_data) # GET THE `payment` TABLE FROM THE METADATA OBJECT payment_table = meta_data.tables['payment'] # SELECT MIN(amount) FROM sakila.`payment`; query = db.select([db.func.min(payment_table.c.amount)]) # FETCH ALL THE RECORDS IN THE RESPONSE result = engine.execute(query).first() # VIEW THE RESULT print(result[0]) Output:
0.00
MAX()
Here we will use sqlalchemy.func.max() function to get maximum element for rows.
Syntax: sqlalchemy.select([sqlalchemy.func.max(sqlalchemy.DeclarativeMeta)])
Code:
Python # IMPORT THE REQUIRED LIBRARY import sqlalchemy as db # DEFINE THE ENGINE (CONNECTION OBJECT) engine = db.create_engine("mysql+pymysql://\ root:password@localhost/sakila") # CREATE THE METADATA OBJECT TO ACCESS THE TABLE meta_data = db.MetaData(bind=engine) db.MetaData.reflect(meta_data) # GET THE `payment` TABLE FROM THE METADATA OBJECT payment_table = meta_data.tables['payment'] # SELECT MAX(amount) FROM sakila.`payment`; query = db.select([db.func.max(payment_table.c.amount)]) # FETCH ALL THE RECORDS IN THE RESPONSE result = engine.execute(query).first() # VIEW THE RESULT print(result[0])
Output:
11.99
SUM()
Here we will use sqlalchemy.func.sum() function to get sum element for rows.
Syntax: sqlalchemy.select([sqlalchemy.func.sum(sqlalchemy.DeclarativeMeta)])
Code:
Python # IMPORT THE REQUIRED LIBRARY import sqlalchemy as db # DEFINE THE ENGINE (CONNECTION OBJECT) engine = db.create_engine("mysql+pymysql://\ root:password@localhost/sakila") # CREATE THE METADATA OBJECT TO ACCESS THE TABLE meta_data = db.MetaData(bind=engine) db.MetaData.reflect(meta_data) # GET THE `payment` TABLE FROM THE METADATA OBJECT payment_table = meta_data.tables['payment'] # SELECT SUM(amount) FROM sakila.`payment`; query = db.select([db.func.sum(payment_table.c.amount)]) # FETCH ALL THE RECORDS IN THE RESPONSE result = engine.execute(query).first() # VIEW THE RESULT print(result[0])
Output:
67416.51
AVG()
Here we will use sqlalchemy.func.avg() function to get average element for rows.
Syntax: sqlalchemy.select([sqlalchemy.func.avg(sqlalchemy.DeclarativeMeta)])
Code:
Python # IMPORT THE REQUIRED LIBRARY import sqlalchemy as db # DEFINE THE ENGINE (CONNECTION OBJECT) engine = db.create_engine("mysql+pymysql://\ root:password@localhost/sakila") # CREATE THE METADATA OBJECT TO ACCESS THE TABLE meta_data = db.MetaData(bind=engine) db.MetaData.reflect(meta_data) # GET THE `payment` TABLE FROM THE METADATA OBJECT payment_table = meta_data.tables['payment'] # SELECT AVG(amount) FROM sakila.`payment`; query = db.select([db.func.avg(payment_table.c.amount)]) # FETCH ALL THE RECORDS IN THE RESPONSE result = engine.execute(query).first() # VIEW THE RESULT print(result[0])
Output:
4.200667
COUNT()
Here we will use sqlalchemy.func.count() function to get number of rows.
Syntax: sqlalchemy.select([sqlalchemy.func.count(sqlalchemy.DeclarativeMeta)])
Code:
Python # IMPORT THE REQUIRED LIBRARY import sqlalchemy as db # DEFINE THE ENGINE (CONNECTION OBJECT) engine = db.create_engine("mysql+pymysql://\ root:password@localhost/sakila") # CREATE THE METADATA OBJECT TO ACCESS THE TABLE meta_data = db.MetaData(bind=engine) db.MetaData.reflect(meta_data) # GET THE `payment` TABLE FROM THE METADATA OBJECT payment_table = meta_data.tables['payment'] # SELECT COUNT(amount) FROM sakila.`payment`; query = db.select([db.func.count(payment_table.c.amount)]) # FETCH ALL THE RECORDS IN THE RESPONSE result = engine.execute(query).first() # VIEW THE RESULT print(result[0])
Output:
16049
Similar Reads
SQLAlchemy Core - Functions SQLAlchemy provides a rich set of functions that can be used in SQL expressions to perform various operations and calculations on the data. SQLAlchemy provides the Function API to work with the SQL functions in a more flexible manner. The Function API is used to construct SQL expressions representin
7 min read
MySQL Aggregate Function MySQL Aggregate Functions are used to calculate values from multiple rows and return a single result, helping in summarizing and analyzing data. They include functions for counting, summing, averaging, and finding maximum or minimum values, often used with the GROUP BY clause. In this article, we wi
3 min read
PL/SQL Aggregate Function In PL/SQL, aggregate functions play an important role in summarizing and analyzing data from large datasets. These built-in SQL functions perform calculations on a set of values and return a single result, making them invaluable for tasks like calculating totals, averages, and identifying the highes
5 min read
SQLAlchemy - Introduction SQLAlchemy is basically referred to as the toolkit of Python SQL that provides developers with the flexibility of using the SQL database. The benefit of using this particular library is to allow Python developers to work with the language's own objects, and not write separate SQL queries. They can b
3 min read
SQLAlchemy Core - Joins SQLAlchemy Core is a Python toolkit that enables developers to create complex database applications. It provides several features, one of which is the ability to join tables. Joining tables allows developers to retrieve data from multiple tables simultaneously, which is useful when the data is rela
3 min read