Python SQLAlchemy - func.count with filter
Last Updated :
28 Feb, 2022
In this article, we are going to see how to perform filter operation with count function in SQLAlchemy against a PostgreSQL database in python
Count with filter operations is performed in different methods using different functions. Such kinds of mathematical operations are database-dependent. In PostgreSQL, the count is performed using a function called count(), and filter operation is performed using filter(). In SQLAlchemy, generic functions like SUM, MIN, MAX are invoked like conventional SQL functions using the func attribute.
Some common functions used in SQLAlchemy are count, cube, current_date, current_time, max, min, mode etc.
Usage: func.count(). func.group_by(), func.max()
Creating table for demonstration
Import necessary functions from the SQLAlchemy package. Establish connection with the PostgreSQL database using create_engine() function as shown below, create a table called books with columns book_id and book_price. Insert record into the tables using insert() and values() function as shown.
Python3
# import necessary packages
import sqlalchemy
from sqlalchemy import create_engine, MetaData, Table,
Column, Numeric, Integer, VARCHAR
from sqlalchemy.engine import result
# establish connections
engine = create_engine(
"database+dialect://username:password@host:port/databasename")
# initialize the Metadata Object
meta = MetaData(bind=engine)
MetaData.reflect(meta)
# create a table schema
books = Table(
'books', meta,
Column('bookId', Integer, primary_key=True),
Column('book_price', Numeric),
Column('genre', VARCHAR),
Column('book_name', VARCHAR)
)
meta.create_all(engine)
# insert records into the table
statement1 = books.insert().values(bookId=1, book_price=12.2,
genre = 'fiction',
book_name = 'Old age')
statement2 = books.insert().values(bookId=2, book_price=13.2,
genre = 'non-fiction',
book_name = 'Saturn rings')
statement3 = books.insert().values(bookId=3, book_price=121.6,
genre = 'fiction',
book_name = 'Supernova')
statement4 = books.insert().values(bookId=4, book_price=100,
genre = 'non-fiction',
book_name = 'History of the world')
statement5 = books.insert().values(bookId=5, book_price=1112.2,
genre = 'fiction',
book_name = 'Sun city')
# execute the insert records statement
engine.execute(statement1)
engine.execute(statement2)
engine.execute(statement3)
engine.execute(statement4)
engine.execute(statement5)
Output:
Sample tableImplementing GroupBy and count in SQLAlchemy
Writing a groupby function has a slightly different procedure than that of a conventional SQL query which is shown below
sqlalchemy.select([
Tablename.c.column_name,
sqlalchemy.func.count(Tablename.c.column_name)
]).group_by(Tablename.c.column_name).filter(Tablename.c.column_name value)
Get the books table from the Metadata object initialized while connecting to the database. Pass the SQL query to the execute() function and get all the results using fetchall() function. Use a for loop to iterate through the results.
The below query returns the count of books in different genres whose prices are greater than Rs. 50.
Python3
# Get the `books` table from the Metadata object
BOOKS = meta.tables['books']
# SQLAlchemy Query to GROUP BY and filter function
query = sqlalchemy.select([
BOOKS.c.genre,
sqlalchemy.func.count(BOOKS.c.genre)
]).group_by(BOOKS.c.genre).filter(BOOKS.c.book_price > 50.0)
# Fetch all the records
result = engine.execute(query).fetchall()
# View the records
for record in result:
print("\n", record)
Output:
The output of the Count and filter function
Similar Reads
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: Students TableSelecting specific column in SQLAlchemy based on filter:To select specific column in SQLAlchemySyntax: sq
2 min read
SQLAlchemy Core - Functions SQLAlchemy provides a rich set of functions that can be used in SQL expressions to perform various operations and calculations on the data. SQLAlchemy provides the Function API to work with the SQL functions in a more flexible manner. The Function API is used to construct SQL expressions representin
7 min read
SQLAlchemy Filter in List SQLAlchemy is a Python's powerful Object-Relational Mapper that, provides flexibility to work with Relational Databases. SQLAlchemy provides a rich set of functions that can be used in SQL expressions to perform various operations and calculations on the data using Python. In SQLAlchemy, we can filt
4 min read
Count-Min Sketch in Python Count-Min Sketch is a probabilistic data structure which approximates the frequency of items in a stream of data. It uses little memory while handling massive amounts of data and producing approximations of the answers. In this post, we'll explore the idea behind the Count-Min Sketch, how it's imple
2 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
SQLAlchemy - Aggregate Functions In this article, we will see how to select the count of rows using SQLAlchemy using Python. Before we begin, let us install the required dependencies using pip: pip install sqlalchemySince we are going to use MySQL in this post, we will also install a SQL connector for MySQL in Python. However, none
4 min read