Floor division in SQLAlchemy
Last Updated :
29 Jan, 2022
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
from sqlalchemy import (create_engine, MetaData,
Table, Column, Numeric, Integer)
engine = create_engine(
meta = MetaData()
books = Table(
'books' , meta,
Column( 'bookId' , Integer, primary_key = True ),
Column( 'book_price' , Numeric),
)
meta.create_all(engine)
statement1 = 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 )
engine.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
from sqlalchemy import func
result = engine.execute('SELECT div(book_price, 3 )\
AS minimum FROM books')
result.fetchall()
|
Output:
Share your thoughts in the comments
Please Login to comment...