- SQLAlchemy Tutorial
- SQLAlchemy - Home
- SQLAlchemy - Introduction
- SQLAlchemy Core
- Expression Language
- Connecting to Database
- Creating Table
- SQL Expressions
- Executing Expression
- Selecting Rows
- Using Textual SQL
- Using Aliases
- Using UPDATE Expression
- Using DELETE Expression
- Using Multiple Tables
- Using Multiple Table Updates
- Parameter-Ordered Updates
- Multiple Table Deletes
- Using Joins
- Using Conjunctions
- Using Functions
- Using Set Operations
- SQLAlchemy ORM
- Declaring Mapping
- Creating Session
- Adding Objects
- Using Query
- Updating Objects
- Applying Filter
- Filter Operators
- Returning List and Scalars
- Textual SQL
- Building Relationship
- Working with Related Objects
- Working with Joins
- Common Relationship Operators
- Eager Loading
- Deleting Related Objects
- Many to Many Relationships
- Dialects
- SQLAlchemy Useful Resources
- SQLAlchemy - Quick Guide
- SQLAlchemy - Useful Resources
- SQLAlchemy - Discussion
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 −
ID | Name | Marks |
---|---|---|
1 | Kamal | 56 |
2 | Fernandez | 85 |
3 | Sunil | 62 |
4 | Bhaskar | 76 |
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())