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

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 sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Ben Hayden]

Awesome, Thanks!

To unsubscribe from this group, send email to sqlalchemy+unsubscribe@googlegroups.com.

[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:

To unsubscribe from this group, send email to sqlalchemy+unsubscribe@googlegroups.com.