date:20160803

Re: [sqlalchemy] nested subqueries in a hybrid expression?

2016-08-03 Thread Mike Bayer

There is still much ambiguity here and inaccuracy (JOINs on the outside 
or JOINs on the inside, the mappings have mistakes like foreign key to 
"pk" but no "pk", mappings without primary keys, "autoload" makes no 
sense as I don't have your tables, etc.), so I can only guess but 
perhaps give you enough clues.   It is highly unusual to have a string 
of four JOINs inside of a column-based subquery, but when you say 
s.query(Cube.restwave), that's what that means here.


import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, deferred, sessionmaker
from sqlalchemy.schema import Column, ForeignKey
from sqlalchemy import func
from sqlalchemy.dialects.postgresql import *
from sqlalchemy.types import Float, Integer, String
from sqlalchemy.ext.hybrid import hybrid_property


Base = declarative_base()
Session = sessionmaker()


class Target(Base):
__tablename__ = 'target'

pk = Column(Integer, primary_key=True)
mangaid = Column(String)


class Wavelength(Base):
__tablename__ = 'wavelength'

pk = Column(Integer, primary_key=True)
wavelength = deferred(Column(ARRAY(Float, zero_indexes=True)))


class NSA(Base):
__tablename__ = 'nsa'

pk = Column(Integer, primary_key=True)
z = Column(Float)


class TargetToNSA(Base):
__tablename__ = 'target_to_nsa'

id = Column(Integer, primary_key=True)
target_pk = Column(Integer, ForeignKey('target.pk'))
nsa_pk = Column(Integer, ForeignKey('nsa.pk'))


class Cube(Base):
__tablename__ = 'cube'

pk = Column(Integer, primary_key=True)
mangaid = Column(String)

target_pk = Column(Integer, ForeignKey('target.pk'))
target = relationship(Target, backref='cubes')

wavelength_pk = Column(Integer, ForeignKey('wavelength.pk'))
wavelength = relationship(Wavelength, backref='cubes')

@hybrid_property
def restwave(self):
raise NotImplementedError()

@restwave.expression
def restwave(cls):
s = Session()
restw = (func.unnest(Wavelength.wavelength) / (1 + 
NSA.z)).label("restw")
unwave = 
s.query(restw).select_from(Wavelength).correlate(NSA).subquery("unwave")


agg_unwave = 
s.query(func.array_agg(unwave.c.restw)).label("restwarr")


joined = s.query(agg_unwave).select_from(
Cube
).join(Target, TargetToNSA, NSA, Wavelength)

return joined.as_scalar()


session = Session()


q = session.query(Cube.restwave).filter(Cube.mangaid == '1-113520')

print q





On 08/03/2016 02:00 PM, Brian Cherinka wrote:

Ok.  Yeah, I have been trying many different ways of getting results.
 The raw SQL that I'm trying to recreate in SQLA is this (for the
restwave column only), which works in postgresql.  The limit was only
there to do the filter the results.  You can ignore that limit.

|
manga=# select c.pk,c.mangaid,c.manga_target_pk, n.z, (select
(array_agg(unwave.restw))[0:5] as restwave from (select
(unnest(w.wavelength)/(1+n.z)) as restw from mangadatadb.wavelength as
w) as unwave)
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;
  pk   |mangaid |manga_target_pk |z  |
 restwave
---+-+-++-
 11211|1-22286|
30678| 
0.099954180419445|{3292.49709827422,3293.25529747001,3294.01371862107,3294.7723617274,3295.53100483373}
  7729|1-22286|
30678| 
0.099954180419445|{3292.49709827422,3293.25529747001,3294.01371862107,3294.7723617274,3295.53100483373}
 11209|1-22298|
15026|0.0614774264395237|{3411.84452637247,3412.63020900144,3413.41612163118,3414.2022642617,3414.98840689221}
  7727|1-22298|
15026|0.0614774264395237|{3411.84452637247,3412.63020900144,3413.41612163118,3414.2022642617,3414.98840689221}
 11219|1-22301|
 35| 
0.105152934789658|{3277.00884941768,3277.76348196558,3278.51833542465,3279.27340979488,3280.02848416512}
(5rows)
|

In SQLA,  this code returns something but it is the wrong array for the
given Cube specified in the filter condition

