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

[Brian Cherinka]

>
>
>
> I've yet to see an unambiguous statement of what "the raw SQL" is.   If 
> it is this: 
>
> 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; 

 

> then that does not, and cannot, correspond to the Python code you are 
> sending.  the JOINs would not be inside of the @expression, you'd have 
> to write them out on the outside. 

 
Yeah, that was the SQL that would be typed into psql.  I know that the 
Python implementation is incorrect.  I've been trying to sort that out.   


I've provided a complete example of how to render SQL extremely similar 
> to what you want, featuring techniques such as correlate() as well as 
> how to nest the queries appropriately.  If at this point you aren't able 
> to manipulate the code to get what you want, then I'd advise not using a 
> hybrid for this query at all. It is extremely complicated in this 
> context, and unless you are deeply familiar with SQLAlchemy APIs, you 
> would just have a bunch of code that you can't effectively maintain. 
>

Ok..thanks for your help.  I appreciate it.  
 

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

[Mike Bayer]

On 08/04/2016 12:03 PM, Brian Cherinka wrote:


Yeah, sorry about that.  Like I said, I don't normally build my classes
this way, defining all the columns in the Base Class.  I define all my
columns, primary and foreign keys, etc manually first, so my SQLA
classes are minimally defined.  And I cobbled together pieces from my
real code and your example to build that test code I sent you.  So I
forgot some things.  I did say it may not be completely right, and you
might have to hack it some.  I apologize.  Here is an attempt at
cleaning it up.

When I try to implement your @expression into my real code, it doesn't
work.  I am getting an error about multiple results returned within the
subquery.

|
@restwave.expression
defrestwave(cls):
s =db.Session()
restw
=(func.unnest(Wavelength.wavelength)/(1+sampledb.NSA.z)).label("restw")
unwave
=s.query(restw).select_from(Wavelength).correlate(sampledb.NSA).subquery("unwave")
agg_unwave
=s.query(func.array_agg(unwave.c.restw)).label("restwarr")
joined =s.query(agg_unwave).select_from(
 Cube

 ).join(sampledb.MangaTarget,sampledb.MangaTargetToNSA,sampledb.NSA,Wavelength)
returnjoined.as_scalar()
|

|
session.query(datadb.Cube.restwave).filter(datadb.Cube.mangaid=='1-113520').all()
|

|
ProgrammingError:(psycopg2.ProgrammingError)more than one row returned
bya subquery used asan expression
 [SQL:'SELECT (SELECT (SELECT array_agg(unwave.restw) AS array_agg_1
\nFROM (SELECT unnest(mangadatadb.wavelength.wavelength) / (%(z_1)s +
mangasampledb.nsa.z) AS restw \nFROM mangadatadb.wavelength) AS unwave)
AS restwarr \nFROM mangadatadb.cube JOIN mangasampledb.manga_target ON
mangasampledb.manga_target.pk = mangadatadb.cube.manga_target_pk JOIN
mangasampledb.manga_target_to_nsa ON mangasampledb.manga_target.pk =
mangasampledb.manga_target_to_nsa.manga_target_pk JOIN mangasampledb.nsa
ON mangasampledb.nsa.pk = mangasampledb.manga_target_to_nsa.nsa_pk JOIN
mangadatadb.wavelength ON mangadatadb.wavelength.pk =
mangadatadb.cube.wavelength_pk) AS anon_1 \nFROM mangadatadb.cube
\nWHERE mangadatadb.cube.mangaid =
%(mangaid_1)s'][parameters:{'z_1':1,'mangaid_1':'1-113520'}]

|

 I don't know if this is because I have multiple versions of the same
object or not.  I tried adding in a selection on version, but the joins
did not work out properly.  This NSA table does not need to be joined
with the versions.  What I expect the above to return is exactly what
the raw SQL returns.


I've yet to see an unambiguous statement of what "the raw SQL" is.   If 
it is this:


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;


then that does not, and cannot, correspond to the Python code you are 
sending.  the JOINs would not be inside of the @expression, you'd have 
to write them out on the outside.


I've provided a complete example of how to render SQL extremely similar 
to what you want, featuring techniques such as correlate() as well as 
how to nest the queries appropriately.  If at this point you aren't able 
to manipulate the code to get what you want, then I'd advise not using a 
hybrid for this query at all. It is extremely complicated in this 
context, and unless you are deeply familiar with SQLAlchemy APIs, you 
would just have a bunch of code that you can't effectively maintain.











