you might need to change more than this, but at least the fundamental
thing about @expression is that it has to return a column, not a Query
or a select(). On either one, calling as_scalar() will give you a
scalar subquery, e.g. a SELECT interpreted as a column.
Assuming there's still problems because once array_agg is involved,
things generally get crazy, send along a Wavelength, NSA and MangaNSA
model with that Cube and I can try putting it together.
On 07/29/2016 02:11 PM, Brian Cherinka wrote:
I'm trying to build a hybrid property / expression in one of my SQLA
base classes, and the expression side requires several subqueries and I
cannot for the life of me figure out the correct syntax. I've tried
many different versions / syntaxes for the expression, using the SQL
alchemy expression language, the ORM language but I can't get any of it
work. What am I missing here? Here is my latest attempt.
The setup is this. I have a class called Cube, which is my base object.
For each object in that cube table, I need to grab an attribute from a
table A and use that attribute to modify an array column from a second
table B. And I want to make this new array queryable, thus the
hybridization.
The equivalent raw SQL is
|
select(select(array_agg(unwave.restw))asrestwarr
from(select(unnest(w.wavelength)/(1+n.z))asrestw
frommangadatadb.wavelength asw)asunwave)frommangadatadb.cube asc join
mangasampledb.manga_target asm on m.pk=c.manga_target_pk join
mangasampledb.manga_target_to_nsa ast on t.manga_target_pk=m.pk join
mangasampledb.nsa asn on n.pk=t.nsa_pk join mangadatadb.wavelength asw
on w.pk=c.wavelength_pk limit 5;
|
The instance side is quite easy. The class side is proving difficult.
What is the proper way to write this? I've read through the
documentation countless times now and it hasn't helped much.
|
classCube
@hybrid_property
defrestwave(self):
ifself.target:
redshift =self.target.NSA_objects[0].z
wave =np.array(self.wavelength.wavelength)
restwave =wave/(1+redshift)
returnrestwave
else:
returnNone
@restwave.expression
defrestwave(cls):
session =db.Session()
nsaz =session.query(sampledb.NSA.z.label('z')).\
join(sampledb.MangaTargetToNSA,sampledb.MangaTarget,Cube).\
filter(Cube.mangaid
==cls.mangaid).subquery('nsaz',with_labels=True)
unwave
=session.query((func.unnest(Wavelength.wavelength)/(1+nsaz.c.z)).label('restw')).subquery('unwave',with_labels=True)
restwave =session.query(func.array_agg(unwave.c.restw))
returnrestwave
|
Trying
|
session.query(datadb.Cube.restwave).filter(datadb.Cube.mangaid=='1-113520').all()
|
fails with
|
AttributeError:'Query'objecthas noattribute 'is_clause_element'
|
I've also tried this.
|
@restwave.expression
defrestwave(cls):
unwave
=select([(func.unnest(Wavelength.wavelength)/(1+sampledb.NSA.z)).label('restw')])
restwave
=select([func.array_agg(unwave.c.restw).label('restwarr')]).select_from(
Cube.__table__.join(Wavelength.wavelength).
join(sampledb.MangaTarget).
join(sampledb.MangaTargetToNSA).
join(sampledb.NSA)
)
returnrestwave
|
and this fails
|
In[6]:datadb.Cube.restwave
---------------------------------------------------------------------------
ArgumentError Traceback(most recent call last)
<ipython-input-6-16300d165395>in<module>()
---->1datadb.Cube.restwave
/Users/Brian/anaconda2/lib/python2.7/site-packages/sqlalchemy/ext/hybrid.pyc
in__get__(self,instance,owner)
738 def__get__(self,instance,owner):
739 ifinstance isNone:
-->740 returnself.expr(owner)
741 else:
742 returnself.fget(instance)
/Users/Brian/Work/sdss/repo/sdss/sdss_python_module/marvin/python/sdss/internal/database/utah/mangadb/DataModelClasses.pyc
inrestwave(cls)
454 # unwave =
select([(func.unnest(Wavelength.wavelength)/(1+sampledb.NSA.z)).label('restw')])
455 # restwave =
select([func.array_agg(unwave.c.restw).label('restwarr')]).select_from(
-->456 # Cube.__table__.join(Wavelength.wavelength).
457 # join(sampledb.MangaTarget).
458 # join(sampledb.MangaTargetToNSA).
/Users/Brian/anaconda2/lib/python2.7/site-packages/sqlalchemy/sql/selectable.pyc
injoin(self,right,onclause,isouter)
350 """
351
--> 352 return Join(self, right, onclause, isouter)
353
354 def outerjoin(self, right, onclause=None):
/Users/Brian/anaconda2/lib/python2.7/site-packages/sqlalchemy/sql/selectable.pyc
in __init__(self, left, right, onclause, isouter)
658 """
659 self.left =_interpret_as_from(left)
-->660 self.right =_interpret_as_from(right).self_group()
661
662 ifonclause isNone:
/Users/Brian/anaconda2/lib/python2.7/site-packages/sqlalchemy/sql/selectable.pyc
in_interpret_as_from(element)
47 returninsp.selectable
48 exceptAttributeError:
--->49 raiseexc.ArgumentError("FROM expression expected")
50
51
ArgumentError:FROM expression expected
|
--
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.
