How to GroupBy and Sum SQL Columns using SQLAlchemy in Python?

As we journey into the realm of Python and its powerful library SQLAlchemy, a wellspring of versatile operations is unveiled. One such capability is the GroupBy and Sum operation on SQL columns - an operation of paramount importance when it comes to database manipulation. SQLAlchemy, a SQL toolkit, and Object-Relational Mapping (ORM) system for Python, offers a wealth of features, facilitating SQL operations in a seamless and Pythonic way. Let's delve into how we can harness SQLAlchemy to GroupBy and Sum SQL columns.

Syntax

In this article, we will explore how to use SQLAlchemy, a popular Python SQL toolkit, to perform grouping and summation of SQL columns. We will demonstrate two approaches − the ORM Sessions approach and the Explicit Sessions approach. The two methodologies influence the force of SQLAlchemy to work on information base tasks and give a perfect and instinctive syntax.

Stmt=session.query(Sales.product,func.sum(Sales.quantity).label('total_quantity')).group_by(Sales.product)

Algorithm

Step-by-step −

• Import necessary modules.

• Establish a session with the database.

• Query the database, specifying the column you wish to group by and sum.

• Use group_by() function to group data.

• Use func.sum() to calculate the sum.

• Execute the command and fetch the results.

• Handle exceptions, if any, and close the session.

Approach 1: Using Explicit Sessions

The first approach is using explicit sessions, which is ideal for application-based codes.

Example

from sqlalchemy import create_engine, Column, Integer, String, func
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

# Create a new engine instance
engine = create_engine('sqlite:///example.db')

Base = declarative_base()

# Define a new table with a name, metadata, and several columns
class Sales(Base):
   __tablename__ = 'sales'
   
   id = Column(Integer, primary_key=True)
   product = Column(String)
   quantity = Column(Integer)

# Create the table
Base.metadata.create_all(engine)

# Prepare data
data = [
   Sales(product='Apples', quantity=5),
   Sales(product='Oranges', quantity=7),
   Sales(product='Apples', quantity=3),
   Sales(product='Bananas', quantity=8),
   Sales(product='Apples', quantity=6),
   Sales(product='Oranges', quantity=9),
]

# Create a session
Session = sessionmaker(bind=engine)
session = Session()

try:
   # Add data to the session
   session.add_all(data)

   # Commit the changes
   session.commit()

   # Create a select statement
   stmt = session.query(Sales.product, func.sum(Sales.quantity).label('total_quantity')).group_by(Sales.product)

   # Execute the statement
   results = stmt.all()

   for result in results:
      print(result)

finally:
   # Close the session
   session.close()

Explanation

In this approach, we follow a similar initial setup as in the previous approach by creating the engine and defining the table structure. We manually create a session factory using sessionmaker and open a session using Session(). Inside a try-finally block, we add the data to the session, commit the changes, and create the select statement for grouping and summation. We execute the statement, process the outcomes, lastly close the session.

These two methodologies give various ways of accomplishing a similar outcome. Contingent upon your venture prerequisites and coding inclinations, you can pick the methodology that best suits your necessities.

Approach 2: Using ORM Sessions

The second approach utilizes SQLAlchemy ORM (Object Relational Mapper) which allows classes to be mapped to tables in the database, thereby offering a high-level, Pythonic interface.

Example

from sqlalchemy import create_engine, Column, Integer, String, func
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

# Create a new engine instance
engine = create_engine('sqlite:///example.db')

Session = sessionmaker(bind=engine)
session = Session()

Base = declarative_base()

# Define a new table with a name, metadata, and several columns
class Sales(Base):
   __tablename__ = 'sales'
    
   id = Column(Integer, primary_key=True)
   product = Column(String)
   quantity = Column(Integer)

# Create the table
Base.metadata.create_all(engine)

# Prepare data
data = [
   Sales(product='Apples', quantity=5),
   Sales(product='Oranges', quantity=7),
   Sales(product='Apples', quantity=3),
   Sales(product='Bananas', quantity=8),
   Sales(product='Apples', quantity=6),
   Sales(product='Oranges', quantity=9),
]

# Add and commit data to the session
session.add_all(data)
session.commit()

# Create a select statement
stmt = session.query(Sales.product, func.sum(Sales.quantity).label('total_quantity')).group_by(Sales.product)

# Execute the statement
results = stmt.all()

for result in results:
   print(result)

session.close()

Explanation

In this approach, we start by creating an engine to connect to the database. Then, we define a session factory using sessionmaker and instantiate a session object. Next, we declare a base class using declarative_base() for our table definitions and define the structure of the table using the class Sales. We create the table in the database using Base.metadata.create_all(engine).

To perform grouping and summation, we add the necessary data to the session using session.add_all(data) and commit the changes using session.commit(). We create a select statement with grouping and summation using session.query and func.sum, and execute it using stmt.all(). Finally, we process the results and close the session using session.close().

Conclusion

Grouping and summing columns in SQL tables are rudimentary yet essential operations in data manipulation and analysis. SQLAlchemy, with its remarkable ease of use and Python-like syntax, bridges the gap between SQL operations and Python programming. The ability to use either explicit sessions or ORM sessions, depending on the context, further adds to SQLAlchemy's appeal. Both of the approaches mentioned above can be tailored as per the requirements, laying a robust foundation for Python users working with SQL databases. Always remember to close your sessions to free up resources. With that, you're now armed with the knowledge to perform GroupBy and Sum operations using SQLAlchemy in Python. Happy Coding!