Building aggregate functions In SQLAlchemy

In this tutorial, I will use SQLAlchemy to perform aggregate operations on a database containing information about states, counties, cities, and government facilities. I will describe briefly the process of building a project to use aggregate functions.

Let us check some concepts before starting:

What is SQLAlchemy?

According to Myers (2016), “The SQLAlchemy ORM is what most people think of when you mention SQLAlchemy. It provides a very effective way to bind database schema and operations to the same data objects used in the application. It offers a way to rapidly build applications and get them into customers’ hands.”

Also, the documentation on the SQLAlchemy website says, “SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL.”

What aggregate functions In SQLAlchemy are?

Aggregate functions are generic function implementations for a set of standard SQL functions that automatically set up the expected return type for each function.

In SQLAlchemy, an aggregate function is a function where the values of multiple rows are grouped to form a single value of a more significant meaning or measurement. These functions are widely used in database queries to perform calculations on data, such as summing values, calculating averages, or finding the minimum or maximum value in a set.

As can be seen from Figure 1 (GeeksforGeeks, 2022), Some standard aggregate functions include:

sum(): Calculates the sum of values, avg(): Computes the average of values, min(): Determines the smallest value, max(): Determines the largest value, and count(): Counts the number of rows.

Figure 1. Common aggregate functions. GeeksforGeeks (2022).

What is Alembic?

Alembic documentation shows that ‘Alembic is a lightweight database migration tool for usage with the SQLAlchemy Database Toolkit for Python.’

According to Myers (2016), ‘Alembic is a tool for handling database changes that leverages SQLAlchemy to perform the migrations. Since SQLAlchemy will only create missing tables when we use the metadata’s create_all method, it does not update the database tables to match any changes we might make to the columns. Nor would it delete tables that we removed from the code. Alembic provides a way to do things like adding/ deleting tables, changing column names, and adding new constraints. Because Alembic uses SQLAlchemy to perform the migrations; they can be used on various backend databases.’

Process to build a project to use aggregate functions.

(You must have Python installed in your system.)

Overview

1. Create your own Git Repo.
2. Creating a Migration Environment.
3. Configuring a Migration Environment.
4. Generating a Base Empty Migration.
5. Autogenerating a Migration.
6. Test the database.
7. Populate the database.
8. Test the database.
9. Build the aggregate operations.
10. Test the aggregate operations.

Step by Step

1. Create your own Git Repo : create your local repo and push it.

2. Creating a Migration Environment : To create the migration environment, we will create a folder labeled lib, initialize the environment, install the dependencies, and run the first alembic command. This initialization process creates the migration environment and also creates an alembic.ini file with the configuration options

3. Configuring a Migration Environment :

4. Generating a Base Empty Migration : This command will create a migration file in the alembic/versions/ subfolder. “Because we do not have any data classes and have made no changes, our upgrade and downgrade methods are empty. So running this migration will have no effect, but it will provide a great foundation for our migration chain.” (Mayers, 2016)

5. Autogenerating a Migration : With our base in place, we can add our data class to our application. For this tutorial, I will add the final version of models.py, but it is supposed that you should build the models.py file in a few steps and generate different migrations files.

6.Test the database! : After running this migration, we can take a peek in the database to make sure the changes happened:

7.Populate the database : You have noticed that we installed click in step number 2; it was not a mistake. We will take advantage of click commands to seed our database. “Click is a Python package for creating beautiful command line interfaces in a composable way with as little code as necessary. It is the “Command Line Interface Creation Kit.” It is highly configurable but comes with sensible defaults out of the box.” (Docs, Click)

• Seed.py : Following a specific order populating the database is crucial. We must start seeding states, which generate state_id that will be used for the others tables, then seed counties table, following cities tables, facilities, and finally, seed associations table. Of course, you can seed the database using another order, but you will probably have to modify NULL or NONE values in some tables’ column IDs.

 @click.group()
 def cli():
     """Manage the database records."""
     pass

 @cli.command()
 def seed_states():
     """ Seed states."""
     session.query(State).delete()
     session.commit()
     session.add_all(states_to_add)
     session.commit()
     click.echo("✅ Done seeding states!")


 @cli.command()
 def seed_counties():
     """ Seed counties."""
     session.query(County).delete()
     session.commit()
     session.add_all(counties_to_add)
     session.commit()
     click.echo("✅ Done seeding counties!")

     # more click commands


 ➜ lib git:(master) python seed.py seed-states
 🌱 Seeding DB...
 ✅ Done seeding states!
 ➜ lib git:(master) ✗ python seed.py seed-counties
 🌱 Seeding DB...
 ✅ Done seeding counties!
 ➜ lib git:(master) ✗ python seed.py seed-cities
 🌱 Seeding DB...
 ✅ Done seeding cities!
 ➜ lib git:(master) ✗ python seed.py seed-facilities
 🌱 Seeding DB...
 ✅ Done seeding facilities!
 ➜ lib git:(master) ✗ python seed.py seed-associations
 🌱 Seeding DB...
 ✅ Done seeding associations!
 ➜ lib git:(master) ✗ >....

8.Test the database : time to check our work! I will use SQLite3 to query the database.

9.Build the aggregate operations : I will use click commands again to perform aggregate operations. Each command prompts the user for input and then executes a database query based on that input. After querying the database, the command outputs a result.

10.Test the aggregate operations using sqlite3: finally, I will use sqlite3 to test the aggregate operations.

Final thoughts:

This tutorial showed how to use SQLAlchemy to query a database. We walked through setting up a project to use aggregate functions. By the end, you should understand how to handle and use such functions effectively.

This article was originally posted on: https://hcoco1-blog.onrender.com/

References:

Myers, J. (2016). Essential sqlalchemy. O’Reilly.

“Documentation.” Alembic, www.alembic.sqlalchemy.org. Accessed 14 Aug. 2023.

“Documentation.” SQLAlchemy, www.sqlalchemy.org/. Accessed 14 Aug. 2023.

GeeksforGeeks. (2022). SQLAlchemy Aggregate Functions. GeeksforGeeks. https://www.geeksforgeeks.org/sqlalchemy-aggregate-functions/