support direct mult-dimensional ARRAY access based on explicit dimensions

[Andrey Semenov]

Here's just an example:

    weights = Column(ARRAY(DECIMAL(precision=2, asdecimal=False), dimensions=2), nullable=False, default=[])

then in query:

CallServiceCampaign.weights[
    func.idx(CallServiceCampaign.goods_ids, literal(goods_id))
][1] != None,

produces:

NotImplementedError: Operator 'getitem' is not supported on this expression

I think this is because https://bitbucket.org/zzzeek/sqlalchemy/src/447ee0af1d2fbb95f2f1244de301f2fe4a87a72f/lib/sqlalchemy/dialects/postgresql/base.py?at=rel_1_0_6#cl-911 doesn't handle dimensions parameter and always returns the underlying array's class on the first non-sliced reference.

[Mike Bayer]

Note that a pg ARRAY with no explicit dimensions is N-dimensional, and I'm not sure if that can be handled without explicit casts.

Below, please use the ARRAY_D workaround for now.

from sqlalchemy.dialects.postgresql import ARRAY, dialect
from sqlalchemy import Integer, column, type_coerce, func

c = column('x', ARRAY(Integer(), dimensions=2))

print c[5].type  # produces INTEGER, should be ARRAY(INTEGER, dimensions=1)


# fails because we aren't an array

try:
    print(c[5][2].type)
except NotImplementedError as e:
    print e

# workaround
print type_coerce(c[5], ARRAY(Integer))[2].type

# better workaround


class ARRAY_D(ARRAY):
    class Comparator(ARRAY.Comparator):
        def __getitem__(self, index):
            super_ = super(ARRAY_D.Comparator, self).__getitem__(index)
            if not isinstance(index, slice) and self.type.dimensions > 1:
                super_ = type_coerce(
                    super_,
                    ARRAY_D(
                        self.type.item_type,
                        dimensions=self.type.dimensions - 1)
                )
            return super_
    comparator_factory = Comparator

c2 = column('x', ARRAY_D(Integer(), dimensions=2))
print c2[5][2].type

print(
    c2[func.idx(column('q'))][2].compile(dialect=dialect())
)

[Mike Bayer]

• add a note, references #3487

→ <<cset eed8bcef3ae1>>

[Mike Bayer]

see also #3499, where we have similar issues with JSON. We need to determine for indexable types a clear picture for what type the return values should be. For ARRAY it is fairly simple. For a JSON, perhaps some option to specify a schema, though that seems quite ominous.

[Mike Bayer]

all of the above is working as of https://bitbucket.org/zzzeek/sqlalchemy/src/39adada25d71ac33d2a468cc19eb61c0fdfa9280/?at=ticket_3499, not merged yet

[Mike Bayer]

• merge of ticket_3499 indexed access branch
• The "hashable" flag on special datatypes such as :class:.postgresql.ARRAY, :class:.postgresql.JSON and :class:.postgresql.HSTORE is now set to False, which allows these types to be fetchable in ORM queries that include entities within the row. fixes #3499
• The Postgresql :class:.postgresql.ARRAY type now supports multidimensional indexed access, e.g. expressions such as somecol[5][6] without any need for explicit casts or type coercions, provided that the :paramref:.postgresql.ARRAY.dimensions parameter is set to the desired number of dimensions. fixes #3487
• The return type for the :class:.postgresql.JSON and :class:.postgresql.JSONB when using indexed access has been fixed to work like Postgresql itself, and returns an expression that itself is of type :class:.postgresql.JSON or :class:.postgresql.JSONB. Previously, the accessor would return :class:.NullType which disallowed subsequent JSON-like operators to be used. part of fixes #3503
• The :class:.postgresql.JSON, :class:.postgresql.JSONB and :class:.postgresql.HSTORE datatypes now allow full control over the return type from an indexed textual access operation, either column[someindex].astext for a JSON type or column[someindex] for an HSTORE type, via the :paramref:.postgresql.JSON.astext_type and :paramref:.postgresql.HSTORE.text_type parameters. also part of fixes #3503
• The :attr:.postgresql.JSON.Comparator.astext modifier no longer calls upon :meth:.ColumnElement.cast implicitly, as PG's JSON/JSONB types allow cross-casting between each other as well. Code that makes use of :meth:.ColumnElement.cast on JSON indexed access, e.g. col[someindex].cast(Integer), will need to be changed to call :attr:.postgresql.JSON.Comparator.astext explicitly. This is part of the refactor in references #3503 for consistency in operator use.

→ <<cset ceeb033054f0>>