Open In App

How to GroupBy and Sum SQL Columns using SQLAlchemy?

Last Updated : 30 Jan, 2022
Improve
Improve
Like Article
Like
Save
Share
Report

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(
  
# 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(
  
# 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(
  
# 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

Combining multiple columns in Pandas groupby with dictionary
Let' see how to combine multiple columns in Pandas using groupby with dictionary with the help of different examples. Example #1: # importing pandas as pd import pandas as pd # Creating a dictionary d = {'id':['1', '2', '3'], 'Column 1.1':[14, 15, 16], 'Column 1.2':[10, 10, 10], 'Column 1.3':[1, 4, 5], 'Column 2.1':[1, 2, 3], 'Column 2.2':[10, 10,
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 for this post. So, what we are going to do is, fir
3 min read
How to get specific columns in SQLAlchemy with filter?
In this article, we will see how to query and select specific columns using SQLAlchemy in Python. For our examples, we have already created a Students table which we will be using: [caption width="800"]Students Table[/caption]Selecting specific column in SQLAlchemy based on filter:To select specific column in SQLAlchemySyntax: sqlalchemy.select(*en
2 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 SQLAlchemy. Database used: So, we have the table named "pl
2 min read
SQLAlchemy - Mapping Table Columns
In this article, we will see how to map table columns using SQLAlchemy in Python. You will need a database (MySQL, PostgreSQL, SQLite, etc) to work with. Since we are going to use MySQL in this post, we will also install a SQL connector for MySQL in Python. However, none of the code implementations changes with change in the database except for the
5 min read
How to sum negative and positive values using GroupBy in Pandas?
In this article, we will discuss how to calculate the sum of all negative numbers and positive numbers in DataFrame using the GroupBy method in Pandas. To use the groupby() method use the given below syntax. Syntax: df.groupby(column_name) Stepwise Implementation Step 1: Creating lambda functions to calculate positive-sum and negative-sum values. p
3 min read
Create a SQL table from Pandas dataframe using SQLAlchemy
In this article, we will discuss how to create a SQL table from Pandas dataframe using SQLAlchemy. As the first steps establish a connection with your existing database, using the create_engine() function of SQLAlchemy. Syntax: from sqlalchemy import create_engine engine = create_engine(dialect+driver://username:password@host:port/database) Explana
3 min read
Read SQL database table into a Pandas DataFrame using SQLAlchemy
To read sql table into a DataFrame using only the table name, without executing any query we use read_sql_table() method in Pandas. This function does not support DBAPI connections. read_sql_table()Syntax : pandas.read_sql_table(table_name, con, schema=None, index_col=None, coerce_float=True, parse_dates=None, columns=None, chunksize=None) Paramete
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 sqlalchemy library takes in the connection URL and r
3 min read
Pandas Groupby and Sum
It's a simple concept but it's an extremely valuable technique that's widely used in data science. It is helpful in the sense that we can : Compute summary statistics for every groupPerform group-specific transformationsDo the filtration of data The dataframe.groupby() involves a combination of splitting the object, applying a function, and combini
2 min read
Article Tags :
Practice Tags :