sqlalchemy.sql.sqltypes.ARRAY

Represent a SQL Array type. .. note:: This type serves as the basis for all ARRAY operations. However, currently **only the PostgreSQL backend has support for SQL arrays in SQLAlchemy**. It is recommended to use the PostgreSQL-specific :class:`sqlalchemy.dialects.postgresql.ARRAY` type directly when using ARRAY types with PostgreSQL, as it provides additional operators specific to that backend. :class:`_types.ARRAY` is part of the Core in support of various SQL standard functions such as :class:`_functions.array_agg` which explicitly involve arrays; however, with the exception of the PostgreSQL backend and possibly some third-party dialects, no other SQLAlchemy built-in dialect has support for this type. An :class:`_types.ARRAY` type is constructed given the "type" of element:: mytable = Table("mytable", metadata, Column("data", ARRAY(Integer)) ) The above type represents an N-dimensional array, meaning a supporting backend such as PostgreSQL will interpret values with any number of dimensions automatically. To produce an INSERT construct that passes in a 1-dimensional array of integers:: connection.execute( mytable.insert(), {"data": [1,2,3]} ) The :class:`_types.ARRAY` type can be constructed given a fixed number of dimensions:: mytable = Table("mytable", metadata, Column("data", ARRAY(Integer, dimensions=2)) ) Sending a number of dimensions is optional, but recommended if the datatype is to represent arrays of more than one dimension. This number is used: * When emitting the type declaration itself to the database, e.g. ``INTEGER[][]`` * When translating Python values to database values, and vice versa, e.g. an ARRAY of :class:`.Unicode` objects uses this number to efficiently access the string values inside of array structures without resorting to per-row type inspection * When used with the Python ``getitem`` accessor, the number of dimensions serves to define the kind of type that the ``[]`` operator should return, e.g. for an ARRAY of INTEGER with two dimensions:: >>> expr = table.c.column[5] # returns ARRAY(Integer, dimensions=1) >>> expr = expr[6] # returns Integer For 1-dimensional arrays, an :class:`_types.ARRAY` instance with no dimension parameter will generally assume single-dimensional behaviors. SQL expressions of type :class:`_types.ARRAY` have support for "index" and "slice" behavior. The Python ``[]`` operator works normally here, given integer indexes or slices. Arrays default to 1-based indexing. The operator produces binary expression constructs which will produce the appropriate SQL, both for SELECT statements:: select(mytable.c.data[5], mytable.c.data[2:7]) as well as UPDATE statements when the :meth:`_expression.Update.values` method is used:: mytable.update().values({ mytable.c.data[5]: 7, mytable.c.data[2:7]: [1, 2, 3] }) The :class:`_types.ARRAY` type also provides for the operators :meth:`.types.ARRAY.Comparator.any` and :meth:`.types.ARRAY.Comparator.all`. The PostgreSQL-specific version of :class:`_types.ARRAY` also provides additional operators. .. container:: topic **Detecting Changes in ARRAY columns when using the ORM** The :class:`_sqltypes.ARRAY` type, when used with the SQLAlchemy ORM, does not detect in-place mutations to the array. In order to detect these, the :mod:`sqlalchemy.ext.mutable` extension must be used, using the :class:`.MutableList` class:: from sqlalchemy import ARRAY from sqlalchemy.ext.mutable import MutableList class SomeOrmClass(Base): # ... data = Column(MutableList.as_mutable(ARRAY(Integer))) This extension will allow "in-place" changes such to the array such as ``.append()`` to produce events which will be detected by the unit of work. Note that changes to elements **inside** the array, including subarrays that are mutated in place, are **not** detected. Alternatively, assigning a new array value to an ORM element that replaces the old one will always trigger a change event. .. versionadded:: 1.1.0 .. seealso:: :class:`sqlalchemy.dialects.postgresql.ARRAY`