A list of the restwave array for each version of

the object with id = '1-113520'.  I thought SQLA was designed precisely
to let users do what I'm trying, i.e. construct complex columns
involving multiple selects, without having to physically add a new
column into the db, or write a new SQL function in the db to call?

|
session.query(datadb.Cube.restwave).join(datadb.Cube,datadb.PipelineInfo,datadb.PipelineVersion).filter(datadb.PipelineVersion=='v1_5_1',datadb.Cube.mangaid=='1-113520').all()

InvalidRequestError: Could not find a FROM clause to join from.  Tried
joining to , but got:
Can't find any foreign key relationships between 'nsa' and 'cube'.

|

On Wednesday, August 3, 2016 at 2:22:43 PM UTC-4, Mike Bayer wrote:

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.

--

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

[Brian Cherinka]

Yeah, sorry about that.  Like I said, I don't normally build my classes 
this way, defining all the columns in the Base Class.  I define all my 
columns, primary and foreign keys, etc manually first, so my SQLA classes 
are minimally defined.  And I cobbled together pieces from my real code and 
your example to build that test code I sent you.  So I forgot some things. 
 I did say it may not be completely right, and you might have to hack it 
some.  I apologize.  Here is an attempt at cleaning it up. 

When I try to implement your @expression into my real code, it doesn't 
work.  I am getting an error about multiple results returned within the 
subquery. 

@restwave.expression
def restwave(cls):
s = db.Session()
restw = (func.unnest(Wavelength.wavelength) / (1 + sampledb.NSA.z)).
label("restw")
unwave = s.query(restw).select_from(Wavelength).correlate(sampledb.
NSA).subquery("unwave")
agg_unwave = s.query(func.array_agg(unwave.c.restw)).label(
"restwarr")
joined = s.query(agg_unwave).select_from(
 Cube
 ).join(sampledb.MangaTarget, sampledb.MangaTargetToNSA, 
sampledb.NSA, Wavelength)
return joined.as_scalar()

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

ProgrammingError: (psycopg2.ProgrammingError) more than one row returned by 
a subquery used as an expression
 [SQL: 'SELECT (SELECT (SELECT array_agg(unwave.restw) AS array_agg_1 
\nFROM (SELECT unnest(mangadatadb.wavelength.wavelength) / (%(z_1)s + 
mangasampledb.nsa.z) AS restw \nFROM mangadatadb.wavelength) AS unwave) AS 
restwarr \nFROM mangadatadb.cube JOIN mangasampledb.manga_target ON 
mangasampledb.manga_target.pk = mangadatadb.cube.manga_target_pk JOIN 
mangasampledb.manga_target_to_nsa ON mangasampledb.manga_target.pk = 
mangasampledb.manga_target_to_nsa.manga_target_pk JOIN mangasampledb.nsa ON 
mangasampledb.nsa.pk = mangasampledb.manga_target_to_nsa.nsa_pk JOIN 
mangadatadb.wavelength ON mangadatadb.wavelength.pk = 
mangadatadb.cube.wavelength_pk) AS anon_1 \nFROM mangadatadb.cube \nWHERE 
mangadatadb.cube.mangaid = %(mangaid_1)s'] [parameters: {'z_1': 1, 
'mangaid_1': '1-113520'}]


 I don't know if this is because I have multiple versions of the same 
object or not.  I tried adding in a selection on version, but the joins did 
not work out properly.  This NSA table does not need to be joined with the 
versions.  What I expect the above to return is exactly what the raw SQL 
returns.  A list of the restwave array for each version of the object with 
id = '1-113520'.  I thought SQLA was designed precisely to let users do 
what I'm trying, i.e. construct complex columns involving multiple selects, 
without having to physically add a new column into the db, or write a new 
SQL function in the db to call?  

session.query(datadb.Cube.restwave).join(datadb.Cube,datadb.PipelineInfo,
datadb.PipelineVersion).filter(datadb.PipelineVersion=='v1_5_1',datadb.Cube.
mangaid=='1-113520').all()

InvalidRequestError: Could not find a FROM clause to join from.  Tried 
joining to , but got: 
Can't find any foreign key relationships between 'nsa' and 'cube'.


On Wednesday, August 3, 2016 at 2:22:43 PM UTC-4, Mike Bayer wrote:
>
> 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. 
>
>

