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 [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.
