Open In App

Floor division in SQLAlchemy

Improve
Improve
Like Article
Like
Save
Share
Report

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 packages
from sqlalchemy import (create_engine, MetaData,
Table, Column, Numeric, Integer)
 
# establish connections
engine = create_engine(
 
# initialize meta data
meta = MetaData()
 
# create a table schema
books = Table(
    'books', meta,
    Column('bookId', Integer, primary_key=True),
    Column('book_price', Numeric),
)
 
meta.create_all(engine)
 
# insert records into the table
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)
 
# execute the insert records statement
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




# import necessary packages
from sqlalchemy import func
 
# pass the sql query statement to
# the execute function
result = engine.execute('SELECT div(book_price,3)\
AS minimum FROM books')
 
# use fetchall() to return all result
result.fetchall()


Output:



Last Updated : 29 Jan, 2022
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads