[sqlalchemy] GROUP BY ... WITH ROLLUP

[Ben Hayden]

Is it possible to use the 'WITH ROLLUP' clause in SQLAlchemy? I'm having 
problems locating any examples on how to do so.

Here's a link to MySQL (dialect I'm using) docs on the statement - 
http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/n_VlfnIvicoJ.
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] GROUP BY ... WITH ROLLUP

[Michael Bayer]

this isn't built in right now, here's a recipe:

from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import ColumnElement, _clause_element_as_expr

class rollup(ColumnElement):
def __init__(self, element):
self.element = _clause_element_as_expr(element)

@compiles(rollup, mysql)
def _mysql_rollup(element, compiler, **kw):
return %s WITH ROLLUP % (compiler.process(element.element, **kw))


from sqlalchemy.orm import Session
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer
Base = declarative_base()
class Foo(Base):
__tablename__ = 'foo'
id = Column(Integer, primary_key=True)
data = Column(Integer)
s = Session()

from sqlalchemy.dialects import mysql
print 
s.query(Foo).group_by(rollup(Foo.data)).statement.compile(dialect=mysql.dialect())
print 
s.query(Foo).group_by(rollup(Foo.__table__.c.data)).statement.compile(dialect=mysql.dialect())




On May 1, 2012, at 11:23 AM, Ben Hayden wrote:

 Is it possible to use the 'WITH ROLLUP' clause in SQLAlchemy? I'm having 
 problems locating any examples on how to do so.
 
 Here's a link to MySQL (dialect I'm using) docs on the statement - 
 http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html
 
 -- 
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To view this discussion on the web visit 
 https://groups.google.com/d/msg/sqlalchemy/-/n_VlfnIvicoJ.
 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] GROUP BY ... WITH ROLLUP

[Ben Hayden]

Awesome, Thanks!

On Tuesday, May 1, 2012 10:41:19 AM UTC-5, Michael Bayer wrote:

 this isn't built in right now, here's a recipe:

 from sqlalchemy.ext.compiler import compiles
 from sqlalchemy.sql.expression import ColumnElement, 
 _clause_element_as_expr

 class rollup(ColumnElement):
 def __init__(self, element):
 self.element = _clause_element_as_expr(element)

 @compiles(rollup, mysql)
 def _mysql_rollup(element, compiler, **kw):
 return %s WITH ROLLUP % (compiler.process(element.element, **kw))


 from sqlalchemy.orm import Session
 from sqlalchemy.ext.declarative import declarative_base
 from sqlalchemy import Column, Integer
 Base = declarative_base()
 class Foo(Base):
 __tablename__ = 'foo'
 id = Column(Integer, primary_key=True)
 data = Column(Integer)
 s = Session()

 from sqlalchemy.dialects import mysql
 print 
 s.query(Foo).group_by(rollup(Foo.data)).statement.compile(dialect=mysql.dialect())
 print 
 s.query(Foo).group_by(rollup(Foo.__table__.c.data)).statement.compile(dialect=mysql.dialect())




 On May 1, 2012, at 11:23 AM, Ben Hayden wrote:

 Is it possible to use the 'WITH ROLLUP' clause in SQLAlchemy? I'm having 
 problems locating any examples on how to do so.

 Here's a link to MySQL (dialect I'm using) docs on the statement - 
 http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html

 -- 
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To view this discussion on the web visit 
 https://groups.google.com/d/msg/sqlalchemy/-/n_VlfnIvicoJ.
 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 view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/LrmBcIBxnNwJ.
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] GROUP BY ... WITH ROLLUP

[Ben Hayden]

Here's a modification we made to ROLLUP so it would work with multiple 
columns:

class rollup(ColumnElement):
def __init__(self, *elements):
self.elements = [_clause_element_as_expr(e) for e in elements]

@compiles(rollup, mysql)
def _mysql_rollup(element, compiler, **kw):
return %s WITH ROLLUP % (', '.join([compiler.process(e, **kw) for e 
in element.elements]))


On Tuesday, May 1, 2012 10:41:19 AM UTC-5, Michael Bayer wrote:

 this isn't built in right now, here's a recipe:

 from sqlalchemy.ext.compiler import compiles
 from sqlalchemy.sql.expression import ColumnElement, 
 _clause_element_as_expr

 class rollup(ColumnElement):
 def __init__(self, element):
 self.element = _clause_element_as_expr(element)

 @compiles(rollup, mysql)
 def _mysql_rollup(element, compiler, **kw):
 return %s WITH ROLLUP % (compiler.process(element.element, **kw))


 from sqlalchemy.orm import Session
 from sqlalchemy.ext.declarative import declarative_base
 from sqlalchemy import Column, Integer
 Base = declarative_base()
 class Foo(Base):
 __tablename__ = 'foo'
 id = Column(Integer, primary_key=True)
 data = Column(Integer)
 s = Session()

 from sqlalchemy.dialects import mysql
 print 
 s.query(Foo).group_by(rollup(Foo.data)).statement.compile(dialect=mysql.dialect())
 print 
 s.query(Foo).group_by(rollup(Foo.__table__.c.data)).statement.compile(dialect=mysql.dialect())




 On May 1, 2012, at 11:23 AM, Ben Hayden wrote:

 Is it possible to use the 'WITH ROLLUP' clause in SQLAlchemy? I'm having 
 problems locating any examples on how to do so.

 Here's a link to MySQL (dialect I'm using) docs on the statement - 
 http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html

 -- 
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To view this discussion on the web visit 
 https://groups.google.com/d/msg/sqlalchemy/-/n_VlfnIvicoJ.
 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 view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/PAE_77ZOQAMJ.
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.