SQLAlchemy Mathematical Functions
Python sqlalchemy func.avg(column)
This function Calculates the average value of all rows in a column. The below code connects to a MySQL database using SQLAlchemy, calculates the average score of students in a table named “student”, and prints the result. Replace the database credentials and ensure the table and column names match your database schema.
Python3
from sqlalchemy import create_engine, MetaData,select,funcengine = create_engine("mysql+pymysql://userName:password@host:port/dbName")metadata = MetaData()metadata.reflect(bind=engine)studentTable=metadata.tables['student']query=select(func.avg(studentTable.c.score).label("Average"))with engine.connect() as connect: result=connect.execute(query).fetchall() print("Average of Student's score is::",result[0][0]) |
Output: In the example we are calculating the average value of scores students.
Avg output
Python sqlalchemy func.count(column)
Counts the NON-NULL values in a column. This code connects to a MySQL database using SQLAlchemy, counts the number of students in a table named “student”, and prints the result. Make sure to replace the database credentials and ensure the table and column names match your database schema.
Python3
query=select(func.count(studentTable.c.name).label("Count of Students"))with engine.connect() as connect: result=connect.execute(query).fetchall() print("Total count of students in class::",result[0][0]) |
Output:
Count Output
Python sqlalchemy func.sum(column)
Calculates the sum of values in a column. This code connects to a MySQL database using SQLAlchemy, calculates the sum of scores from a table named “student”, and prints the result. Make sure to replace the database credentials and ensure the table and column names match your database schema.
Python3
query=select(func.sum(studentTable.c.score).label("Sum of scores"))with engine.connect() as connect: result=connect.execute(query).fetchall() print("Sum of scores of students::",result[0][0]) |
Output
Sum Output
Python sqlalchemy func.max(column_name)
Finds the max value in the given column.
This code connects to a MySQL database using SQLAlchemy, calculates the maximum score from a table named “student”, and prints the result. Make sure to replace the database credentials and ensure the table and column names match your database schema.
Python3
query=select(func.max(studentTable.c.score).label("Max Score"))with engine.connect() as connect: result=connect.execute(query).fetchall() print("Maximum Score in Student scores::",result[0][0]) |
Output
Max Output
Python sqlalchemy max.group_by
In this example we are grouping the students by Grade and then finding the max score in corresponding group
Python3
query=select(studentTable.c.grade, func.max(studentTable.c.score). label("maxscore")).group_by(studentTable.c.grade)with engine.connect() as connect: result=connect.execute(query).fetchall() print("grade || max score") for data in result: print(data[0],"||",data[1]) |
Output:
Max with Group By output
Python sqlalchemy func.min(column_name)
Used to find the minimum value in column. In the following example we are calculating the minimum value in each grade and count of students in each grade.
Python3
query=select(studentTable.c.grade,func.min(studentTable.c.score),func.count(studentTable.c.score))with engine.connect() as connect: result=connect.execute(query).fetchall() print("grade | min value | count") for data in result: print(data[0],data[1],data[2]) |
Output:
Min output
Python sqlalchemy func.floor(Value)
The floor() function rounds down a numeric value to the nearest integer that is less than or equal to the original value. This code connects to a database using SQLAlchemy, calculates the floor value of 3.6 using the func.floor function, and prints the result
Python3
query=select(func.floor(3.6))with engine.connect() as connect: result=connect.execute(query).fetchall() print("Floor value of 3.6 is",result[0][0]) |
Output
Floor value of 3.6 is 3.0
Python sqlalchemy floor with avg
This code uses SQLAlchemy to connect to a database, calculates the average of the score column in the studentTable, and then rounds the result to the nearest integer using the func.floor function. Finally, it prints the result, which is the average score of students rounded to the nearest integer.
Python3
query=select(func.floor(func.avg(studentTable.c.score)).label("Average"))with engine.connect() as connect: result=connect.execute(query).fetchall() print("Average of Student's score rounded to nearest integer::",result[0][0]) |
Output
Python sqlalchemy func.abs(Value):
abs() function to calculate the absolute value of a numeric expression. The func.abs() function takes a single argument, which can be a numeric value and returns its absolute value.
Python3
query=select(func.abs(10),literal(10),func.abs(-20),literal(-20),func.abs(30+43-100),literal(30+43-100))with engine.connect() as connect: result=connect.execute(query).fetchall()print("Abs values of abs value and actual value::",*result[0]) |
Output
Abs output
SQLAlchemy Core – Functions
SQLAlchemy provides a rich set of functions that can be used in SQL expressions to perform various operations and calculations on the data. SQLAlchemy provides the Function API to work with the SQL functions in a more flexible manner. The Function API is used to construct SQL expressions representing function calls and can be applied to columns. SQL functions are invoked by using the func namespace.
Prerequisites
Contact Us