How to use sum and order by in SQLAlchemy query?

In this article, we are going to see how to perform the sum and count function in SQLAlchemy against a PostgreSQL database in python.

SUM and count operations are performed in different methods using different functions. Such kinds of mathematical operations are database-dependent. In PostgreSQL, Group by is performed using a function called sum(), and count operation is performed using count(). In SQLAlchemy, generic functions like SUM, MIN, MAX are invoked like conventional SQL functions using the func attribute.

Some common functions used in SQLAlchemy are count, cube, current_date, current_time, max, min, mode etc.

Usage: func.sum(). func.group_by(), func.sum()

Create a sample table for demonstration:

Import necessary functions from the SQLAlchemy package. And Establish a connection with the PostgreSQL database using create_engine() function as shown below, create a table called books with columns book_id and book_price. Insert record into the tables using insert() and values() function as shown.

Python3

# import necessary packages
import sqlalchemy
from sqlalchemy import create_engine, MetaData,
Table, Column, Numeric, Integer, VARCHAR
from sqlalchemy.engine import result

# establish connections
engine = create_engine(
    "postgresql+psycopg2://postgres:Saibaba97%40@127.0.0.1:5432/test")

# initialize the Metadata Object
meta = MetaData(bind=engine)
MetaData.reflect(meta)

# create a table schema
books = Table(
    'books', meta,
    Column('bookId', Integer, primary_key=True),
    Column('book_price', Numeric),
    Column('genre', VARCHAR),
    Column('book_name', VARCHAR)
)

meta.create_all(engine)

# insert records into the table
statement1 = books.insert().values(bookId=1, book_price=12.2,
                                   genre = 'fiction',
                                   book_name = 'Old age')
statement2 = books.insert().values(bookId=2, book_price=13.2,
                                   genre = 'non-fiction',
                                   book_name = 'Saturn rings')
statement3 = books.insert().values(bookId=3, book_price=121.6,
                                   genre = 'fiction',
                                   book_name = 'Supernova')
statement4 = books.insert().values(bookId=4, book_price=100,
                                   genre = 'non-fiction',
                                   book_name = 'History of the world')
statement5 = books.insert().values(bookId=5, book_price=1112.2,
                                   genre = 'fiction',
                                   book_name = 'Sun city')

# execute the insert records statement
engine.execute(statement1)
engine.execute(statement2)
engine.execute(statement3)
engine.execute(statement4)
engine.execute(statement5)

Output:

Sample table

Implementing sum and order by in SQLAlchemy

Writing a sum and order by function has a slightly different procedure than that of a conventional SQL query which is  shown below

sqlalchemy.select([

Tablename.c.column_name,

sqlalchemy.func.sum(Tablename.c.column_name)

]).group_by(Tablename.c.column_name).order_by(Tablename.c.column_name)

Get the books table from the Metadata object initialized while connecting to the database and pass the SQL query to the execute() function and get all the results using fetchall() function and use a for loop to iterate through the results.

This SQL query returns the sum of book prices based on the genre of the book and orders alphabetically based on the genre of the book.

Python3

# Get the `books` table from the Metadata object
BOOKS = meta.tables['books']

# SQLAlchemy Query to use sum and order by function
query = sqlalchemy.select([
    BOOKS.c.genre,
    sqlalchemy.func.sum(BOOKS.c.book_price)
]).group_by(BOOKS.c.genre).order_by(BOOKS.c.genre)

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

# View the records
for record in result:
    print("\n", record)

Output:

The output of  sum and order by function