SQLAlchemy Core – Other Functions
Python sqlalchemy func.group_concat(column, separator)
This function is used to concatenate string from multiple rows into a single string using various clauses. When we using the group_by clause a single row may contains multiple values. If your are not using the group_concat() function we can see only one value in result instead of getting the all the values. So we can group_concat() to get the all string values in group. In the following example we are grouping the students based on their grades and retrieving the results by group_concat() method.
Python3
query = select( studentTable.c.grade, func.group_concat(studentTable.c.name, ',').label('names')).group_by(studentTable.c.grade)with engine.connect() as connect: result=connect.execute(query).fetchall() for data in result: print(data[0],data[1]) |
Output
Python sqlalchemy case()
case() function is used to construct a SQL CASE expression within your queries. The CASE expression allows you to conditionally evaluate and return different values based on specified conditions.
Syntax:
case(
(condition1,vlaue1),
(condition2,value2),
........
else_=default_value
)
In the following example we are assigning grade points to student based on score
Python3
query = select( studentTable.c.name,studentTable.c.score, case( (and_(studentTable.c.score>=91,studentTable.c.score<=100),10), (and_(studentTable.c.score>=81, studentTable.c.score<=90) , 9), (and_(studentTable.c.score>=71, studentTable.c.score<=80) , 8), (and_(studentTable.c.score>=61, studentTable.c.score<=70) , 7), (and_(studentTable.c.score>=51, studentTable.c.score<=60) , 6), (and_(studentTable.c.score>=41, studentTable.c.score<=50) , 5), else_=0 ).label("Grade Points"))with engine.connect() as connect: result=connect.execute(query).fetchall() for data in result: print(*data) |
Output
Case 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