Sqlalchemy 简明教程

SQLAlchemy Core - Using Functions

本章讨论了 SQLAlchemy 中使用的一些重要函数。

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

标准 SQL 推荐了许多大多数方言都实现的函数。它们根据传递给它的参数返回单个值。一些 SQL 函数将列作为参数，而另一些则是通用的。 Thefunc keyword in SQLAlchemy API is used to generate these functions 。

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.

在 SQL 中，now() 是一个通用函数。以下语句使用 func 渲染 now() 函数 −

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),)

另一方面，count() 函数返回从表中选择的行数，由 func 的以下用法呈现 −

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())

从上面的代码中，将获取 students 表中行数的计数。

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

使用包含以下数据的 Employee 表演示了一些内置 SQL 函数 −

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

max() 函数通过以下使用 SQLAlchemy 中的 func 来实现，它将产生 85，即获得的总最高分 −

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())

类似地，将返回 56，即最低分数的 min() 函数将通过以下代码渲染 −

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())

因此，AVG() 函数也可以通过使用以下代码实现 −

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())