On Thu, Jan 4, 2018 at 6:49 AM, sector119 <[email protected]> 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]
>
> 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
>
> --
> 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 [email protected].
> To post to this group, send email to [email protected].
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
