Re: [sqlalchemy] [Q][0.7.9] problem with with_entities(), over() and label()

2013-06-07 Thread Ladislav Lenart 
On 6.6.2013 19:33, Michael Bayer wrote:
 
 On Jun 6, 2013, at 12:56 PM, Ladislav Lenart lenart...@volny.cz wrote:
 
 Hello.

 I have already solved the issue by using subquery:

 SELECT
t.id AS t_id,
t.rownum AS t_rownum
 FROM (
SELECT
FROM
foo.id AS id,
row_number() OVER (ORDER BY foo.id) AS rownum
 ) AS t
 WHERE rownum % 50 = 1

 I have just tried your suggestion about using HAVING instead of WHERE, but 
 that
 fails with the same error. Thus a label cannot be used inside a query.

 However, I am still curious whether the original WindowedRangeQuery recipe at

http://www.sqlalchemy.org/trac/wiki/UsageRecipes/WindowedRangeQuery

 works or also has this error.
 
 the recipe as you noted uses from_self(), which means, wrap myself in a 
 subquery, so that's where the necessary subquery is applied.

That explains it :-)


Thank you,

Ladislav Lenart

-- 
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 http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

[sqlalchemy] [Q][0.7.9] problem with with_entities(), over() and label()

2013-06-06 Thread Ladislav Lenart 
Hello.

I am trying to adapt WindowedRangeQuery recipe

http://www.sqlalchemy.org/trac/wiki/UsageRecipes/WindowedRangeQuery

I have just stumbled upon the following issue:

def _q_windows(self, window_size):
q = self._query.with_entities(
self._column,
func.row_number().over(order_by=self._column).label('rownum'),
)
if window_size  1:
q = q.filter(rownum % {} = 1.format(window_size))
return q

where

self._query = session.query(Foo)
self._column = Foo.id
window_size = 2

The returned query q produces the following SQL:

SELECT
foo.id AS foo_id,
row_number() OVER (ORDER BY foo.id) AS rownum
FROM foo
WHERE rownum % 2 = 1

When executed, it generates the following error:

sqlalchemy.exc.ProgrammingError:
(ProgrammingError) column rownum does not exist

I am kind of lost here, because something very similar should work according to
the original recipe. Note that I apply with_entities() on the original query
whereas the recipe uses from_self() on the column.

Do you have any idea what should I do to fix this?


Thank you in advance,

Ladislav Lenart

-- 
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 http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [sqlalchemy] [Q][0.7.9] problem with with_entities(), over() and label()

2013-06-06 Thread Ladislav Lenart 
Hello.

I have already solved the issue by using subquery:

SELECT
t.id AS t_id,
t.rownum AS t_rownum
FROM (
SELECT
FROM
foo.id AS id,
row_number() OVER (ORDER BY foo.id) AS rownum
) AS t
WHERE rownum % 50 = 1

I have just tried your suggestion about using HAVING instead of WHERE, but that
fails with the same error. Thus a label cannot be used inside a query.

However, I am still curious whether the original WindowedRangeQuery recipe at

http://www.sqlalchemy.org/trac/wiki/UsageRecipes/WindowedRangeQuery

works or also has this error.


Thank you,

Ladislav Lenart


On 6.6.2013 18:26, Charlie Clark wrote:
 Am 06.06.2013, 16:30 Uhr, schrieb Ladislav Lenart lenart...@volny.cz:
 
 The returned query q produces the following SQL:
SELECT
 foo.id AS foo_id,
 row_number() OVER (ORDER BY foo.id) AS rownum
 FROM foo
 WHERE rownum % 2 = 1
 When executed, it generates the following error:
sqlalchemy.exc.ProgrammingError:
 (ProgrammingError) column rownum does not exist
 
 Just a guess but the error is coming from Postgres. You'll need to  
 doublecheck the docs but I don't think rownum is available for the WHERE  
 clause because OVER works like the aggregate functions. You can either try  
 wrapping the SELECT in correlated subquery as the example shows or simply  
 use HAVING rownum % 2 = 1 (I think this is best approach but I'm far from  
 an expert). Try the SQL directly in the DB to see which works.
 
 Charlie

-- 
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 http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [sqlalchemy] [Q][0.7.9] problem with with_entities(), over() and label()

2013-06-06 Thread Michael Bayer 

On Jun 6, 2013, at 12:56 PM, Ladislav Lenart lenart...@volny.cz wrote:

 Hello.
 
 I have already solved the issue by using subquery:
 
 SELECT
t.id AS t_id,
t.rownum AS t_rownum
 FROM (
SELECT
FROM
foo.id AS id,
row_number() OVER (ORDER BY foo.id) AS rownum
 ) AS t
 WHERE rownum % 50 = 1
 
 I have just tried your suggestion about using HAVING instead of WHERE, but 
 that
 fails with the same error. Thus a label cannot be used inside a query.
 
 However, I am still curious whether the original WindowedRangeQuery recipe at
 
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/WindowedRangeQuery
 
 works or also has this error.

the recipe as you noted uses from_self(), which means, wrap myself in a 
subquery, so that's where the necessary subquery is applied.


-- 
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 http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [sqlalchemy] [Q][0.7.9] problem with with_entities(), over() and label()

2013-06-06 Thread Charlie Clark 

Am 06.06.2013, 18:56 Uhr, schrieb Ladislav Lenart lenart...@volny.cz:


Hello.
I have already solved the issue by using subquery:
SELECT
t.id AS t_id,
t.rownum AS t_rownum
FROM (
SELECT
FROM
foo.id AS id,
row_number() OVER (ORDER BY foo.id) AS rownum
) AS t
WHERE rownum % 50 = 1


I have just tried your suggestion about using HAVING instead of WHERE,  
but that fails with the same error. Thus a label cannot be used inside a  
query.


Yeah, sorry. As it says in the docs:


If the query contains any window functions (see Section 3.5, Section 9.21  
and Section 4.2.8), these functions are evaluated after any grouping,  
aggregation, and HAVING filtering is performed



So, it would be possible with standard aggregate functions, well something  
like it is, but not with anything like row_number() which must use a  
window. Compared with Python seems a weird way of striding through the  
results but maybe that's just SQL.


Charlie
--
Charlie Clark
Managing Director
Clark Consulting  Research
German Office
Kronenstr. 27a
Düsseldorf
D- 40217
Tel: +49-211-600-3657
Mobile: +49-178-782-6226

--
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 http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.