In this article, we will see how to perform floor division in SQLAlchemy against a PostgreSQL database in python.
Floor division is performed in different methods using different functions. Such kinds of mathematical operations are database-dependent. In PostgreSQL, floor division is performed using a function called div(). 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.cube(), 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 packagesfrom sqlalchemy import (create_engine, MetaData,Table, Column, Numeric, Integer)# establish connectionsengine = create_engine(# initialize meta datameta = MetaData()# create a table schemabooks = Table( 'books', meta, Column('bookId', Integer, primary_key=True), Column('book_price', Numeric),)meta.create_all(engine)# insert records into the tablestatement1 = books.insert().values(bookId=1, book_price=12.2)statement2 = books.insert().values(bookId=2, book_price=13.2)statement3 = books.insert().values(bookId=3, book_price=121.6)statement4 = books.insert().values(bookId=4, book_price=100)statement5 = books.insert().values(bookId=5, book_price=1112.2)# execute the insert records statementengine.execute(statement1)engine.execute(statement2)engine.execute(statement3)engine.execute(statement4)engine.execute(statement5) |
Output:
Implementing floor division in SQLAlchemy
Now import func.div() function from sqlalchemy and perform floor division as shown below
Python3
# import necessary packagesfrom sqlalchemy import func# pass the sql query statement to # the execute functionresult = engine.execute('SELECT div(book_price,3)\AS minimum FROM books')# use fetchall() to return all resultresult.fetchall() |
Output:
