SQLAlchemy Core - Using Functions

Some of the important functions used in SQLAlchemy are discussed in this chapter.

Standard SQL has recommended many functions which are implemented by most dialects. They return a single value based on the arguments passed to it. Some SQL functions take columns as arguments whereas some are generic. Thefunc keyword in SQLAlchemy API is used to generate these functions.

In SQL, now() is a generic function. Following statements renders the now() function using func −

from sqlalchemy.sql import func
result = conn.execute(select([func.now()]))
print (result.fetchone())

Sample result of above code may be as shown below −

(datetime.datetime(2018, 6, 16, 6, 4, 40),)

On the other hand, count() function which returns number of rows selected from a table, is rendered by following usage of func −

from sqlalchemy.sql import func
result = conn.execute(select([func.count(students.c.id)]))
print (result.fetchone())

From the above code, count of number of rows in students table will be fetched.

Some built-in SQL functions are demonstrated using Employee table with following data −

The max() function is implemented by following usage of func from SQLAlchemy which will result in 85, the total maximum marks obtained −

from sqlalchemy.sql import func
result = conn.execute(select([func.max(employee.c.marks)]))
print (result.fetchone())

Similarly, min() function that will return 56, minimum marks, will be rendered by following code −

from sqlalchemy.sql import func
result = conn.execute(select([func.min(employee.c.marks)]))
print (result.fetchone())

So, the AVG() function can also be implemented by using the below code −

from sqlalchemy.sql import func
result = conn.execute(select([func.avg(employee.c.marks)]))
print (result.fetchone())

Functions are normally used in the columns clause of a select statement. 
They can also be given label as well as a type. A label to function allows the result 
to be targeted in a result row based on a string name, and a type is required when 
you need result-set processing to occur.from sqlalchemy.sql import func

result = conn.execute(select([func.max(students.c.lastname).label('Name')]))

print (result.fetchone())