Re: [sqlalchemy] postgres with .. select queries
Great! I'll test it ASAP. BTW, is it hard to implement CTE with the ORM layer? :P Thanks!! 2012/3/1 Michael Bayer mike...@zzzcomputing.com right, I had the notion that it meant the JOIN is up at the top but that's silly, it works fine so here's the patch without anything not implemented: http://www.sqlalchemy.org/trac/attachment/ticket/1859/1859.patch someone else noted that you can use CTEs with INSERT, UPDATE, DELETE also, that will be for another day, similar syntax should work On Mar 1, 2012, at 3:12 PM, Claudio Freire wrote: On Thu, Mar 1, 2012 at 5:08 PM, Michael Bayer mike...@zzzcomputing.com wrote: OK also, this is ready to go in from my perspective, I don't make usage of CTEs in my normal work at the moment, so hopefully those people here interested in the feature can give this a review, maybe even try the patch, because this will be it ! Why is joining not supported in that patch? I would imagine it should be possible, with explicit join conditions of course. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- -- Pau Tallada Crespí Dep. d'Astrofísica i Cosmologia Port d'Informació Científica (PIC) Tel: +34 93 586 8233 -- -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] postgres with .. select queries
On Mar 2, 2012, at 6:32 AM, Pau Tallada wrote: Great! I'll test it ASAP. BTW, is it hard to implement CTE with the ORM layer? :P what you'd do is use query() to create a SELECT statement, then call .statement.cte() on it. Then you use that in another Query as something you can select FROM just like any other subquery. If you're doing recursive + UNION I think it's best to keep that on the core selectable you get back from cte(). It seems like WITH RECURSIVE...UNION needs a lot of aliasing so I'm not seeing a way to cut down on that verbosity. You should be able to use an aliased(MyClass) construct in the same way you'd normally do sometable.alias(). I can add query.cte() as a shortcut for that, it's basically analogous to query.subquery(). Thanks!! 2012/3/1 Michael Bayer mike...@zzzcomputing.com right, I had the notion that it meant the JOIN is up at the top but that's silly, it works fine so here's the patch without anything not implemented: http://www.sqlalchemy.org/trac/attachment/ticket/1859/1859.patch someone else noted that you can use CTEs with INSERT, UPDATE, DELETE also, that will be for another day, similar syntax should work On Mar 1, 2012, at 3:12 PM, Claudio Freire wrote: On Thu, Mar 1, 2012 at 5:08 PM, Michael Bayer mike...@zzzcomputing.com wrote: OK also, this is ready to go in from my perspective, I don't make usage of CTEs in my normal work at the moment, so hopefully those people here interested in the feature can give this a review, maybe even try the patch, because this will be it ! Why is joining not supported in that patch? I would imagine it should be possible, with explicit join conditions of course. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- -- Pau Tallada Crespí Dep. d'Astrofísica i Cosmologia Port d'Informació Científica (PIC) Tel: +34 93 586 8233 -- -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] postgres with .. select queries
I would love to see CTE integrated into SQLA too :) 2012/2/29 Michael Bayer mike...@zzzcomputing.com rough road ahead on that one for now, we have a @compiles recipe so far at http://www.sqlalchemy.org/trac/attachment/ticket/1859/cte_demo.py . You'd need to open it up to make it more generic than just WITH RECURSIVE. On Feb 29, 2012, at 11:38 AM, Chris Withers wrote: How would I do a with x as (...) select ... in sqlalchemy orm-ish? Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- -- Pau Tallada Crespí Dep. d'Astrofísica i Cosmologia Port d'Informació Científica (PIC) Tel: +34 93 586 8233 -- -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] postgres with .. select queries
Right.. So what should I do about oracle CONNECT BY, which is what they have instead of WITH RECURSIVE... Consider that to be a different construct and ignore it for now? Or should there be some approach that approximates between WITH RECURSIVE and CONNECT BY transparently? The latter approach has eluded me since structurally they are so different. I guess a literal CTE implementation to start with doesn't prevent a later agnostic construct from being created. On Mar 1, 2012, at 6:44 AM, Pau Tallada tall...@pic.es wrote: I would love to see CTE integrated into SQLA too :) 2012/2/29 Michael Bayer mike...@zzzcomputing.com rough road ahead on that one for now, we have a @compiles recipe so far at http://www.sqlalchemy.org/trac/attachment/ticket/1859/cte_demo.py . You'd need to open it up to make it more generic than just WITH RECURSIVE. On Feb 29, 2012, at 11:38 AM, Chris Withers wrote: How would I do a with x as (...) select ... in sqlalchemy orm-ish? Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- -- Pau Tallada Crespí Dep. d'Astrofísica i Cosmologia Port d'Informació Científica (PIC) Tel: +34 93 586 8233 -- -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] postgres with .. select queries
On Mar 1, 2012, at 9:41 AM, Michael Bayer wrote: Right.. So what should I do about oracle CONNECT BY, which is what they have instead of WITH RECURSIVE... Consider that to be a different construct and ignore it for now? Or should there be some approach that approximates between WITH RECURSIVE and CONNECT BY transparently? The latter approach has eluded me since structurally they are so different. I guess a literal CTE implementation to start with doesn't prevent a later agnostic construct from being created. Well, the SQL standard points at WITH RECURSIVE which is more general anyway. W.R. is basically an inductive query loop construct (base case UNION induction step) where CONNECT BY only handles key-based tree retrieval, no? Also, basic WITH support (without RECURSIVE) would be much appreciated- that could offer more flexibility than FROM-subqueries and could open the door for W.R. Cheers, M -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] postgres with .. select queries
On Thu, Mar 1, 2012 at 12:11 PM, A.M. age...@themactionfaction.com wrote: Well, the SQL standard points at WITH RECURSIVE which is more general anyway. W.R. is basically an inductive query loop construct (base case UNION induction step) where CONNECT BY only handles key-based tree retrieval, no? Also, basic WITH support (without RECURSIVE) would be much appreciated- that could offer more flexibility than FROM-subqueries and could open the door for W.R. Also, CTE support (recursive or not) would be easily implementable by a special case of selectable, whose visitor only outputs the name, and prepends to the string the WITH blah AS bleh. import sqlalchemy as sa somestuff = sa.select(...).cte(somestuff) somequery = sa.select( somestuff.c.somecolumn, somestuff.c.someval == 3) Just to give an example. I think this form makes a lot more sense in the SQLA API than the context manager. This form can be joined and operated on like any other table, and it could even be recursive if proper care is taken and generative API is used: somestuff = sa.select(...).cte(somestuff) somestuff.append_from(somestuff.recurse) somestuff.append_whereclause(somestuff.c.id == somestuff.recurse.c.parent_id) Just a possibility. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] postgres with .. select queries
On Mar 1, 2012, at 10:47 AM, Claudio Freire wrote:
On Thu, Mar 1, 2012 at 12:11 PM, A.M. age...@themactionfaction.com wrote:
Well, the SQL standard points at WITH RECURSIVE which is more general
anyway. W.R. is basically an inductive query loop construct (base case UNION
induction step) where CONNECT BY only handles key-based tree retrieval, no?
Also, basic WITH support (without RECURSIVE) would be much appreciated- that
could offer more flexibility than FROM-subqueries and could open the door
for W.R.
Also, CTE support (recursive or not) would be easily implementable by
a special case of selectable, whose visitor only outputs the name, and
prepends to the string the WITH blah AS bleh.
import sqlalchemy as sa
somestuff = sa.select(...).cte(somestuff)
somequery = sa.select( somestuff.c.somecolumn, somestuff.c.someval == 3)
I can say quite literally that you read my mind, or vice versa, I gave a quick
try with select().with_(), abandoned that and added cte() just like you said
here. By using the compiler to find the CTEs, then tacking them onto the
outermost SELECT at the end, the feature add affects virtually no existing code
at all so this can go right in.There's probably a lot of ways to trip it up
but using it carefully seems to lead to the correct results, testing two of the
examples at http://www.postgresql.org/docs/8.4/static/queries-with.html .
Both examples are tricky as the CTEs refer to themselves or each other.
See the patch at http://www.sqlalchemy.org/trac/ticket/1859.
Here's an example (note I left out the name here which you can of course put
in, but even the anon_X thing works out):
from sqlalchemy.sql import table, column, select, func
parts = table('parts',
column('part'),
column('sub_part'),
column('quantity'),
)
included_parts = select([parts.c.sub_part, parts.c.part, parts.c.quantity]).\
where(parts.c.part=='our part').cte(recursive=True)
incl_alias = included_parts.alias()
parts_alias = parts.alias()
included_parts = included_parts.union(
select([parts_alias.c.part, parts_alias.c.sub_part,
parts_alias.c.quantity]).\
where(parts_alias.c.part==incl_alias.c.sub_part)
)
s = select([included_parts.c.sub_part,
func.sum(included_parts.c.quantity).label('total_quantity')]).\
group_by(included_parts.c.sub_part)
print s
output:
WITH RECURSIVE anon_1(sub_part, part, quantity) AS (
(SELECT parts.sub_part AS sub_part, parts.part AS part, parts.quantity AS
quantity
FROM parts
WHERE parts.part = :part_1 UNION SELECT parts_1.part AS part, parts_1.sub_part
AS sub_part, parts_1.quantity AS quantity
FROM parts AS parts_1, anon_1 AS anon_2
WHERE parts_1.part = anon_2.sub_part)
)
SELECT anon_1.sub_part, sum(anon_1.quantity) AS total_quantity
FROM anon_1 GROUP BY anon_1.sub_part
I think this form makes a lot more sense in the SQLA API than the
context manager.
yeah that was just a scratch idea.
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] postgres with .. select queries
OK also, this is ready to go in from my perspective, I don't make usage of CTEs
in my normal work at the moment, so hopefully those people here interested in
the feature can give this a review, maybe even try the patch, because this will
be it !
On Mar 1, 2012, at 12:40 PM, Michael Bayer wrote:
On Mar 1, 2012, at 10:47 AM, Claudio Freire wrote:
On Thu, Mar 1, 2012 at 12:11 PM, A.M. age...@themactionfaction.com wrote:
Well, the SQL standard points at WITH RECURSIVE which is more general
anyway. W.R. is basically an inductive query loop construct (base case
UNION induction step) where CONNECT BY only handles key-based tree
retrieval, no?
Also, basic WITH support (without RECURSIVE) would be much appreciated-
that could offer more flexibility than FROM-subqueries and could open the
door for W.R.
Also, CTE support (recursive or not) would be easily implementable by
a special case of selectable, whose visitor only outputs the name, and
prepends to the string the WITH blah AS bleh.
import sqlalchemy as sa
somestuff = sa.select(...).cte(somestuff)
somequery = sa.select( somestuff.c.somecolumn, somestuff.c.someval == 3)
I can say quite literally that you read my mind, or vice versa, I gave a
quick try with select().with_(), abandoned that and added cte() just like
you said here. By using the compiler to find the CTEs, then tacking them
onto the outermost SELECT at the end, the feature add affects virtually no
existing code at all so this can go right in.There's probably a lot of
ways to trip it up but using it carefully seems to lead to the correct
results, testing two of the examples at
http://www.postgresql.org/docs/8.4/static/queries-with.html .Both
examples are tricky as the CTEs refer to themselves or each other.
See the patch at http://www.sqlalchemy.org/trac/ticket/1859.
Here's an example (note I left out the name here which you can of course
put in, but even the anon_X thing works out):
from sqlalchemy.sql import table, column, select, func
parts = table('parts',
column('part'),
column('sub_part'),
column('quantity'),
)
included_parts = select([parts.c.sub_part, parts.c.part, parts.c.quantity]).\
where(parts.c.part=='our part').cte(recursive=True)
incl_alias = included_parts.alias()
parts_alias = parts.alias()
included_parts = included_parts.union(
select([parts_alias.c.part, parts_alias.c.sub_part,
parts_alias.c.quantity]).\
where(parts_alias.c.part==incl_alias.c.sub_part)
)
s = select([included_parts.c.sub_part,
func.sum(included_parts.c.quantity).label('total_quantity')]).\
group_by(included_parts.c.sub_part)
print s
output:
WITH RECURSIVE anon_1(sub_part, part, quantity) AS (
(SELECT parts.sub_part AS sub_part, parts.part AS part, parts.quantity AS
quantity
FROM parts
WHERE parts.part = :part_1 UNION SELECT parts_1.part AS part,
parts_1.sub_part AS sub_part, parts_1.quantity AS quantity
FROM parts AS parts_1, anon_1 AS anon_2
WHERE parts_1.part = anon_2.sub_part)
)
SELECT anon_1.sub_part, sum(anon_1.quantity) AS total_quantity
FROM anon_1 GROUP BY anon_1.sub_part
I think this form makes a lot more sense in the SQLA API than the
context manager.
yeah that was just a scratch idea.
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] postgres with .. select queries
On Thu, Mar 1, 2012 at 5:08 PM, Michael Bayer mike...@zzzcomputing.com wrote: OK also, this is ready to go in from my perspective, I don't make usage of CTEs in my normal work at the moment, so hopefully those people here interested in the feature can give this a review, maybe even try the patch, because this will be it ! Why is joining not supported in that patch? I would imagine it should be possible, with explicit join conditions of course. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] postgres with .. select queries
right, I had the notion that it meant the JOIN is up at the top but that's silly, it works fine so here's the patch without anything not implemented: http://www.sqlalchemy.org/trac/attachment/ticket/1859/1859.patch someone else noted that you can use CTEs with INSERT, UPDATE, DELETE also, that will be for another day, similar syntax should work On Mar 1, 2012, at 3:12 PM, Claudio Freire wrote: On Thu, Mar 1, 2012 at 5:08 PM, Michael Bayer mike...@zzzcomputing.com wrote: OK also, this is ready to go in from my perspective, I don't make usage of CTEs in my normal work at the moment, so hopefully those people here interested in the feature can give this a review, maybe even try the patch, because this will be it ! Why is joining not supported in that patch? I would imagine it should be possible, with explicit join conditions of course. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] postgres with .. select queries
How would I do a with x as (...) select ... in sqlalchemy orm-ish? Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] postgres with .. select queries
rough road ahead on that one for now, we have a @compiles recipe so far at http://www.sqlalchemy.org/trac/attachment/ticket/1859/cte_demo.py . You'd need to open it up to make it more generic than just WITH RECURSIVE. On Feb 29, 2012, at 11:38 AM, Chris Withers wrote: How would I do a with x as (...) select ... in sqlalchemy orm-ish? Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
