Python SQLAlchemy - Group_by and return max date

In this article, we are going to see how to use Group_by and return max date SQLAlchemy in Python.

Installing SQLAlchemy

SQLAlchemy is available via 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 installed using -

pip install flask-sqlalchemy

Example

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 users table. The table has 6 columns and 7 records as shown below.

In the above table, we can see that there are 3 distinct users namely, John, Emma, and Liam. We will GROUP BY on the basis of their first_name and last_name. The table has a created_on field which is a TIMESTAMP data type. It will be used to pick the maximum date for a distinct user.

Approach:

1. First, we import the sqlalchemy library as db for simplicity. All the sqlalchemy objects, methods, etc will be imported using db prefix for better clarity.
2. We then create the engine which will serve as a connection to the database to perform all the database operations.
3. Create the metadata object. The metadata object `metadata` contains all the information about our database.
4. Use the metadata information to fetch the users table from database.
5. We can now write an SQLAlchemy query to fetch the required records. We perform GROUP BY operation on first_name and last_name fields and in the SELECT query retrieve the maximum date using the SQLalchemy's `func.max()` function.
6. Print all the fetched records. In the output we can view that we have only 3 distinct users and the corresponding updated_on field for all of the users is the maximum date that we have in the table entries.

Below is the implementation:

import sqlalchemy as db
from sqlalchemy.engine import result

# 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 `users` table from the Metadata object
USERS = meta_data.tables['users']

# SQLAlchemy Query to GROUP BY and fetch MAX date
query = db.select([
    USERS.c.email,
    USERS.c.first_name,
    USERS.c.last_name,
    db.func.max(USERS.c.created_on)
]).group_by(USERS.c.first_name, USERS.c.last_name)

# Fetch all the records
result = engine.execute(query).fetchall()

# View the records
for record in result:
    print("\n", record)

Output: