SQLAlchemy 1.1 Documentation
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.더 보기
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.
더 보기
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, in the style accepted for example by 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
버전 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)
버전 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.버전 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])
-
self_group
(against=None)¶
-
within_group
(*order_by)¶ Produce a WITHIN GROUP (ORDER BY expr) clause against this function.
Used against so-called “ordered set aggregate” and “hypothetical set aggregate” functions, including
percentile_cont
,rank
,dense_rank
, etc.See
within_group()
for a full description.버전 1.1에 추가.
-
within_group_type
(within_group)¶ For types that define their return type as based on the criteria within a WITHIN GROUP (ORDER BY) expression, called by the
WithinGroup
construct.Returns None by default, in which case the function’s normal
.type
is used.
-
-
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()
버전 0.8에 추가:
GenericFunction
now supports automatic registration of new functions as well as package and custom naming support.버전 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.
OrderedSetAgg
(*args, **kwargs)¶ Bases:
sqlalchemy.sql.functions.GenericFunction
Define a function where the return type is based on the sort expression type as defined by the expression passed to the
FunctionElement.within_group()
method.-
array_for_multi_clause
= False¶
-
identifier
= 'OrderedSetAgg'¶
-
name
= 'OrderedSetAgg'¶
-
within_group_type
(within_group)¶
-
-
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.
array_agg
(*args, **kwargs)¶ Bases:
sqlalchemy.sql.functions.GenericFunction
support for the ARRAY_AGG function.
The
func.array_agg(expr)
construct returns an expression of typetypes.ARRAY
.e.g.:
stmt = select([func.array_agg(table.c.values)[2:5]])
버전 1.1에 추가.
더 보기
postgresql.array_agg()
- PostgreSQL-specific version that returnspostgresql.ARRAY
, which has PG-specific operators added.-
identifier
= 'array_agg'¶
-
name
= 'array_agg'¶
-
type
¶ alias of
ARRAY
-
-
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.
cume_dist
(*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
.버전 1.1에 추가.
-
identifier
= 'cume_dist'¶
-
name
= 'cume_dist'¶
-
type
= Numeric()¶
-
-
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.
dense_rank
(*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
.버전 1.1에 추가.
-
identifier
= 'dense_rank'¶
-
name
= 'dense_rank'¶
-
type
= Integer()¶
-
-
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.
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.
버전 1.1에 추가.
-
identifier
= 'mode'¶
-
name
= 'mode'¶
-
-
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.
percent_rank
(*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
.버전 1.1에 추가.
-
identifier
= 'percent_rank'¶
-
name
= 'percent_rank'¶
-
type
= Numeric()¶
-
-
class
sqlalchemy.sql.functions.
percentile_cont
(*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 sort expression’s type.버전 1.1에 추가.
-
array_for_multi_clause
= True¶
-
identifier
= 'percentile_cont'¶
-
name
= 'percentile_cont'¶
-
-
class
sqlalchemy.sql.functions.
percentile_disc
(*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 sort expression’s type.버전 1.1에 추가.
-
array_for_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
.버전 1.1에 추가.
-
identifier
= 'rank'¶
-
name
= 'rank'¶
-
type
= Integer()¶
-
-
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
-