sqlalchemy.types.ARRAY

Represent a SQL Array type.

_types.ARRAY is part of the Core in support of various SQL standard functions such as _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 _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 _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 .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 _types.ARRAY instance with no dimension parameter will generally assume single-dimensional behaviors.

SQL expressions of type _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 _expression.Update.values method is used:

mytable.update().values({
    mytable.c.data[5]: 7,
    mytable.c.data[2:7]: [1, 2, 3]
})

The _types.ARRAY type also provides for the operators .types.ARRAY.Comparator.any and .types.ARRAY.Comparator.all. The PostgreSQL-specific version of _types.ARRAY also provides additional operators.