SQL and Generic Functions¶
SQL functions which are known to SQLAlchemy with regards to database-specific
rendering, return types and argument behavior. Generic functions are invoked
like 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 or
unknown to SQLAlchemy, built-in or user defined. The section here only
describes those functions where SQLAlchemy already knows what argument and
return types are in use.
SQL function API, factories, and built-in functions.
-
class
sqlalchemy.sql.functions.
AnsiFunction
(**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 description of public methods.See also
func
- namespace which produces registered or ad-hocFunction
instances.GenericFunction
- allows creation of registered function types.
-
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 function types.-
__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 which is suitable for the FROM clause.
e.g.:
from sqlalchemy.sql import column stmt = select([column('data')]).select_from( func.unnest(Table.data).alias('data_view') )
Would produce:
SELECT data FROM unnest(sometable.data) AS data_view
New in version 0.9.8: The
FunctionElement.alias()
method is now supported. Previously, this method’s behavior was undefined and did not behave consistently across versions.
-
clauses
¶ Return the underlying
ClauseList
which contains the arguments for thisFunctionElement
.
-
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 with an anonymously named column.
An interim approach to providing named columns for a function as a FROM clause is to build a
select()
with the desired 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()
to produce a SELECT construct.Note that
FunctionElement
can be passed to theConnectable.execute()
method ofConnection
orEngine
.
-
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.
-
get_children
(**kwargs)¶
-
over
(partition_by=None, order_by=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.New in version 0.7.
-
packagenames
= ()¶
-
scalar
()¶ Execute this
FunctionElement
against an embedded ‘bind’ and return a scalar value.This first calls
select()
to produce a SELECT construct.Note that
FunctionElement
can be passed to theConnectable.scalar()
method ofConnection
orEngine
.
-
select
()¶ Produce a
select()
construct against thisFunctionElement
.This is shorthand for:
s = select([function_element])
-
-
class
sqlalchemy.sql.functions.
GenericFunction
(*args, **kwargs)¶ Bases:
sqlalchemy.sql.functions.Function
Define a ‘generic’ function.
A generic function is a pre-established
Function
class that is instantiated automatically when called by name from thefunc
attribute. Note that calling any name fromfunc
has the effect that a newFunction
instance is created automatically, given that name. The primary use case for defining aGenericFunction
class is so that a function of a particular name may be given a fixed return type. It can also include custom argument parsing schemes as well as additional methods.Subclasses of
GenericFunction
are automatically registered under the name of the class. For example, a user-defined functionas_utc()
would be 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 into packages by specifying the “package” attribute when defining
GenericFunction
. Third party libraries containing many functions may want to use this in order to avoid name conflicts with other systems. For example, if ouras_utc()
function were part of a package “time”:class as_utc(GenericFunction): type = DateTime package = "time"
The above function would be available from
func
using the package nametime
:print select([func.time.as_utc()])
A final option is to allow the function to be accessed from one name in
func
but to render as a different name. Theidentifier
attribute will override the name used to access the function as loaded fromfunc
, but will retain the usage ofname
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()
New in version 0.8:
GenericFunction
now supports automatic registration of new functions as well as package and custom naming support.Changed in version 0.8: The attribute name
type
is used to specify the function’s return type at the class level. Previously, the name__return_type__
was used. This name is still recognized for backwards-compatibility.-
coerce_arguments
= True¶
-
identifier
= 'GenericFunction'¶
-
name
= 'GenericFunction'¶
-
-
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.
char_length
(arg, **kwargs)¶ Bases:
sqlalchemy.sql.functions.GenericFunction
-
identifier
= 'char_length'¶
-
name
= 'char_length'¶
-
type
¶ alias of
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
String
-
-
class
sqlalchemy.sql.functions.
count
(expression=None, **kwargs)¶ Bases:
sqlalchemy.sql.functions.GenericFunction
The ANSI COUNT aggregate function. With no arguments, emits COUNT *.
-
identifier
= 'count'¶
-
name
= 'count'¶
-
type
¶ alias of
Integer
-
-
class
sqlalchemy.sql.functions.
current_date
(**kwargs)¶ Bases:
sqlalchemy.sql.functions.AnsiFunction
-
identifier
= 'current_date'¶
-
name
= 'current_date'¶
-
type
¶ alias of
Date
-
-
class
sqlalchemy.sql.functions.
current_time
(**kwargs)¶ Bases:
sqlalchemy.sql.functions.AnsiFunction
-
identifier
= 'current_time'¶
-
name
= 'current_time'¶
-
type
¶ alias of
Time
-
-
class
sqlalchemy.sql.functions.
current_timestamp
(**kwargs)¶ Bases:
sqlalchemy.sql.functions.AnsiFunction
-
identifier
= 'current_timestamp'¶
-
name
= 'current_timestamp'¶
-
type
¶ alias of
DateTime
-
-
class
sqlalchemy.sql.functions.
current_user
(**kwargs)¶ Bases:
sqlalchemy.sql.functions.AnsiFunction
-
identifier
= 'current_user'¶
-
name
= 'current_user'¶
-
type
¶ alias of
String
-
-
class
sqlalchemy.sql.functions.
localtime
(**kwargs)¶ Bases:
sqlalchemy.sql.functions.AnsiFunction
-
identifier
= 'localtime'¶
-
name
= 'localtime'¶
-
type
¶ alias of
DateTime
-
-
class
sqlalchemy.sql.functions.
localtimestamp
(**kwargs)¶ Bases:
sqlalchemy.sql.functions.AnsiFunction
-
identifier
= 'localtimestamp'¶
-
name
= 'localtimestamp'¶
-
type
¶ alias of
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.
next_value
(seq, **kw)¶ Bases:
sqlalchemy.sql.functions.GenericFunction
Represent the ‘next value’, given a
Sequence
as its single argument.Compiles into the appropriate function on each backend, or will raise NotImplementedError if used on a backend that 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
DateTime
-
-
class
sqlalchemy.sql.functions.
random
(*args, **kwargs)¶ Bases:
sqlalchemy.sql.functions.GenericFunction
-
identifier
= 'random'¶
-
name
= 'random'¶
-
-
sqlalchemy.sql.functions.
register_function
(identifier, fn, package='_default')¶ Associate a callable with a particular func. name.
This is normally called by _GenericMeta, but is also available by itself so that a non-Function construct can be associated with the
func
accessor (i.e. CAST, EXTRACT).
-
class
sqlalchemy.sql.functions.
session_user
(**kwargs)¶ Bases:
sqlalchemy.sql.functions.AnsiFunction
-
identifier
= 'session_user'¶
-
name
= 'session_user'¶
-
type
¶ alias of
String
-
-
class
sqlalchemy.sql.functions.
sum
(*args, **kwargs)¶ Bases:
sqlalchemy.sql.functions.ReturnTypeFromArgs
-
identifier
= 'sum'¶
-
name
= 'sum'¶
-
-
class
sqlalchemy.sql.functions.
sysdate
(**kwargs)¶ Bases:
sqlalchemy.sql.functions.AnsiFunction
-
identifier
= 'sysdate'¶
-
name
= 'sysdate'¶
-
type
¶ alias of
DateTime
-
-
class
sqlalchemy.sql.functions.
user
(**kwargs)¶ Bases:
sqlalchemy.sql.functions.AnsiFunction
-
identifier
= 'user'¶
-
name
= 'user'¶
-
type
¶ alias of
String
-