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

[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())

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

[Ben Hayden]

Awesome, Thanks!

Post by Michael Bayer
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import ColumnElement,
_clause_element_as_expr
self.element = _clause_element_as_expr(element)
@compiles(rollup, "mysql")
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()
__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())
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 unsubscribe from this group, send email to
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.

[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]))

Post by Michael Bayer
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import ColumnElement,
_clause_element_as_expr
self.element = _clause_element_as_expr(element)
@compiles(rollup, "mysql")
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()
__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())
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 unsubscribe from this group, send email to
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.