SQLAlchemy – Aggregate Functions

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 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