Re: [sqlalchemy] NotImplementedError: Operator 'getitem' is not supported on this expression

2018-01-04 Thread Mike Bayer 
On Thu, Jan 4, 2018 at 3:33 PM, sector119  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]



>
> 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  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 "", line 1, in 
>> >   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 +

Re: [sqlalchemy] NotImplementedError: Operator 'getitem' is not supported on this expression

2018-01-04 Thread 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 написал:
>
> On Thu, Jan 4, 2018 at 6:49 AM, sector119  
> 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 "", line 1, in  
> >   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 
> > 
>

Re: [sqlalchemy] NotImplementedError: Operator 'getitem' is not supported on this expression

2018-01-04 Thread Mike Bayer 
On Thu, Jan 4, 2018 at 6:49 AM, sector119  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 "", line 1, in 
>   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+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.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and

[sqlalchemy] NotImplementedError: Operator 'getitem' is not supported on this expression

2018-01-04 Thread 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 "", line 1, in 
  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+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.