SQL and Generic Functions

SQL functions which are known to SQLAlchemy with regards to database-specificrendering, return types and argument behavior. Generic functions are invokedlike all SQL functions, using the func attribute:

select([func.count()]).select_from(sometable)

Note that any name not known to func generates the function name as is- there is no restriction on what SQL functions can be called, known orunknown to SQLAlchemy, built-in or user defined. The section here onlydescribes those functions where SQLAlchemy already knows what argument andreturn types are in use.

SQL function API, factories, and built-in functions.

• class sqlalchemy.sql.functions.AnsiFunction(*args, **kwargs)

• Bases: sqlalchemy.sql.functions.GenericFunction

  • identifier = 'AnsiFunction'
  • name = 'AnsiFunction'
• class sqlalchemy.sql.functions.Function(name, *clauses, **kw)
• Bases: sqlalchemy.sql.functions.FunctionElement

Describe a named SQL function.

See the superclass FunctionElement for a descriptionof public methods.

See also

func - namespace which produces registered or ad-hocFunction instances.

GenericFunction - allows creation of registered functiontypes.

• init(name, *clauses, **kw)
• Construct a Function.

The func construct is normally used to constructnew Function instances.

• class sqlalchemy.sql.functions.FunctionAsBinary(fn, left_index, right_index)

• Bases: sqlalchemy.sql.expression.BinaryExpression

  • property left
  • property right
• class sqlalchemy.sql.functions.FunctionElement(*clauses, **kwargs)
• Bases: sqlalchemy.sql.expression.Executable, sqlalchemy.sql.expression.ColumnElement, sqlalchemy.sql.expression.FromClause

Base for SQL function-oriented constructs.

See also

Function - named SQL function.

func - namespace which produces registered or ad-hocFunction instances.

GenericFunction - allows creation of registered functiontypes.

• init(*clauses, **kwargs)

• Construct a FunctionElement.

• alias(name=None, flat=False)

• Produce a Alias construct against thisFunctionElement.

This construct wraps the function in a named alias whichis suitable for the FROM clause, in the style accepted for exampleby PostgreSQL.

e.g.:

from sqlalchemy.sql import column
 
stmt = select([column('data_view')]).\
    select_from(SomeTable).\
    select_from(func.unnest(SomeTable.data).alias('data_view')
)

Would produce:

SELECT data_view
FROM sometable, unnest(sometable.data) AS data_view

New in version 0.9.8: The FunctionElement.alias() methodis now supported. Previously, this method’s behavior wasundefined and did not behave consistently across versions.

• ascomparison(_left_index, right_index)
• Interpret this expression as a boolean comparison between two values.

A hypothetical SQL function “is_equal()” which compares to valuesfor equality would be written in the Core expression language as:

expr = func.is_equal("a", "b")

If “is_equal()” above is comparing “a” and “b” for equality, theFunctionElement.as_comparison() method would be invoked as:

expr = func.is_equal("a", "b").as_comparison(1, 2)

Where above, the integer value “1” refers to the first argument of the“is_equal()” function and the integer value “2” refers to the second.

This would create a BinaryExpression that is equivalent to:

BinaryExpression("a", "b", operator=op.eq)

However, at the SQL level it would still render as“is_equal(‘a’, ‘b’)”.

The ORM, when it loads a related object or collection, needs to be ableto manipulate the “left” and “right” sides of the ON clause of a JOINexpression. The purpose of this method is to provide a SQL functionconstruct that can also supply this information to the ORM, when usedwith the relationship.primaryjoin parameter. The returnvalue is a containment object called FunctionAsBinary.

An ORM example is as follows:

class Venue(Base):
    __tablename__ = 'venue'
    id = Column(Integer, primary_key=True)
    name = Column(String)
 
    descendants = relationship(
        "Venue",
        primaryjoin=func.instr(
            remote(foreign(name)), name + "/"
        ).as_comparison(1, 2) == 1,
        viewonly=True,
        order_by=name
    )

Above, the “Venue” class can load descendant “Venue” objects bydetermining if the name of the parent Venue is contained within thestart of the hypothetical descendant value’s name, e.g. “parent1” wouldmatch up to “parent1/child1”, but not to “parent2/child1”.

Possible use cases include the “materialized path” example given above,as well as making use of special SQL functions such as geometricfunctions to create join conditions.

- Parameters
- 
  - 

