Oh, check this out:
(SA 0.4.7)
>>> from sqlalchemy import *
>>> s = select(["x", "y"]).select_from("table")
>>> qlist = [s.limit(10).order_by('x').self_group(),
>>> s.limit(10).order_by('x').self_group()]
>>> print union_all(*qlist).order_by("foo").limit(10)
SELECT x, y
FROM table
LIMIT 10 UNION ALL SELECT x, y
FROM table
LIMIT 10 ORDER BY foo
LIMIT 10
>>> for q in qlist:
... print q
...
(SELECT x, y
FROM table ORDER BY x
LIMIT 10)
(SELECT x, y
FROM table ORDER BY x
LIMIT 10)
On Fri, Dec 5, 2008 at 4:55 PM, Bo Shi <[EMAIL PROTECTED]> wrote:
> I had to upgrade to 0.4.7 from 0.4.2, but your sample query works,
> however, my application of it does not.
>
> Sorry I'm being so light on details, I'll try to reproduce with a
> complete sample versus using snippets of production code.
>
> Each select statement is generated like so:
>
> sel = select(pre_select + selectlist, from_obj=join_datatables(tables))
>
> I apply self_group() using a list comprehension and if I print each
> select statement in the list individually, the parentheses show up.
>
> If I then union_all(*querylist) and print that, the parentheses disappear.
>
> Weird.
>
> I should note that the individual selects have filters, an order by
> clause and a limit, but the following works fine so I would not expect
> that to be a problem.
>
>>>> from sqlalchemy import *
>>>> s = select(["x", "y"]).select_from("table")
>>>> print s.self_group()
> (SELECT x, y
> FROM table)
>>>> print union_all(s.self_group(), s.self_group()).order_by("foo")
> (SELECT x, y
> FROM table) UNION ALL (SELECT x, y
> FROM table) ORDER BY foo
>>>> print union_all(s.limit(10).self_group(),
>>>> s.limit(10).self_group()).order_by("foo").limit(10)
> (SELECT x, y
> FROM table
> LIMIT 10) UNION ALL (SELECT x, y
> FROM table
> LIMIT 10) ORDER BY foo
> LIMIT 10
>>>> import sqlalchemy as sa
>>>> sa.__version__
> '0.4.7'
>
>
> On Fri, Dec 5, 2008 at 4:23 PM, Michael Bayer <[EMAIL PROTECTED]> wrote:
>>
>>
>> >>> from sqlalchemy import *
>> >>> s = select(["x", "y"]).select_from("table")
>> >>> print union_all(s.self_group(), s.self_group()).order_by("foo")
>> (SELECT x, y
>> FROM table) UNION ALL (SELECT x, y
>> FROM table) ORDER BY foo
>>
>>
>> On Dec 5, 2008, at 4:17 PM, Bo Shi wrote:
>>
>>>
>>> Thanks for the quick response!
>>>
>>> The following does *not* work. Am I making the call incorrectly?
>>>
>>> sel = union_all(*[q.self_group() for q in querylist])
>>>
>>>
>>> On Fri, Dec 5, 2008 at 4:08 PM, Michael Bayer <[EMAIL PROTECTED]
>>> > wrote:
>>>>
>>>> try calling self_group() on each select object.
>>>>
>>>> On Dec 5, 2008, at 3:55 PM, Bo Shi wrote:
>>>>
>>>>>
>>>>> Hi all,
>>>>>
>>>>> There appear to be some nuances to using order by statements with
>>>>> set
>>>>> operations like unions in MySQL but the following is allowed*:
>>>>>
>>>>> (SELECT a,b from DBA.tbl ORDER BY b LIMIT 5)
>>>>> UNION ALL
>>>>> (SELECT a,b from DBB.tbl ORDER BY b LIMIT 5)
>>>>> ORDER BY b
>>>>>
>>>>> When I attempt to generate such a statement with:
>>>>>
>>>>> union_all(*list_of_select_objs),
>>>>>
>>>>> The SQL generated lacks parentheses around the SELECT statements (in
>>>>> addition to dropping the order by clauses, but that appears to be
>>>>> expected behavior). Is there a way to put the parentheses in?
>>>>>
>>>>>
>>>>> *just an example, the query i've written is meaningless/useless :-)
>>>>>
>>>>>
>>>>> --
>>>>> Bo Shi
>>>>> 207-469-8264
>>>>>
>>>>>>
>>>>
>>>>
>>>>>
>>>>
>>>
>>>
>>>
>>> --
>>> Bo Shi
>>> 207-469-8264
>>>
>>> >
>>
>>
>> >>
>>
>
>
>
> --
> Bo Shi
> 207-469-8264
>
--
Bo Shi
207-469-8264
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
