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 Functions
In 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 Table
If 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 sqlalchemy as db
engine = db.create_engine("mysql+pymysql://\
root:password@localhost/sakila")
meta_data = db.MetaData(bind=engine)
db.MetaData.reflect(meta_data)
payment_table = meta_data.tables['payment']
query = db.select([db.func.min(payment_table.c.amount)])
result = engine.execute(query).first()
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 sqlalchemy as db
engine = db.create_engine("mysql+pymysql://\
root:password@localhost/sakila")
meta_data = db.MetaData(bind=engine)
db.MetaData.reflect(meta_data)
payment_table = meta_data.tables['payment']
query = db.select([db.func.max(payment_table.c.amount)])
result = engine.execute(query).first()
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 sqlalchemy as db
engine = db.create_engine("mysql+pymysql://\
root:password@localhost/sakila")
meta_data = db.MetaData(bind=engine)
db.MetaData.reflect(meta_data)
payment_table = meta_data.tables['payment']
query = db.select([db.func.sum(payment_table.c.amount)])
result = engine.execute(query).first()
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 sqlalchemy as db
engine = db.create_engine("mysql+pymysql://\
root:password@localhost/sakila")
meta_data = db.MetaData(bind=engine)
db.MetaData.reflect(meta_data)
payment_table = meta_data.tables['payment']
query = db.select([db.func.avg(payment_table.c.amount)])
result = engine.execute(query).first()
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 sqlalchemy as db
engine = db.create_engine("mysql+pymysql://\
root:password@localhost/sakila")
meta_data = db.MetaData(bind=engine)
db.MetaData.reflect(meta_data)
payment_table = meta_data.tables['payment']
query = db.select([db.func.count(payment_table.c.amount)])
result = engine.execute(query).first()
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
6 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
Using SQLite Aggregate functions in Python
In this article, we are going to see how to use the aggregate function in SQLite Python. An aggregate function is a database management function that groups the values of numerous rows into a single summary value. Average (i.e., arithmetic mean), sum, max, min, Count are common aggregation functions
4 min read
SQLAlchemy Core - Conjunctions
SQLAlchemy is a popular Python programming SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL in a Pythonic way. SQLAlchemy ORM or object-relational mapper is a component that provides an abstraction layer over the SQL database which mak
6 min read
DAX Aggregate Functions in Power BI
Microsoft Power BI is a tool that helps businesses to analyze data and create interactive reports and visualizations. It can connect to various data sources such as Excel, SQL databases, cloud services, etc and can perform aggregate functions on them for analysis. In this article we will learn about
6 min read
SQLAlchemy Core - Set Operations
SQLAlchemy Core is a powerful tool for working with databases in Python. One of the key features of SQLAlchemy Core is its support for set operations, which allow you to perform complex queries on your data. In this article, we will explore the basics of set operations in SQLAlchemy Core and provide
7 min read
Group by and count function in SQLAlchemy
In this article, we are going to see how to perform Groupby and count function in SQLAlchemy against a PostgreSQL database in Python. Group by and count operations are performed in different methods using different functions. Such kinds of mathematical operations are database-dependent. In PostgreSQ
2 min read