left_index – the integer 1-based index of the function argumentthat serves as the “left” side of the expression.

  - 

right_index – the integer 1-based index of the function argumentthat serves as the “right” side of the expression.

New in version 1.3.

• clauses

• Return the underlying ClauseList which containsthe arguments for this FunctionElement.

• property columns

• The set of columns exported by this FunctionElement.

Function objects currently have no result column names built in;this method returns a single-element column collection withan anonymously named column.

An interim approach to providing named columns for a functionas a FROM clause is to build a select() with thedesired columns:

from sqlalchemy.sql import column
 
stmt = select([column('x'), column('y')]).                select_from(func.myfunction())

• execute()
• Execute this FunctionElement against an embedded‘bind’.

This first calls select() toproduce a SELECT construct.

Note that FunctionElement can be passed tothe Connectable.execute() method of Connectionor Engine.

• filter(*criterion)
• Produce a FILTER clause against this function.

Used against aggregate and window functions,for database backends that support the “FILTER” clause.

The expression:

func.count(1).filter(True)

is shorthand for:

from sqlalchemy import funcfilter
funcfilter(func.count(1), True)

New in version 1.0.0.

See also

FunctionFilter

funcfilter()

• getchildren(**kwargs_)
• Return immediate child elements of this ClauseElement.

This is used for visit traversal.

**kwargs may contain flags that change the collection that isreturned, for example to return a subset of items in order tocut down on larger traversals, or to return child items from adifferent context (such as schema-level collections instead ofclause-level).

• over(partition_by=None, order_by=None, rows=None, range=None_)
• Produce an OVER clause against this function.

Used against aggregate or so-called “window” functions,for database backends that support window functions.

The expression:

func.row_number().over(order_by='x')

is shorthand for:

from sqlalchemy import over
over(func.row_number(), order_by='x')

See over() for a full description.

• packagenames = ()
• scalar()
• Execute this FunctionElement against an embedded‘bind’ and return a scalar value.

This first calls select() toproduce a SELECT construct.

Note that FunctionElement can be passed tothe Connectable.scalar() method of Connectionor Engine.

• select()
• Produce a select() constructagainst this FunctionElement.

This is shorthand for:

s = select([function_element])

• selfgroup(_against=None)
• Apply a ‘grouping’ to this ClauseElement.

This method is overridden by subclasses to return a“grouping” construct, i.e. parenthesis. In particularit’s used by “binary” expressions to provide a groupingaround themselves when placed into a larger expression,as well as by select() constructs when placed intothe FROM clause of another select(). (Note thatsubqueries should be normally created using theSelect.alias() method, as many platforms requirenested SELECT statements to be named).

As expressions are composed together, the application ofself_group() is automatic - end-user code should neverneed to use this method directly. Note that SQLAlchemy’sclause constructs take operator precedence into account -so parenthesis might not be needed, for example, inan expression like x OR (y AND z) - AND takes precedenceover OR.

The base self_group() method of ClauseElementjust returns self.

• withingroup(*orderby)
• Produce a WITHIN GROUP (ORDER BY expr) clause against this function.

Used against so-called “ordered set aggregate” and “hypotheticalset aggregate” functions, including percentile_cont,rank, dense_rank, etc.

See within_group() for a full description.

New in version 1.1.

• withingroup_type(_within_group)
• For types that define their return type as based on the criteriawithin a WITHIN GROUP (ORDER BY) expression, called by theWithinGroup construct.

Returns None by default, in which case the function’s normal .typeis used.

• class sqlalchemy.sql.functions.GenericFunction(*args, **kwargs)
• Bases: sqlalchemy.sql.functions.Function

Define a ‘generic’ function.

A generic function is a pre-established Functionclass that is instantiated automatically when calledby name from the func attribute. Note thatcalling any name from func has the effect thata new Function instance is created automatically,given that name. The primary use case for defininga GenericFunction class is so that a functionof a particular name may be given a fixed return type.It can also include custom argument parsing schemes as wellas additional methods.

Subclasses of GenericFunction are automaticallyregistered under the name of the class. Forexample, a user-defined function as_utc() wouldbe available immediately:

from sqlalchemy.sql.functions import GenericFunction
from sqlalchemy.types import DateTime
 
class as_utc(GenericFunction):
    type = DateTime
 
print select([func.as_utc()])

