Group by and count function in SQLAlchemy

Last Updated : 29 Jan, 2022

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 PostgreSQL, Group by is performed using a function called group_by(), 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.count(). func.group_by(), func.max()

Creating Table for demonstration:

Import necessary functions from the SQLAlchemy package. And then 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(     "database+dialect://username:password@host:port/databasename")  # 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:

Implementing GroupBy and count in SQLAlchemy

Writing a groupby 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.count(Tablename.c.column_name) ]).group_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.

The below query returns the count of books in all genre:

Python3

# Get the `books` table from the  # Metadata object BOOKS = meta.tables['books']  # Write a SQL query using groupby  # and count function query = sqlalchemy.select([     BOOKS.c.genre,     sqlalchemy.func.count(BOOKS.c.genre) ]).group_by(BOOKS.c.genre)  # get all the records result = engine.execute(query).fetchall()  # print all the records for i in result:     print("\n", i)