Thanks Mike. That ARRAY_D class did the trick. Thanks for pointing it out.
On Sunday, May 22, 2016 at 11:52:11 PM UTC-4, Mike Bayer wrote: > > > > On 05/22/2016 07:12 PM, Brian Cherinka wrote: > > > > What's the proper way to return in an ORM query the value of a Postgres > > array attribute at a given specific index within the array? > > > > I have a db table with a column called value, which is a 2d array, > > defined as REAL[][]. > > > > My ModelClass is defined as > > > > | > > classEmLine(Base): > > __tablename__ ='emline' > > __table_args__ ={'autoload':True,'schema':'dapdb'} > > > > def__repr__(self): > > return'<EmLine (pk={0})'.format(self.pk) > > > > value =Column(ARRAY(Float,dimensions=2,zero_indexes=True)) > > ivar =Column(ARRAY(Float,dimensions=2,zero_indexes=True)) > > mask =Column(ARRAY(Integer,dimensions=2,zero_indexes=True)) > > > > | > > > > Pure SQL indexing an array works just fine > > | > > selecte.value[16][17]fromdapdb.emline ase; > > | > > > > But SQLalchemy does not > > | > > session.query(dapdb.EmLine.value[16][17]).first() > > | > > > > returns the error > > | > > NotImplementedError:Operator'getitem'isnotsupported on thisexpression > > this is a bug that's been fixed for 1.1. It's detailed here: > > http://docs.sqlalchemy.org/en/latest/changelog/migration_11.html#correct-sql-types-are-established-from-indexed-access-of-array-json-hstore > > > For multi-dimensional access, this can be worked around for a one-off > using type_coerce: > > >>> from sqlalchemy import type_coerce > >>> type_coerce(c[4], ARRAY(Integer))[5] > > There is also a generalized workaround created for the bug that you can > see at > https://bitbucket.org/zzzeek/sqlalchemy/issues/3487#comment-20200804 . > It involves creation of an ARRAY subclass that does the right thing > within __getitem__. That subclass can be a drop-in replacement for > regular ARRAY. > > > > > > > > > > | > > > > I've tried defining a hybrid method/expression in my ModelClass, and > running > > | > > session.query(dapdb.EmLine.singleat('value',16,17)).first() > > | > > > > but I'm getting the same "getitem" error > > > > | > > classEmLine(Base): > > __tablename__ ='emline' > > __table_args__ ={'autoload':True,'schema':'mangadapdb'} > > > > def__repr__(self): > > return'<EmLine (pk={0})'.format(self.pk) > > > > value =Column(ARRAY(Float,dimensions=2,zero_indexes=True)) > > ivar =Column(ARRAY(Float,dimensions=2,zero_indexes=True)) > > mask =Column(ARRAY(Integer,dimensions=2,zero_indexes=True)) > > > > @hybrid_method > > defsingleat(self,name,x,y): > > param =self.__getattribute__(name) > > returnparam[x][y] > > > > @singleat.expression > > defsingleat(cls,name,x,y): > > param =cls.__getattribute__(cls,name) > > print(param,x,y) > > returnfunc.ARRAY(param)[x][y] > > > > | > > > > In my singleat expression, I've tried a variety of returns. return > > func.ARRAY(param)[x][y] ; return param[x][y]. What's the proper syntax > > to match the actual SQL array indexing? > > > > > > > -- 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.