User-defined generic functions can be organized intopackages by specifying the “package” attribute when definingGenericFunction. Third party librariescontaining many functions may want to use this in orderto avoid name conflicts with other systems. For example,if our as_utc() function were part of a package“time”:

class as_utc(GenericFunction):
    type = DateTime
    package = "time"

The above function would be available from funcusing the package name time:

print select([func.time.as_utc()])

A final option is to allow the function to be accessedfrom one name in func but to render as a different name.The identifier attribute will override the name used toaccess the function as loaded from func, but will retainthe usage of name as the rendered name:

class GeoBuffer(GenericFunction):
    type = Geometry
    package = "geo"
    name = "ST_Buffer"
    identifier = "buffer"

The above function will render as follows:

>>> print func.geo.buffer()
ST_Buffer()

• coercearguments = True_
• identifier = 'GenericFunction'
• name = 'GenericFunction'
• • class sqlalchemy.sql.functions.OrderedSetAgg(*args, **kwargs)
  • Bases: sqlalchemy.sql.functions.GenericFunction

Define a function where the return type is based on the sortexpression type as defined by the expression passed to theFunctionElement.within_group() method.

• arrayfor_multi_clause = False_
• identifier = 'OrderedSetAgg'
• name = 'OrderedSetAgg'
• withingroup_type(_within_group)
• For types that define their return type as based on the criteriawithin a WITHIN GROUP (ORDER BY) expression, called by theWithinGroup construct.

Returns None by default, in which case the function’s normal .typeis used.

• class sqlalchemy.sql.functions.ReturnTypeFromArgs(*args, **kwargs)
• Bases: sqlalchemy.sql.functions.GenericFunction

Define a function whose return type is the same as its arguments.

• identifier = 'ReturnTypeFromArgs'
• name = 'ReturnTypeFromArgs'
• • class sqlalchemy.sql.functions.arrayagg(args, *kwargs_)
  • Bases: sqlalchemy.sql.functions.GenericFunction

support for the ARRAY_AGG function.

The func.array_agg(expr) construct returns an expression oftype types.ARRAY.

e.g.:

stmt = select([func.array_agg(table.c.values)[2:5]])

New in version 1.1.

See also

postgresql.array_agg() - PostgreSQL-specific version thatreturns postgresql.ARRAY, which has PG-specific operatorsadded.

• identifier = 'array_agg'
• name = 'array_agg'
• type
• alias of sqlalchemy.sql.sqltypes.ARRAY

• class sqlalchemy.sql.functions.charlength(_arg, **kwargs)

• Bases: sqlalchemy.sql.functions.GenericFunction

  • identifier = 'char_length'
  • name = 'char_length'
  • type
  • alias of sqlalchemy.sql.sqltypes.Integer

• class sqlalchemy.sql.functions.coalesce(*args, **kwargs)

• Bases: sqlalchemy.sql.functions.ReturnTypeFromArgs

  • identifier = 'coalesce'
  • name = 'coalesce'
• class sqlalchemy.sql.functions.concat(*args, **kwargs)

• Bases: sqlalchemy.sql.functions.GenericFunction

  • identifier = 'concat'
  • name = 'concat'
  • type
  • alias of sqlalchemy.sql.sqltypes.String

• class sqlalchemy.sql.functions.count(expression=None, **kwargs)

• Bases: sqlalchemy.sql.functions.GenericFunction

The ANSI COUNT aggregate function. With no arguments,emits COUNT *.

E.g.:

from sqlalchemy import func
from sqlalchemy import select
from sqlalchemy import table, column
 
my_table = table('some_table', column('id'))
 
stmt = select([func.count()]).select_from(my_table)

Executing stmt would emit:

SELECT count(*) AS count_1
FROM some_table

• identifier = 'count'
• name = 'count'
• type
• alias of sqlalchemy.sql.sqltypes.Integer

• class sqlalchemy.sql.functions.cube(*args, **kwargs)
• Bases: sqlalchemy.sql.functions.GenericFunction

Implement the CUBE grouping operation.

This function is used as part of the GROUP BY of a statement,e.g. Select.group_by():

stmt = select(
    [func.sum(table.c.value), table.c.col_1, table.c.col_2]
    ).group_by(func.cube(table.c.col_1, table.c.col_2))

New in version 1.2.

• identifier = 'cube'
• name = 'cube'
• • class sqlalchemy.sql.functions.cumedist(args, *kwargs_)
  • Bases: sqlalchemy.sql.functions.GenericFunction