-- 
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 import ForeignKey, func
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
from sqlalchemy import create_engine

# import sqlite3
# conn = sqlite3.connect('/Users/Brian/Work/python/manga/test_sqla.db')
# c = conn.cursor()
# c.execute('''create table cube (pk integer primary key, mangaid text, wavelength_pk integer, target_pk integer);''')
# c.execute('''create table nsa (pk integer primary key, z real);''')
# c.execute('''create table target (pk integer primary key, mangaid text);''')
# c.execute('''create table target_to_nsa (pk integer primary 

Re: [sqlalchemy] Relationship with query time evaluated primaryjoin

[Florian Rüchel]

Awesome idea, thanks for the reply! I ended up combining this approach with 
the callable_ argument and don't have to explicitly pass the argument now 
at all.

This is a great approach and it is fairly simple as well. Thank you a lot.

On Friday, 5 August 2016 00:24:30 UTC+10, Mike Bayer wrote:
>
>
>
> On 08/04/2016 10:14 AM, Florian Rüchel wrote: 
> > I have a relationship that depends on a query time variable to determine 
> > the correct join. The use case is request-time localization in a web 
> > application. When running the query during a request, I want to 
> > determine the locale and only load the translation for the current 
> > language for a given object. However, the primaryjoin condition callable 
> > is evaluated at mapping time instead which only happens once instead of 
> > on every request. 
> > 
> > Here is a quick sample: 
> > 
> > def get_myobj_primaryjoin(): 
> > return and_(MyObj.id == MyObjI18N.obj_id, request.locale == 
> > MyObjI18N.lang) 
> > 
> > 
> > class MyObj(Base): 
> > id = Column(Integer, primary_key=True) 
> > _current_translation = relationship(MyObjI18N, uselist=False, 
> > primaryjoin=get_myobj_primaryjoin, lazy='joined') 
> > 
> > 
> > class MyObjI18N(Base): 
> > obj_id = Column(ForeignKey(MyObj.id), primary_key=True) 
> > lang = Column(String) 
> > 
> > This should give a rough idea of the issue: request.locale changes at 
> > query time, that is, if I do MyObj.query in two different requests, it 
> > won't work, it will always take the first time it was called. 
> > 
> > Note that I was previously using a with_transformation approach when 
> > building the query but I wanted to remove the necessity to add that 
> > every time a build a query and would have it much rather built 
> implicitly. 
> > 
> > Any ideas are highly appreciated, no argument I can pass to 
> > "relationship" seems to help my use case. 
>
>
> we use a bound parameter for this and a recipe for getting a value in 
> there can be seen at 
> https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/GlobalFilter . 
> In particular the lazyload case can only be affected using a custom 
> MapperOption as described near the bottom of that recipe. 
>
>
>
> > 
> > -- 
> > 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+...@googlegroups.com  
> > . 
> > To post to this group, send email to sqlal...@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] Relationship with query time evaluated primaryjoin

[Mike Bayer]

On 08/04/2016 10:14 AM, Florian Rüchel wrote:

I have a relationship that depends on a query time variable to determine
the correct join. The use case is request-time localization in a web
application. When running the query during a request, I want to
determine the locale and only load the translation for the current
language for a given object. However, the primaryjoin condition callable
is evaluated at mapping time instead which only happens once instead of
on every request.

Here is a quick sample:

def get_myobj_primaryjoin():
return and_(MyObj.id == MyObjI18N.obj_id, request.locale ==
MyObjI18N.lang)


class MyObj(Base):
id = Column(Integer, primary_key=True)
_current_translation = relationship(MyObjI18N, uselist=False,
primaryjoin=get_myobj_primaryjoin, lazy='joined')


class MyObjI18N(Base):
obj_id = Column(ForeignKey(MyObj.id), primary_key=True)
lang = Column(String)

This should give a rough idea of the issue: request.locale changes at
query time, that is, if I do MyObj.query in two different requests, it
won't work, it will always take the first time it was called.

Note that I was previously using a with_transformation approach when
building the query but I wanted to remove the necessity to add that
every time a build a query and would have it much rather built implicitly.

Any ideas are highly appreciated, no argument I can pass to
"relationship" seems to help my use case.



we use a bound parameter for this and a recipe for getting a value in 
there can be seen at 
https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/GlobalFilter . 
   In particular the lazyload case can only be affected using a custom 
MapperOption as described near the bottom of that recipe.






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