sqlalchemy.sql.functions._FunctionGenerator

Generate SQL function expressions.

.func is a special object instance which generates SQL functions based on name-based attributes, e.g.:

>>> print(func.count(1))
count(:param_1)

The returned object is an instance of .Function, and is a column-oriented SQL element like any other, and is used in that way:

>>> print(select(func.count(table.c.id)))
SELECT count(sometable.id) FROM sometable

Any name can be given to .func. If the function name is unknown to SQLAlchemy, it will be rendered exactly as is. For common SQL functions which SQLAlchemy is aware of, the name may be interpreted as a generic function which will be compiled appropriately to the target database:

>>> print(func.current_timestamp())
CURRENT_TIMESTAMP

To call functions which are present in dot-separated packages, specify them in the same manner:

>>> print(func.stats.yield_curve(5, 10))
stats.yield_curve(:yield_curve_1, :yield_curve_2)

SQLAlchemy can be made aware of the return type of functions to enable type-specific lexical and result-based behavior. For example, to ensure that a string-based function returns a Unicode value and is similarly treated as a string in expressions, specify ~sqlalchemy.types.Unicode as the type:

>>> print(func.my_string(u'hi', type_=Unicode) + ' ' +
...       func.my_string(u'there', type_=Unicode))
my_string(:my_string_1) || :my_string_2 || my_string(:my_string_3)

The object returned by a .func call is usually an instance of .Function. This object meets the "column" interface, including comparison and labeling functions. The object can also be passed the ~.Connectable.execute method of a _engine.Connection or _engine.Engine, where it will be wrapped inside of a SELECT statement first:

print(connection.execute(func.current_timestamp()).scalar())

In a few exception cases, the .func accessor will redirect a name to a built-in expression such as .cast or .extract, as these names have well-known meaning but are not exactly the same as "functions" from a SQLAlchemy perspective.

Functions which are interpreted as "generic" functions know how to calculate their return type automatically. For a listing of known generic functions, see :ref:`generic_functions`.