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)) as restwarr from (select (unnest(w.
wavelength)/(1+n.z)) as restw from mangadatadb.wavelength as w) as unwave)
from mangadatadb.cube as c join mangasampledb.manga_target as m on
m.pk=c.manga_target_pk
join mangasampledb.manga_target_to_nsa as t on t.manga_target_pk=m.pk join
mangasampledb.nsa as n on n.pk=t.nsa_pk join mangadatadb.wavelength as w 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.
class Cube
@hybrid_property
def restwave(self):
if self.target:
redshift = self.target.NSA_objects[0].z
wave = np.array(self.wavelength.wavelength)
restwave = wave/(1+redshift)
return restwave
else:
return None
@restwave.expression
def restwave(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))
return restwave
Trying
session.query(datadb.Cube.restwave).filter(datadb.Cube.mangaid==
'1-113520').all()
fails with
AttributeError: 'Query' object has no attribute 'is_clause_element'
I've also tried this.
@restwave.expression
def restwave(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)
)
return restwave
and this fails
In [6]: datadb.Cube.restwave
---------------------------------------------------------------------------
ArgumentError Traceback (most recent call last)
<ipython-input-6-16300d165395> in <module>()
----> 1 datadb.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 if instance is None:
--> 740 return self.expr(owner)
741 else:
742 return self.fget(instance)
/Users/Brian/Work/sdss/repo/sdss/sdss_python_module/marvin/python/sdss/
internal/database/utah/mangadb/DataModelClasses.pyc in restwave(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 in join(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 if onclause is None:
/Users/Brian/anaconda2/lib/python2.7/site-packages/sqlalchemy/sql/selectable
.pyc in _interpret_as_from(element)
47 return insp.selectable
48 except AttributeError:
---> 49 raise exc.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.
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.
