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 написал:
>
> On Thu, Jan 4, 2018 at 6:49 AM, sector119 <sect...@gmail.com <javascript:>>
> 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 sqlalchemy+...@googlegroups.com <javascript:>.
> > To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>.
> > 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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
