[sqlalchemy] Re: MySQL, unions and ordering
you'd say, s.alias().select()
it makes subqueries which MySQL probably doesn't require.
On Dec 5, 2008, at 10:35 PM, Bo Shi wrote:
Thanks; the monkeypatch approach works nicely. Using the alias()
method will raise
AttributeError: 'Alias' object has no attribute '_order_by_clause'
On Fri, Dec 5, 2008 at 7:25 PM, Michael Bayer [EMAIL PROTECTED]
wrote:
there's logic which is removing the order_by's from the selects, and
in that case this is what's blowing away the parenthesis as well.
Some databases don't even allow ORDER BY inside of the queries used
in
a UNION since in the absense of LIMIT/OFFSET, which also is not
standard SQL, they have no effect.
However I dont think its good form for SQLA to be whacking the ORDER
BY from the unions if that is in fact what was requested.So this
behavior is changed in the 0.5 series in r5425. As far as the 0.4
series, we're only supporting critical bugfixes there and I'd like to
avoid any behavioral changes (0.4 is also on a more conservative
release schedule). If you're truly stuck with 0.4, you can use
select.order_by(...).alias().select() to get an equivalent query
which
is insulated from changes (and is probably more compatible across
databases), or to get exactly the same SQL here's a safe monkeypatch
approach:
from sqlalchemy import *
s = select([x, y]).select_from(table)
def frozen_order_by(s):
s = s.self_group()
s.order_by = lambda *args: s
return s
qlist = [s.limit(10).order_by('x'), s.limit(10).order_by('x')]
print union_all(*[frozen_order_by(q) for q in
qlist]).order_by(foo).limit(10)
frozen_order_by() calls self_group() thereby generating a new
select()
so that the original is unchanged.
On Dec 5, 2008, at 5:08 PM, Bo Shi wrote:
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
[sqlalchemy] Re: MySQL, unions and ordering
Right; my bad - I misread the instructions.
On Sat, Dec 6, 2008 at 9:41 AM, Michael Bayer [EMAIL PROTECTED] wrote:
you'd say, s.alias().select()
it makes subqueries which MySQL probably doesn't require.
On Dec 5, 2008, at 10:35 PM, Bo Shi wrote:
Thanks; the monkeypatch approach works nicely. Using the alias()
method will raise
AttributeError: 'Alias' object has no attribute '_order_by_clause'
On Fri, Dec 5, 2008 at 7:25 PM, Michael Bayer [EMAIL PROTECTED]
wrote:
there's logic which is removing the order_by's from the selects, and
in that case this is what's blowing away the parenthesis as well.
Some databases don't even allow ORDER BY inside of the queries used
in
a UNION since in the absense of LIMIT/OFFSET, which also is not
standard SQL, they have no effect.
However I dont think its good form for SQLA to be whacking the ORDER
BY from the unions if that is in fact what was requested.So this
behavior is changed in the 0.5 series in r5425. As far as the 0.4
series, we're only supporting critical bugfixes there and I'd like to
avoid any behavioral changes (0.4 is also on a more conservative
release schedule). If you're truly stuck with 0.4, you can use
select.order_by(...).alias().select() to get an equivalent query
which
is insulated from changes (and is probably more compatible across
databases), or to get exactly the same SQL here's a safe monkeypatch
approach:
from sqlalchemy import *
s = select([x, y]).select_from(table)
def frozen_order_by(s):
s = s.self_group()
s.order_by = lambda *args: s
return s
qlist = [s.limit(10).order_by('x'), s.limit(10).order_by('x')]
print union_all(*[frozen_order_by(q) for q in
qlist]).order_by(foo).limit(10)
frozen_order_by() calls self_group() thereby generating a new
select()
so that the original is unchanged.
On Dec 5, 2008, at 5:08 PM, Bo Shi wrote:
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
[sqlalchemy] Re: MySQL, unions and ordering
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 --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: MySQL, unions and ordering
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 --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: MySQL, unions and ordering
that's correct. what does it render ? 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 --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: MySQL, unions and ordering
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 --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: MySQL, unions and ordering
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 --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: MySQL, unions and ordering
there's logic which is removing the order_by's from the selects, and
in that case this is what's blowing away the parenthesis as well.
Some databases don't even allow ORDER BY inside of the queries used in
a UNION since in the absense of LIMIT/OFFSET, which also is not
standard SQL, they have no effect.
However I dont think its good form for SQLA to be whacking the ORDER
BY from the unions if that is in fact what was requested.So this
behavior is changed in the 0.5 series in r5425. As far as the 0.4
series, we're only supporting critical bugfixes there and I'd like to
avoid any behavioral changes (0.4 is also on a more conservative
release schedule). If you're truly stuck with 0.4, you can use
select.order_by(...).alias().select() to get an equivalent query which
is insulated from changes (and is probably more compatible across
databases), or to get exactly the same SQL here's a safe monkeypatch
approach:
from sqlalchemy import *
s = select([x, y]).select_from(table)
def frozen_order_by(s):
s = s.self_group()
s.order_by = lambda *args: s
return s
qlist = [s.limit(10).order_by('x'), s.limit(10).order_by('x')]
print union_all(*[frozen_order_by(q) for q in
qlist]).order_by(foo).limit(10)
frozen_order_by() calls self_group() thereby generating a new select()
so that the original is unchanged.
On Dec 5, 2008, at 5:08 PM, Bo Shi wrote:
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
[sqlalchemy] Re: MySQL, unions and ordering
Thanks; the monkeypatch approach works nicely. Using the alias()
method will raise
AttributeError: 'Alias' object has no attribute '_order_by_clause'
On Fri, Dec 5, 2008 at 7:25 PM, Michael Bayer [EMAIL PROTECTED] wrote:
there's logic which is removing the order_by's from the selects, and
in that case this is what's blowing away the parenthesis as well.
Some databases don't even allow ORDER BY inside of the queries used in
a UNION since in the absense of LIMIT/OFFSET, which also is not
standard SQL, they have no effect.
However I dont think its good form for SQLA to be whacking the ORDER
BY from the unions if that is in fact what was requested.So this
behavior is changed in the 0.5 series in r5425. As far as the 0.4
series, we're only supporting critical bugfixes there and I'd like to
avoid any behavioral changes (0.4 is also on a more conservative
release schedule). If you're truly stuck with 0.4, you can use
select.order_by(...).alias().select() to get an equivalent query which
is insulated from changes (and is probably more compatible across
databases), or to get exactly the same SQL here's a safe monkeypatch
approach:
from sqlalchemy import *
s = select([x, y]).select_from(table)
def frozen_order_by(s):
s = s.self_group()
s.order_by = lambda *args: s
return s
qlist = [s.limit(10).order_by('x'), s.limit(10).order_by('x')]
print union_all(*[frozen_order_by(q) for q in
qlist]).order_by(foo).limit(10)
frozen_order_by() calls self_group() thereby generating a new select()
so that the original is unchanged.
On Dec 5, 2008, at 5:08 PM, Bo Shi wrote:
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
--
Bo Shi
207-469-8264
--~--~-~--~~~---~--~~
You received this message because you are subscribed to