|
In[20]:rwquery
=session.query(datadb.Cube.restwave).filter(datadb.Cube.mangaid=='1-113520')

In[21]:printrwquery
SELECT (SELECT array_agg(unwave.restw)AS array_agg_1
FROM (SELECT unnest(mangadatadb.wavelength.wavelength)/(%(z_1)s
+nsaz.z)AS restw
FROM mangadatadb.wavelength,(SELECT mangasampledb.nsa.z AS z
FROM mangasampledb.nsa JOIN mangasampledb.manga_target_to_nsa ON
mangasampledb.nsa.pk =mangasampledb.manga_target_to_nsa.nsa_pk JOIN
mangasampledb.manga_target ON mangasampledb.manga_target.pk

Re: [sqlalchemy] nested subqueries in a hybrid expression?

2016-08-03 Thread Brian Cherinka

Ok.  Yeah, I have been trying many different ways of getting results.  The 
raw SQL that I'm trying to recreate in SQLA is this (for the restwave 
column only), which works in postgresql.  The limit was only there to do 
the filter the results.  You can ignore that limit.

manga=# select c.pk,c.mangaid,c.manga_target_pk, n.z, (select 
(array_agg(unwave.restw))[0:5] as restwave 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;
  pk   | mangaid | manga_target_pk | z  |   
 restwave
---+-+-++-
 11211 | 1-22286 |   30678 |  0.099954180419445 | {3292.49709827422,
3293.25529747001,3294.01371862107,3294.7723617274,3295.53100483373}
  7729 | 1-22286 |   30678 |  0.099954180419445 | {3292.49709827422,
3293.25529747001,3294.01371862107,3294.7723617274,3295.53100483373}
 11209 | 1-22298 |   15026 | 0.0614774264395237 | {3411.84452637247,
3412.63020900144,3413.41612163118,3414.2022642617,3414.98840689221}
  7727 | 1-22298 |   15026 | 0.0614774264395237 | {3411.84452637247,
3412.63020900144,3413.41612163118,3414.2022642617,3414.98840689221}
 11219 | 1-22301 |  35 |  0.105152934789658 | {3277.00884941768,
3277.76348196558,3278.51833542465,3279.27340979488,3280.02848416512}
(5 rows)

In SQLA,  this code returns something but it is the wrong array for the 
given Cube specified in the filter condition

In [20]: rwquery = session.query(datadb.Cube.restwave).filter(datadb.Cube.
mangaid=='1-113520')

In [21]: print rwquery
SELECT (SELECT array_agg(unwave.restw) AS array_agg_1
FROM (SELECT unnest(mangadatadb.wavelength.wavelength) / (%(z_1)s + nsaz.z) 
AS restw
FROM mangadatadb.wavelength, (SELECT mangasampledb.nsa.z AS z
FROM mangasampledb.nsa JOIN mangasampledb.manga_target_to_nsa ON 
mangasampledb.nsa.pk = mangasampledb.manga_target_to_nsa.nsa_pk JOIN 
mangasampledb.manga_target ON mangasampledb.manga_target.pk = mangasampledb.
manga_target_to_nsa.manga_target_pk JOIN mangadatadb.cube ON mangasampledb.
manga_target.pk = mangadatadb.cube.manga_target_pk
WHERE mangadatadb.cube.mangaid = mangadatadb.cube.mangaid) AS nsaz) AS 
unwave) AS anon_1
FROM mangadatadb.cube
WHERE mangadatadb.cube.mangaid = %(mangaid_1)s

In [22]: rwave = rwquery.first()

In [23]: rwave[0][0:10]
Out[23]:
[3292.49709827422,
 3293.25529747001,
 3294.01371862107,
 3294.7723617274,
 3295.53100483373,
 3296.28986989532,
 3297.04895691218,
 3297.80826588431,
 3298.56779681171,
 3299.32732773911]


I do rwquery.first() here instead of .one() or .all() because in my 
database, I actually have different versions of the same object, 6 
versions, which I don't care about.  But that's fine, I can add additional 
filters later.  In the code setup I sent up, there is only one version of 
each cube object.   The above results gets produced with the @expression

@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)).as_scalar()

return restwave

I don't need any limits and I think I need 4 selects, the one in the query 
I run explicitly s.query(Cube.restwave)  and three inside the @expression. 
  When I grab the actual cube instance object and access restwave on the 
instance side, it works just fine. 

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

