How to count rows with SELECT COUNT(*) with SQLAlchemy?

In this article, we will see how to select the count of rows using SQLAlchemy in Python.

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 SQL connectors.

pip install pymysql

We will use the sample sakila database from mySQL. In this article, we will cover 2 examples, one each for SQLAchemy Core and ORM layers. In both examples, we will count the number of records present in the actor table within the sakila database. If you do not have the sakila database and want to follow along with this article without installing it then use the SQL script present in the link mentioned below to create the required schema and actor table along with the records. Sakila Actor Table Script

The SQL query which we are looking at in the below two examples is –

SELECT COUNT(*) FROM sakila.`actor`;

SQLAlchemy Core

SQLAlchemy Core is a schema-centric model that means everything is treated as a part of the database i.e., rows, columns, tables, etc. In the above example, we have created the metadata object to access the database objects like the table. Using this object we get the metadata of the actor table. This metadata information is then used to query to the table using the SQLAlchemy syntax mentioned below.

Syntax: sqlalchemy.select([sqlalchemy.func.count()]).select_from(sqlalchemy.DeclarativeMeta).scalar()

Python

import sqlalchemy as db

# DEFINE THE ENGINE (CONNECTION OBJECT)
engine = db.create_engine("mysql+pymysql://root:password@localhost/sakila")

# CREATE THE METADATA OBJECT TO ACCESS THE TABLE
meta_data = db.MetaData(bind=engine)
db.MetaData.reflect(meta_data)

# GET THE `actor` TABLE FROM THE METADATA OBJECT
actor_table = meta_data.tables['actor']

# SELECT COUNT(*) FROM Actor
result = db.select([db.func.count()]).select_from(actor_table).scalar()

print("Count:", result)

Output:

Count: 200

SQLAlchemy ORM

SQLAlchemy Core uses an object-centric view that encapsulates the schema with business objects. It is a more pythonic implementation as we can see the tables represented in the class format. We have used this class object to query the actor table using the SQLAlchemy syntax mentioned below.

Syntax: sqlalchemy.orm.Session.query(sqlalchemy.DeclarativeMeta).count()

Python

import sqlalchemy as db
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

# DEFINE THE ENGINE (CONNECTION OBJECT)
engine = db.create_engine("mysql+pymysql://root:password@localhost/sakila")

# CREATE THE TABLE MODEL TO USE IT FOR QUERYING
class Actor(Base):

    __tablename__ = 'actor'

    actor_id    = db.Column(db.SmallInteger,
                            primary_key=True,
                            autoincrement=True)
    first_name  = db.Column(db.String(45))
    last_name   = db.Column(db.String(45))
    last_update = db.Column(db.DateTime)
    city        = db.Column(db.String(20))

# CREATE A SESSION OBJECT TO INITIATE QUERY IN DATABASE
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()

# SELECT COUNT(*) FROM Actor
result = session.query(Actor).count()

print("Count:", result)

Output:

Count: 200