# SQLAlchemy 2.0 Documentation and more

# SQLAlchemy 2.0 Documentation

# SQL and Generic Functions

SQL functions are invoked by using the [func](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.func) namespace.
See the tutorial at [Working with SQL Functions](https://docs.sqlalchemy.org/en/20/tutorial/data_select.html#tutorial-functions) for background on how to
use the [func](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.func) object to render SQL functions in statements.

See also

[Working with SQL Functions](https://docs.sqlalchemy.org/en/20/tutorial/data_select.html#tutorial-functions) - in the [SQLAlchemy Unified Tutorial](https://docs.sqlalchemy.org/en/20/tutorial/index.html#unified-tutorial)

## Function API

The base API for SQL functions, which provides for the [func](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.func)
namespace as well as classes that may be used for extensibility.

| Object Name | Description |
| --- | --- |
| AnsiFunction | Define a function in “ansi” format, which doesn’t render parenthesis. |
| Function | Describe a named SQL function. |
| FunctionElement | Base for SQL function-oriented constructs. |
| GenericFunction | Define a ‘generic’ function. |
| register_function(identifier, fn[, package]) | Associate a callable with a particular func. name. |

   class sqlalchemy.sql.functions.AnsiFunction

*inherits from* [sqlalchemy.sql.functions.GenericFunction](#sqlalchemy.sql.functions.GenericFunction)

Define a function in “ansi” format, which doesn’t render parenthesis.

    class sqlalchemy.sql.functions.Function

*inherits from* [sqlalchemy.sql.functions.FunctionElement](#sqlalchemy.sql.functions.FunctionElement)

Describe a named SQL function.

The [Function](#sqlalchemy.sql.functions.Function) object is typically generated from the
[func](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.func) generation object.

  Parameters:

- ***clauses** – list of column expressions that form the arguments
  of the SQL function call.
- **type_** – optional [TypeEngine](https://docs.sqlalchemy.org/en/20/core/type_api.html#sqlalchemy.types.TypeEngine) datatype object that will be
  used as the return value of the column expression generated by this
  function call.
- **packagenames** –
  a string which indicates package prefix names
  to be prepended to the function name when the SQL is generated.
  The [func](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.func) generator creates these when it is called using
  dotted format, e.g.:
  ```
  func.mypackage.some_function(col1, col2)
  ```

See also

[Working with SQL Functions](https://docs.sqlalchemy.org/en/20/tutorial/data_select.html#tutorial-functions) - in the [SQLAlchemy Unified Tutorial](https://docs.sqlalchemy.org/en/20/tutorial/index.html#unified-tutorial)

[func](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.func) - namespace which produces registered or ad-hoc
[Function](#sqlalchemy.sql.functions.Function) instances.

[GenericFunction](#sqlalchemy.sql.functions.GenericFunction) - allows creation of registered function
types.

| Member Name | Description |
| --- | --- |
| __init__() | Construct aFunction. |

   method [sqlalchemy.sql.functions.Function.](#sqlalchemy.sql.functions.Function)__init__(*name:str*, **clauses:_ColumnExpressionOrLiteralArgument[Any]*, *type_:_TypeEngineArgument[_T]|None=None*, *packagenames:Tuple[str,...]|None=None*) → None

Construct a [Function](#sqlalchemy.sql.functions.Function).

The [func](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.func) construct is normally used to construct
new [Function](#sqlalchemy.sql.functions.Function) instances.

     class sqlalchemy.sql.functions.FunctionElement

*inherits from* [sqlalchemy.sql.expression.Executable](https://docs.sqlalchemy.org/en/20/core/selectable.html#sqlalchemy.sql.expression.Executable), [sqlalchemy.sql.expression.ColumnElement](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.ColumnElement), [sqlalchemy.sql.expression.FromClause](https://docs.sqlalchemy.org/en/20/core/selectable.html#sqlalchemy.sql.expression.FromClause), `sqlalchemy.sql.expression.Generative`

Base for SQL function-oriented constructs.

This is a [generic type](https://peps.python.org/pep-0484/#generics),
meaning that type checkers and IDEs can be instructed on the types to
expect in a [Result](https://docs.sqlalchemy.org/en/20/core/connections.html#sqlalchemy.engine.Result) for this function. See
[GenericFunction](#sqlalchemy.sql.functions.GenericFunction) for an example of how this is done.

See also

[Working with SQL Functions](https://docs.sqlalchemy.org/en/20/tutorial/data_select.html#tutorial-functions) - in the [SQLAlchemy Unified Tutorial](https://docs.sqlalchemy.org/en/20/tutorial/index.html#unified-tutorial)

[Function](#sqlalchemy.sql.functions.Function) - named SQL function.

[func](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.func) - namespace which produces registered or ad-hoc
[Function](#sqlalchemy.sql.functions.Function) instances.

[GenericFunction](#sqlalchemy.sql.functions.GenericFunction) - allows creation of registered function
types.

| Member Name | Description |
| --- | --- |
| __init__() | Construct aFunctionElement. |
| alias() | Produce aAliasconstruct against thisFunctionElement. |
| as_comparison() | Interpret this expression as a boolean comparison between two
values. |
| c | synonym forFunctionElement.columns. |
| clauses | Return the underlyingClauseListwhich contains
the arguments for thisFunctionElement. |
| column_valued() | Return thisFunctionElementas a column expression that
selects from itself as a FROM clause. |
| columns | The set of columns exported by thisFunctionElement. |
| filter() | Produce a FILTER clause against this function. |
| over() | Produce an OVER clause against this function. |
| scalar_table_valued() | Return a column expression that’s against thisFunctionElementas a scalar
table-valued expression. |
| select() | Produce aselect()construct
against thisFunctionElement. |
| self_group() | Apply a ‘grouping’ to thisClauseElement. |
| table_valued() | Return aTableValuedAliasrepresentation of thisFunctionElementwith table-valued expressions added. |
| within_group() | Produce a WITHIN GROUP (ORDER BY expr) clause against this function. |
| within_group_type() | For types that define their return type as based on the criteria
within a WITHIN GROUP (ORDER BY) expression, called by theWithinGroupconstruct. |

   method [sqlalchemy.sql.functions.FunctionElement.](#sqlalchemy.sql.functions.FunctionElement)__init__(**clauses:_ColumnExpressionOrLiteralArgument[Any]*) → None

Construct a [FunctionElement](#sqlalchemy.sql.functions.FunctionElement).

  Parameters:

- ***clauses** – list of column expressions that form the arguments
  of the SQL function call.
- ****kwargs** – additional kwargs are typically consumed by
  subclasses.

See also

[func](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.func)

[Function](#sqlalchemy.sql.functions.Function)

     method [sqlalchemy.sql.functions.FunctionElement.](#sqlalchemy.sql.functions.FunctionElement)alias(*name:str|None=None*, *joins_implicitly:bool=False*) → [TableValuedAlias](https://docs.sqlalchemy.org/en/20/core/selectable.html#sqlalchemy.sql.expression.TableValuedAlias)

Produce a [Alias](https://docs.sqlalchemy.org/en/20/core/selectable.html#sqlalchemy.sql.expression.Alias) construct against this
[FunctionElement](#sqlalchemy.sql.functions.FunctionElement).

Tip

The [FunctionElement.alias()](#sqlalchemy.sql.functions.FunctionElement.alias) method is part of the
mechanism by which “table valued” SQL functions are created.
However, most use cases are covered by higher level methods on
[FunctionElement](#sqlalchemy.sql.functions.FunctionElement) including
[FunctionElement.table_valued()](#sqlalchemy.sql.functions.FunctionElement.table_valued), and
[FunctionElement.column_valued()](#sqlalchemy.sql.functions.FunctionElement.column_valued).

This construct wraps the function in a named alias which
is suitable for the FROM clause, in the style accepted for example
by PostgreSQL.  A column expression is also provided using the
special `.column` attribute, which may
be used to refer to the output of the function as a scalar value
in the columns or where clause, for a backend such as PostgreSQL.

For a full table-valued expression, use the
[FunctionElement.table_valued()](#sqlalchemy.sql.functions.FunctionElement.table_valued) method first to
establish named columns.

e.g.:

```
>>> from sqlalchemy import func, select, column
>>> data_view = func.unnest([1, 2, 3]).alias("data_view")
>>> print(select(data_view.column))
SELECT data_view
FROM unnest(:unnest_1) AS data_view
```

The [FunctionElement.column_valued()](#sqlalchemy.sql.functions.FunctionElement.column_valued) method provides
a shortcut for the above pattern:

```
>>> data_view = func.unnest([1, 2, 3]).column_valued("data_view")
>>> print(select(data_view))
SELECT data_view
FROM unnest(:unnest_1) AS data_view
```

Added in version 1.4.0b2: Added the `.column` accessor

   Parameters:

- **name** – alias name, will be rendered as `AS <name>` in the
  FROM clause
- **joins_implicitly** –
  when True, the table valued function may be
  used in the FROM clause without any explicit JOIN to other tables
  in the SQL query, and no “cartesian product” warning will be
  generated.  May be useful for SQL functions such as
  `func.json_each()`.
  Added in version 1.4.33.

See also

[Table-Valued Functions](https://docs.sqlalchemy.org/en/20/tutorial/data_select.html#tutorial-functions-table-valued) -
in the [SQLAlchemy Unified Tutorial](https://docs.sqlalchemy.org/en/20/tutorial/index.html#unified-tutorial)

[FunctionElement.table_valued()](#sqlalchemy.sql.functions.FunctionElement.table_valued)

[FunctionElement.scalar_table_valued()](#sqlalchemy.sql.functions.FunctionElement.scalar_table_valued)

[FunctionElement.column_valued()](#sqlalchemy.sql.functions.FunctionElement.column_valued)

     method [sqlalchemy.sql.functions.FunctionElement.](#sqlalchemy.sql.functions.FunctionElement)as_comparison(*left_index:int*, *right_index:int*) → FunctionAsBinary

Interpret this expression as a boolean comparison between two
values.

This method is used for an ORM use case described at
[Custom operators based on SQL functions](https://docs.sqlalchemy.org/en/20/orm/join_conditions.html#relationship-custom-operator-sql-function).

A hypothetical SQL function “is_equal()” which compares to values
for 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, the
[FunctionElement.as_comparison()](#sqlalchemy.sql.functions.FunctionElement.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](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.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 able
to manipulate the “left” and “right” sides of the ON clause of a JOIN
expression. The purpose of this method is to provide a SQL function
construct that can also supply this information to the ORM, when used
with the [relationship.primaryjoin](https://docs.sqlalchemy.org/en/20/orm/relationship_api.html#sqlalchemy.orm.relationship.params.primaryjoin) parameter. The
return value 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 by
determining if the name of the parent Venue is contained within the
start of the hypothetical descendant value’s name, e.g. “parent1” would
match 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 geometric
functions to create join conditions.

  Parameters:

- **left_index** – the integer 1-based index of the function argument
  that serves as the “left” side of the expression.
- **right_index** – the integer 1-based index of the function argument
  that serves as the “right” side of the expression.

Added in version 1.3.

See also

[Custom operators based on SQL functions](https://docs.sqlalchemy.org/en/20/orm/join_conditions.html#relationship-custom-operator-sql-function) -
example use within the ORM

     attribute [sqlalchemy.sql.functions.FunctionElement.](#sqlalchemy.sql.functions.FunctionElement)c

synonym for [FunctionElement.columns](#sqlalchemy.sql.functions.FunctionElement.columns).

    attribute [sqlalchemy.sql.functions.FunctionElement.](#sqlalchemy.sql.functions.FunctionElement)clauses

Return the underlying [ClauseList](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.ClauseList) which contains
the arguments for this [FunctionElement](#sqlalchemy.sql.functions.FunctionElement).

    method [sqlalchemy.sql.functions.FunctionElement.](#sqlalchemy.sql.functions.FunctionElement)column_valued(*name:str|None=None*, *joins_implicitly:bool=False*) → TableValuedColumn[_T]

Return this [FunctionElement](#sqlalchemy.sql.functions.FunctionElement) as a column expression that
selects from itself as a FROM clause.

E.g.:

```
>>> from sqlalchemy import select, func
>>> gs = func.generate_series(1, 5, -1).column_valued()
>>> print(select(gs))
SELECT anon_1
FROM generate_series(:generate_series_1, :generate_series_2, :generate_series_3) AS anon_1
```

This is shorthand for:

```
gs = func.generate_series(1, 5, -1).alias().column
```

   Parameters:

- **name** – optional name to assign to the alias name that’s generated.
  If omitted, a unique anonymizing name is used.
- **joins_implicitly** –
  when True, the “table” portion of the column
  valued function may be a member of the FROM clause without any
  explicit JOIN to other tables in the SQL query, and no “cartesian
  product” warning will be generated. May be useful for SQL functions
  such as `func.json_array_elements()`.
  Added in version 1.4.46.

See also

[Column Valued Functions - Table Valued Function as a Scalar Column](https://docs.sqlalchemy.org/en/20/tutorial/data_select.html#tutorial-functions-column-valued) - in the [SQLAlchemy Unified Tutorial](https://docs.sqlalchemy.org/en/20/tutorial/index.html#unified-tutorial)

[Column Valued Functions](https://docs.sqlalchemy.org/en/20/dialects/postgresql.html#postgresql-column-valued) - in the [PostgreSQL](https://docs.sqlalchemy.org/en/20/dialects/postgresql.html) documentation

[FunctionElement.table_valued()](#sqlalchemy.sql.functions.FunctionElement.table_valued)

     attribute [sqlalchemy.sql.functions.FunctionElement.](#sqlalchemy.sql.functions.FunctionElement)columns

The set of columns exported by this [FunctionElement](#sqlalchemy.sql.functions.FunctionElement).

This is a placeholder collection that allows the function to be
placed in the FROM clause of a statement:

```
>>> from sqlalchemy import column, select, func
>>> stmt = select(column("x"), column("y")).select_from(func.myfunction())
>>> print(stmt)
SELECT x, y FROM myfunction()
```

The above form is a legacy feature that is now superseded by the
fully capable [FunctionElement.table_valued()](#sqlalchemy.sql.functions.FunctionElement.table_valued)
method; see that method for details.

See also

[FunctionElement.table_valued()](#sqlalchemy.sql.functions.FunctionElement.table_valued) - generates table-valued
SQL function expressions.

     property entity_namespace: _EntityNamespace

overrides FromClause.entity_namespace as functions are generally
column expressions and not FromClauses.

    property exported_columns: [ColumnCollection](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.ColumnCollection)[str, KeyedColumnElement[Any]]

A [ColumnCollection](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.ColumnCollection)
that represents the “exported”
columns of this [Selectable](https://docs.sqlalchemy.org/en/20/core/selectable.html#sqlalchemy.sql.expression.Selectable).

The “exported” columns for a [FromClause](https://docs.sqlalchemy.org/en/20/core/selectable.html#sqlalchemy.sql.expression.FromClause)
object are synonymous
with the [FromClause.columns](https://docs.sqlalchemy.org/en/20/core/selectable.html#sqlalchemy.sql.expression.FromClause.columns) collection.

Added in version 1.4.

See also

[Selectable.exported_columns](https://docs.sqlalchemy.org/en/20/core/selectable.html#sqlalchemy.sql.expression.Selectable.exported_columns)

[SelectBase.exported_columns](https://docs.sqlalchemy.org/en/20/core/selectable.html#sqlalchemy.sql.expression.SelectBase.exported_columns)

     method [sqlalchemy.sql.functions.FunctionElement.](#sqlalchemy.sql.functions.FunctionElement)filter(**criterion:_ColumnExpressionArgument[bool]*) → Self | [FunctionFilter](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.FunctionFilter)[_T]

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

See also

[Special Modifiers WITHIN GROUP, FILTER](https://docs.sqlalchemy.org/en/20/tutorial/data_select.html#tutorial-functions-within-group) -
in the [SQLAlchemy Unified Tutorial](https://docs.sqlalchemy.org/en/20/tutorial/index.html#unified-tutorial)

[FunctionFilter](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.FunctionFilter)

[funcfilter()](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.funcfilter)

     method [sqlalchemy.sql.functions.FunctionElement.](#sqlalchemy.sql.functions.FunctionElement)over(***, *partition_by:_ByArgument|None=None*, *order_by:_ByArgument|None=None*, *rows:Tuple[int|None,int|None]|None=None*, *range_:Tuple[int|None,int|None]|None=None*, *groups:Tuple[int|None,int|None]|None=None*) → [Over](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.Over)[_T]

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()](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.over) for a full description.

See also

[over()](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.over)

[Using Window Functions](https://docs.sqlalchemy.org/en/20/tutorial/data_select.html#tutorial-window-functions) - in the [SQLAlchemy Unified Tutorial](https://docs.sqlalchemy.org/en/20/tutorial/index.html#unified-tutorial)

     method [sqlalchemy.sql.functions.FunctionElement.](#sqlalchemy.sql.functions.FunctionElement)scalar_table_valued(*name:str*, *type_:_TypeEngineArgument[_T]|None=None*) → ScalarFunctionColumn[_T]

Return a column expression that’s against this
[FunctionElement](#sqlalchemy.sql.functions.FunctionElement) as a scalar
table-valued expression.

The returned expression is similar to that returned by a single column
accessed off of a [FunctionElement.table_valued()](#sqlalchemy.sql.functions.FunctionElement.table_valued)
construct, except no FROM clause is generated; the function is rendered
in the similar way as a scalar subquery.

E.g.:

```
>>> from sqlalchemy import func, select
>>> fn = func.jsonb_each("{'k', 'v'}").scalar_table_valued("key")
>>> print(select(fn))
SELECT (jsonb_each(:jsonb_each_1)).key
```

Added in version 1.4.0b2.

See also

[FunctionElement.table_valued()](#sqlalchemy.sql.functions.FunctionElement.table_valued)

[FunctionElement.alias()](#sqlalchemy.sql.functions.FunctionElement.alias)

[FunctionElement.column_valued()](#sqlalchemy.sql.functions.FunctionElement.column_valued)

     method [sqlalchemy.sql.functions.FunctionElement.](#sqlalchemy.sql.functions.FunctionElement)select() → [Select](https://docs.sqlalchemy.org/en/20/core/selectable.html#sqlalchemy.sql.expression.Select)

Produce a [select()](https://docs.sqlalchemy.org/en/20/core/selectable.html#sqlalchemy.sql.expression.select) construct
against this [FunctionElement](#sqlalchemy.sql.functions.FunctionElement).

This is shorthand for:

```
s = select(function_element)
```

     method [sqlalchemy.sql.functions.FunctionElement.](#sqlalchemy.sql.functions.FunctionElement)self_group(*against:OperatorType|None=None*) → [ClauseElement](https://docs.sqlalchemy.org/en/20/core/foundation.html#sqlalchemy.sql.expression.ClauseElement)

Apply a ‘grouping’ to this [ClauseElement](https://docs.sqlalchemy.org/en/20/core/foundation.html#sqlalchemy.sql.expression.ClauseElement).

This method is overridden by subclasses to return a “grouping”
construct, i.e. parenthesis.   In particular it’s used by “binary”
expressions to provide a grouping around themselves when placed into a
larger expression, as well as by [select()](https://docs.sqlalchemy.org/en/20/core/selectable.html#sqlalchemy.sql.expression.select)
constructs when placed into the FROM clause of another
[select()](https://docs.sqlalchemy.org/en/20/core/selectable.html#sqlalchemy.sql.expression.select).  (Note that subqueries should be
normally created using the [Select.alias()](https://docs.sqlalchemy.org/en/20/core/selectable.html#sqlalchemy.sql.expression.Select.alias) method,
as many
platforms require nested SELECT statements to be named).

As expressions are composed together, the application of
[self_group()](#sqlalchemy.sql.functions.FunctionElement.self_group) is automatic - end-user code should never
need to use this method directly.  Note that SQLAlchemy’s
clause constructs take operator precedence into account -
so parenthesis might not be needed, for example, in
an expression like `x OR (y AND z)` - AND takes precedence
over OR.

The base [self_group()](#sqlalchemy.sql.functions.FunctionElement.self_group) method of
[ClauseElement](https://docs.sqlalchemy.org/en/20/core/foundation.html#sqlalchemy.sql.expression.ClauseElement)
just returns self.

    method [sqlalchemy.sql.functions.FunctionElement.](#sqlalchemy.sql.functions.FunctionElement)table_valued(**expr:_ColumnExpressionOrStrLabelArgument[Any]*, ***kw:Any*) → [TableValuedAlias](https://docs.sqlalchemy.org/en/20/core/selectable.html#sqlalchemy.sql.expression.TableValuedAlias)

Return a [TableValuedAlias](https://docs.sqlalchemy.org/en/20/core/selectable.html#sqlalchemy.sql.expression.TableValuedAlias) representation of this
[FunctionElement](#sqlalchemy.sql.functions.FunctionElement) with table-valued expressions added.

e.g. to use the SQLite form of `generate_series()` (including
hidden columns “start”, “stop”, “step”):

```
>>> fn = func.generate_series(1, 5).table_valued(
...     "value", "start", "stop", "step"
... )

>>> print(select(fn))
SELECT anon_1.value, anon_1.start, anon_1.stop, anon_1.step
FROM generate_series(:generate_series_1, :generate_series_2) AS anon_1
>>> print(select(fn.c.value, fn.c.stop).where(fn.c.value > 2))
SELECT anon_1.value, anon_1.stop
FROM generate_series(:generate_series_1, :generate_series_2) AS anon_1
WHERE anon_1.value > :value_1
```

Backends like PostgreSQL need the accessed columns to be explicitly
named in “AS” clause.  To achieve this, use
[TableValuedAlias.render_derived()](https://docs.sqlalchemy.org/en/20/core/selectable.html#sqlalchemy.sql.expression.TableValuedAlias.render_derived); be sure to consult the
[PostgreSQL-specific documentation for table valued functions](https://docs.sqlalchemy.org/en/20/dialects/postgresql.html#postgresql-table-valued) for additional examples:

```
>>> fn = func.generate_series(1, 5).table_valued("value").render_derived()

>>> print(select(fn))
SELECT anon_1.value FROM
generate_series(:generate_series_1, :generate_series_2) AS anon_1(value)
```

A WITH ORDINALITY expression may be generated by passing the keyword
argument [FunctionElement.table_valued.with_ordinality](#sqlalchemy.sql.functions.FunctionElement.table_valued.params.with_ordinality),
illustrated below using PostgreSQL’s syntax:

```
>>> fn = func.generate_series(4, 1, -1).table_valued(
...     "gen", with_ordinality="ordinality"
... )
>>> print(select(fn.render_derived()))
SELECT anon_1.gen, anon_1.ordinality
FROM generate_series(:generate_series_1, :generate_series_2, :generate_series_3)
WITH ORDINALITY AS anon_1(gen, ordinality)
```

   Parameters:

- ***expr** – A series of string column names that will be added to the
  `.c` collection of the resulting [TableValuedAlias](https://docs.sqlalchemy.org/en/20/core/selectable.html#sqlalchemy.sql.expression.TableValuedAlias)
  construct as columns.  [column()](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.column) objects with or without
  datatypes may also be used.
- **name** – optional name to assign to the alias name that’s generated.
  If omitted, a unique anonymizing name is used.
- **with_ordinality** – string name that when present results in the
  `WITH ORDINALITY` clause being added to the alias, and the given
  string name will be added as a column to the .c collection
  of the resulting [TableValuedAlias](https://docs.sqlalchemy.org/en/20/core/selectable.html#sqlalchemy.sql.expression.TableValuedAlias).
- **joins_implicitly** –
  when True, the table valued function may be
  used in the FROM clause without any explicit JOIN to other tables
  in the SQL query, and no “cartesian product” warning will be generated.
  May be useful for SQL functions such as `func.json_each()`.
  Added in version 1.4.33.

Added in version 1.4.0b2.

See also

[Table-Valued Functions](https://docs.sqlalchemy.org/en/20/tutorial/data_select.html#tutorial-functions-table-valued) - in the [SQLAlchemy Unified Tutorial](https://docs.sqlalchemy.org/en/20/tutorial/index.html#unified-tutorial)

[Table-Valued Functions on PostgreSQL](https://docs.sqlalchemy.org/en/20/dialects/postgresql.html#postgresql-table-valued) - in the [PostgreSQL](https://docs.sqlalchemy.org/en/20/dialects/postgresql.html) documentation

[FunctionElement.scalar_table_valued()](#sqlalchemy.sql.functions.FunctionElement.scalar_table_valued) - variant of
[FunctionElement.table_valued()](#sqlalchemy.sql.functions.FunctionElement.table_valued) which delivers the
complete table valued expression as a scalar column expression

[FunctionElement.column_valued()](#sqlalchemy.sql.functions.FunctionElement.column_valued)

[TableValuedAlias.render_derived()](https://docs.sqlalchemy.org/en/20/core/selectable.html#sqlalchemy.sql.expression.TableValuedAlias.render_derived) - renders the alias
using a derived column clause, e.g. `AS name(col1, col2, ...)`

     method [sqlalchemy.sql.functions.FunctionElement.](#sqlalchemy.sql.functions.FunctionElement)within_group(**order_by:_ColumnExpressionArgument[Any]*) → [WithinGroup](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.WithinGroup)[_T]

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](#sqlalchemy.sql.functions.percentile_cont),
[rank](#sqlalchemy.sql.functions.rank), [dense_rank](#sqlalchemy.sql.functions.dense_rank), etc.

See [within_group()](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.within_group) for a full description.

See also

[Special Modifiers WITHIN GROUP, FILTER](https://docs.sqlalchemy.org/en/20/tutorial/data_select.html#tutorial-functions-within-group) -
in the [SQLAlchemy Unified Tutorial](https://docs.sqlalchemy.org/en/20/tutorial/index.html#unified-tutorial)

     method [sqlalchemy.sql.functions.FunctionElement.](#sqlalchemy.sql.functions.FunctionElement)within_group_type(*within_group:WithinGroup[_S]*) → [TypeEngine](https://docs.sqlalchemy.org/en/20/core/type_api.html#sqlalchemy.types.TypeEngine) | None

For types that define their return type as based on the criteria
within a WITHIN GROUP (ORDER BY) expression, called by the
[WithinGroup](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.WithinGroup) construct.

Returns None by default, in which case the function’s normal `.type`
is used.

     class sqlalchemy.sql.functions.GenericFunction

*inherits from* [sqlalchemy.sql.functions.Function](#sqlalchemy.sql.functions.Function)

Define a ‘generic’ function.

A generic function is a pre-established [Function](#sqlalchemy.sql.functions.Function)
class that is instantiated automatically when called
by name from the [func](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.func) attribute.    Note that
calling any name from [func](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.func) has the effect that
a new [Function](#sqlalchemy.sql.functions.Function) instance is created automatically,
given that name.  The primary use case for defining
a [GenericFunction](#sqlalchemy.sql.functions.GenericFunction) 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](#sqlalchemy.sql.functions.GenericFunction) are automatically
registered under the name of the class.  For
example, a user-defined function `as_utc()` would
be available immediately:

```
from sqlalchemy.sql.functions import GenericFunction
from sqlalchemy.types import DateTime

class as_utc(GenericFunction):
    type = DateTime()
    inherit_cache = True

print(select(func.as_utc()))
```

User-defined generic functions can be organized into
packages by specifying the “package” attribute when defining
[GenericFunction](#sqlalchemy.sql.functions.GenericFunction).   Third party libraries
containing many functions may want to use this in order
to 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"
    inherit_cache = True
```

The above function would be available from [func](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.func)
using the package name `time`:

```
print(select(func.time.as_utc()))
```

A final option is to allow the function to be accessed
from one name in [func](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.func) but to render as a different name.
The `identifier` attribute will override the name used to
access the function as loaded from [func](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.func), but will retain
the usage of `name` as the rendered name:

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

The above function will render as follows:

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

The name will be rendered as is, however without quoting unless the name
contains special characters that require quoting.  To force quoting
on or off for the name, use the `quoted_name`
construct:

```
from sqlalchemy.sql import quoted_name

class GeoBuffer(GenericFunction):
    type = Geometry()
    package = "geo"
    name = quoted_name("ST_Buffer", True)
    identifier = "buffer"
    inherit_cache = True
```

The above function will render as:

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

Type parameters for this class as a
[generic type](https://peps.python.org/pep-0484/#generics) can be passed
and should match the type seen in a [Result](https://docs.sqlalchemy.org/en/20/core/connections.html#sqlalchemy.engine.Result). For example:

```
class as_utc(GenericFunction[datetime.datetime]):
    type = DateTime()
    inherit_cache = True
```

The above indicates that the following expression returns a `datetime`
object:

```
connection.scalar(select(func.as_utc()))
```

Added in version 1.3.13: The [quoted_name](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.quoted_name) construct is now
recognized for quoting when used with the “name” attribute of the
object, so that quoting can be forced on or off for the function
name.

     function sqlalchemy.sql.functions.register_function(*identifier:str*, *fn:Type[Function[Any]]*, *package:str='_default'*) → None

Associate a callable with a particular func. name.

This is normally called by GenericFunction, but is also
available by itself so that a non-Function construct
can be associated with the [func](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.func) accessor (i.e.
CAST, EXTRACT).

## Selected “Known” Functions

These are [GenericFunction](#sqlalchemy.sql.functions.GenericFunction) implementations for a selected set of
common SQL functions that set up the expected return type for each function
automatically.  The are invoked in the same way as any other member of the
[func](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.func) namespace:

```
select(func.count("*")).select_from(some_table)
```

Note that any name not known to [func](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.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.

| Object Name | Description |
| --- | --- |
| aggregate_strings | Implement a generic string aggregation function. |
| array_agg | Support for the ARRAY_AGG function. |
| char_length | The CHAR_LENGTH() SQL function. |
| coalesce |  |
| concat | The SQL CONCAT() function, which concatenates strings. |
| count | The ANSI COUNT aggregate function.  With no arguments,
emits COUNT *. |
| cube | Implement theCUBEgrouping operation. |
| cume_dist | Implement thecume_disthypothetical-set aggregate function. |
| current_date | The CURRENT_DATE() SQL function. |
| current_time | The CURRENT_TIME() SQL function. |
| current_timestamp | The CURRENT_TIMESTAMP() SQL function. |
| current_user | The CURRENT_USER() SQL function. |
| dense_rank | Implement thedense_rankhypothetical-set aggregate function. |
| grouping_sets | Implement theGROUPINGSETSgrouping operation. |
| localtime | The localtime() SQL function. |
| localtimestamp | The localtimestamp() SQL function. |
| max | The SQL MAX() aggregate function. |
| min | The SQL MIN() aggregate function. |
| mode | Implement themodeordered-set aggregate function. |
| next_value | Represent the ‘next value’, given aSequenceas its single argument. |
| now | The SQL now() datetime function. |
| percent_rank | Implement thepercent_rankhypothetical-set aggregate function. |
| percentile_cont | Implement thepercentile_contordered-set aggregate function. |
| percentile_disc | Implement thepercentile_discordered-set aggregate function. |
| random | The RANDOM() SQL function. |
| rank | Implement therankhypothetical-set aggregate function. |
| rollup | Implement theROLLUPgrouping operation. |
| session_user | The SESSION_USER() SQL function. |
| sum | The SQL SUM() aggregate function. |
| sysdate | The SYSDATE() SQL function. |
| user | The USER() SQL function. |

   class sqlalchemy.sql.functions.aggregate_strings

*inherits from* [sqlalchemy.sql.functions.GenericFunction](#sqlalchemy.sql.functions.GenericFunction)

Implement a generic string aggregation function.

This function will concatenate non-null values into a string and
separate the values by a delimiter.

This function is compiled on a per-backend basis, into functions
such as `group_concat()`, `string_agg()`, or `LISTAGG()`.

e.g. Example usage with delimiter ‘.’:

```
stmt = select(func.aggregate_strings(table.c.str_col, "."))
```

The return type of this function is [String](https://docs.sqlalchemy.org/en/20/core/type_basics.html#sqlalchemy.types.String).

    class sqlalchemy.sql.functions.array_agg

*inherits from* `sqlalchemy.sql.functions.ReturnTypeFromArgs`

Support for the ARRAY_AGG function.

The `func.array_agg(expr)` construct returns an expression of
type [ARRAY](https://docs.sqlalchemy.org/en/20/core/type_basics.html#sqlalchemy.types.ARRAY).

e.g.:

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

See also

[array_agg()](https://docs.sqlalchemy.org/en/20/dialects/postgresql.html#sqlalchemy.dialects.postgresql.array_agg) - PostgreSQL-specific version that
returns [ARRAY](https://docs.sqlalchemy.org/en/20/dialects/postgresql.html#sqlalchemy.dialects.postgresql.ARRAY), which has PG-specific operators
added.

     class sqlalchemy.sql.functions.char_length

*inherits from* [sqlalchemy.sql.functions.GenericFunction](#sqlalchemy.sql.functions.GenericFunction)

The CHAR_LENGTH() SQL function.

    class sqlalchemy.sql.functions.coalesce

*inherits from* `sqlalchemy.sql.functions.ReturnTypeFromOptionalArgs`

     class sqlalchemy.sql.functions.concat

*inherits from* [sqlalchemy.sql.functions.GenericFunction](#sqlalchemy.sql.functions.GenericFunction)

The SQL CONCAT() function, which concatenates strings.

E.g.:

```
>>> print(select(func.concat("a", "b")))
SELECT concat(:concat_2, :concat_3) AS concat_1
```

String concatenation in SQLAlchemy is more commonly available using the
Python `+` operator with string datatypes, which will render a
backend-specific concatenation operator, such as :

```
>>> print(select(literal("a") + "b"))
SELECT :param_1 || :param_2 AS anon_1
```

     class sqlalchemy.sql.functions.count

*inherits from* [sqlalchemy.sql.functions.GenericFunction](#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
```

     class sqlalchemy.sql.functions.cube

*inherits from* [sqlalchemy.sql.functions.GenericFunction](#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()](https://docs.sqlalchemy.org/en/20/core/selectable.html#sqlalchemy.sql.expression.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))
```

Added in version 1.2.

     class sqlalchemy.sql.functions.cume_dist

*inherits from* [sqlalchemy.sql.functions.GenericFunction](#sqlalchemy.sql.functions.GenericFunction)

Implement the `cume_dist` hypothetical-set aggregate function.

This function must be used with the [FunctionElement.within_group()](#sqlalchemy.sql.functions.FunctionElement.within_group)
modifier to supply a sort expression to operate upon.

The return type of this function is [Numeric](https://docs.sqlalchemy.org/en/20/core/type_basics.html#sqlalchemy.types.Numeric).

    class sqlalchemy.sql.functions.current_date

*inherits from* [sqlalchemy.sql.functions.AnsiFunction](#sqlalchemy.sql.functions.AnsiFunction)

The CURRENT_DATE() SQL function.

    class sqlalchemy.sql.functions.current_time

*inherits from* [sqlalchemy.sql.functions.AnsiFunction](#sqlalchemy.sql.functions.AnsiFunction)

The CURRENT_TIME() SQL function.

    class sqlalchemy.sql.functions.current_timestamp

*inherits from* [sqlalchemy.sql.functions.AnsiFunction](#sqlalchemy.sql.functions.AnsiFunction)

The CURRENT_TIMESTAMP() SQL function.

    class sqlalchemy.sql.functions.current_user

*inherits from* [sqlalchemy.sql.functions.AnsiFunction](#sqlalchemy.sql.functions.AnsiFunction)

The CURRENT_USER() SQL function.

    class sqlalchemy.sql.functions.dense_rank

*inherits from* [sqlalchemy.sql.functions.GenericFunction](#sqlalchemy.sql.functions.GenericFunction)

Implement the `dense_rank` hypothetical-set aggregate function.

This function must be used with the [FunctionElement.within_group()](#sqlalchemy.sql.functions.FunctionElement.within_group)
modifier to supply a sort expression to operate upon.

The return type of this function is [Integer](https://docs.sqlalchemy.org/en/20/core/type_basics.html#sqlalchemy.types.Integer).

    class sqlalchemy.sql.functions.grouping_sets

*inherits from* [sqlalchemy.sql.functions.GenericFunction](#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()](https://docs.sqlalchemy.org/en/20/core/selectable.html#sqlalchemy.sql.expression.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_()](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.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),
    )
)
```

Added in version 1.2.

     class sqlalchemy.sql.functions.localtime

*inherits from* [sqlalchemy.sql.functions.AnsiFunction](#sqlalchemy.sql.functions.AnsiFunction)

The localtime() SQL function.

    class sqlalchemy.sql.functions.localtimestamp

*inherits from* [sqlalchemy.sql.functions.AnsiFunction](#sqlalchemy.sql.functions.AnsiFunction)

The localtimestamp() SQL function.

    class sqlalchemy.sql.functions.max

*inherits from* `sqlalchemy.sql.functions.ReturnTypeFromArgs`

The SQL MAX() aggregate function.

    class sqlalchemy.sql.functions.min

*inherits from* `sqlalchemy.sql.functions.ReturnTypeFromArgs`

The SQL MIN() aggregate function.

    class sqlalchemy.sql.functions.mode

*inherits from* `sqlalchemy.sql.functions.OrderedSetAgg`

Implement the `mode` ordered-set aggregate function.

This function must be used with the [FunctionElement.within_group()](#sqlalchemy.sql.functions.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.

    class sqlalchemy.sql.functions.next_value

*inherits from* [sqlalchemy.sql.functions.GenericFunction](#sqlalchemy.sql.functions.GenericFunction)

Represent the ‘next value’, given a [Sequence](https://docs.sqlalchemy.org/en/20/core/defaults.html#sqlalchemy.schema.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.

    class sqlalchemy.sql.functions.now

*inherits from* [sqlalchemy.sql.functions.GenericFunction](#sqlalchemy.sql.functions.GenericFunction)

The SQL now() datetime function.

SQLAlchemy dialects will usually render this particular function
in a backend-specific way, such as rendering it as `CURRENT_TIMESTAMP`.

    class sqlalchemy.sql.functions.percent_rank

*inherits from* [sqlalchemy.sql.functions.GenericFunction](#sqlalchemy.sql.functions.GenericFunction)

Implement the `percent_rank` hypothetical-set aggregate function.

This function must be used with the [FunctionElement.within_group()](#sqlalchemy.sql.functions.FunctionElement.within_group)
modifier to supply a sort expression to operate upon.

The return type of this function is [Numeric](https://docs.sqlalchemy.org/en/20/core/type_basics.html#sqlalchemy.types.Numeric).

    class sqlalchemy.sql.functions.percentile_cont

*inherits from* `sqlalchemy.sql.functions.OrderedSetAgg`

Implement the `percentile_cont` ordered-set aggregate function.

This function must be used with the [FunctionElement.within_group()](#sqlalchemy.sql.functions.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 [ARRAY](https://docs.sqlalchemy.org/en/20/core/type_basics.html#sqlalchemy.types.ARRAY) of the sort
expression’s type.

    class sqlalchemy.sql.functions.percentile_disc

*inherits from* `sqlalchemy.sql.functions.OrderedSetAgg`

Implement the `percentile_disc` ordered-set aggregate function.

This function must be used with the [FunctionElement.within_group()](#sqlalchemy.sql.functions.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 [ARRAY](https://docs.sqlalchemy.org/en/20/core/type_basics.html#sqlalchemy.types.ARRAY) of the sort
expression’s type.

    class sqlalchemy.sql.functions.random

*inherits from* [sqlalchemy.sql.functions.GenericFunction](#sqlalchemy.sql.functions.GenericFunction)

The RANDOM() SQL function.

    class sqlalchemy.sql.functions.rank

*inherits from* [sqlalchemy.sql.functions.GenericFunction](#sqlalchemy.sql.functions.GenericFunction)

Implement the `rank` hypothetical-set aggregate function.

This function must be used with the [FunctionElement.within_group()](#sqlalchemy.sql.functions.FunctionElement.within_group)
modifier to supply a sort expression to operate upon.

The return type of this function is [Integer](https://docs.sqlalchemy.org/en/20/core/type_basics.html#sqlalchemy.types.Integer).

    class sqlalchemy.sql.functions.rollup

*inherits from* [sqlalchemy.sql.functions.GenericFunction](#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()](https://docs.sqlalchemy.org/en/20/core/selectable.html#sqlalchemy.sql.expression.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))
```

Added in version 1.2.

     class sqlalchemy.sql.functions.session_user

*inherits from* [sqlalchemy.sql.functions.AnsiFunction](#sqlalchemy.sql.functions.AnsiFunction)

The SESSION_USER() SQL function.

    class sqlalchemy.sql.functions.sum

*inherits from* `sqlalchemy.sql.functions.ReturnTypeFromArgs`

The SQL SUM() aggregate function.

    class sqlalchemy.sql.functions.sysdate

*inherits from* [sqlalchemy.sql.functions.AnsiFunction](#sqlalchemy.sql.functions.AnsiFunction)

The SYSDATE() SQL function.

    class sqlalchemy.sql.functions.user

*inherits from* [sqlalchemy.sql.functions.AnsiFunction](#sqlalchemy.sql.functions.AnsiFunction)

The USER() SQL function.

---

# SQLAlchemy 2.0 Documentation

# SQLAlchemy Core

The breadth of SQLAlchemy’s SQL rendering engine, DBAPI integration,
transaction integration, and schema description services are documented here.
In contrast to the ORM’s domain-centric mode of usage, the SQL Expression
Language provides a schema-centric usage paradigm.

- [SQL Statements and Expressions API](https://docs.sqlalchemy.org/en/20/core/expression_api.html)
  - [Column Elements and Expressions](https://docs.sqlalchemy.org/en/20/core/sqlelement.html)
  - [Operator Reference](https://docs.sqlalchemy.org/en/20/core/operators.html)
  - [SELECT and Related Constructs](https://docs.sqlalchemy.org/en/20/core/selectable.html)
  - [Insert, Updates, Deletes](https://docs.sqlalchemy.org/en/20/core/dml.html)
  - [SQL and Generic Functions](https://docs.sqlalchemy.org/en/20/core/functions.html)
  - [Custom SQL Constructs and Compilation Extension](https://docs.sqlalchemy.org/en/20/core/compiler.html)
  - [Expression Serializer Extension](https://docs.sqlalchemy.org/en/20/core/serializer.html)
  - [SQL Expression Language Foundational Constructs](https://docs.sqlalchemy.org/en/20/core/foundation.html)
  - [Visitor and Traversal Utilities](https://docs.sqlalchemy.org/en/20/core/visitors.html)
- [Schema Definition Language](https://docs.sqlalchemy.org/en/20/core/schema.html)
  - [Describing Databases with MetaData](https://docs.sqlalchemy.org/en/20/core/metadata.html)
  - [Reflecting Database Objects](https://docs.sqlalchemy.org/en/20/core/reflection.html)
  - [Column INSERT/UPDATE Defaults](https://docs.sqlalchemy.org/en/20/core/defaults.html)
  - [Defining Constraints and Indexes](https://docs.sqlalchemy.org/en/20/core/constraints.html)
  - [Customizing DDL](https://docs.sqlalchemy.org/en/20/core/ddl.html)
- [SQL Datatype Objects](https://docs.sqlalchemy.org/en/20/core/types.html)
  - [The Type Hierarchy](https://docs.sqlalchemy.org/en/20/core/type_basics.html)
  - [Custom Types](https://docs.sqlalchemy.org/en/20/core/custom_types.html)
  - [Base Type API](https://docs.sqlalchemy.org/en/20/core/type_api.html)
- [Engine and Connection Use](https://docs.sqlalchemy.org/en/20/core/engines_connections.html)
  - [Engine Configuration](https://docs.sqlalchemy.org/en/20/core/engines.html)
  - [Working with Engines and Connections](https://docs.sqlalchemy.org/en/20/core/connections.html)
  - [Connection Pooling](https://docs.sqlalchemy.org/en/20/core/pooling.html)
  - [Core Events](https://docs.sqlalchemy.org/en/20/core/events.html)
- [Core API Basics](https://docs.sqlalchemy.org/en/20/core/api_basics.html)
  - [Events](https://docs.sqlalchemy.org/en/20/core/event.html)
  - [Runtime Inspection API](https://docs.sqlalchemy.org/en/20/core/inspection.html)
  - [Core Exceptions](https://docs.sqlalchemy.org/en/20/core/exceptions.html)
  - [Core Internals](https://docs.sqlalchemy.org/en/20/core/internals.html)

---

# SQLAlchemy 2.0 Documentation

# Runtime Inspection API

The inspection module provides the [inspect()](#sqlalchemy.inspect) function,
which delivers runtime information about a wide variety
of SQLAlchemy objects, both within the Core as well as the
ORM.

The [inspect()](#sqlalchemy.inspect) function is the entry point to SQLAlchemy’s
public API for viewing the configuration and construction
of in-memory objects.   Depending on the type of object
passed to [inspect()](#sqlalchemy.inspect), the return value will either be
a related object which provides a known interface, or in many
cases it will return the object itself.

The rationale for [inspect()](#sqlalchemy.inspect) is twofold.  One is that
it replaces the need to be aware of a large variety of “information
getting” functions in SQLAlchemy, such as
[Inspector.from_engine()](https://docs.sqlalchemy.org/en/20/core/reflection.html#sqlalchemy.engine.reflection.Inspector.from_engine) (deprecated in 1.4),
[instance_state()](https://docs.sqlalchemy.org/en/20/orm/session_api.html#sqlalchemy.orm.attributes.instance_state), [class_mapper()](https://docs.sqlalchemy.org/en/20/orm/mapping_api.html#sqlalchemy.orm.class_mapper),
and others.    The other is that the return value of [inspect()](#sqlalchemy.inspect)
is guaranteed to obey a documented API, thus allowing third party
tools which build on top of SQLAlchemy configurations to be constructed
in a forwards-compatible way.

| Object Name | Description |
| --- | --- |
| inspect(subject[, raiseerr]) | Produce an inspection object for the given target. |

   function sqlalchemy.inspect(*subject:Any*, *raiseerr:bool=True*) → Any

Produce an inspection object for the given target.

The returned value in some cases may be the
same object as the one given, such as if a
[Mapper](https://docs.sqlalchemy.org/en/20/orm/mapping_api.html#sqlalchemy.orm.Mapper) object is passed.   In other
cases, it will be an instance of the registered
inspection type for the given object, such as
if an [Engine](https://docs.sqlalchemy.org/en/20/core/connections.html#sqlalchemy.engine.Engine) is passed, an
[Inspector](https://docs.sqlalchemy.org/en/20/core/reflection.html#sqlalchemy.engine.reflection.Inspector) object is returned.

  Parameters:

- **subject** – the subject to be inspected.
- **raiseerr** – When `True`, if the given subject
  does not
  correspond to a known SQLAlchemy inspected type,
  [sqlalchemy.exc.NoInspectionAvailable](https://docs.sqlalchemy.org/en/20/core/exceptions.html#sqlalchemy.exc.NoInspectionAvailable)
  is raised.  If `False`, `None` is returned.

## Available Inspection Targets

Below is a listing of many of the most common inspection targets.

- `Connectable` (i.e. [Engine](https://docs.sqlalchemy.org/en/20/core/connections.html#sqlalchemy.engine.Engine),
  [Connection](https://docs.sqlalchemy.org/en/20/core/connections.html#sqlalchemy.engine.Connection)) - returns an [Inspector](https://docs.sqlalchemy.org/en/20/core/reflection.html#sqlalchemy.engine.reflection.Inspector) object.
- [ClauseElement](https://docs.sqlalchemy.org/en/20/core/foundation.html#sqlalchemy.sql.expression.ClauseElement) - all SQL expression components, including
  [Table](https://docs.sqlalchemy.org/en/20/core/metadata.html#sqlalchemy.schema.Table), [Column](https://docs.sqlalchemy.org/en/20/core/metadata.html#sqlalchemy.schema.Column), serve as their own inspection objects,
  meaning any of these objects passed to [inspect()](#sqlalchemy.inspect) return themselves.
- `object` - an object given will be checked by the ORM for a mapping -
  if so, an [InstanceState](https://docs.sqlalchemy.org/en/20/orm/internals.html#sqlalchemy.orm.InstanceState) is returned representing the mapped
  state of the object.  The [InstanceState](https://docs.sqlalchemy.org/en/20/orm/internals.html#sqlalchemy.orm.InstanceState) also provides access
  to per attribute state via the [AttributeState](https://docs.sqlalchemy.org/en/20/orm/internals.html#sqlalchemy.orm.AttributeState) interface as well
  as the per-flush “history” of any attribute via the [History](https://docs.sqlalchemy.org/en/20/orm/session_api.html#sqlalchemy.orm.attributes.History)
  object.
  See also
  [Inspection of Mapped Instances](https://docs.sqlalchemy.org/en/20/orm/mapping_styles.html#orm-mapper-inspection-instancestate)
- `type` (i.e. a class) - a class given will be checked by the ORM for a
  mapping - if so, a [Mapper](https://docs.sqlalchemy.org/en/20/orm/mapping_api.html#sqlalchemy.orm.Mapper) for that class is returned.
  See also
  [Inspection of Mapper objects](https://docs.sqlalchemy.org/en/20/orm/mapping_styles.html#orm-mapper-inspection-mapper)
- mapped attribute - passing a mapped attribute to [inspect()](#sqlalchemy.inspect), such
  as `inspect(MyClass.some_attribute)`, returns a [QueryableAttribute](https://docs.sqlalchemy.org/en/20/orm/internals.html#sqlalchemy.orm.QueryableAttribute)
  object, which is the [descriptor](https://docs.sqlalchemy.org/en/20/glossary.html#term-descriptor) associated with a mapped class.
  This descriptor refers to a [MapperProperty](https://docs.sqlalchemy.org/en/20/orm/internals.html#sqlalchemy.orm.MapperProperty), which is usually
  an instance of [ColumnProperty](https://docs.sqlalchemy.org/en/20/orm/internals.html#sqlalchemy.orm.ColumnProperty)
  or [RelationshipProperty](https://docs.sqlalchemy.org/en/20/orm/internals.html#sqlalchemy.orm.RelationshipProperty), via its `QueryableAttribute.property`
  attribute.
- [AliasedClass](https://docs.sqlalchemy.org/en/20/orm/queryguide/api.html#sqlalchemy.orm.util.AliasedClass) - returns an [AliasedInsp](https://docs.sqlalchemy.org/en/20/orm/queryguide/api.html#sqlalchemy.orm.util.AliasedInsp) object.
