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
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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.
