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 table
Python SQLAlchemy – func.count with filter
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()