sqlalchemy.dialects.postgresql.ARRAY

class documentation

class ARRAY(sqltypes.ARRAY):

Known subclasses: sqlalchemy.dialects.postgresql.pg8000._PGARRAY, sqlalchemy.dialects.postgresql.psycopg2._PGARRAY

PostgreSQL ARRAY type.

Changed in version 1.1: The _postgresql.ARRAY type is now a subclass of the core _types.ARRAY type.

The _postgresql.ARRAY type is constructed in the same way as the core _types.ARRAY type; a member type is required, and a number of dimensions is recommended if the type is to be used for more than one dimension:

from sqlalchemy.dialects import postgresql

mytable = Table("mytable", metadata,
        Column("data", postgresql.ARRAY(Integer, dimensions=2))
    )

The _postgresql.ARRAY type provides all operations defined on the core _types.ARRAY type, including support for "dimensions", indexed access, and simple matching such as .types.ARRAY.Comparator.any and .types.ARRAY.Comparator.all. _postgresql.ARRAY class also provides PostgreSQL-specific methods for containment operations, including .postgresql.ARRAY.Comparator.contains .postgresql.ARRAY.Comparator.contained_by, and .postgresql.ARRAY.Comparator.overlap, e.g.:

mytable.c.data.contains([1, 2])

The _postgresql.ARRAY type may not be supported on all PostgreSQL DBAPIs; it is currently known to work on psycopg2 only.

Additionally, the _postgresql.ARRAY type does not work directly in conjunction with the .ENUM type. For a workaround, see the special type at :ref:`postgresql_array_of_enum`.

See Also

_types.ARRAY - base array type

_postgresql.array - produces a literal array value.

Class	`Comparator`	Define comparison operations for `_types.ARRAY`.
Method	`__init__`	Construct an ARRAY.
Method	`_proc_array`	Undocumented
Method	`bind_expression`	Given a bind value (i.e. a `.BindParameter` instance), return a SQL expression in its place.
Method	`bind_processor`	Return a conversion function for processing bind values.
Method	`compare_values`	Compare two values for equality.
Method	`result_processor`	Return a conversion function for processing result row values.
Instance Variable	`as_tuple`	Undocumented
Instance Variable	`dimensions`	Undocumented
Instance Variable	`item_type`	Undocumented
Instance Variable	`zero_indexes`	If True, Python zero-based indexes should be interpreted as one-based on the SQL expression side.
Property	`_against_native_enum`	Undocumented
Property	`hashable`	Flag, if False, means values from this type aren't hashable.
Property	`python_type`	Return the Python type object expected to be returned by instances of this type, if known.

Inherited from ARRAY:

Method	`_set_parent`	Support SchemaEventTarget
Method	`_set_parent_with_dispatch`	Support SchemaEventTarget
Class Variable	`__visit_name__`	Undocumented
Class Variable	`_is_array`	Undocumented

Inherited from TypeEngine (via ARRAY):

Method	`adapt`	Produce an "adapted" form of this type, given an "impl" class to work with.
Method	`as_generic`	Return an instance of the generic type corresponding to this type using heuristic rule. The method may be overridden if this heuristic rule is not sufficient.
Method	`coerce_compared_value`	Suggest a type for a 'coerced' Python value in an expression.
Method	`column_expression`	Given a SELECT column expression, return a wrapping SQL expression.
Method	`compare_against_backend`	Compare this type against the given backend type.
Method	`compile`	Produce a string-compiled form of this `.TypeEngine`.
Method	`dialect_impl`	Return a dialect-specific implementation for this `.TypeEngine`.
Method	`evaluates_none`	Return a copy of this type which has the `.should_evaluate_none` flag set to True.
Method	`get_dbapi_type`	Return the corresponding type object from the underlying DB-API, if any.
Method	`literal_processor`	Return a conversion function for processing literal values that are to be rendered directly without using binds.
Method	`with_variant`	Produce a new type object that will utilize the given type when applied to the dialect of the given name.
Class Variable	`should_evaluate_none`	If True, the Python constant `None` is considered to be handled explicitly by this type.
Class Variable	`sort_key_function`	A sorting function that can be passed as the key to sorted.
Static Method	`_to_instance`	Undocumented
Method	`__repr__`	Undocumented
Method	`__str__`	Undocumented
Method	`_cached_bind_processor`	Return a dialect-specific bind processor for this type.
Method	`_cached_custom_processor`	Undocumented
Method	`_cached_literal_processor`	Return a dialect-specific literal processor for this type.
Method	`_cached_result_processor`	Return a dialect-specific result processor for this type.
Method	`_compare_type_affinity`	Undocumented
Method	`_default_dialect`	Undocumented
Method	`_dialect_info`	Return a dialect-specific registry which caches a dialect-specific implementation, bind processing function, and one or more result processing functions.
Method	`_gen_dialect_impl`	Undocumented
Method	`_resolve_for_literal`	adjust this type given a literal Python value that will be stored in a bound parameter.
Method	`_unwrapped_dialect_impl`	Return the 'unwrapped' dialect impl for this type.
Method	`copy`	Undocumented
Method	`copy_value`	Undocumented
Class Variable	`_is_table_value`	Undocumented
Class Variable	`_is_tuple_type`	Undocumented
Class Variable	`_is_type_decorator`	Undocumented
Class Variable	`_isnull`	Undocumented
Class Variable	`_sqla_type`	Undocumented
Property	`_generic_type_affinity`	Undocumented
Property	`_has_bind_expression`	memoized boolean, check if bind_expression is implemented.
Property	`_has_column_expression`	memoized boolean, check if column_expression is implemented.
Property	`_static_cache_key`	Undocumented
Property	`_type_affinity`	Return a rudimental 'affinity' value expressing the general class of type.