Implement the cume_dist hypothetical-set aggregate function.

This function must be used with the FunctionElement.within_group()modifier to supply a sort expression to operate upon.

The return type of this function is Numeric.

New in version 1.1.

• identifier = 'cume_dist'
• name = 'cume_dist'
• type = Numeric()
• • class sqlalchemy.sql.functions.currentdate(args, *kwargs_)
  • Bases: sqlalchemy.sql.functions.AnsiFunction

• identifier = 'current_date'

• name = 'current_date'
• type
• alias of sqlalchemy.sql.sqltypes.Date

• class sqlalchemy.sql.functions.currenttime(args, *kwargs_)

• Bases: sqlalchemy.sql.functions.AnsiFunction

  • identifier = 'current_time'
  • name = 'current_time'
  • type
  • alias of sqlalchemy.sql.sqltypes.Time

• class sqlalchemy.sql.functions.currenttimestamp(args, *kwargs_)

• Bases: sqlalchemy.sql.functions.AnsiFunction

  • identifier = 'current_timestamp'
  • name = 'current_timestamp'
  • type
  • alias of sqlalchemy.sql.sqltypes.DateTime

• class sqlalchemy.sql.functions.currentuser(args, *kwargs_)

• Bases: sqlalchemy.sql.functions.AnsiFunction

  • identifier = 'current_user'
  • name = 'current_user'
  • type
  • alias of sqlalchemy.sql.sqltypes.String

• class sqlalchemy.sql.functions.denserank(args, *kwargs_)

• Bases: sqlalchemy.sql.functions.GenericFunction

Implement the dense_rank hypothetical-set aggregate function.

This function must be used with the FunctionElement.within_group()modifier to supply a sort expression to operate upon.

The return type of this function is Integer.

New in version 1.1.

• identifier = 'dense_rank'
• name = 'dense_rank'
• type = Integer()
• • class sqlalchemy.sql.functions.groupingsets(args, *kwargs_)
  • Bases: sqlalchemy.sql.functions.GenericFunction

Implement the GROUPING SETS grouping operation.

This function is used as part of the GROUP BY of a statement,e.g. Select.group_by():

stmt = select(
    [func.sum(table.c.value), table.c.col_1, table.c.col_2]
).group_by(func.grouping_sets(table.c.col_1, table.c.col_2))

In order to group by multiple sets, use the tuple_() construct:

from sqlalchemy import tuple_
 
stmt = select(
    [
        func.sum(table.c.value),
        table.c.col_1, table.c.col_2,
        table.c.col_3]
).group_by(
    func.grouping_sets(
        tuple_(table.c.col_1, table.c.col_2),
        tuple_(table.c.value, table.c.col_3),
    )
)

New in version 1.2.

• identifier = 'grouping_sets'
• name = 'grouping_sets'
• • class sqlalchemy.sql.functions.localtime(*args, **kwargs)
  • Bases: sqlalchemy.sql.functions.AnsiFunction

• identifier = 'localtime'

• name = 'localtime'
• type
• alias of sqlalchemy.sql.sqltypes.DateTime

• class sqlalchemy.sql.functions.localtimestamp(*args, **kwargs)

• Bases: sqlalchemy.sql.functions.AnsiFunction

  • identifier = 'localtimestamp'
  • name = 'localtimestamp'
  • type
  • alias of sqlalchemy.sql.sqltypes.DateTime

• class sqlalchemy.sql.functions.max(*args, **kwargs)

• Bases: sqlalchemy.sql.functions.ReturnTypeFromArgs

  • identifier = 'max'
  • name = 'max'
• class sqlalchemy.sql.functions.min(*args, **kwargs)

• Bases: sqlalchemy.sql.functions.ReturnTypeFromArgs

  • identifier = 'min'
  • name = 'min'
• class sqlalchemy.sql.functions.mode(*args, **kwargs)
• Bases: sqlalchemy.sql.functions.OrderedSetAgg

implement the mode ordered-set aggregate function.

This function must be used with the FunctionElement.within_group()modifier to supply a sort expression to operate upon.

The return type of this function is the same as the sort expression.

New in version 1.1.

• identifier = 'mode'
• name = 'mode'
• • class sqlalchemy.sql.functions.nextvalue(_seq, **kw)
  • Bases: sqlalchemy.sql.functions.GenericFunction

Represent the ‘next value’, given a Sequenceas its single argument.

