In this article, we are going to see how to perform the orderby function before using a groupby function in SQLAlchemy against a PostgreSQL database in Python.
PostgreSQL, Group by is performed using a function called groupby(), and order by the operation is performed using orderby().
Usage: func.sum(). func.group_by(), func.sum(), func.order_by()
Creating table for demonstration
Import necessary functions from the SQLAlchemy package. Establish connection with the PostgreSQL database using create_engine() function as shown below and 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 packagesimport sqlalchemyfrom sqlalchemy import create_engine, MetaData, Table, Column, Numeric, Integer, VARCHARfrom sqlalchemy.engine import result# establish connectionsengine = create_engine(# initialize the Metadata Objectmeta = MetaData(bind=engine)MetaData.reflect(meta)# create a table schemabooks = Table( 'books', meta, Column('book_id', Integer, primary_key=True), Column('book_price', Numeric), Column('genre', VARCHAR), Column('book_name', VARCHAR))meta.create_all(engine)# insert records into the tablestatement1 = books.insert().values(book_id=1, book_price=12.2, genre='fiction', book_name='Old age')statement2 = books.insert().values(book_id=2, book_price=13.2, genre='non-fiction', book_name='Saturn rings')statement3 = books.insert().values(book_id=3, book_price=121.6, genre='fiction', book_name='Supernova')statement4 = books.insert().values(book_id=4, book_price=100, genre='non-fiction', book_name='History of the world')statement5 = books.insert().values(book_id=5, book_price=1112.2, genre='fiction', book_name='Sun city')# execute the insert records statementengine.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 an orderby function before 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.sum(Tablename.c.column_name)
]).order_by(Tablename.c.column_name).group_by(Tablename.c.column_name)
Get the books table from the Metadata object initialized while connecting to the database. Pass the SQL query to the execute() function and get all the results using fetchall() function. Use a for loop to iterate through the results. The SQLAlchemy query shown in the below code groups the book by genre, then order the books alphabetically based on genre and return the sum of sales for each genre.
Python3
# Get the `books` table from the Metadata objectBOOKS = meta.tables['books']# SQLAlchemy Query to ORDER BY and GROUP BY query = sqlalchemy.select([ BOOKS.c.genre, sqlalchemy.func.sum(BOOKS.c.book_price)]).order_by(BOOKS.c.genre).group_by(BOOKS.c.genre)# Fetch all the recordsresult = engine.execute(query).fetchall()# View the recordsfor record in result: print("\n", record) |
Output:
Orderby before group by query result