Inherited from Traversible (via ARRAY, TypeEngine):

Method	`get_children`	Return immediate child `.visitors.Traversible` elements of this `.visitors.Traversible`.
Method	`__class_getitem__`	Undocumented

def __init__(self, item_type, as_tuple=False, dimensions=None, zero_indexes=False):

overrides sqlalchemy.types.ARRAY.__init__

Construct an ARRAY.

E.g.:

Column('myarray', ARRAY(Integer))

Arguments are:

Parameters
item_type	The data type of items of this array. Note that dimensionality is irrelevant here, so multi-dimensional arrays like `INTEGER[][]`, are constructed as `ARRAY(Integer)`, not as `ARRAY(ARRAY(Integer))` or such.
as_tuple	Undocumented
dimensions	if non-None, the ARRAY will assume a fixed number of dimensions. This will cause the DDL emitted for this ARRAY to include the exact number of bracket clauses `[]`, and will also optimize the performance of the type overall. Note that PG arrays are always implicitly "non-dimensioned", meaning they can store any number of dimensions no matter how they were declared.
zero_indexes	Undocumented
as_tuple=False	Specify whether return results should be converted to tuples from lists. DBAPIs such as psycopg2 return lists by default. When tuples are returned, the results are hashable.
zero_indexes=False	when True, index values will be converted between Python zero-based and PostgreSQL one-based indexes, e.g. a value of one will be added to all index values before passing to the database. New in version 0.9.5.

def _proc_array(self, arr, itemproc, dim, collection):

Undocumented

def bind_expression(self, bindvalue):

overrides sqlalchemy.types.TypeEngine.bind_expression

overridden in sqlalchemy.dialects.postgresql.pg8000._PGARRAY, sqlalchemy.dialects.postgresql.psycopg2._PGARRAY

Given a bind value (i.e. a .BindParameter instance), return a SQL expression in its place.

This is typically a SQL function that wraps the existing bound parameter within the statement. It is used for special data types that require literals being wrapped in some special database function in order to coerce an application-level value into a database-specific format. It is the SQL analogue of the .TypeEngine.bind_processor method.

This method is called during the SQL compilation phase of a statement, when rendering a SQL string. It is not called against specific values.

Note that this method, when implemented, should always return the exact same structure, without any conditional logic, as it may be used in an executemany() call against an arbitrary number of bound parameter sets.

Note

This method is only called relative to a dialect specific type object, which is often private to a dialect in use and is not the same type object as the public facing one, which means it's not feasible to subclass a .types.TypeEngine class in order to provide an alternate _types.TypeEngine.bind_expression method, unless subclassing the _types.UserDefinedType class explicitly.

To provide alternate behavior for _types.TypeEngine.bind_expression, implement a _types.TypeDecorator class and provide an implementation of _types.TypeDecorator.bind_expression.

See Also

:ref:`types_typedecorator`

See Also

:ref:`types_sql_value_processing`

def bind_processor(self, dialect):

overrides sqlalchemy.types.TypeEngine.bind_processor

Return a conversion function for processing bind values.

Returns a callable which will receive a bind parameter value as the sole positional argument and will return a value to send to the DB-API.

If processing is not necessary, the method should return None.

Note

This method is only called relative to a dialect specific type object, which is often private to a dialect in use and is not the same type object as the public facing one, which means it's not feasible to subclass a .types.TypeEngine class in order to provide an alternate _types.TypeEngine.bind_processor method, unless subclassing the _types.UserDefinedType class explicitly.

To provide alternate behavior for _types.TypeEngine.bind_processor, implement a _types.TypeDecorator class and provide an implementation of _types.TypeDecorator.process_bind_param.

See Also

:ref:`types_typedecorator`

Parameters
dialect	Dialect instance in use.

def compare_values(self, x, y):

overrides sqlalchemy.types.ARRAY.compare_values

Compare two values for equality.

def result_processor(self, dialect, coltype):

overrides sqlalchemy.types.TypeEngine.result_processor

Return a conversion function for processing result row values.

Returns a callable which will receive a result row column value as the sole positional argument and will return a value to return to the user.

If processing is not necessary, the method should return None.

Note

This method is only called relative to a dialect specific type object, which is often private to a dialect in use and is not the same type object as the public facing one, which means it's not feasible to subclass a .types.TypeEngine class in order to provide an alternate _types.TypeEngine.result_processor method, unless subclassing the _types.UserDefinedType class explicitly.

To provide alternate behavior for _types.TypeEngine.result_processor, implement a _types.TypeDecorator class and provide an implementation of _types.TypeDecorator.process_result_value.

See Also

:ref:`types_typedecorator`

Parameters
dialect	Dialect instance in use.
coltype	DBAPI coltype argument received in cursor.description.

as_tuple =

overrides sqlalchemy.types.ARRAY.as_tuple

Undocumented

dimensions =

overrides sqlalchemy.types.ARRAY.dimensions

Undocumented

item_type =

overrides sqlalchemy.types.ARRAY.item_type

Undocumented

zero_indexes =

overrides sqlalchemy.types.ARRAY.zero_indexes

If True, Python zero-based indexes should be interpreted as one-based on the SQL expression side.

@util.memoized_property
_against_native_enum =

Undocumented

@property
hashable =

overrides sqlalchemy.types.ARRAY.hashable

Flag, if False, means values from this type aren't hashable.

Used by the ORM when uniquing result lists.

@property
python_type =

overrides sqlalchemy.types.ARRAY.python_type

Return the Python type object expected to be returned by instances of this type, if known.

Basically, for those types which enforce a return type, or are known across the board to do such for all common DBAPIs (like int for example), will return that type.

If a return type is not defined, raises NotImplementedError.

Note that any type also accommodates NULL in SQL which means you can also get back None from any type in practice.