Re: [sqlalchemy] nested subqueries in a hybrid expression?

2016-08-03 Thread Mike Bayer


OK first problem is, the SQL you showed me 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;


that has three SELECTs, a LIMIT, and no WHERE.

Then the code has three calls to session.query() inside "def restwave", 
no LIMIT.  Then theres *another* call to session.query(Cube.restwave) 
with a filter() (e.g. a WHERE), and no LIMIT.


These don't match up.  Can you show me the *complete* SQL you want at 
the very end of the whole thing?  I don't know how many levels of SELECT 
you want (the code asks for four), and I don't know if you want the 
LIMIT inside the subquery, and other things like that.  That is, where 
does "def restwave()" end and the outer query takes over.








On 08/03/2016 11:57 AM, Brian Cherinka wrote:

Ok. Here is my test file.  I tried to set it up as much as I could, but
I don't normally set up my db and sessions this way, so you may have to
hack a bit here and there to finish some setup.  My original setup has
classes from two different schema.  I don't know if that makes any
difference.  I've also added some lines to add objects into the tables
that I think you can use to recreate my problem.   Let me know if you
need anything more in this file.

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


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

Re: [sqlalchemy] nested subqueries in a hybrid expression?

2016-08-03 Thread Brian Cherinka

Ok. Here is my test file.  I tried to set it up as much as I could, but I 
don't normally set up my db and sessions this way, so you may have to hack 
a bit here and there to finish some setup.  My original setup has classes 
from two different schema.  I don't know if that makes any difference. 
 I've also added some lines to add objects into the tables that I think you 
can use to recreate my problem.   Let me know if you need anything more in 
this file. 

-- 
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.
import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, deferred, sessionmaker
from sqlalchemy.schema import Column
from sqlalchemy.dialects.postgresql import *
from sqlalchemy.types import Float, Integer, String
from sqlalchemy.ext.hybrid import hybrid_property


Base = declarative_base()
Session = sessionmaker()


class Target(Base):
__tablename__ = 'target'
__table_args__ = {'autoload': True, 'schema': 'sampledb'}

id = Column(Integer, primary_key=True)
mangaid = Column(String)


class Wavelength(Base):
__tablename__ = 'wavelength'
__table_args__ = {'autoload': True, 'schema': 'datadb'}

pk = Column(Integer, primary_key=True)
wavelength = deferred(Column(ARRAY(Float, zero_indexes=True)))

def __repr__(self):
return ''.format(self.pk)


class NSA(Base):
__tablename__ = 'nsa'
__table_args__ = {'autoload': True, 'schema': 'sampledb'}

z = Column(Float)

def __repr__(self):
return ''.format(self.pk, self.nsaid)


class TargetToNSA(Base):
__tablename__ = 'target_to_nsa'
__table_args__ = {'autoload': True, 'schema': 'sampledb'}

target_pk = Column(Integer, ForeignKey('target.pk'))
nsa_pk = Column(Integer, ForeignKey('nsa.pk'))

def __repr__(self):
return ''.format(self.pk)

# Relationships
NSA.targets = relationship(Target, backref='NSA_objects', secondary=TargetToNSA.__table__)


class Cube(Base):
__tablename__ = 'cube'
__table_args__ = {'autoload': True, 'schema': 'datadb'}

pk = Column(Integer, primary_key=True)
mangaid = Column(String)

target_pk = Column(Integer, ForeignKey('target.pk'))
target = relationship(Target, backref='cubes')

wavelength_pk = Column(Integer, ForeignKey('wavelength.pk'))
wavelength = relationship(Wavelength, backref='cubes')

@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 = Session.object_session(self)
nsaz = session.query(NSA.z.label('z')).\
join(TargetToNSA, Target, 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)).as_scalar()

return restwave


session = Session()

# Add objects
session.begin()
wave = Wavelength(wavelength=[3950.00, 4000.00, 5100.00, 5200.00, 6212.00, 8348.00])
session.add(wave)
nsa = [NSA(z=0.0543), NSA(z=1.234567), NSA(z=0.0167534563)]
session.add_all(nsa)
targets = [Target(mangaid='1-234567', NSA_objects=nsa[0]),
   Target(mangaid='1-113520', NSA_objects=nsa[2]),
   Target(mangaid='1-367842', NSA_objects=nsa[1])]
