How to GroupBy and Sum SQL Columns using SQLAlchemy?
Last Updated :
30 Jan, 2022
In this article, we are going to see how to use GroupBy and Sum in SQLAlchemy.
Installing SQLAlchemy
SQLAlchemy is available via pip install package.
pip install sqlalchemy
However, if you are using a flask you can make use of its own implementation of SQLAlchemy. It can be installed using -
pip install flask-sqlalchemy
In the examples, we will make use of common syntax.
- sqlalchemy.create_engine(URL): Creates an engine object that can either be used directly to interact with the database or can be passed to a Session object to work with the ORM. The typical form of a database URL is "dialect+driver://username:password@host:port/database"
- sqlalchemy.select(*entities): The primary construct used to generate SELECT statements. Entities is typically a series of Columns to select.
- sqlalchemy.select(*entities).group_by(column_name): The primary construct used to generate GROUP BY statements.
- sqlalchemy.func.sum(column_name): The SQL SUM() aggregate function.
- sqlalchemy.engine.execute(statement): Executes the given statement and returns a result object.
Using GroupBy and Sum in columns
Example 1:
Before we move ahead, we need to have a database and a table to work with. For this example, we are using mySQL database and have created a sales table. The table has 3 columns and 9 records as shown below.
sales table
In the above table, we will use the company column for the grouping and aggregation will be done on the no_of_invoices column.
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 `sales` table from the Metadata object
SALES = meta_data.tables['sales']
# SQLAlchemy Query to GROUP BY and aggregate SUM
query = db.select([SALES.c.company, db.func.sum(SALES.c.no_of_invoices)]) \
.group_by(SALES.c.company)
# Fetch all the records
result = engine.execute(query).fetchall()
# View the records
for record in result:
print("\n", "Company:", record[0],
"| Sum of Invoices:",
record[1])
Output:
Example 1 Output
Explanation:
- First we import the sqlalchemy library as db for simplicity. All the sqlalchemy object, methods, etc will be imported using db prefix for better clarity.
- We then create the engine which will serve as a connection to the database to perform all the database operations.
- Create the metadata object. The metadata object `metadata` contains all the information about our database.
- Use the metadata information to fetch the sales table from database.
- We can now write an SQLAlchemy query to fetch the required records. We first group by on the basis of company name using the `group_by()` method and then find the sum of the number of invoices using the SQLalchemy's `func.sum()` function.
- Print the output. In the output we can view that we have the distinct company names and their corresponding sum of invoices produced.
Example 2:
In this example, let us consider the following students table
students table
In this example, we will perform a GROUP BY operation taking two columns as reference.
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 GROUP BY and aggregate SUM
query = db.select([
STUDENTS.c.first_name,
STUDENTS.c.last_name,
db.func.sum(STUDENTS.c.score)
]).group_by(STUDENTS.c.first_name, STUDENTS.c.last_name)
# Fetch all the records
result = engine.execute(query).fetchall()
# View the records
for record in result:
print("\n", record[0], record[1],
"| Total Score:", record[2])
Output:
Example 2 Output
Explanation:
The above code is pretty similar to the one discussed in Example 1 except for the fact that in this example we performed GROUP BY operation on multiple columns, namely, the first_name and the last_name fields. Multiple fields can be mentioned inside the group_by() method as separate parameters.
Example 3:
Considering the students table mentioned in Example 2, let us look at how the output changes if we perform GROUP BY operation on the course field.
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 GROUP BY and aggregate SUM
query = db.select([
STUDENTS.c.course,
db.func.sum(STUDENTS.c.score)
]).group_by(STUDENTS.c.course)
# Fetch all the records
result = engine.execute(query).fetchall()
# View the records
for record in result:
print("\n", "Course:", record[0],
"| Total Score:", record[1])
Output:
Example 3 Output
Similar Reads
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 installe
2 min read
How To Select Only One Column Using SQLAlchemy?
In this article, we are going to see how to select only one column using SQLAlchemy in Python. SQLAlchemy is a large SQL toolkit with lots of different components. The two largest components are SQLAlchemy Core and SQLAlchemy ORM. The major difference between them is SQLAlchemy Core is a schema-cent
3 min read
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, Gr
3 min read
Join with sum and count of grouped rows in SQLAlchemy
SQLAlchemy is a popular Python ORM (Object-Relational Mapping) library that provides a convenient way to interact with databases. One of the common tasks when working with databases is to perform joins between tables and calculate aggregate values based on grouped rows. In this article, we will expl
4 min read
Using Sqlalchemy to insert MySQL Timestamp Column Values
This article is about how we can add a timestamp column with values in MYSQL along with other data in an SQL database. Timestamp is quite useful as it provides the time when that particular entity was created in the database. Here we will use SQLAlchemy as it is a popular Python programming SQL tool
3 min read
How to get column names from SQLAlchemy?
In this article, we will discuss how to get column names using SQLAlchemy in Python. SQLAlchemy is an open-source SQL toolkit and object-relational mapper for the Python programming language released under the MIT License. It gives full power and flexibility of SQL to an application. To follow along
3 min read
Count SQL Table Column Using Python
Prerequisite: Python: MySQL Create Table In this article, we are going to see how to count the table column of a MySQL Table Using Python. Python allows the integration of a wide range of database servers with applications. A database interface is required to access a database from Python. MySQL Con
2 min read
Connecting to SQL Database using SQLAlchemy in Python
In this article, we will see how to connect to an SQL database using SQLAlchemy in Python. To connect to a SQL database using SQLAlchemy we will require the sqlalchemy library installed in our python environment. It can be installed using pip - !pip install sqlalchemyThe create_engine() method of sq
3 min read
How to divide two columns in SQLAlchemy?
In this article, we are going to divide two columns using the SQLAlchemy module of python. Installing SQLAlchemy To install SQLAlchemy, run the following command in the terminal. pip install sqlalchemy pymysql So, what we have to do in this post is to divide two columns and get output using SQLAlche
2 min read
Sum multiple columns in SQLAlchemy
In this article, we are going to sum multiple columns and get results using the SQLAlchemy module of python. Installation To install the SQLAlchemy module, run the following command in your terminal: pip install sqlalchemy pymysql Note: pymysql is a dependency of SQLAlchemy which we need to install
3 min read