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 sqlalchemy
from sqlalchemy import create_engine, MetaData, Table,
Column, Numeric, Integer, VARCHAR
from sqlalchemy.engine import result
engine = create_engine(
meta = MetaData(bind = engine)
MetaData.reflect(meta)
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)
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' )
engine.execute(statement1)
engine.execute(statement2)
engine.execute(statement3)
engine.execute(statement4)
engine.execute(statement5)
|
Output:
Sample table
Implementing 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
BOOKS = meta.tables[ 'books' ]
query = sqlalchemy.select([
BOOKS.c.genre,
sqlalchemy.func.count(BOOKS.c.genre)
]).group_by(BOOKS.c.genre). filter (BOOKS.c.book_price > 50.0 )
result = engine.execute(query).fetchall()
for record in result:
print ( "\n" , record)
|
Output:
The output of the Count and filter function
Like Article
Suggest improvement
Share your thoughts in the comments
Please Login to comment...