Create a sample table for demonstration
Import necessary functions from the SQLAlchemy package. And Establish a 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 packagesimport sqlalchemyfrom sqlalchemy import create_engine, MetaData,Table, Column, Numeric, Integer, VARCHARfrom sqlalchemy.engine import result# establish connectionsengine = create_engine( "postgresql+psycopg2://postgres:Saibaba97%40@127.0.0.1:5432/test")# initialize the Metadata Objectmeta = MetaData(bind=engine)MetaData.reflect(meta)# create a table schemabooks = 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 tablestatement1 = 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 statementengine.execute(statement1)engine.execute(statement2)engine.execute(statement3)engine.execute(statement4)engine.execute(statement5) |
Output:
Sample table
How to use sum and order by in SQLAlchemy query?
In this article, we are going to see how to perform the sum and count function in SQLAlchemy against a PostgreSQL database in python.
SUM and count operations are performed in different methods using different functions. Such kinds of mathematical operations are database-dependent. In PostgreSQL, Group by is performed using a function called sum(), and count operation is performed using count(). 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.sum(). func.group_by(), func.sum()
Contact Us