session.add_all(targets)
session.flush()
cubes = [Cube(wavelength=wave, mangaid='1-987345'),
 Cube(wavelength=wave, mangaid='1-234567', target=targets[0]),
 Cube(wavelength=wave, mangaid='1-367842', target=targets[2]),
 Cube(wavelength=wave, mangaid='1-113520', target=targets[1])]
session.commit()


q = session.query(Cube.restwave).filter(Cube.mangaid == '1-113520')

print q

Re: [sqlalchemy] nested subqueries in a hybrid expression?

2016-08-03 Thread Brian Cherinka

Awesome. Thanks.  Ok. I'll work on it again, and get back to you as soon as 
I can.  

On Tuesday, August 2, 2016 at 3:57:10 PM UTC-4, Mike Bayer wrote:
>
> What I need is a complete .py file that sets up a *minimal* version of 
> *every* class required, then the Query object, then prints it.   I'll 
> mangle it to do the right thing. 
>
> Like this: 
>
> from sqlalchemy import * 
> from sqlalchemy.orm import * 
> from sqlalchemy.ext.declarative import declarative_base 
>
> Base = declarative_base() 
>
>
> class A(Base): 
>  __tablename__ = 'a' 
>  id = Column(Integer, primary_key=True) 
>  bs = relationship("B") 
>
> class B(Base): 
>  __tablename__ = 'b' 
>  id = Column(Integer, primary_key=True) 
>  a_id = Column(ForeignKey('a.id')) 
>
>
> s = Session() 
>
> q = s.query(A).join(B) 
>
> print q 
>
>
>
>
>
>
>
> On 08/02/2016 02:45 PM, Brian Cherinka wrote: 
> > So I managed to get something to return using this definition of the 
> > @expression, however, I'm not quite there yet. 
> > 
> > |@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)).as_scalar() 
> > 
> > 
> > return restwave| 
> > 
> > Using the example query, 
> > 
> > | 
> > rwquery 
> > 
> =session.query(datadb.Cube.restwave).filter(datadb.Cube.mangaid=='1-113520') 
>
> > rw =rwquery.first() 
> > | 
> > 
> > I am getting a modified wavelength array, but it's the wrong one, using 
> > the wrong ID.  For the ID 1-113520 I should be modifying the wavelength 
> > array by (1+0.016765) and instead it's just grabbing the very first 
> > value in the NSA.z column, which corresponds to (1+0.099954).  I think 
> > this is because my filter condition is not getting passed into the nsaz 
> > subquery, where it needs to go.   Do you know how I can pass filter 
> > condition parameters down into any subqueries I may have in me 
> > expression select statements?  Is that what .correlate() does? 
> > 
> > My query looks like 
> > 
> > | 
> > In[24]:printrwquery 
> > SELECT (SELECT array_agg(unwave.restw)AS array_agg_1 
> > FROM (SELECT unnest(mangadatadb.wavelength.wavelength)/(%(z_1)s 
> > +nsaz.z)AS restw 
> > FROM mangadatadb.wavelength,(SELECT mangasampledb.nsa.z AS z 
> > FROM mangasampledb.nsa JOIN mangasampledb.manga_target_to_nsa ON 
> > mangasampledb.nsa.pk =mangasampledb.manga_target_to_nsa.nsa_pk JOIN 
> > mangasampledb.manga_target ON mangasampledb.manga_target.pk 
> > =mangasampledb.manga_target_to_nsa.manga_target_pk JOIN mangadatadb.cube 
> > ON mangasampledb.manga_target.pk =mangadatadb.cube.manga_target_pk 
> > WHERE mangadatadb.cube.mangaid =mangadatadb.cube.mangaid)AS nsaz)AS 
> > unwave)AS anon_1 
> > FROM mangadatadb.cube 
> > WHERE mangadatadb.cube.mangaid =%(mangaid_1)s 
> > 
> > | 
> > 
> > 
> > 
>
>

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

Re: [sqlalchemy] nested subqueries in a hybrid expression?

Re: [sqlalchemy] nested subqueries in a hybrid expression?

Re: [sqlalchemy] nested subqueries in a hybrid expression?

Re: [sqlalchemy] nested subqueries in a hybrid expression?

Re: [sqlalchemy] nested subqueries in a hybrid expression?

5 matches

Site Navigation

Mail list logo

Footer information