How to use avg and sum in SQLAlchemy Query?

In this article, we are going to see how to use avg and sum in SQLAlchemy query using Python.

Installing SQLAlchemy

SQLAlchemy is available via the pip install package.

pip install sqlalchemy

However, if you are using flask you can make use of its own implementation of SQLAlchemy. It can be installed using –

pip install flask-sqlalchemy

Example

Before we move ahead, we need to have a database and a table to work with. For this example, we are using the MySQL database and have created a students table. The table has 3 columns and 6 records as shown below.

In the table, we have a float column `percentage` on which we will perform our average and sum operations using SQLAlchemy.

Approach:

1. First, we import the sqlalchemy library as db for simplicity. All the sqlalchemy objects, methods, etc will be imported using db prefix for better clarity.
2. We then create the engine which will serve as a connection to the database to perform all the database operations.
3. Create the metadata object. The metadata object `metadata` contains all the information about our database.
4. Use the metadata information to fetch the students table from the database.
5. We can now write an SQLAlchemy query to fetch the required records. We first extract the average value of the percentage column using SQLalchemy’s `func.avg()` function. Then we use the `func.sum()` function to get the sum of the values in the percentage column. Note that in both cases we have used the method `func.round(val, 2)` to round off the values to 2 decimal places.
6. Print the output. In the output we can view that we have both the sum and average values for the percentage field.

Below is the implementation:

Python

import sqlalchemy as db

# Define the Engine (Connection Object)
engine = db.create_engine(
    "mysql+pymysql://root:password@localhost/Geeks4Geeks")

# Create the Metadata Object
meta_data = db.MetaData(bind=engine)
db.MetaData.reflect(meta_data)

# Get the `students` table from the Metadata object
STUDENTS = meta_data.tables['students']

# SQLAlchemy Query to get AVG
query = db.select([db.func.round(db.func.avg(STUDENTS.c.percentage), 2)])

# Fetch the records
avg_result = engine.execute(query).fetchall()

# SQLAlchemy Query to get SUM
query = db.select([db.func.round(db.func.sum(STUDENTS.c.percentage), 2)])

# Fetch the records
sum_result = engine.execute(query).fetchall()

# View the records
print("\nAverage: ", avg_result[0])
print("\nSum: ", sum_result[0])

Output: