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

[sector119]

Hello,

I try to rewrite "meters_readings[generate_subscripts(meters_readings, 1)][1]" from plain sql query that works 
with sqlalchemy and Database.meters_readings[func.generate_subscripts(Database.meters_readings, 1)][1] doesn't work
I get NotImplementedError: Operator 'getitem' is not supported on this expression

Is it possible?

def get_person_meters_q(dbsession, person_id):
    database = dbsession.query(
            Database.person_id,
            Database.service_id,
            Database.person_id_internal,
            Database.meters_readings,
            (Database.meters_readings[func.generate_subscripts(Database.meters_readings, 1)][1]).label('meter_id'),
            (Database.meters_readings[func.generate_subscripts(Database.meters_readings, 1)][2]).label('organization_reading'),
            Database.date
        ).subquery()

    meter_readings = dbsession.query(
            MeterReading.user_id,
            MeterReading.reading
        ). \
        distinct(
            MeterReading.service_id,
            MeterReading.person_id_internal,
            MeterReading.meter_id). \
        filter(
            and_(
                MeterReading.person_id == database.c.person_id,
                MeterReading.service_id == database.c.service_id,
                MeterReading.meter_id == database.c.meter_id,
                MeterReading.commit_date > database.c.date,
                MeterReading.rollback_date == None,
                MeterReading.reading != None
            )
        ). \
        order_by(
            MeterReading.service_id,
            MeterReading.person_id_internal,
            MeterReading.meter_id,
            MeterReading.commit_date.desc(),
            MeterReading.commit_time.desc()
        ).subquery().lateral()

    q = dbsession.query(
            meter_readings,
            database
        ). \
        select_from(database). \
        outerjoin(meter_readings, true()). \
        filter(Database.person_id == person_id)

    return q

  File "/home/sector119/PycharmProjects/epsilon/epsilon/scripts/testdb.py", line 98, in get_person_meters_q

    (Database.meters_readings[func.generate_subscripts(Database.meters_readings, 1)][1]).label('meter_id'),

  File "/home/sector119/PythonVirtualEnv/epsilon/lib/python3.5/site-packages/sqlalchemy/sql/operators.py", line 411, in __getitem__

    return self.operate(getitem, index)

  File "/home/sector119/PythonVirtualEnv/epsilon/lib/python3.5/site-packages/sqlalchemy/sql/elements.py", line 692, in operate

    return op(self.comparator, *other, **kwargs)

  File "/home/sector119/PythonVirtualEnv/epsilon/lib/python3.5/site-packages/sqlalchemy/sql/operators.py", line 411, in __getitem__

    return self.operate(getitem, index)

  File "<string>", line 1, in <lambda>

  File "/home/sector119/PythonVirtualEnv/epsilon/lib/python3.5/site-packages/sqlalchemy/sql/type_api.py", line 63, in operate

    return o[0](self.expr, op, *(other + o[1:]), **kwargs)

  File "/home/sector119/PythonVirtualEnv/epsilon/lib/python3.5/site-packages/sqlalchemy/sql/default_comparator.py", line 192, in _getitem_impl

    _unsupported_impl(expr, op, other, **kw)

  File "/home/sector119/PythonVirtualEnv/epsilon/lib/python3.5/site-packages/sqlalchemy/sql/default_comparator.py", line 197, in _unsupported_impl

    "this expression" % op.__name__)

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

[Mike Bayer]

On Thu, Jan 4, 2018 at 6:49 AM, sector119 <sect...@gmail.com> wrote:
> Hello,
>
>
> I try to rewrite "meters_readings[generate_subscripts(meters_readings,
> 1)][1]" from plain sql query that works
>
> with sqlalchemy and
> Database.meters_readings[func.generate_subscripts(Database.meters_readings,
> 1)][1] doesn't work

your func.generate_subscripts() function doesn't know that it needs to
return a datatype that fulfills the "Indexable" contract, that of
ARRAY, JSON and HSTORE (though PG documents this as returning "setof",
that supports indexing? shrugs). You need to give it the type you
need, which I guess in this case the closest is ARRAY:

>>> f = func.generate_subscripts(column('q'), 1, type_=ARRAY(Integer))
>>> print f[5]
(generate_subscripts(q, :generate_subscripts_1))[:generate_subscripts_2]

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[sector119]

Thank you, Mike, but I need something like this

type_coerce(

    Database.meters_readings[

        func.generate_subscripts(Database.meters_readings, 1)

    ], type_=ARRAY(Text)

)[1]

Database.meters_readings is two-dimensional array like [ ['1', '2', '3'], ['4', '5', '6'] ], so

Database.meters_readings[func.generate_subscripts.....] must return ['1', '2', '3'] or ['4', '5', '6'],
and than I want to get 1-st or second element of this "sub-array"

func.generate_subscripts have to return int and it is Ok, but why I can't get that "sub-array" without type_coerce? 

четверг, 4 января 2018 г., 17:48:56 UTC+2 пользователь Mike Bayer написал:

[Mike Bayer]

On Thu, Jan 4, 2018 at 3:33 PM, sector119 <sect...@gmail.com> wrote:
> Thank you, Mike, but I need something like this
>
>
> type_coerce(
>
> Database.meters_readings[
>
> func.generate_subscripts(Database.meters_readings, 1)
>
> ], type_=ARRAY(Text)
>
> )[1]
>
>
> Database.meters_readings is two-dimensional array like [ ['1', '2', '3'],
> ['4', '5', '6'] ], so

okey doke, give your array two dimensions:

>>> f = func.generate_subscripts(column('x'), type_=ARRAY(Integer, dimensions=2))
>>> print f[1][2]
(generate_subscripts(x))[:generate_subscripts_1][:param_1]