Compiles into the appropriate function on each backend,or will raise NotImplementedError if used on a backendthat does not provide support for sequences.

• identifier = 'next_value'
• name = 'next_value'
• type = Integer()
• • class sqlalchemy.sql.functions.now(*args, **kwargs)
  • Bases: sqlalchemy.sql.functions.GenericFunction

• identifier = 'now'

• name = 'now'
• type
• alias of sqlalchemy.sql.sqltypes.DateTime

• class sqlalchemy.sql.functions.percentrank(args, *kwargs_)
• Bases: sqlalchemy.sql.functions.GenericFunction

Implement the percent_rank hypothetical-set aggregate function.

This function must be used with the FunctionElement.within_group()modifier to supply a sort expression to operate upon.

The return type of this function is Numeric.

New in version 1.1.

• identifier = 'percent_rank'
• name = 'percent_rank'
• type = Numeric()
• • class sqlalchemy.sql.functions.percentilecont(args, *kwargs_)
  • Bases: sqlalchemy.sql.functions.OrderedSetAgg

implement the percentile_cont ordered-set aggregate function.

This function must be used with the FunctionElement.within_group()modifier to supply a sort expression to operate upon.

The return type of this function is the same as the sort expression,or if the arguments are an array, an types.ARRAY of the sortexpression’s type.

New in version 1.1.

• arrayfor_multi_clause = True_
• identifier = 'percentile_cont'
• name = 'percentile_cont'
• • class sqlalchemy.sql.functions.percentiledisc(args, *kwargs_)
  • Bases: sqlalchemy.sql.functions.OrderedSetAgg

implement the percentile_disc ordered-set aggregate function.

This function must be used with the FunctionElement.within_group()modifier to supply a sort expression to operate upon.

The return type of this function is the same as the sort expression,or if the arguments are an array, an types.ARRAY of the sortexpression’s type.

New in version 1.1.

• arrayfor_multi_clause = True_
• identifier = 'percentile_disc'
• name = 'percentile_disc'
• • class sqlalchemy.sql.functions.random(*args, **kwargs)
  • Bases: sqlalchemy.sql.functions.GenericFunction

• identifier = 'random'

• name = 'random'
• • class sqlalchemy.sql.functions.rank(*args, **kwargs)
  • Bases: sqlalchemy.sql.functions.GenericFunction

Implement the rank hypothetical-set aggregate function.

This function must be used with the FunctionElement.within_group()modifier to supply a sort expression to operate upon.

The return type of this function is Integer.

New in version 1.1.

• identifier = 'rank'
• name = 'rank'
• type = Integer()
• • sqlalchemy.sql.functions.registerfunction(_identifier, fn, package='_default')
  • Associate a callable with a particular func. name.

This is normally called by _GenericMeta, but is alsoavailable by itself so that a non-Function constructcan be associated with the func accessor (i.e.CAST, EXTRACT).

• class sqlalchemy.sql.functions.rollup(*args, **kwargs)
• Bases: sqlalchemy.sql.functions.GenericFunction

Implement the ROLLUP grouping operation.

This function is used as part of the GROUP BY of a statement,e.g. Select.group_by():

stmt = select(
    [func.sum(table.c.value), table.c.col_1, table.c.col_2]
).group_by(func.rollup(table.c.col_1, table.c.col_2))

New in version 1.2.

• identifier = 'rollup'
• name = 'rollup'
• • class sqlalchemy.sql.functions.sessionuser(args, *kwargs_)
  • Bases: sqlalchemy.sql.functions.AnsiFunction

• identifier = 'session_user'

• name = 'session_user'
• type
• alias of sqlalchemy.sql.sqltypes.String

• class sqlalchemy.sql.functions.sum(*args, **kwargs)

• Bases: sqlalchemy.sql.functions.ReturnTypeFromArgs

  • identifier = 'sum'
  • name = 'sum'
• class sqlalchemy.sql.functions.sysdate(*args, **kwargs)

• Bases: sqlalchemy.sql.functions.AnsiFunction

  • identifier = 'sysdate'
  • name = 'sysdate'
  • type
  • alias of sqlalchemy.sql.sqltypes.DateTime

• class sqlalchemy.sql.functions.user(*args, **kwargs)

• Bases: sqlalchemy.sql.functions.AnsiFunction

  • identifier = 'user'
  • name = 'user'
  • type
  • alias of sqlalchemy.sql.sqltypes.String